oracle 执行计划（oracle执行计划影响估值的因素）

本文目录

• oracle执行计划影响估值的因素
• Oracle如何根据动态性能视图来查看某条SQL语句的执行计划
• Oracle中利用10053事件来分析Oracle是如何做出最终的执行计划
• 如何获取执行计划

oracle执行计划影响估值的因素

1 统计信息。
2 执行计划读取顺序：从最里面的对其开始，从上到下。
2 绑定变量窥视
3 自适应绑定变量。
4 11g新特性之基数反馈（Cardinality Feedback）A
5 STA
6 SPA
7 sql profile
8 驱动表异常，导致执行计划异常，（例如在表连接过程中，被驱动表可以使用连接健索引，而被驱动表（数据量很大）由于异常导致成为驱动表）

Oracle如何根据动态性能视图来查看某条SQL语句的执行计划

Oracle 如何根据动态性能视图来查看某条SQL语句的执行计划


1、授权某用户查看 sys.v$sql_plan 的权限
2、创建类plan_table的视图
SQL》 create or replace view dynamic_plan_table
2 as
3 select rawtohex(address)|| ’_’ ||child_number statement_id,
4 sysdate timestamp,operation,options,object_node,
5 object_owner,object_name,0 object_instance,
6 optimizer,search_columns,id,parent_id,position,
7 cost,cardinality,bytes,other_tag,partition_start,
8 partition_stop,partition_id,other,distribution,
9 cpu_cost,io_cost,temp_space,access_predicates,
10 filter_predicates
11 from v$sql_plan;
视图已创建。
3、查看执行计划
SQL》 select plan_table_output
2 from table(dbms_xplan.display
3 (’dynamic_plan_table’,
4 (select rawtohex(address)||’_’||child_number x
5 from v$sql where sql_text=
6 ’select * from user_tables’),
7 ’serial’))
8 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 703 (100)|
|* 1 | HASH JOIN | | 2513 | 5700K| 703 (7)|
| 2 | FIXED TABLE FULL | X$KSPPCV | 100 | 196K| 0 (0)|
| 3 | MERGE JOIN CARTESIAN | | 2513 | 755K| 703 (7)|
|* 4 | HASH JOIN OUTER | | 2513 | 620K| 661 (1)|
|* 5 | HASH JOIN RIGHT OUTER | | 2513 | 547K| 473 (2)|
| 6 | TABLE ACCESS FULL | USER$ | 84 | 1428 | 3 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | HASH JOIN OUTER | | 2513 | 505K| 470 (2)|
|* 8 | HASH JOIN | | 2513 | 485K| 415 (1)|
| 9 | TABLE ACCESS FULL | TS$ | 7 | 133 | 4 (0)|
|* 10 | HASH JOIN OUTER | | 2513 | 439K| 410 (1)|
| 11 | NESTED LOOPS | | 2485 | 325K| 352 (1)|
|* 12 | TABLE ACCESS FULL | OBJ$ | 3142 | 110K| 236 (2)|
|* 13 | TABLE ACCESS CLUSTER| TAB$ | 1 | 98 | 1 (0)|
|* 14 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|
| 15 | TABLE ACCESS FULL | SEG$ | 7092 | 311K| 58 (0)|
| 16 | INDEX FAST FULL SCAN | I_OBJ1 | 69116 | 539K| 54 (0)|
| 17 | INDEX FAST FULL SCAN | I_OBJ2 | 69116 | 2024K| 187 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 18 | BUFFER SORT | | 1 | 55 | 516 (10)|
|* 19 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
4 - access("T"."BOBJ#"="CO"."OBJ#")
5 - access("CX"."OWNER#"="CU"."USER#")
7 - access("T"."DATAOBJ#"="CX"."OBJ#")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8 - access("T"."TS#"="TS"."TS#")
10 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND
"T"."TS#"="S"."TS#")
12 - filter(("O"."OWNER#"=USERENV(’SCHEMAID’) AND
BITAND("O"."FLAGS",128)=0))
13 - filter(BITAND("T"."PROPERTY",1)=0)
14 - access("O"."OBJ#"="T"."OBJ#")
19 - filter("KSPPI"."KSPPINM"=’_dml_monitoring_enabled’)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- ’DYNAMIC_PLAN_TABLE’ is old version
已选择45行。
，

Oracle中利用10053事件来分析Oracle是如何做出最终的执行计划

我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的

