Three ways Oracle deletes a SQL execution plan cached in the shared pool

After executing an SQL statement for the first time in Oracle, the SQL statement is parsed hard, and the execution plan and parse tree are cached in the shared pool. It is convenient to perform this SQL statement again without having to do hard parsing to facilitate application system extension. However, if the SQL corresponds to a change in the table data volume or other reasons, the Shared pool cache execution Plan and parse tree is no longer applicable to the current situation, the SQL execution efficiency drops rapidly, in this case the SQL cache in the shared pool execution plan and parse tree to clean out To re-parse the SQL to generate a new execution plan and parse tree.

There are three ways to delete a SQL cache execution plan from a shared pool:

• alter system flush Shared_pool;

• DDL Operations on tables

• Dbms_shared_pool.purge bag (10.2.0.4 and above)

The effect of the above three methods decreases in descending order, and the following examples are used to illustrate

Create a test table

[Email protected]>create table S1 as select * from Dba_objects; Table created. [Email protected]>create table s2 as select * from Dba_objects; Table created.

1, alter system flush Shared_pool;

This command clears all the data cached in the shared pool and naturally deletes the SQL pair execution plan that you want to delete, but the negative effect is that it clears all the data in the shared pool and affects it too much. production systems Be sure to use this command with caution.

Execute two queries and view the cache in the shared pool

[Email protected]>select object_name from s1 where object_id=20;object_ NAME------------------------------Icol$[email protected]>select object_name from s2  where object_id=20;object_name------------------------------Icol$[email protected]>col  sql_text for a80[email protected]>select sql_text,sql_id,version_count,executions  from v$sqlarea where sql_text like  ' select object_name from s% '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select object_name from s1  Where object_id=20 1s45nwjtws2tj     11select object_name from  s2 where object_id=20 a6gw4ht2unxun     11[eMail protected]>select object_name from s1 where object_id=20;object_ NAME------------------------------Icol$[email protected]>select sql_text,sql_id,version_count, executions from v$sqlarea where sql_text like  ' Select object_name from  s% '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select object_name from s1  Where object_id=20 1s45nwjtws2tj     12select object_name from  s2 where object_id=20 a6gw4ht2unxun     11

The above query for the table S1 and Table S2 query, from the output can be seen above the execution of the two SQL execution plan and the parse tree is cached in the shared pool, executed again directly with the cached execution plan (executions becomes 2). Now you want to delete the table S1 corresponding to the SQL execution plan, execute the ALTER system flush Shared_pool;

[Email protected]>alter system flush Shared_pool; System altered. [Email protected]>select sql_text,sql_id,version_count,executions from V$sqlarea where Sql_text like ' select Object_ name from s% '; no rows selected

From the above query results can be seen that the command did delete the S1 corresponding to the SQL execution plan, but also the table S2 corresponding SQL execution plan also deleted, hurt the innocent.

2. Perform DDL operations on the table

Once a DDL operation is performed on a table, all shared cursor in the library cache that contains the table in SQL text is marked as invalidated by Oracle (invalid), which means that the parse tree and execution plan stored in these shared cursor will no longer be reused. So when Oracle executes the SQL associated with this table again, it uses hard parsing. But the disadvantage of this approach is that the scope is still too wide, because once a DDL operation is performed on a table, all the SQL associated with the table is executed again using hard parsing. This is not good, especially for OLTP-type applications, because this can result in a sharp increase in the number of hard resolutions in a short period of time, which in turn affects the performance of the system.

[Email protected]>select object_name from s1 where object_id=20;object_ NAME------------------------------ICOL$[EMAIL PROTECTED]>SELECT OBJECT_NAME FROM S1  where object_id=30;object_name------------------------------I_cobj#[email protected]>select  sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like   ' select object_name from s% '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select object_name from s1  Where object_id=20 1s45nwjtws2tj     11select object_name from  s1 where object_id=30 1hdyqyxhtavqs     11[email protected] >select object_name from&nBsp;s1 where object_id=20;object_name------------------------------icol$[email protected]> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text  like  ' select object_name from s% '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select object_name from s1  Where object_id=20 1s45nwjtws2tj     12select object_name from  s1 where object_id=30 1hdyqyxhtavqs     11

