Shulou(Shulou.com)06/01 Report--

After the first execution of a SQL statement in Oracle, the SQL statement is hard parsed, and the execution plan and parsing tree are cached in Shared Pool. It is convenient to execute this SQL statement again in the future without hard parsing, which is convenient for the expansion of the application system. However, if the SQL corresponds to a sudden change in the amount of table data or other reasons, the execution plan and parsing tree cached in Shared Pool are no longer applicable to the current situation, and the execution efficiency of SQL drops rapidly. In this case, it is necessary to clean up the execution plan and parsing tree cached in the Shared Pool in order to redo hard parsing on the SQL and generate a new execution plan and parsing tree.

There are three ways to remove the execution plan of the SQL cache from Shared Pool:

Alter system flush shared_pool

Do DDL operation on the table

Dbms_shared_pool.purge package (10.2.0.4 and above)

The scope of influence of the above three methods decreases successively. The following examples are used to demonstrate.

Create a test table

Zx@MYDB > create table S1 as select * from dba_objects;Table created.zx@MYDB > 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, so you can delete the execution plan of the SQL that you want to delete, but the negative effect is that it erases all the data in the Shared Pool, which has too much impact. The production system must use this command carefully.

Execute two queries and view the cache in Shared Pool

Zx@MYDB > select object_name from S1 where object_id=20;OBJECT_NAME--ICOL$zx@MYDB > select object_name from S2 where object_id=20;OBJECT_NAME--ICOL$zx@MYDB > col sql_text for a80zx@MYDB > 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 1 1select object_name from S2 where object_id=20 a6gw4ht2unxun 1 1zx@MYDB > select object_name from S1 where object_id=20 OBJECT_NAME--ICOL$zx@MYDB > 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 1 2select object_name from s2 where object_id=20 a6gw4ht2unxun 1 1

The above query queries table S1 and table S2 respectively. From the output, you can see that the execution plan and parsing tree of the two SQL executed above are cached in Shared Pool, and the cached execution plan is directly used when executed again (EXECUTIONS becomes 2). Now you want to delete the execution plan of table S1 corresponding to SQL and execute alter system flush shared_pool

Zx@MYDB > alter system flush shared_pool;System altered.zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like's select object_name from s% destroy no rows selected

From the query results above, we can see that the command does delete the execution plan of S1 corresponding to SQL, but it also deletes the execution plan of SQL corresponding to table S2, which hurts the innocent.

2. Do DDL operation on the table

Once an DDL operation is performed on a table, all Shared Cursor in the library cache that contain the table in the SQL text will be marked as invalid by Oracle, which means that the parse trees and execution plans stored in these Shared Cursor can no longer be reused, so hard parsing is used when Oracle executes the SQL associated with the table again. But the drawback of this approach is that it is still too wide-ranging, because once a DDL operation is performed on a table, hard parsing is used for all SQL associated with that table again. This is very bad, especially for OLTP-type applications, because it may lead to a sharp increase in the number of hard parsing in a short period of time, which in turn affects the performance of the system.

Zx@MYDB > select object_name from S1 where object_id=20;OBJECT_NAME--ICOL$zx@MYDB > select object_name from S1 where object_id=30;OBJECT_NAME--I_COBJ#zx@MYDB > 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 1 1select object_name from S1 where object_id=30 1hdyqyxhtavqs 1 1zx@MYDB > select object_name from S1 where object_id=20 OBJECT_NAME--ICOL$zx@MYDB > 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 1 2select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1

The above query makes two different queries on table S1. From the output, you can see that the execution plan and parsing tree of the two SQL executed above are cached in Shared Pool, and the cached execution plan is directly used when executed again (EXECUTIONS becomes 2). Now to delete the execution plan for object_id=20 corresponding to SQL, here you choose to COMMENT the table, which is also a DDL operation.

Zx@MYDB > comment on table S1 is' test shared cursor';Comment created.zx@MYDB > 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 1 2 INVALID_UNAUTHselect object_name from S1 where object_id=30 1hdyqyxhtavqs 1 1 INVALID_UNAUTHzx@MYDB > select object_name from S1 where object_id=20 OBJECT_NAME--ICOL$zx@MYDB > 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 1 1 VALIDselect object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 INVALID_UNAUTH

As can be seen from the above output, the execution plan cached in Shared Pool after the DDL operation on table S1 is not cleared, but the corresponding execution plan status of both SQL is changed to "INVALID_UNAUTH". When SQL is executed again, hard parsing will be done, parsing tree and execution plan will be re-cached.

3. Dbms_shared_pool.purge package

It is a method introduced from Oracle 10.2.0.4, it can be used to delete the specified Shared Cursor cached in the library cache, and its scope of influence is limited to the corresponding Shared Cursor of the target SQL, that is to say, it allows Oracle to use hard parsing when performing the target SQL and remains the same as before when executing all other SQL.

Zx@MYDB > alter system flush shared_pool;System altered.zx@MYDB > select object_name from S1 where object_id=20;OBJECT_NAME--ICOL$zx@MYDB > select object_name from S1 where object_id=30 OBJECT_NAME--I_COBJ#zx@MYDB > 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

Now you want to delete the execution plan and parse tree of the SQL cache for object_id=20.

Zx@MYDB > exec sys.dbms_shared_pool.purge ('00000000B4F85A1852752049); PL/SQL procedure successfully completed.zx@MYDB > 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, we can see that the execution plan and parsing tree of the SQL cache corresponding to object_id=20 have been deleted, while the execution plan of SQL corresponding to object_id=30 has not been affected.

It should be noted that if dbms_shared_pool.purge is used in 10.2.0.4, the agent must set event 5614566 (alter session set events' 5614566 trace name context forever') before using it, otherwise dbms_shared_pool.purge will not work, and this restriction no longer exists in versions above 10.2.0.4. If no dbms_shared_pool package is installed by default, you can execute @? / rdbms/admin/dbmspool.sql

Refer to "SQL Optimization based on Oracle"

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.