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

This article mainly introduces how Oracle views historical changes in SQL execution plans. It has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

Suddenly received an alarm that the number of active sessions is soaring, check the database, for an OLTP type query should go index range scan but become all direct path read, all sql go full table scan. The tragedy is that the table is a history table 185G... Therefore, a lot of session accumulation is caused, and the foreground application is affected. Going back to the problem itself, if you look at changes to the sql execution plan??

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

DBA_HIST_SQL_PLAN

DBA_HIST_SQLSTAT

DBA_HIST_SNAPSHOT

View historical execution information for statements, whether and when changes occurred. If so, find out how the previous execution plan compares to the current execution plan.

Use sql to discover when a sql execution plan 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 checking out the changes in the implementation plan, you can use the following sql to check 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 ID,TIMESTAMP;

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.

From the above results, it can be seen that the execution plan changed at 11:57 C_MEM_XX_FATDT0 The previous index range scan changed to full table scan!!

Thank you for reading this article carefully. I hope that the article "How to view the historical changes of SQL execution plan" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

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.