Articles from Kerry Osborne

Index

Creating Test Scripts With Bind Variables

GATHER_PLAN_STATISTICS

+++++Oracle Support Sanctions Manually Created SQL Profiles!

1. move sql profile from test to production 2) fix a good exec plan by sql_id and plan_hash_value

How to Attach a SQL Profile to a Different Statement – Take 2

1.move an execution plan to a different sql

 

Creating Test Scripts With Bind Variables

July 23, 2009, 9:10 am

Here’s a nifty little script that one of my co-workers (Jack Augustin) wrote to speed up the process of building test scripts. I thought it was a great idea, but of course I can never leave well enough alone, so I had to modify it slightly to my own taste (which by the way, I hope you will do as well). Anyway, the reason for the script is that we often get requests to improve a SQL statement that uses bind variables (sometimes lots of them). It’s pretty common to build a test script that can be run in SQL*Plus by grabbing the sql text and the values of the bind variables from the shared pool. Building the script though can be very tedious if the statements are long and particularly if there are a lot of bind variables. So this script eliminates most of that tedious work by generating a first cut at a test script based on a sql_id. The test script defines the variables, sets the values of the variables, and then executes the sql statement. A little manual editing may still be necessary, but it saves a lot of time.

Jack’s original version used V$SQL_BIND_CAPTURE to get bind variable values. I changed it to use the OTHER_XML column of V$SQL_PLAN. There are a few differences in these approaches.

  • v$sql_plan has the values used when the statement was parsed.
  • v$sql_plan usually has more bind variables in cases where there are a large number of bind variables. This is due to the fact that v$sql_bind_capture is controlled by _bind_capture_area_size which defaults to a value that is quite a bit smaller than the area used by v$sql_plan (thus a smaller number of bind variables).
  • v$sql_bind_capture has the data type, v$sql_plan does not. (it’s got a numeric code for the data type – 1 for varchar2, 2 for number, etc…)

I called the script build_bind_vars.sql.

Well I was feeling all good about my version since it worked better on statements with lots of bind variables. But last week I was working on a system where they had turned off bind variable peeking via the _OPTIM_PEEK_USER_BINDS parameter. Thus, no bind info in the OTHER_XML field. Rats! Back to V$SQL_BIND_CAPTURE. So I made a copy of the script but changed it to get the values from V$SQL_BIND_CAPTURE.

Naturally I called this one build_bind_vars2.sql.

Note: these scripts depend on a little function called display_raw which I got from Greg Rahn. Here’s a script to create the function: create_display_raw.sql

Now here’s an example:

> !sql
sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 23 08:51:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @find_sql
Enter value for sql_text: select%:4%:5%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
3hcvdhu22mjqu      0 1890474001          1           .01           76 select box_id from rule_set_rop$ where rs_obj# = :1 and ec_o
                                                                      bj# = :2  and rule_id = :3 and rule_or_piece = :4 and rop_id
                                                                       = :5 order by eval_id

57antna43jh0a      0 3630768429          1           .04          252 select box_id, value from rule_set_iot$ where rs_obj# = :1 a
                                                                      nd ec_obj# = :2  and rule_id = :3 and rule_or_piece = :4 and
                                                                       rop_id <> :5 order by rop_id
...

SQL> @build_bind_vars
Enter SQL ID ==> 3hcvdhu22mjqu
Enter Child Number ==> 0

variable N1 NUMBER
variable N2 NUMBER
variable N3 NUMBER
variable N4 NUMBER
variable N5 NUMBER

begin

end;

/

select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ where rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and rule_or_piece = :N4
and rop_id = :N5 order by eval_id;

SQL> -- Oops, nothing in the section setting the variable values!
SQL> -- Let's see what's actually in the OTHER_XML field
SQL> SQL> select other_xml from v$sql_plan
  2  where sql_id = '3hcvdhu22mjqu'
  3  and other_xml is not null
  4  /

