Thursday, October 29, 2015


SQL Profile Creation with hints and outlines



SQL Profile can be manually created using below procedure from SQLT


Using SQLT oracle support provided tool

 

  1. Please navigate to below directory location under SQLT folder

               cd sqlt/utl

  1. Run the script “coe_xfr_sql_profile.sql “  by passing SQL_ID and Desired Plan Hash Value as Parameters

             @coe_xfr_sql_profile.sql <sql_id> <plan_hash_value>
         Ex: @coe_xfr_sql_profile.sql gpm6wzuy8yabq 1950795681
 
 The above script generates a script with name like below. Please run it which will create the SQL Profile 
 
            @coe_xfr_sql_profile_sql_id_plan_hash_value.sql
              Ex: @coe_xfr_sql_profile_gpm6wzuy8yabq_1950795681.sql
 
 
Without SQLT


 You can create the SQL Profile without SQLT by manually pulling the outline data hints from the execution plan and manually passing them to the SQL.
 
  • Getting outline data from explain plan in Memory
set linesize 1000
set pagesize 900
set long 2000000000
select * from table (dbms_xplan.display_cursor('1afgp23tqc7f3',0, 'ALL +outline')); 
 
The above output should give you all the explain plans for the sql along with outline data below it. Please select the outline data from the desired plan.
 
 
  • Getting Outline Data from AWR
            set linesize 1000
            set pagesize 900
            set long 2000000000
            select * from table(dbms_xplan.display_awr('6y6kfwf7c23gx8', null,         
            null,'outline peeked_binds'));
 
 
The above output should give you all the explain plans for the sql along with outline data below it. Please select the outline data from the desired plan.
      Once you get the outline data manually add the outline hints in the below procedure (at       
             the “outline hints” below)
 
DECLARE
clsql_text CLOB;
BEGIN
select sql_text into clsql_text from dba_hist_sqltext where sql_id = 'xxxxxx';  
    
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => clsql_text,
      profile => sqlprof_attr(q'!outline hints!',
      q'!outline hints!'
      q'!outline hints!'),
name => 'desired name',
      force_match => TRUE
);
END;
/
 
 
Example:
 

DECLARE

   clsql_text   CLOB;

BEGIN

   SELECT sql_text

     INTO clsql_text

     FROM dba_hist_sqltext

    WHERE sql_id = '6y6kfwf7c23gx8';

 

   DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

      sql_text      => clsql_text,

      profile       => sqlprof_attr (

                         q'!BEGIN_OUTLINE_DATA!',

                         q'!IGNORE_OPTIM_EMBEDDED_HINTS!',

                         q'!OPTIMIZER_FEATURES_ENABLE('12.1.0.2')!',

                         q'!DB_VERSION('12.1.0.2')!',

                         q'!OPT_PARAM('optimizer_dynamic_sampling' 11)!',

                         q'!FIRST_ROWS!',

                         q'!OUTLINE_LEAF(@"SEL$1")!',                                                 

                         q'!USE_NL(@"SEL$1" "B"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "F"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "C"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "E"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "D"@"SEL$1")!',

                         q'!NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")!',

                         q'!END_OUTLINE_DATA!'),

      name          => 'SQLPROFILE_77tusxuddphkn',

      force_match   => TRUE);

END;

/
 
Dropping the SQL Profile 
 
begin dbms_sqltune.drop_sql_profile('SQLPROFILE_77tusxuddphkn'); end;
 
 


Check if SQL is using profile
 
select slq_id, sql_profile from v$sql;


Note: sql_profile should not be null if the sql is using the profile.

 

 
Posted by at No comments:

Thursday, March 29, 2012

Adaptive Cursor Sharing


