sql执行计划历史变更
##通过SQLid获取执行计划
select * from table(dbms_xplan.display_cursor('&1',0,'ADVANCED'));
第一部分：
---
SQLID
CHILD NUMBER
HASH值
SQL文本
-------------------------------------------------------------------
SQL_ID 99mz7zrx7muwf, child number 0
-------------------------------------
select /*+ real_exp_example1 */ t1.PCT_FREE,t2.CACHE,t1.NUM_ROWS from
t1,t2 where t1.TABLE_NAME=t2.TABLE_NAME
Plan hash value: 2959412835
-------------------------------------------------------------------------
第二部分
1.执行SQL的内部执行步骤
2.执行顺序
3.谓词信息
4.列信息
5.优化器评估执行步骤返回结果集的Cardinality,成本
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
|* 1 | HASH JOIN | | 502 | 23092 | 35 (0)| 00:00:01 | ---星号表示有驱动或者过滤条件
| 2 | TABLE ACCESS FULL| T2 | 500 | 10500 | 8 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 2345 | 58625 | 27 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id): ------查询块的名称
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T2@SEL$1
3 - SEL$1 / T1@SEL$1
Outline Data ---SQL的内部HINT组合
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id): --------谓词信息
---------------------------------------------------
1 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME") --------access驱动查询条件
Column Projection Information (identified by operation id): --每个步骤对应的列信息
-----------------------------------------------------------
1 - (#keys=1) "T1"."NUM_ROWS"[NUMBER,22], "T2"."CACHE"[VARCHAR2,20],
"T1"."PCT_FREE"[NUMBER,22], "T1"."NUM_ROWS"[NUMBER,22],
"T1"."PCT_FREE"[NUMBER,22]
2 - "T2"."TABLE_NAME"[VARCHAR2,128], "T2"."CACHE"[VARCHAR2,20]
3 - "T1"."TABLE_NAME"[VARCHAR2,128], "T1"."PCT_FREE"[NUMBER,22],
"T1"."NUM_ROWS"[NUMBER,22]
第三部分 NOTE部分
1.执行计划的额外补充信息
1.1 dynamic sampling --动态采样
dynamic sampling used for this statement
1.2 Cardinality feedback --修正执行计划的Cardinality值
cardinality feedback used for this statement
1.3 SQL Profile --配置文件
SQL profile SYS_sqlprof_01339cc6e9800001 used for this statement
Note
-----
- this is an adaptive plan
##查看sql的执行计划
1.explain plan
2.dbms_xplan
3.sqlplus 中的autotrace
4.10046
5.10053
6.AWR或者Statspack
7.现成的脚本
8.display_cursor_9i.sql
##通过sqid获取 执行计划
select * from table(dbms_xplan.display_awr('02gbv93w8y4tt'));
select * from table(dbms_xplan.display_cursor('02gbv93w8y4tt',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
2.1 explain plan 同PL/SQL中的F5
explain plan for 目标SQL;
select * from table(dbms_xplan.display);
SQL> explain plan for select t1.PCT_FREE,t2.CACHE,t1.NUM_ROWS from t1,t2 where t1.TABLE_NAME=t2.TABLE_NAME ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 502 | 23092 | 35 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 502 | 23092 | 35 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 500 | 10500 | 8 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 2345 | 58625 | 27 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- this is an adaptive plan
19 rows selected.
2.2
##日期输出的格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
##查询SQL的第一次和最后一次加载时间
select FIRST_LOAD_TIME,LAST_LOAD_TIME from v$sql where sql_id='30xjd4p8tyw9g';
##查询SQL的历史绑定变量
select VALUE_STRING ,DATATYPE from DBA_HIST_SQLBIND where sql_id='3bbypvvgmbk05' order by SNAP_ID;
##查看sql的执行计划是否发生改变
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='&1' order by TIMESTAMP;
##查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化！
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 hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id ='&1'
and plan_hash_value in (2763422316,1102996740)
order by ID,TIMESTAMP;
##HInt 调优
##hash
select /*+ use_hash(c,b) */ nvl(sum(b.out_num), 0) as deal_out_num_gg
from BILLING.t_bill_log_collect c, BILLING.t_bill_log_deal b
where c.relevance_field = b.relevance_field
and b.system_type in ('gg', 'gl')
and c.collect_time >= to_date('20180806003000', 'yyyymmddhh24miss')
and c.collect_time < to_date('20180806010000' , 'yyyymmddhh24miss');
from BILLING.t_bill_log_collect c, BILLING.t_bill_log_deal b
##嵌套，C为驱动表，B为被驱动表
select /*+ use_nl(c,b) */ nvl(sum(b.out_num), 0) as deal_out_num_gg
from BILLING.t_bill_log_collect c, BILLING.t_bill_log_deal b
where c.relevance_field = b.relevance_field
and b.system_type in ('gg', 'gl')
and c.collect_time >= to_date('20180806003000', 'yyyymmddhh24miss')
and c.collect_time < to_date('20180806010000' , 'yyyymmddhh24miss');
##强制走索引
/*+ index(b index_name) */
今天中午，突然接收到active session 数目飙高的报警，查看数据库，对于一个OLTP 类型的查询本应该走index range scan 却变成全部是 direct path read ，所有的sql 走了全表扫描。悲剧的是那个表是一个历史表 185G。。故造成了许多session堆积，前台应用受到影响。回到问题本身，如果查看sql执行计划的变更？？
oracle 10G 以后可以通过下面的三个视图查询到sql执行计划的历史信息：
DBA_HIST_SQL_PLAN
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT
查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。
使用如下sql 可以发现某个sql的执行计划什么时候发生了变化！
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24: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
查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化！
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 hh24: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