The above query makes two different queries against table S1, and from the output it can be seen that the execution plan and the parse tree of the two SQL executed above are cached in the shared pool and executed again directly with the cached execution plan (executions becomes 2). Now to delete the execution plan for the object_id=20 corresponding SQL, choose to annotate the table (COMMENT), which is also a DDL operation.

[email protected]>comment on table s1 is  ' Test shared cursor '; Comment created. [email protected]>select sql_text,sql_id,version_count,executions,object_status from v$ sqlarea where sql_text like  ' select object_name from s% '; Sql_text sql_id  version_count executions object_ STATUS-------------------------------------------------------------------------------- ------------------ --------------------- ------------- ---------- ---------------Select object_name from  s1 where object_id=20 1s45nwjtws2tj     12 invalid_ unauthselect object_name from s1 where object_id=30 1hdyqyxhtavqs      11 invalid_unauth[email protected]>select object_name from s1  where object_id=20;object_name------------------------------Icol$[email protected]>select sql_text,sql_id,version_count,executions,object_status  from v$sqlarea where sql_text like  ' select object_name from s% '; Sql_text sql_id  version_count executions object_ STATUS-------------------------------------------------------------------------------- ------------------ --------------------- ------------- ---------- ---------------Select object_name from  s1 where object_id=20 1s45nwjtws2tj     11 validselect  object_name from s1 where object_id=30 1hdyqyxhtavqs      11 invalid_unauth

As can be seen from the above output, the table S1 do DDL operation is slow to exist in the shared pool execution plan is not cleared, but two SQL corresponding to the execution plan state has become "Invalid_unauth", when the execution of SQL will be hard to parse, Re-caches the parse tree and execution plan.

3. Dbms_shared_pool.purge Bag

It is a method introduced from Oracle 10.2.0.4, which can be used to delete a shared cursor in the cache of a specified cache in the library, and its scope of influence is limited to the shared cursor of the target SQL. That is, it allows Oracle to use hard parsing when executing the target SQL and remains the same as it did when all other SQL was executed.

[email protected]>alter system flush shared_pool; System altered. [Email protected]>select object_name from s1 where object_id=20;object_ NAME------------------------------ICOL$[EMAIL PROTECTED]>SELECT OBJECT_NAME FROM S1  where object_id=30;object_name------------------------------I_cobj#[email protected]>select  sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea  where sql_text like  ' select object_name from s% '; sql_text     sql_id     version_count executions  Object_status   address       hash_ VALUE------------------------------------------------------------ ---------------------------------------  ------------- ---------- --------------- ---------------- ----------select object_name from s1 where object_id=20      1s45nwjtws2tj 1    1 valid      00000000b4f85a18  1942752049select object_name from s1 where object_id=30      1hdyqyxhtavqs 1    1 VALID       00000000be7e56c8 1637183192

The

Now deletes the execution plan and parse tree for the SQL cache that corresponds to object_id=20.

[Email protected]>exec sys.dbms_shared_pool.purge (' 00000000b4f85a18,1942752049 ', ' C ');P l/sql  Procedure successfully completed. [Email protected]>select sql_text,sql_id,version_count,executions,object_status,address,hash_ value from v$sqlarea where sql_text like  ' Select object_name from  s% '; sql_text     sql_id     version_count executions  Object_status   address       hash_ VALUE------------------------------------------------------------ ---------------------------------------  ------------- ---------- --------------- ---------------- ----------select  Object_name from s1 where object_id=30     1hdyqyxhtavqs 1     1 valid      00000000be7e56c8 1637183192

From the output it can be seen that the execution plan and parse tree of object_id=20 corresponding SQL cache have been deleted, while the execution plan of object_id=30 corresponding SQL is not affected.

It is important to note that if you use Dbms_shared_pool.purge in 10.2.0.4, you must have an agent set event 5614566 before use (Alter session set events ' 5614566 trace name Context forever '), otherwise dbms_shared_pool.purge will not work, this limit is no longer present in the 10.2.0.4 version. If the Dbms_shared_pool package is not installed by default, you can perform @?/rdbms/admin/dbmspool.sql

Refer to Oracle-based SQL optimization

Official Document: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_shared_pool.htm#ARPLS68077

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1904641

Three ways Oracle deletes a SQL execution plan cached in the shared pool