The idea behind ACS is to improve the execution plans for statements with bind variables. CBO has been enhanced to allow multiple execution plans to be used for a single statement with bind variables, without hard parsing the SQL every time. The magic is done in the soft parse phase of the statement with the help of three new views. This feature is very useful on columns with skewed data, where there are histograms created onto these columns to help optimizer determine the cost of the execution plan. Unlike previous Oracle versions where CBO was peeking values for bind variables, it was not guaranteed that generated plan was “good” enough for all possible values of the bind variables.  In 10g, first hard parsed plan with bind variable peeked was used for all other values of bind variable. Obviously this approach was not good.

What is bind variable peeking?
Bind variable peeking is when Oracle’s CBO peeks (waits until he gets the ) value of the bind variable and then optimizes the SQL. But, this is very important: this is done in the hard parsing phase of the SQL. The idea with bind variables is to hard parse it once, and then to reuse N times same SQL statement (soft parsing).

Example:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
-- Simulate server reboot
LM@ORCL112> alter system flush shared_pool;

System altered

LM@ORCL112> alter system flush buffer_cache;

System altered

LM@ORCL112> --CREATE TABLE with skewed data
LM@ORCL112> DROP TABLE test1 purge;

Table dropped

LM@ORCL112> CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users;

Table created

LM@ORCL112> DECLARE
  2  i NUMBER;
  3  nbrows NUMBER;
  4  BEGIN
  5    i:=1;
  6    nbrows:=50000;
  7    LOOP
  8      EXIT WHEN i>nbrows;
  9      IF (i=1) THEN
 10        INSERT INTO test1 VALUES(1,RPAD('A',49,'A'));
 11      ELSE
 12        INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
 13      END IF;
 14      i:=i+1;
 15    END LOOP;
 16    COMMIT;
 17  END;
 18  /

PL/SQL procedure successfully completed

LM@ORCL112> --CREATE INDEX
LM@ORCL112> CREATE  INDEX test1_idx_id ON test1(id) TABLESPACE users;

Index created

Note that I’ve used method_opt to collect histograms for the table. Histograms are important to determine data distribution in column. They are important to CBO to determine selectivity on bind variable.

