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

View historical changes to the sql execution plan

After oracle 10G, you can query the historical information of the sql execution plan through the following three views:

DBA_HIST_SQL_PLAN

DBA_HIST_SQLSTAT

DBA_HIST_SNAPSHOT

Check the historical execution information of the statement, whether it has changed and when it has changed. If there is a change, find out the previous implementation plan and compare it with the current implementation plan.

Use the following sql to find out when the execution plan of a sql has changed!

Select distinct SQL_ID

PLAN_HASH_VALUE

To_char (TIMESTAMP, 'yyyymmdd hh34:mi:ss') TIMESTAMP

From dba_hist_sql_plan

Where SQL_ID = '68wnxdjxwwn2h'

Order by TIMESTAMP

SQL_ID PLAN_HASH_VALUE TIMESTAMP

68wnxdjxwwn2h 235510920 20111020 21:25:23

68wnxdjxwwn2h 1542630049 20120612 11:57:23

68wnxdjxwwn2h 2754593971 20120612 12:43:34

After seeing the changes in the execution plan, you can use the following sql to see what changes have taken place!

Col options for a15

Col operation for a20

Col object_name for a20

Select plan_hash_value

Id

Operation

Options

Object_name

Depth

Cost

To_char (TIMESTAMP, 'yyyymmdd hh34:mi:ss')

From DBA_HIST_SQL_PLAN

Where sql_id = '68wnxdjxwwn2h'

And plan_hash_value in (1542630049, 2754593971, 2620382595)

Order by TIMESTAMP,ID

PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME COST TO_CHAR (TIMESTAMP

235510920 0 SELECT STATEMENT 39 20111020 21:25:23

235510920 1 NESTED LOOPS 20111020 21:25:23

235510920 2 NESTED LOOPS 39 20111020 21:25:23

235510920 3 VIEW 11 20111020 21:25:23

235510920 4 WINDOW SORT PUSHED RANK 11 20111020 21:25:23

235510920 5 TABLE ACCESS FULL C_ETL_DATA_VALIDITY 10 20111020 21:25:23

235510920 6 PARTITION LIST ITERATOR 2 20111020 21:25:23

235510920 7 INDEX RANGE SCAN IDX_C_MEM_XXXXXXXX_ID 2 20111020 21:25:23

235510920 8 TABLE ACCESS BY LOCAL INDEX C_MEM_XXXXXXXX_FATDT0 4 20111020 21:25:23

ROWID

1542630049 0 SELECT STATEMENT 7854 20120612 11:57:23

1542630049 1 NESTED LOOPS 7854 20120612 11:57:23

1542630049 2 VIEW 28 20120612 11:57:23

1542630049 3 WINDOW SORT PUSHED RANK 28 20120612 11:57:23

1542630049 4 TABLE ACCESS FULL C_ETL_DATA_VALIDITY 27 20120612 11:57:23

1542630049 5 PARTITION LIST ITERATOR 7826 20120612 11:57:23

1542630049 6 TABLE ACCESS FULL C_MEM_XXXXXXXX_FATDT0 7826 20120612 11:57:23

2754593971 0 SELECT STATEMENT 43 20120612 12:43:34

2754593971 1 PX COORDINATOR 20120612 12:43:34

2754593971 2 PX SEND QC (RANDOM): TQ10001 20120612 12:43:34

2754593971 3 NESTED LOOPS 20120612 12:43:34

2754593971 4 NESTED LOOPS 43 20120612 12:43:34

2754593971 5 BUFFER SORT 20120612 12:43:34

2754593971 6 PX RECEIVE 20120612 12:43:34

2754593971 7 PX SEND BROADCAST: TQ10000 20120612 12:43:34

2754593971 8 VIEW 28 20120612 12:43:34

2754593971 9 WINDOW SORT PUSHED RANK 28 20120612 12:43:34

2754593971 10 TABLE ACCESS FULL C_ETL_DATA_VALIDITY 27 20120612 12:43:34

2754593971 11 PX PARTITION LIST ITERATOR 2 20120612 12:43:34

2754593971 12 INDEX RANGE SCAN IDX_C_MEM_XXXXXXXX_ID 2 20120612 12:43:34

2754593971 13 TABLE ACCESS BY LOCAL INDEX C_MEM_XXXXXXXX_FATDT0 15 20120612 12:43:34

ROWID

2620382595 0 SELECT STATEMENT 5 20120612 18:27:37

2620382595 1 TABLE ACCESS BY INDEX ROWID C_MEM_XXXXXXXX_BAKUP 5 20120612 18:27:37

2620382595 2 INDEX RANGE SCAN IDX_C_MEM_XXXXXXXX_BA 3 20120612 18:27:37

KUP_ID

33 rows selected.

As you can see from the above results, the execution plan changes at 11:57. C_MEM_XXXXXXXX_FATDT0 has changed from index range scan to full table scan!

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.