watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

Now let's take the implicit parameter _optim_peek_user_binds Set to false Turn off bound variable snooping :

SQL> alter session set "_optim_peek_user_binds" = false;  Session changed .

And then keep x unchanged ,y It is amended as follows 1000, Execute the goal again SQL: See the target SQL Corresponding column version_count and executions The value changes from the previous 1 Change into 2, explain Oracle In the execution of this SQL It's hard parsing :


SQL> exec :y := 1000;

 

PL/SQL  Process completed successfully .

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

SQL> set linesi 200

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               2          2



That means SQL Where parent cursor There are two hanging on the bottom child cursor:

SQL> select plan_hash_value,child_number from v$sql where sql_id='9dhu3xk2zu531'; PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------     1410530761            0     2351893609            1

and child_number by 1 Of child cursor The corresponding execution plan is as follows :


SQL> select * from table(dbms_xplan.display_cursor('9dhu3xk2zu531',1,
'advanced'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 1

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 2351893609

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   230 |  1150 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:Y>=:X)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

It can be seen from the above display that , The first step is index fast full scan , At this time, we are going to scan the index range , There is... In the execution plan   OPT_PARAM('_optim_peek_user_binds' 'false'), It doesn't appear when binding side snooping is enabled before “peeking binds” The content of , This means that the function has been disabled .

        The previous introduction makes Oracle Execution target SQL Again, using hard parsing is using DDL operation , But because of its wide influence , It may cause some performance problems , Now let's talk about one way, just the goal SQL Corresponding shared cursor, That is to say, we can only let Oracle In carrying out this goal SQL Use hard parsing , Carry out other SQL It's all the same , remain unchanged .

        This method uses DBMS_SHARED_POOL.PURGE.DBMS_SHARED_POOL.PURGE It's from 10.2.0.4 Introduced , Used to delete the specified cache in the library cache shared cursor,DBMS_SHARED_POOL.PURGE It can make Oracle In carrying out the goal SQL Use hard parsing , It's if one of them SQL Corresponding shared cursor Be deleted , Then execute the program again SQL Naturally, hard parsing is used .

The following query targets SQL Corresponding address And columns hash_value:


SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          ADDRESS          HASH_VALUE

----------------------------------------------------------------- --------------- ---------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6   00007FFD0FADBBB0 2571518534

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn   00007FFCD84D4A60  924127028

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531   00007FFD0F8F7170 2247955553


Then it is spliced into a string, and input parameters are passed into DBMS_SHARED_POOL.PURGE And implement , The second parameter is a constant c, To delete is to delete shared cursor;

SQL> exec sys.dbms_shared_pool.purge('00007FFD0F8F7170,2247955553','c'); PL/SQL  Process completed successfully .

And then look at the discovery target SQL Corresponding shared cursor It was really deleted :


SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1



Extra attention :

If you want to in 10.2.0.4 Used in version dbms_shared_pool.purge, Then use the previous manual settings event 5614566(alter session set events ‘5614566 trace name context forever’), otherwise dbms_shared_pool.purge Will not work , There is no such limitation in the above version .

Now it's testing, and the changes are as follows :


SQL> exec :y := 60000;

 

PL/SQL  Process completed successfully .

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     58577

 

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               2          1



executions by 1 Hard parsing to prove that it does execute , however version_count yes 2? Should be 1 That's right , For the following query child cursor There's really only one , So it should be for 1, May be Oracle bug Lead to .


SQL> select plan_hash_value,child_number from v$sql where
sql_id='9dhu3xk2zu531';

 

PLAN_HASH_VALUE CHILD_NUMBER

--------------- ------------

     2351893609            0

Let's look at the implementation plan :


SQL> select * from table(dbms_xplan.display_cursor('9dhu3xk2zu531',0,
'advanced'));

 

PLAN_TABLE_OUTPUT
----------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 2351893609

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   230 |  1150 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / [email protected]$1

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:Y>=:X)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

Have chosen 47 That's ok .

Choose yes or no idx_t1 Index range scan for , It means to turn off the binding variables , No matter what value is passed in, it doesn't affect Oracle For the goal SQL Change of execution plan , And its result set can be seen from the above execution plan, no matter what the input value is ,cardinality All for 230, The following explains how to calculate

The calculation formula is as follows :

Cardinality = NUM_ROWS * SelectivitySelectivity = 0.05 * 0.05

notes :

1)、 The above calculation company is applicable to disable binding variable snooping and where The condition is the target column between x and y Of selectivity and cardinality Calculation

2)、num_rows Represents the number of records in the table where the target column is located .

3)、where Condition is “ Target column between x and y”, amount to “ Target column >= x and Target column <= y”. about “ Target column >= x” and “ Target column <= y” for ,Oracle Will use 5% The choice rate of , therefore “ Target column >= x and Target column <= y” The total selectivity is 0.05*0.05

Put the previous query num_rows and 0.05*0.05 Bring in the formula , View results :

SQL> select round(91944*0.05*0.05) from dual; ROUND(91944*0.05*0.05)----------------------                   230