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

This article will explain in detail the execution process of ORACLE 11G using SPM to adjust SQL statements. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Let me explain to you:

After the ITSM database migration was upgraded to 11G, several SQL statements had incorrect execution plans, and all of these statements used bound variables.

The original adjustment idea is to get the binding variable values of these statements, directly replace the binding variables of the SQL statement with the literal values obtained, adjust the SQL to the correct execution plan, get the correct execution plan and import SPM. However, during the actual adjustment, it is found that although the execution plan obtained by literal quantity can be normally imported into SPM, it can not be used by the corresponding SQL statement, and the SQL statement still uses the wrong plan to execute the query. In the later adjustment, it is consistent with the way of using SQL statement in actual production, the adjusted SQL statement is executed by binding variables, and then the obtained plan is imported into SPM, and it is found that the statement can use the correct plan in SPM.

ORACLE 11G uses SPM to adjust the execution of SQL statements

1) get the SQL_ID of the SQL statement that executed the wrong plan, and currently load the bad execution plan into the SPM:

Variable cnt number

Execute: cnt: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID = >'& SQL_ID', PLAN_HASH_VALUE = > & HASH_VALUE)

Check the SPM to confirm that the relevant SQL plan has been loaded to the SPM. LOAD's entry is generally up-to-date:

Select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT from dba_SQL_PLAN_BASELINES where ACCEPTED = 'YES'

Order by LAST_MODIFIED

SQL_HANDLE PLAN_NAME

SQL_4079a044d6e19677 SQL_PLAN_40yd08mbfffddfdw555d8

2) adjust the SQL statement, such as adding a new hint, to make sure you get a good and correct execution plan. Execute the adjusted statement to get SQL_ID and Plan hash value:

Select sql_id,plan_hash_value from v$sql where sql_text like'% / * + test2-nbh INDEX (demand_state_alias%'

Note: for SQL that binds variables, it is also best to bind variables to get the correct execution plan. If literal is used, the execution plan, although loaded, may not be used by the SQL statement. At the same time, you can add some special hints to the SQL statement to make it easy to get the modified statement, such as adding the test2-nbh flag to the above query.

3) load the correct execution plan into SPM and prepare it to replace the wrong execution plan:

Variable cnt number

Exec: cnt: = dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID = >'& SQL_ID',PLAN_HASH_VALUE = > & plan_hash_value,SQL_HANDLE = >'& SQL_HANDLE')

SQL_ID: dzfky5zdzc231-this is obtained from the query in step 2

Plan hash value: 751013780-this is obtained from the query in step 2

SQL_HANDLE

SQL_4079a044d6e19677-this sql_handle is the sql_handle generated in step 1

4) verify that the SPM execution plan is correct

Select * from dba_sql_plan_baselines where CREATED > sysdate-1/48 order by created

There are two SPM records whose SQL_HANDLE is SQL_4079a044d6e19677, which can be determined by chronological order.

A good execution plan can also be done through the following formula:

Select * from

Table (dbms_xplan.DISPLAY_SQL_PLAN_BASELINE ('& sql_handle','&PLAN_NAME')

The sql_handle and PLAN_NAME here are from the ones generated in step 1

5) after verifying which execution plan is wrong, delete the bad execution plan from the SPM

Variable cnt number

Exec: cnt: = dbms_spm.DROP_SQL_PLAN_BASELINE (SQL_HANDLE= >'& SQL_HANDLE', PLAN_NAME= >'& PLAN_NAME')

5) re-execute the statement

6) check whether the statement execution plan is normal

Select

EXECUTIONS,PLAN_HASH_VALUE,ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/EXECUTIONS,LAST_ACTIVE_TIME,ROWS_PROCESSED

From v$sql where EXECUTIONS > 0 and sql_id='&sql'; select * from table (dbms_xplan.display_cursor ('& sql'))

About ORACLE 11G using SPM to adjust the SQL statement execution process is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.