OTHER_XML
--------------------------------------------------------------------------------
<other_xml><info type="db_version">10.2.0.3</info><info type="parse_schema"><![C
DATA["SYS"]]></info><info type="plan_hash">4025366187</info><outline_data><hint>
<![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_
ENABLE('10.2.0.3')]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint
><![CDATA[INDEX(@"SEL$1" "RULE_SET_ROP$"@"SEL$1" ("RULE_SET_ROP$"."RS_OBJ#" "RUL
E_SET_ROP$"."EC_OBJ#" "RULE_SET_ROP$"."RULE_ID" "RULE_SET_ROP$"."RULE_OR_PIECE"
"RULE_SET_ROP$"."ROP_ID" "RULE_SET_ROP$"."EVAL_ID"))]]></hint></outline_data></o
ther_xml>

1 row selected.

SQL> -- Ah, no bind data in the OTHER_XML column, we'll have to get values from v$sql_bind_capture
SQL> @build_bind_vars2
Enter SQL ID ==> 3hcvdhu22mjqu
Enter Child Number ==>

variable N1 NUMBER
variable N2 NUMBER
variable N3 NUMBER
variable N4 NUMBER
variable N5 NUMBER

begin

:N1 := 8809;
:N2 := 8794;
:N3 := 0;
:N4 := 0;
:N5 := 0;

end;

/

select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ where rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and rule_or_piece = :N4
and rop_id = :N5 order by eval_id;

SQL> -- OK, let's try executing the script
SQL> @3hcvdhu22mjqu

PL/SQL procedure successfully completed.

no rows selected

SQL> -- Well it looks like it worked, let's see if we can find the test statement
SQL> @find_sql
Enter value for sql_text: %test 3hcvdhu22mjqu%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7ndfkkg214gd1      0 4025366187          1           .01           85 select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ wh
                                                                      ere rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and r
                                                                      ule_or_piece = :N4 and rop_id = :N5 order by eval_id

1 row selected.

I have been using this script a lot recently. Seems like I could keep adding things to it, but it does the job for the most part. (I still need to edit the output file occasionally to clean up split words, etc…) Also, the data type mapping is not complete in build_bind_vars2.sql, it just has types 1 and 2. Most likely I won’t get around to adding other types until I actually need them. So anyway, there it is. Let me know if you have any ideas for improving it.

GATHER_PLAN_STATISTICS

February 9, 2010, 12:02 pm

Hey, I just thought of something. One of the things I do fairly regularly when struggling with a SQL statement that is not behaving goes like this:

  1. create a copy of the statement in a test script
  2. add the GATHER_PLAN_STATISTICS hint
  3. duplicate all the bind variables (with values used at parse if possible)
  4. run it
  5. run xplan on it

This provides the ability to see how the optimizer’s estimates line up with reality using the DBMS_XPLAN.DISPLAY_CURSOR function with the ALLSTATS format option. It’s very easy to see where the optimizer is having problems by comparing the A-Rows column (actual rows) with the E-Rows column (estimated rows). Anywhere there is a big difference (think orders of magnitude), there is a potential problem. Well actually it’s a little more complicated than that, because the A-Rows are cumulative while the E-Rows are not. You have to multiply the E-Row by Starts (or divide A-Rows by the number of executions) in order to compare apples to apples. If you need a little more info see this post by Jonathan Lewis or this one by Greg Rahn. Here’s how the output looks in case you haven’t seen it before:

SYS@LAB1024> @fs
Enter value for sql_text: %gather_plan%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
dvfumy503zfnw      0 1660344652          3           .00           25 select /*+ gather_plan_statistics */ t1.x, t2.y from kso.t1
                                                                      t1, kso.t2 t2 where t1.y = t2.x and rownum < 10

SYS@LAB1024> !cat dplan_allstats.sql
set lines 180
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats  +peeked_binds'))
/

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: dvfumy503zfnw
Enter value for child_no:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dvfumy503zfnw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t1.x, t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x and
rownum < 10