我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的信息呢?通过10053事件可以Oracle依据的执行代价和如何做出执行计划的.如果我们发现某一条SQL语句的执行计划和想像的不一样,我们就可以去看看Oracle所使用的统计分析数据是否准确,是不是统计信息太久没有分析了,重新分析有问题的对象,最终让Oracle做出正确的执行计划。
我们来做一次10053事件的示例:
SQL》 create table t1 as select rownum rn from dba_objects;
Table created.
SQL》 create index ind_t1 on t1(rn);
Index created.
SQL》 exec dbms_stats.gather_table_stats(user,’t1’,cascade=》true);
PL/SQL procedure successfully completed.
SQL》 create table t2 as select rn, ’t2’ name from t1 where rncreate index ind_t2 on t2(rn);
Index created.
SQL》 exec dbms_stats.gather_table_stats(user,’t2’,cascade=》true);
PL/SQL procedure successfully completed.
SQL》 alter session set tracefile_identifier=’mysession’;
Session altered.
SQL》 alter session set events ’10053 trace name context forever,level 1’;
Session altered.
SQL》 explain plan for select t2.* from t1,t2 where t1.rnalter session set events ’10053 trace name context off’;
Session altered.
和上次讲SQL_TRACE的时候一样,生成的trace文件的路径是$ORACLE_BASE/admin/SID/udump目录.
与SQL_TRACE和10046事件不同的是,生成的trace文件不能用tkprof处理,只能阅读原始的trace文件.
对trace文件做一个大体的介绍:
**************************
Predicate Move-Around (PM)
**************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."RN" "RN","T2"."NAME" "NAME" FROM "YORKER"."T1" "T1","YORKER"."T2" "T2" WHERE "T1"."RN"
，

如何获取执行计划

如何oracle的获取执行计划1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。 explan plan for your_sql; select * from table(dbms_xplan.display);2.获取具有outline信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了


如何oracle的获取执行计划
1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。
explan plan for your_sql;
select * from table(dbms_xplan.display);
2.获取具有outline信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了解更多oracle内部的hint
explan plan for your_sql;
select * from table(dbms_xplan.display(null, null,’advanced -projection’))
3.真实的执行计划，可以看到实际的 Starts（执行次数） | E-Rows（估算的返回行数） | A-Rows（实际的返回行数）
ALTER SESSION SET STATISTICS_LEVEL=ALL;
execute your_sql;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’ALLSTATS LAST’))

那么这3中获取执行计划的方式可以写到一个脚本getplan.sql，用的时候非常方便。
--getplan.sql
set feedback off timing off ver off
pro ’general,outline,starts’
pro
acc type prompt ’Enter value for plan type:’ default ’general’
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’ALLSTATS LAST’)) where ’&&type’=’starts’;
select * from table(dbms_xplan.display) where ’&&type’=’general’;
select * from table(dbms_xplan.display(null, null,’advanced -projection’)) where ’&&type’=’outline’;
set feedback on timing on ver on
undef type
测试如下：
SQL》 select * from a;
ID NAME
---------- ----------
1 a1
2 a2
3 a3
4 a4
5 a5
SQL》 select * from b;
ID NAME
---------- ----------
1 b1
2 b2



--执行计划1：普通执行计划
SQL》 explain plan for select a.*,(select name from b where b.id=a.id) from a;
Explained.
Elapsed: 00:00:00.04
SQL》 @getplan
’general,outline,starts’
Enter value for plan type: ----这里输入general或回车
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3653839899
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| B | 1 | 20 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 5 | 100 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement



--执行计划2：outline执行计划
SQL》 explain plan for select a.*,(select name from b where b.id=a.id) from a;
Explained.
Elapsed: 00:00:00.01
SQL》 @getplan
’general,outline,starts’
Enter value for plan type:outline --这里输入outline
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3653839899
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| B | 1 | 20 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 5 | 100 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / B@SEL$2
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "B"@"SEL$2")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE(’10.2.0.4’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--执行计划3：real执行计划
SQL》 set serveroutput off
SQL》 ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
Elapsed: 00:00:00.00
SQL》 select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL》 @getplan
’general,outline,starts’
Enter value for plan type:starts --这里输入starts
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 3653839899
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| B | 5 | 1 | 2 |00:00:00.01 | 35 |
| 2 | TABLE ACCESS FULL| A | 1 | 5 | 5 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--注意：
--第3种执行计划不能多次获取，只能执行1次，获取一次，否则会获取不到
下面再次获取一下试试：
SQL》 @getplan
’general,outline,starts’
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID dvp8nn63wuhs8, child number 0
-------------------------------------
select * from table(dbms_xplan.display(null, null,’advanced -projection’))
where ’starts’=’outline’
Plan hash value: 3440229843
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | 0 |00:00:00.01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 0 | 0 |00:00:00.01 | --第二次无法获取真实的执行计划
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- rule based optimizer used (consider using cbo)


--第3种执行计划要关掉set serveroutput off，否则也不能获取执行计划。
测试如下：
这里和上面的测试是同一个会话，所以没有再执行ALTER SESSION SET STATISTICS_LEVEL=ALL;了。
SQL》 set serveroutput on
SQL》 select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL》 @getplan
’general,outline,starts’
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; --也无法获取真实的执行计划
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)