LM@ORCL112> -- GATHER STATS
LM@ORCL112> EXEC DBMS_STATS.GATHER_TABLE_STATS( user,'test1', estimate_percent=>100, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');

PL/SQL procedure successfully completed

LM@ORCL112> -- present data
LM@ORCL112> select count(*),id from test1 group by id;

  COUNT(*)         ID
---------- ----------
         1          1
     49999      50000

LM@ORCL112> --HISTORGRAMS
LM@ORCL112> COLUMN column_name FORMAT A20;
LM@ORCL112> COLUMN last_active_time FORMAT DATE;
LM@ORCL112> SELECT column_name, histogram FROM user_tab_cols WHERE  table_name = 'TEST1';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
ID                   FREQUENCY   ß we have histogram on ID colum as data is skewed
DESCR                NONE

LM@ORCL112> SELECT column_name, num_distinct, num_buckets, histogram
  2  FROM user_tab_col_statistics WHERE table_name = 'TEST1';

COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
ID                              2           2 FREQUENCY

LM@ORCL112> SELECT table_name, column_name, endpoint_number, endpoint_value
  2  FROM user_histograms s WHERE table_name = 'TEST1';

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TEST1                          ID                                 1              1
TEST1                          ID                             50000          50000

LM@ORCL112> COLUMN SQL_TEXT FORMAT A20;
LM@ORCL112> COLUMN PLAN_NAME FORMAT A40;
LM@ORCL112> COLUMN last_executed FORMAT A20;
LM@ORCL112> SET LINE 400;
Cannot SET LINE

LM@ORCL112> -- EXECUTE STATEMENTS FOR THE FIRST TIME USING BIND VARIABLE
lm@ORCL112>variable id NUMBER;
lm@ORCL112>EXEC :id:=50000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
     49999

lm@ORCL112>exec DBMS_LOCK.SLEEP(15);

PL/SQL procedure successfully completed.

We have two new columns in V$SQL view (IS_BIND_SENSITIVE and IS_BIND_AWARE) and three new views (V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS).

IS_BIND_SENSITIVE - If this column contains a value of (Y)es, it means that column on bind variable has histogram and that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity.
IS_BIND_AWARE – Provide CBO with ability to determine the selectivity of any where clause condition that uses bind variables.  If set to YES CBO realized that current plan might not be the optimal for all provided bind variable.
V$SQL_CS_HISTOGRAM – Distributes the frequency (three-bucket histogram) at which Oracle used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.
V$SQL_CS_SELECTIVITY – Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges.
V$SQL_CS_STATISTICS – Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The column PEEKED = Y, then the bind set has been used to build the adaptive cursor.

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id
In our case cursor is bind sensitive but is not bind aware.


lm@ORCL112>EXEC :id:=1;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         1

lm@ORCL112>exec DBMS_LOCK.SLEEP(15);

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id


CBO executed second time with soft parse using cursor that is in memory. CBO obviously made mistake as selecting 49999 record and 1 record using the same execution plan is not good. But; we will see that CBO will adapt in the future run.
lm@ORCL112>EXEC :id:=25000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         0

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id
For the given bind value (ID=2500) oracle returned zero records but also produced new child cursor. Oracle mainly uses CPU and buffer gets to determine whether given bind variable is good candidate for new cursor. We also see that IS_BIND_AWARE for the second cursor is set Y(es).
Below are presented both plans in cursor cache for the given SQL. One is using fast full index scan and the other index range scan.

lm@ORCL112>select * from table(dbms_xplan.display_cursor(sql_id =>'9b47dvm2wscnn',cursor_child_no => 1));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b47dvm2wscnn, child number 1
-------------------------------------
SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id

Plan hash value: 1440861145

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_ID |     1 |     3 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("ID"=:ID)


19 rows selected.

lm@ORCL112>select * from table(dbms_xplan.display_cursor(sql_id =>'9b47dvm2wscnn',cursor_child_no => 0));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b47dvm2wscnn, child number 0
-------------------------------------
SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id

Plan hash value: 150050721

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |       |    28 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TEST1_IDX_ID | 49999 |   146K|    28   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter("ID"=:ID)


19 rows selected.

lm@ORCL112> -- EXECUTE STATEMENTS FOR THE SECOND TIME --
lm@ORCL112>EXEC :id:=50000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
     49999

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id


CBO create new child cursor with the same excution plan (plan_hash_value) as child no 0. This time IS_BIND_AWARE for child no 2 is set to Y. So CBO learned its lesson.

lm@ORCL112>EXEC :id:=1;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         1

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y N SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          3 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id


This time CBO created new cursor child no 3 that is identical as cursor child 1. This time CBO used right plan index range scan for bind variable value id=1. So CBO adapts with all the info that collected during run.  Cursor child 0 and 1 have IS_SHAREABLE column set to N and will be first to age out.

lm@ORCL112>EXEC :id:=25000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         0

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y N SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          3 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id


From now on CBO uses soft parse and uses proper child cursors 2 and 3 for given bind variable value. Everything looks the same except EXECUTIONS column.

lm@ORCL112>-- EXECUTE STATEMENTS FOR THE THIRD TIME
lm@ORCL112>EXEC :id:=50000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
     49999

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y N SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          3 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id


lm@ORCL112>EXEC :id:=1;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         1

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y N SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          3 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          3 29-MAR-12
                                          ERE id=:id


lm@ORCL112>EXEC :id:=25000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

  COUNT(*)
----------
         0

lm@ORCL112>SELECT sql_id,plan_hash_value hash_value,child_number child_no,is_bind_sensitive bind_sens,is_bind_aware bind_aw,s.IS_SHAREABLE sherable,sql_text,s.EXECUTIONS, s.LAST_ACTIVE_TIME
  2  FROM v$sql s WHERE sql_text LIKE 'SELECT /* BNDW%';

SQL_ID        HASH_VALUE   CHILD_NO B B S SQL_TEXT                                 EXECUTIONS LAST_ACTI
------------- ---------- ---------- - - - ---------------------------------------- ---------- ---------
9b47dvm2wscnn  150050721          0 Y N N SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          1 Y Y N SELECT /* BNDW */ count(*) FROM test1 WH          1 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn  150050721          2 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          2 29-MAR-12
                                          ERE id=:id

9b47dvm2wscnn 1440861145          3 Y Y Y SELECT /* BNDW */ count(*) FROM test1 WH          4 29-MAR-12
                                          ERE id=:id


lm@ORCL112>SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='9b47dvm2wscnn';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
1F21A9B4 3318493844 9b47dvm2wscnn            3          0          4
1F21A9B4 3318493844 9b47dvm2wscnn            3          1          0
1F21A9B4 3318493844 9b47dvm2wscnn            3          2          0
1F21A9B4 3318493844 9b47dvm2wscnn            2          0          0
1F21A9B4 3318493844 9b47dvm2wscnn            2          1          2
1F21A9B4 3318493844 9b47dvm2wscnn            2          2          0
1F21A9B4 3318493844 9b47dvm2wscnn            1          0          1
1F21A9B4 3318493844 9b47dvm2wscnn            1          1          0
1F21A9B4 3318493844 9b47dvm2wscnn            1          2          0
1F21A9B4 3318493844 9b47dvm2wscnn            0          0          1
1F21A9B4 3318493844 9b47dvm2wscnn            0          1          1
1F21A9B4 3318493844 9b47dvm2wscnn            0          2          0

12 rows selected.

lm@ORCL112>SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='9b47dvm2wscnn';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
1F21A9B4 3318493844 9b47dvm2wscnn            3 =ID                                               0 0.000009   0.000022
1F21A9B4 3318493844 9b47dvm2wscnn            2 =ID                                               0 0.899973   1.099967
1F21A9B4 3318493844 9b47dvm2wscnn            1 =ID                                               0 0.000009   0.000011

lm@ORCL112>SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='9b47dvm2wscnn';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
1F21A9B4 3318493844 9b47dvm2wscnn            3          2342552567 Y          1              2        2     0
1F21A9B4 3318493844 9b47dvm2wscnn            2          1293584543 Y          1          50000         106          0
1F21A9B4 3318493844 9b47dvm2wscnn            1          3704044026 Y          1              1       22     0
1F21A9B4 3318493844 9b47dvm2wscnn            0          1293584543 Y          1          50000         153          0

lm@ORCL112>--THESE ARE PLANS IN CURSOR CACHE
lm@ORCL112>select * from table(dbms_xplan.display_cursor(sql_id =>'9b47dvm2wscnn',cursor_child_no => 3));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  9b47dvm2wscnn, child number 3
-------------------------------------
SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id

Plan hash value: 1440861145

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_ID |     1 |     3 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("ID"=:ID)


19 rows selected.

lm@ORCL112>select * from table(dbms_xplan.display_cursor(sql_id =>'9b47dvm2wscnn',cursor_child_no => 2));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  9b47dvm2wscnn, child number 2
-------------------------------------
SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id

Plan hash value: 150050721

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |       |    28 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TEST1_IDX_ID | 49999 |   146K|    28   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter("ID"=:ID)


19 rows selected.

lm@ORCL112>






When we flush shared pool we lose valuable information that could affect performances. Instead flushing whole shared pool one  should use oracle package to delete particular cursor and all child cursors.

dbms_shared_pool.purge('<address,hash_value>','C');


Sometime we can't afford optimizer to make mistakes and learn from them. In this situation after scheduled server reboot we can pin particular cursor in cursor cache from data dictionary.

SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE …
dbms_shared_pool.keep('<address,hash_value>', 'C');
Posted by at No comments:
Subscribe to: Posts (Atom)