Plan hash value: 1660344652

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |              |      3 |        |     27 |00:00:00.01 |      75 |
|   2 |   NESTED LOOPS                |              |      3 |      9 |     27 |00:00:00.01 |      75 |
|   3 |    TABLE ACCESS FULL          | T1           |      3 |  53629 |     27 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2           |     27 |      1 |     27 |00:00:00.01 |      60 |
|*  5 |     INDEX UNIQUE SCAN         | SYS_C0014104 |     27 |      1 |     27 |00:00:00.01 |      33 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   5 - access("T1"."Y"="T2"."X")

23 rows selected.

Just a quick note on the correlation between STARTS, E-ROWS, and A-ROWS. I picked a bit of a bad example due to the “rownum < 10″ predicate. It causes processing to stop on the full table scan after 9 rows are retrieved. But it does show a mismatch in the number of estimated rows and the number of actual rows. Keep in mind that the actual rows are cumulative, so it’s the total rows this step has handled regardless of the number of executions. This statement has been executed 3 times (see the 3 under Starts on line 1). And the full table scan was executed (Started) 3 times (once per execution). So the optimizer was expecting 53629 rows for each execution, but got on average 9 rows per execution. Note also that the Starts column is the actual number of times the operation was executed, not the number of times that the optimizer thinks it will be executed. Here’s another little bit of output to show that Starts is actual, not estimated:

 
SYS@LAB1024> -- here's a query without the "rownum<10" predicate
SYS@LAB1024> @fs
Enter value for sql_text: %t1_nl_join_agg.sql%
Enter value for sql_id:

SQL_ID         CHILD    PLAN_HASH        EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ------------ ------------ ------------- ------------ ------------------------------------------------------------
a36w6pfkgp2sy      0    174109304            1          3.77       53,745 select /*+ gather_plan_statistics */ avg(x*2) from ( select
                                                                          /* t1_nl_join_agg.sql */ t1.x, t2.y from kso.t1 t1, kso.t2
                                                                          t2 where t1.y = t2.x )

1 row selected.

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: a36w6pfkgp2sy
Enter value for child_no:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a36w6pfkgp2sy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(x*2) from ( select /* t1_nl_join_agg.sql */ t1.x,
t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x )

Plan hash value: 174109304

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:03.77 |   53745 |    169 |
|   2 |   NESTED LOOPS      |              |      1 |  53629 |  25994 |00:00:02.94 |   53745 |    169 |
|   3 |    TABLE ACCESS FULL| T1           |      1 |  53629 |  53629 |00:00:00.80 |     114 |    105 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014104 |  53629 |      1 |  25994 |00:00:02.41 |   53631 |     64 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."Y"="T2"."X")

21 rows selected.

SYS@LAB1024> -- the stats are 100% correct on the full table scan (line 3),
SYS@LAB1024> -- but off by 2X on the NL join (25994 vs. 53629 on line 2)
SYS@LAB1024> -- so let's mess up the stats on T1 to see if Starts is optimizer guess or actual
SYS@LAB1024> -- set rows in T1 to 1234
SYS@LAB1024>
SYS@LAB1024> exec dbms_stats.set_table_stats(ownname => 'KSO', tabname => 'T1', numrows => 1234);

PL/SQL procedure successfully completed.

SYS@LAB1024> @flush_sql
Enter value for sql_id: a36w6pfkgp2sy

PL/SQL procedure successfully completed.

SYS@LAB1024> @t1_nl_join_agg.sql

    AVG(X*2)
------------
108794.25383

1 row selected.

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: a36w6pfkgp2sy
Enter value for child_no:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a36w6pfkgp2sy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(x*2) from ( select /* t1_nl_join_agg.sql */
t1.x, t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x )

