Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use sql plan baseline in the database, the article is very detailed, has a certain reference value, interested friends must read it!

Test content:

1. The change law of CREATED,LAST_MODIFIED,LAST_EXECUTED,LAST_VERIFIED of four time-related fields in dba_sql_plan_baselines table.

2. Several methods of changing candidate sql plan into accepted sql plan baseline.

3. When the sql plan baseline corresponding to the SQL statement fails, Optimizer evolves the newly generated execution plan into sql plan baseline.

4. Different users execute the same sql statement according to the tables under their own users, and the sharing mechanism of sql plan baseline

Create a test table:

Grant connect,resource,unlimited tablespace to scott identified by sdfg_1234

Create table scott.t1 tablespace ts_pub as select * from dba_objects

Create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum'scott',tabname= >'T1 recording method FALSE = > 'for all columns size 1 recording recorder cascade = > TRUE,no_invalidate= > FALSE)

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' t2recording method for all columns size = > TRUE,no_invalidate= > FALSE)

1. The changing rules of the time-related fields in the dba_sql_plan_baselines table, involving the following four fields

CREATED

LAST_MODIFIED

LAST_EXECUTED

LAST_VERIFIED

# enable sql capture at session level and automatically generate the first sql plan baseline

-- session 1, set capture at Session level

SQL > select * from dba_sql_plan_baselines

No rows selected

Alter system optimizer_capture_sql_plan_baselines=TRUE

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

-- session 2 is not recorded in the dbathing sql planning baselines because the above sql was executed only once

Select sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified from dba_sql_plan_baselines

-- session 1, execute sql again

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

-- session 2 dbathing sqlure planetary baselines produces the first sql plan baseline, and the first initial state is accepted

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines

# the value of the last_ verified field in the above result is empty because it is the first baseline generated by this sql, so it has not been verified; because it is the new sql plan baseline, the other three time fields are the same.

CREATED:02-JUL-14 02.37.20.000000 PM

LAST_MODIFIED:02-JUL-14 02.37.20.000000 PM

LAST_EXECUTED:02-JUL-14 02.37.20.000000 PM

LAST_VERIFIED:NULL

# the value of the last_ verified field in the above result is empty, because it is the first baseline generated by this sql, so it has not been passed.

-- session 1, execute sql for the third time, and disable the sql capture parameter before execution

Alter session set optimizer_capture_sql_plan_baselines=FALSE

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

-- session 2, observe the status of the time field. The values of the CREATED and LAST MODIFIED fields have not changed. It is understandable that the LAST_ execute value should be changed to the last execution time, but the fact has not changed. Even if the alter system flush shared_pool reexecutes the statement later, it will not change.

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines

# check that the corresponding execution plan of sql_plan_baseline is FTS through DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE

Select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle= > 'SQL_d11d993788ae4828',plan_name= >' SQL_PLAN_d27ct6y4awk1822a9c5af'))

PLAN_TABLE_OUTPUT

-

SQL handle: SQL_d11d993788ae4828

SQL text: select count (*) from scott.t1 where object_id in (select object_id from

Scott.t2)

Plan name: SQL_PLAN_d27ct6y4awk1822a9c5af Plan id: 581551535

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

PLAN_TABLE_OUTPUT

-

Plan hash value: 1240933221

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

| | 0 | SELECT STATEMENT | | 1 | 9 | 462 (2) | 00:00:06 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| | * 2 | HASH JOIN RIGHT SEMI | | 3 | 27 | 462 (2) | 00:00:06 |

| | 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T1 | 177k | 1042K | 455 (1) | 00:00:06 |

PLAN_TABLE_OUTPUT

-

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = "OBJECT_ID")

28 rows selected.

# create an index on the object_id field of the T1 table and execute sql again

Create index scott.ind_objid_t1 on scott.t1 (object_id) tablespace ts_pub

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' T1 recording method for all columns size = > TRUE,no_invalidate= > FALSE)

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' t2recording method for all columns size = > TRUE,no_invalidate= > FALSE)

# another plan_name= SQL_PLAN_d27ct6y4awk18b1b38b11 is generated in dba_sql_plan_baselines (the sql_handle is the same as the previous sql), but not the baseline of the accepted. The CREATED and LAST_MODIFIED fields of this record indicate the creation time of the baseline. LAST_EXECUTED and LAST_VERIFIED are null values.

Col sql_handle format a20

Col creator format a5

Col sql_text format a50

Col created format a30

Col last_modified format a30

Col last_executed format a30

Col last_verified format a30

Set linesize 190

Set pagesize 200

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines

# execute sql. Although there is an index, FTS is still used because of the existence of baseline.

Set autotrace traceonly

SQL > select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Execution Plan

Plan hash value: 1240933221

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

| | 0 | SELECT STATEMENT | | 1 | 9 | 462 (2) | 00:00:06 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| | * 2 | HASH JOIN RIGHT SEMI | | 3 | 27 | 462 (2) | 00:00:06 |

| | 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T1 | 177k | 1042K | 455 (1) | 00:00:06 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = "OBJECT_ID")

Note

-

-SQL plan baseline "SQL_PLAN_d27ct6y4awk1822a9c5af" used for this statement

Statistics

0 recursive calls

0 db block gets

2557 consistent gets

2556 physical reads

0 redo size

526 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

# artificial evolution of sql plan baseline, according to the comparison before and after Buffer Get optimization, the performance of sql plan baseline using index is 232 times that of FTS. In the case of oracle, the performance of accept new sql plan baseline is determined by the value of the implied parameter _ plan_verify_improvement_margin (default value is 150x, which means 1.5x). In this case, the new sql plan baseline of accept has reached 232, so it is verified and accepted.

Set serveroutput on

Set long 10000

Declare

Result_clob clob

Begin

Result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle= > 'SQL_d11d993788ae4828',plan_name= >' SQL_PLAN_d27ct6y4awk18b1b38b11',verify= > 'YES',commit= >' YES')

Dbms_output.put_line (result_clob)

End

/

Evolve SQL Plan Baseline

Report

-

Inputs:

-

SQL_HANDLE = SQL_d11d993788ae4828

PLAN_NAME =

SQL_PLAN_d27ct6y4awk18b1b38b11

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY =

YES

COMMIT = YES

Plan:

SQL_PLAN_d27ct6y4awk18b1b38b11

-

Plan was

Verified: Time used. 901 seconds.

Plan passed performance criterion: 232.77

Times better than baseline plan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

Execution Status:

COMPLETE COMPLETE

Rows Processed: 1

one

Elapsed Time (ms): 59.641. 298 200.14

CPU Time (ms): 34.444 0

Buffer Gets:

2557 11 232.45

Physical Read Requests: 0

0

Physical Write Requests: 0 0

Physical Read

Bytes: 0 0

Physical Write Bytes:

0 0

Executions: 1

one

--

Report

Summary

-

Number of plans verified: 1

Number of plans accepted: 1

PL/SQL procedure successfully completed.

# check the corresponding sql plan baseline,LAST_VERIFIED and PLAN_NAME=SQL_PLAN_d27ct6y4awk18b1b38b11

LAST_MODIFIED is at the same time, LAST_VERIFIED indicates that the Verify action is completed at this time, and LAST_MODIFIED indicates that the action is completed at the same time

The time when this baseline was changed from not accepted to accepted after the Verify passed.

CREATED: 02-JUL-14 03.22.41.000000 PM

LAST_MODIFIED: 02-JUL-14 03.44.10.000000 PM

LAST_VERIFIED:02-JUL-14 03.44.10.000000 PM

# after executing the SQL, it is found that the last_executed time is already the latest time

SQL > select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines

LAST_EXECUTED:02-JUL-14 04.25.33.000000 PM

# display the execution plan of Plan_name=SQL_PLAN_d27ct6y4awk18b1b38b11 with dbms_xplan.display_sql_plan_baseline, this time using Nest Loop

SQL > select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle= > 'SQL_d11d993788ae4828',plan_name= >' SQL_PLAN_d27ct6y4awk18b1b38b11'))

PLAN_TABLE_OUTPUT

SQL handle: SQL_d11d993788ae4828

SQL text: select count (*) from scott.t1 where object_id in (select object_id fro

M

Scott.t2)

Plan name: SQL_PLAN_d27ct6y4awk18b1b38b11 Plan id: 2981333777

PLAN_TABLE_OUTPUT

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan hash value: 2406492491

-

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

| |

PLAN_TABLE_OUTPUT

-

| | 0 | SELECT STATEMENT | | 1 | 9 | 56 (2) | 00:00 |

: 01 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| |

| | 2 | NESTED LOOPS | | 99 | 891 | 56 (2) | 00:00 |

: 01 |

PLAN_TABLE_OUTPUT

| | 3 | SORT UNIQUE | | 99 | 297 | 5 (0) | 00:00 |

: 01 |

| | 4 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00 |

: 01 |

| | * 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0) | 00:00 |

: 01 |

PLAN_TABLE_OUTPUT

-

Predicate Information (identified by operation id):

5-access ("OBJECT_ID" = "OBJECT_ID")

Phase summary:

Time that CREATED:sql plan is generated to plan_history (can be accept or not accept status)

The time of the last modification of LAST_MODIFIED:sql plan, which reflects the not during the evolution of sql plan

The sql plan of accetped is updated to the time when the accepted action occurs, and it also reflects the use of alter_sql_plan_baseline

For the time of any property change in sql plan

The time when the LAST_VERIFIED:sql plan was last verified. If the same plan is verified again, if it is repeated.

Line validation, the time still stays at the time of the first verification; when the first sql plan automatically becomes sql plan baseline

The last_verified time is empty, indicating that it has not gone through verify. Even if the first sql plan is evolved manually, the last_verified time is still empty.

LAST_EXECUTED: nominally the time of the last execution, the actual test is fixed at the time of the first execution, followed by

The execution of does not update the

2. Several methods to change sql plan into accepted sql plan baseline.

(1) to call the Dbms_spm.evolve_sql_plan_baseline function, you need to call it manually (sql plan evolve advisor has been introduced in version 12c to achieve automatic evolution of sql plan baseline). This is the most commonly used method. Only the following instructions are provided:

Verify=yes indicates that it has been verified by optimizer

Verify=no means to be forced to accepted state without optimizer verification.

(2) call the Dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET function. Here, use the LOAD_PLANS_FROM_CURSOR_CACHE function to change the execution plan that already exists in shared pool from load to baseline, and the status changes to accepted.

# execute sql to make it cache to shared pool

Variable v_objid number

Exec: v_objid:=1000

Select count (*) from scott.t1 where object_id select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select count (*) from scott.t1%'

SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

-

Select count (*) from scott.t1 where object_id'9hup7n51za19u',cursor_child_no= > 0 format = > 'ALL'))

PLAN_TABLE_OUTPUT

SQL_ID 9hup7n51za19u, child number 0

-

Select count (*) from scott.t1 where object_id4020739011,fixed= > 'NO',enabled= >' YES')

Dbms_output.put_line (result_int)

End

/

# the sql plan baseline has been found in dba_sql_plan_baselines and has been accepted

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_text like'% vested objid'

# this sql plan baseline can already be used when executing sql again

Variable v_objid number

Exec: v_objid:=500

Select count (*) from scott.t1 where object_id'SQL_d11d993788ae4828',plan_name= > 'SQL_PLAN_d27ct6y4awk18b1b38b11')

Dbms_output.put_line (result_int)

End

/

-- successfully deleted the plan with only one FTS left

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

# execute dbms_sqltune, generate and accept optimization recommendations

-- generate tuning tasks

Declare

My_task_name varchar2 (30)

My_sqltext clob

Begin

My_sqltext:='select count (*) from scott.t1 where object_id in (select object_id from scott.t2)'

My_task_name:=dbms_sqltune.create_tuning_task (sql_text= > my_sqltext,user_name= > 'SCOTT',scope= >' COMPREHENSIVE',time_limit= > 60 taskmaker name = > 'scott_sql_tune_1',description= >' tune 1')

End

/

-- perform tuning tasks

Begin

Dbms_sqltune.execute_tuning_task (task_name= > 'scott_sql_tune_1')

End

/

# View the sqltune report and intercept the relevant content

Set long 9000

Set longchunksize 1000

Set linesize 800

Select dbms_sqltune.report_tuning_task ('scott_sql_tune_1') from dual

1-Original With Adjusted Cost

-

Plan hash value: 1240933221

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SCOTT_SQL_TUNE_1')

- - - - - -

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

| | 0 | SELECT STATEMENT | | 1 | 9 | 462 (2) | 00:00:06 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| | * 2 | HASH JOIN RIGHT SEMI | | 3 | 27 | 462 (2) | 00:00:06 |

| | 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T1 | 177k | 1042K | 455 (1) | 00:00:06 |

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SCOTT_SQL_TUNE_1')

2-Using SQL Profile

-

Plan hash value: 2406492491

-

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

-

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SCOTT_SQL_TUNE_1')

- - - - - -

| | 0 | SELECT STATEMENT | | 1 | 9 | 56 (2) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| | 2 | NESTED LOOPS | | 99 | 891 | 56 (2) | 00:00:01 |

| | 3 | SORT UNIQUE | | 99 | 297 | 5 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

# accept the Profile recommended by Advisor to follow the index. At the same time, you can see that another plan of accepted=yes has been added to the dba_sql_plan_baseline, which we just deleted, indicating that accepting the tuning result of dbms_sqltune can also realize the evolution of sql plan baseline.

Execute dbms_sqltune.accept_sql_profile (task_name= > 'scott_sql_tune_1',task_owner= >' SCOTT',replace= > TRUE)

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

# verify that the new sql plan baseline has been used

SQL > set autotrace traceonly explain

SQL > select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Execution Plan

Plan hash value: 2406492491

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 9 | 56 (2) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 9 |

| | 2 | NESTED LOOPS | | 99 | 891 | 56 (2) | 00:00:01 |

| | 3 | SORT UNIQUE | | 99 | 297 | 5 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

5-access ("OBJECT_ID" = "OBJECT_ID")

Note

-

-SQL profile "SYS_SQLPROF_0146fae6b2110000" used for this statement

-SQL plan baseline "SQL_PLAN_d27ct6y4awk18b1b38b11" used for this statement

Phase summary:

Method (1) is suitable for sql plan that already exists in sql plan history but has not been accepted. It can evolve after optimizer verification (verify=yes), or directly evolve to sql plan baseline without verification (verify=no).

Method (2) without turning on session-level or system-level automatic capture (optimizer_capture_sql_plan_baselines=FALSE), manually load the generated execution plan as sql plan baseline, that is, bypass the evaluation of optimizer and evolve directly to accepted plan. This method requires manual confirmation that the execution plan must be optimal, otherwise it will cause performance problems for subsequent SQL executed according to the baseline

Method (3) after there is a performance problem in the statement, turn to sql tuning advisor to get and apply optimization suggestions to generate the sql plan baseline of accepted, which belongs to the category of post-tuning.

3. If the sql plan baseline corresponding to the SQL statement fails, the sql plan evolution will skip the verify step and directly become accepted.

# Drop removes the original sql plan baseline

Declare

Result_int pls_integer

Begin

Result_int:=dbms_spm.drop_sql_plan_baseline (sql_handle= > 'SQL_d11d993788ae4828')

End

/

# rebuilding the test environment

Create table scott.t1 tablespace ts_pub as select * from dba_objects

Create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum'scott',tabname= >'T1 recording method FALSE = > 'for all columns size 1 recording recorder cascade = > TRUE,no_invalidate= > FALSE)

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' t2recording method for all columns size = > TRUE,no_invalidate= > FALSE)

Alter session set optimizer_capture_sql_plan_baselines=TRUE

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2);-- execute at least twice

Alter session set optimizer_capture_sql_plan_baselines=FALSE

# drop drop the index, execute sql again, and observe that in dba_sql_plan_baselines, the plan REPRODUCED corresponding to the index becomes NO, and this plan baseline becomes invalid due to the influence of drop on the index. At the same time, a plan of FTS is added, but the status is not accepted.

Drop index scott.ind_objid_t1

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

# now evolve the plan of FTS into Accepted sql plan baseline. From the output of the EVOLVE_SQL_PLAN_BASELINE function, you can see that although verify=YES is specified, because the indexed plan is invalid, oracle directly accept this plan without verify.

Set serveroutput on

Set long 10000

Declare

Result_clob clob

Begin

Result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle= > 'SQL_d11d993788ae4828',plan_name= >' SQL_PLAN_d27ct6y4awk1822a9c5af',verify= > 'YES',commit= >' YES')

Dbms_output.put_line (result_clob)

End

/

Evolve SQL Plan Baseline

Report

-

Inputs:

-

SQL_HANDLE = SQL_d11d993788ae4828

PLAN_NAME =

SQL_PLAN_d27ct6y4awk1822a9c5af

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY =

YES

COMMIT = YES

Plan:

SQL_PLAN_d27ct6y4awk1822a9c5af

-

Plan was

Not verified.

Using cost-based plan as could not reproduce any

Accepted and

Enabled baseline plan.

Plan was changed to an accepted

Plan.

-

Report

Summary

-

Number of plans verified: 0

Number of plans accepted: 1

# the result of evolution verifies that the sql plan baseline corresponding to FTS has become Accepted=yes.

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

# for the indexed sql plan baseline, in order to make it effective again, that is, if reproduced changes from NO to YES, the index must be re-established and sql must be executed once.

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Create index scott.ind_objid_t1 on scott.t1 (object_id) tablespace ts_pub

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' T1 recording method for all columns size = > TRUE,no_invalidate= > FALSE)

Exec dbms_stats.gather_table_stats (ownname= > 'scott',tabname= >' t2recording method for all columns size = > TRUE,no_invalidate= > FALSE)

# Verify alone cannot make it effective again, indicating that it is already accepted sql plan baseline

Set serveroutput on

Set long 10000

Declare

Result_clob clob

Begin

Result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle= > 'SQL_d11d993788ae4828',plan_name= >' SQL_PLAN_d27ct6y4awk18b1b38b11',verify= > 'YES',commit= >' YES')

Dbms_output.put_line (result_clob)

End

/

Evolve SQL Plan Baseline

Report

-

Inputs:

-

SQL_HANDLE = SQL_d11d993788ae4828

PLAN_NAME =

SQL_PLAN_d27ct6y4awk18b1b38b11

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY =

YES

COMMIT = YES

Plan:

SQL_PLAN_d27ct6y4awk18b1b38b11

-

It is

Already an accepted

Plan.

-

Report

Summary

-

There were no SQL plan baselines that required processing.

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

# only if you re-execute sql,reproduced will it become YES. In addition, you can also observe that the last_verified fields of these two valid sql plan baseline are empty, indicating that the two sql plan entered without going through verify, and indirectly indicating that there was no valid sql plan baseline at that time, and that they were directly "guaranteed" into the sql plan baseline.

Select count (*) from scott.t1 where object_id in (select object_id from scott.t2)

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'

1. Different users execute the same sql statement according to the tables under their own users, and the sharing mechanism of sql plan baseline

Test scenario description: two users each have a table named T1 under scott1 and scott2, set up a non-unique index named ind_objid_t on scott1.t1 (object_id), and execute select * from T1 where object_id't1',method_opt= > 'for all columns size 1 magic cascadeunder scott1 users > TRUE,no_invalidate= > FALSE)

# generate tables under scott2 users

Grant connect,resource,unlimited tablespace to scott2 identified by scott2_5678

Grant plustrace to scott2

Create table scott2.t1 tablespace ts_pub as select * from dba_objects

Exec dbms_stats.gather_table_stats (ownname= > 'scott2',tabname= >' T1 recording method for all columns size = > TRUE,no_invalidate= > FALSE)

# # Clean up the sql plan baseline in the existing environment and keep dba_sql_plan_baseline empty

Set serveroutput on

Declare

Result_int pls_integer

Cursor t_cur is select distinct sql_handle from dba_sql_plan_baselines

Begin

For v_cur in t_cur loop

Result_int:=dbms_spm.drop_sql_plan_baseline (sql_handle= > v_cur.sql_handle)

Dbms_output.put_line (result_int)

End loop

End

/

Alter system flush shared_pool

# # scott1 users generate the first sql plan baseline

Sqlplus scott1/scott1_1234

Alter session set optimizer_capture_sql_plan_baselines=true

Select * from T1 where object_id'SQL_91e3f036b4b3ac44',plan_name= > 'SQL_PLAN_93szh7uub7b2453067583');-- the corresponding execution plan is index range scan

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT | | 3560 | 337k | 213 (0) | |

00:00:03 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 3560 | 337k | 213K (0) |

00:00:03 |

| | * 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 10 (0) |

00:00:01 |

Scenario (1): the Scott2.t1 (object_id) field has no index, and the Scott2 user executes select * from T1 where object_id'SQL_PLAN_93szh7uub7b24dbd90e8e');-- the plan_name= SQL_PLAN_93szh7uub7b24dbd90e8e execution plan is as follows

SQL handle: SQL_91e3f036b4b3ac44

SQL text: select * from T1 where object_id'scott2',tabname= >'T1 methods for all columns size = > TRUE,no_invalidate= > FALSE)

# # when scott2 executes sql, you can see that plan_name=SQL_PLAN_93szh7uub7b2453067583 has become REPRODUCED=YES again, and you can see that plan_name=SQL_PLAN_93szh7uub7b2453067583 has been used again through the execution plan of sql statement

Set autotrace traceonly

Select * from T1 where object_id2000000)

Select table_name,index_name,clustering_factor from user_indexes where table_name='T1'

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR

-

T1 IND_OBJID_T 2000000

Set autotrace traceonly

Select * from T1 where object_id'SQL_91e3f036b4b3ac44',plan_name= > 'SQL_PLAN_93szh7uub7b24dbd90e8e')

Dbms_output.put_line (result_int)

End

/

-- there is only one plan= SQL_PLAN_93szh7uub7b2453067583 that is indexed.

Select

Sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRO

DUCED from dba_sql_plan_baselines

-- scott2 user execution sql,plan= SQL_PLAN_93szh7uub7b2453067583 will be enabled

Alter session set optimizer_use_sql_plan_baselines=TRUE

Set autotrace traceonly

Select * from T1 where object_id't1',method_opt= > 'for all columns size

1 > TRUE,no_invalidate= > cascade > FALSE)

# # re-execute sql and get a different execution plan (the difference here mainly refers to the change of the index name and the access path is still index range

Scan), the result is that a new plan_name=SQL_PLAN_93szh7uub7b2483309cfd has been added to the dba_sql_plan_baseline, compared with this

At the same time, it is also found that the plan_name= SQL_PLAN_93szh7uub7b2453067583 reproduced attribute under the scott1 user becomes NO, the original

Because the index name changes and does not match, that is, IND_OBJID_T! = IND_OBJID_T2

Set autotrace traceonly

Select * from T1 where object_id'SQL_91e3f036b4b3ac44',plan_name= > 'SQL_PLAN_93szh7uub7b24dbd90e8e')

Result_int2:=dbms_spm.drop_sql_plan_baseline (sql_handle= > 'SQL_91e3f036b4b3ac44',plan_name= >' SQL_PLAN_93szh7uub7b2483309cfd')

Dbms_output.put_line (result_int1)

Dbms_output.put_line (result_int2)

End

/

Select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines

-- rebuild the index on scott2.t1

Drop index scott2.ind_objid_t2

Create unique index scott2.ind_objid_t on scott2.t1 (object_id) tablespace ts_pub

Exec dbms_stats.gather_table_stats (ownname= > 'scott2',tabname= >' T1 recording method for all columns size = > TRUE,no_invalidate= > FALSE)

# # scott2 executes sql and observes that the plan_name=SQL_PLAN_93szh7uub7b2453067583 of scott1 users can still be utilized

Set autotrace traceonly

Select * from T1 where object_id't1',method_opt= > 'for all columns size

1 > TRUE,no_invalidate= > cascade > FALSE)

# # scott users can be reused to execute sql,sql plan baseline

Set autotrace traceonly

Select * from T1 where object_id

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.