Plan hash value: 174109304

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:03.63 |   53745 |
|   2 |   NESTED LOOPS      |              |      1 |   1234 |  25994 |00:00:03.20 |   53745 |
|   3 |    TABLE ACCESS FULL| T1           |      1 |   1234 |  53629 |00:00:00.86 |     114 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014104 |  53629 |      1 |  25994 |00:00:02.41 |   53631 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."Y"="T2"."X")

21 rows selected.

SYS@LAB1024> -- note that Starts on the Index Lookup inside the NL Join is 53629 (line 4)
SYS@LAB1024> -- this is the number of times that operation was actually executed,
SYS@LAB1024> -- not 1234, which is the number of times the optimizer thought is would be executed
SYS@LAB1024> -- so Starts and A-rows are actual values, E-Rows is the optimizer estimate
SYS@LAB1024> -- (at least for NL Join)

Back to the topic at hand, my normal steps (i.e. generate test script, add GATHER_PLAN_STATISTICS hint, etc…)

I have a script that helps generate the test script (see this post: Creating Test Scripts with Bind Variables). But there are definite drawbacks to this approach. It can be difficult to duplicate the exact environment that the optimizer sees when running the production code, even if you are testing on the production instance. The bottom line is that you have to be pretty careful to make sure that your test script is really doing the same thing that the production code is doing.

Which brings me to my idea (finally)! Here’s a quick way to get extended plan statistics for any statement without modifying the code or creating a test version. Drum roll please ….

Just create a one line SQL Profile (using create_1_hint_sql_profile.sql) with the GATHER_PLAN_STATISTICS hint. You can use dplan_allstats.sql to see the extended statistics. Here’s an example:

> !sql
sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 28 17:49:56 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LAB112> -- note: SQL_ID 84q0zxfzn5u6s is the statement I want to look at
SYS@LAB112>
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): PROFILE_84q0zxfzn5u6s_GPS
Enter value for category (DEFAULT):
Enter value for force_matching (false):
Enter value for hint: GATHER_PLAN_STATISTICS
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.

PL/SQL procedure successfully completed.

SYS@LAB112> @sql_profiles
Enter value for sql_text:
Enter value for name:

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROFILE_84q0zxfzn5u6s_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO

7 rows selected.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_GPS

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
GATHER_PLAN_STATISTICS

1 rows selected.

SYS@LAB112> @avgskewi

AVG(PK_COL)
-----------
   15636133

1 row selected.

SYS@LAB112> @dplan_allstats
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133

Plan hash value: 3723858078

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.01 |      35 |
|   1 |  SORT AGGREGATE              |           |      1 |      1 |      1 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |      1 |     35 |     32 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |      1 |     35 |     32 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COL1"=136133)

Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_GPS used for this statement
   - SQL plan baseline SQL_84Q0ZXFZN5U6S_3723858078 used for this statement

25 rows selected.

SYS@LAB112> @sql_hints
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0

OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))

6 rows selected.

Works great! Now you don’t have to do all that work just to see the rowsource execution stats.

Note that the Outline Hints stored in the OTHER_XML column of V$sql_plan have no record of the GATHER_PLAN_STATISTICS hint, even though it was clearly executed (otherwise we’d have gotten a warning message in the XPLAN output and no A-Rows column).

Note also that this example was done on an 11gR2 database and that the statement was found in the SQL Management Base (i.e. there was a Baseline in place for this statement already). The Notes section of the plan output shows that both the Profile and the Baseline were used. This got me wondering if it really merged the hints from the Profile and the Baseline. The short answer is YES, it appears that it does. I’ve got an example, but since that’s a different topic and this post is already pretty long, I’ll just save it for another post.

As always, your comments are welcomed.

UPDATE: This has proved pretty useful, so I wrote a little script that just prompts for a sql_id and creates a SQL Profile with the GATHER_PLAN_STATISTICS hint. I have just uploaded it to the site here: gps.sql

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: