orale性能调优工具--EXPLAIN PLAN和DBMS_XPLAN
来源:互联网 发布:手机图片点击放大js 编辑:程序博客网 时间:2024/06/07 21:45
概述:有多种方法可用来找出哪些sql语句需要优化,最简单的方法都是分析保存在v$sql视图中的缓存的SQL信息。这个视图包含存储在共享池中的SQL语句相关的信息,虽然逻辑读(logical read)最多或者时间消耗(elapsed time)最大的SQL语句通常都是优化的好目标,然而只有仔细观察每一个步骤才能找准最佳优化时机。在10g中,我们可以使用缓存的查询计划的统计信息来定位sql执行中可能需要特别注意的步骤,如视图v$sql_plan展示所有缓存的sql语句的执行计划,而v$sql_plan_statistics则展示计划中每个步骤的执行次数,IO次数以及处理得记录数。
1.通过挖掘v$sql,确定具有较高消耗时间,CPU或IO需求的SQL语句。通过使用v$sql_plan和v$sql_plan_statistics,我们可以找到执行操作不尽人意的的SQL语句。
a.select sql_id, child_number, sql_text, elapsed_time
from (select sql_id,
child_number,
sql_text,
elapsed_time,
cpu_time,
disk_reads,
rank() over(order by elapsed_time desc) as elapsed_rank
from v$sql)
where elapsed_rank <= 10;
b.DBMS_XPLAN.DISPLAY_CURSOR()看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('6axh886a13hhn','0'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6axh886a13hhn, child number 0
-------------------------------------
select NVL(LINE,'NONE') LINE into :b0 from (select A.DESCRIPTION LINE
,count(LH.LOTID) from LOTHISTORY LH ,AREA A where
((((((((LH.WORKAREA=A.WORKAREA and A.WORKAREA='EOL') and
LH.WORKAREA='EOL') and A.LEVELNO=2) and A.DETAILAREATYPE='Normal') and
LH.OLDLINEID=A.AREAID) and LH.LOTID=trim(:b1)) and
LH.EVENTID='TrackOut') and LH.OLDOPERATIONID in ('BFOP001')) group by
A.DESCRIPTION order by count(LOTID) desc ) where ROWNUM=1
Plan hash value: 4058338800
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 343 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 129 | 343 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 73 | 343 (1)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 73 | 343 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 1 | 73 | 341 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 7 | TABLE ACCESS BY INDEX ROWID| LOTHISTORY | 1 | 41 | 339 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_LOTHISTORY_LOTID | 466 | | 6 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | AREA_PK | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AREA | 1 | 32 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 - filter(("LH"."OLDOPERATIONID"='BFOP001' AND "LH"."EVENTID"='TrackOut' AND "LH"."OLDLINEID"
IS NOT NULL AND "LH"."WORKAREA"='EOL'))
8 - access("LH"."LOTID"=TRIM(:B1))
9 - access("A"."WORKAREA"='EOL' AND "LH"."OLDLINEID"="A"."AREAID")
filter("LH"."WORKAREA"="A"."WORKAREA")
10 - filter(("A"."LEVELNO"=2 AND "A"."DETAILAREATYPE"='Normal'))
40 rows selected.
2.dbms_xplan工具介绍:
与手工查询执行计划相比,使用dbms_xplan通常可以获得更好的结果,因为它的语法更加简单,还提供多种有用的输出格式,并且可以利用缓存的执行计划信息。
调用dbms_xplan函数最简单的方法是使用select * from table()语法:
最常用的两个函数:
function display(table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL',
filter_preds varchar2 default null)
return dbms_xplan_type_table
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
DISPLAY函数展示了PLAN_TABLE中的执行计划,而DISPLAY_CURSOR则展示了再v$sql_plan中缓存的执行计划的信息。
(1).BASIC只展示执行计划:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('9q26krp7wx6y8','0','BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
insert into LOTCARDDEFECT (select D.* ,trim(:b0) SHIFTDAY
,TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6') from (select LC.LOTID
,D.REASONCODE ,D.WORKAREA ,D.OPERATIONID ,sum(D.DEFECTQUANTITY) QTY
from PRODUCTSPEC PS ,LOTDEFECTDETAIL D ,LOTCARD LC ,DEFECTCODE DC where
((((((((((((1=1 and PS.WORKAREA=D.WORKAREA) and
PS.PRODUCTSPECID=D.PRODUCTSPECID) and D.LOTID like (LC.LOTID||'%')) and
D.REASONCODE=DC.DEFECTCODE) and D.WORKAREA=DC.WORKAREA) and
PS.WORKAREA<>'EOL') and D.WORKAREA<>'EOL') and PS.PRODUCTFAMILY in
('VERMONT','VERMONT+')) and LC.SHIFTDAY=trim(:b0)) and D.REASONCODE in
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(select DEFECTCODE from LOTCARDDEFECTHEADER DH where
((DH.HEADERTYPE='ITEM' and DH.WORKAREA<>'EOL') and
DH.REVISION='2014-07-07'))) and D.TESTLEVEL=0) and DC.TESTFLAG<>'Y')
group by LC.LOTID,D.WORKAREA,D.OPERATIONID,D.REASONCODE union select
LC.LOTID ,'ETC' REASONCODE ,D.WORKAREA ,D.OPERATIONID
,sum(D.DEFECTQUANTITY) QTY from PRODUCTSPEC PS ,LOTDEFECTDETAIL D
,LOTCARD LC ,DEFECTCODE DC where ((((((((((((1=1 a
Plan hash value: 964220270
--------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | |
| 2 | VIEW | |
| 3 | SORT UNIQUE | |
| 4 | UNION-ALL | |
| 5 | HASH GROUP BY | |
| 6 | HASH JOIN | |
| 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL | PRODUCTSPEC |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 9 | HASH JOIN | |
| 10 | TABLE ACCESS FULL | LOTDEFECTDETAIL |
| 11 | MERGE JOIN CARTESIAN | |
| 12 | TABLE ACCESS BY INDEX ROWID| LOTCARD |
| 13 | INDEX RANGE SCAN | LOTCARD_IDX01 |
| 14 | BUFFER SORT | |
| 15 | SORT UNIQUE | |
| 16 | TABLE ACCESS FULL | LOTCARDDEFECTHEADER |
| 17 | TABLE ACCESS FULL | DEFECTCODE |
| 18 | HASH GROUP BY | |
| 19 | HASH JOIN RIGHT ANTI | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 20 | TABLE ACCESS FULL | LOTCARDDEFECTHEADER |
| 21 | HASH JOIN | |
| 22 | TABLE ACCESS FULL | DEFECTCODE |
| 23 | HASH JOIN | |
| 24 | TABLE ACCESS FULL | LOTDEFECTDETAIL |
| 25 | MERGE JOIN CARTESIAN | |
| 26 | TABLE ACCESS BY INDEX ROWID| LOTCARD |
| 27 | INDEX RANGE SCAN | LOTCARD_IDX01 |
| 28 | BUFFER SORT | |
| 29 | TABLE ACCESS FULL | PRODUCTSPEC |
| 30 | HASH GROUP BY | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 31 | HASH JOIN | |
| 32 | TABLE ACCESS FULL | DEFECTCODE |
| 33 | HASH JOIN | |
| 34 | TABLE ACCESS FULL | LOTDEFECTDETAIL |
| 35 | MERGE JOIN CARTESIAN | |
| 36 | TABLE ACCESS BY INDEX ROWID | LOTCARD |
| 37 | INDEX RANGE SCAN | LOTCARD_IDX01 |
| 38 | BUFFER SORT | |
| 39 | TABLE ACCESS FULL | PRODUCTSPEC |
--------------------------------------------------------------------
66 rows selected.
(2).TYPICAL:默认输出设置
(3).ALL:输出所有信息
DBMS_XPLAN 输出例子:
explain plan for select department_name, last_name, job_title
from hr.employees
join hr.departments
using (department_id)
join hr.jobs
using (job_id)
order by department_name, job_title;
SQL> select * from table(dbms_xplan.display(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1492005721
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 |6678 | 10 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 106 |6678 | 10 (20)| 00:00:01 |
|* 2 | HASH JOIN | | 106 |6678 | 9 (12)| 00:00:01 |
| 3 | MERGE JOIN | | 107 |5029 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | SORT JOIN | | 107 |2140 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES |107 | 2140 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
6 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
22 rows selected.
3.解释执行计划:
(1).缩进越多的访问路径越先被执行。
(2)缩进到同样级别的步骤,先执行最上面的那条语句。
4.虚拟索引(Vitual index)是指没有创建对应的物理实体的索引。虚拟索引的目的,是在不必耗时,耗CPU,耗IO以及消耗大量存储空间区实际创建的索引的情况下,来判断一个索引是否能够对SQL优化起到作用。
SQL> explain plan for select * from sh.sales where amount_sold >1000;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |404K| 11M|526 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| |404K| 11M|526 (2)| 00:00:01 | 1 |28 |
|* 2 | TABLE ACCESS FULL | SALES | 404K|11M| 526 (2)| 00:00:01 | 1 | 28 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("AMOUNT_SOLD">1000)
14 rows selected.
建虚拟索引:
SQL> alter session set "_use_nosegment_indexes"=TRUE;
Session altered.
SQL> create index sh.sales_vit on sh.sales(amount_sold) nosegment;
Index created.
SQL> select * from table(dbms_xplan.display(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1798903108
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 404K| 11M| 366(0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 404K| 11M| 366(0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | SALES_VIT | 404K| | 13(0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("AMOUNT_SOLD">1000)
14 rows selected.
本例中如果建立索引是有用的,优化器估算的成本从526降366,因此在不创建索引的情况下,可以使用虚拟索引来确定优化器是否应该使用索引。
- orale性能调优工具--EXPLAIN PLAN和DBMS_XPLAN
- Explain Plan&& dbms_xplan
- ORACLE SQL优化工具之--EXPLAIN PLAN
- Explain plan
- Explain Plan
- explain plan
- Explain Plan
- explain plan
- oracle的autotrace和explain plan
- Explain plan Oracle优化工具安装及使用
- MySql性能调优利器之Explain
- 如何对Oracle中Struct和Array进行Explain Plan
- 执行计划 - EXPLAIN PLAN产生的操作和选项值
- EXPLAIN PLAN产生的操作和选项值
- autotrace 和explain plan for可能导致执行计划错误
- Explain Plan 使用
- explain plan初接触
- Oracle Explain Plan总结
- [WeChall] Training: WWW-Robots (HTTP, Training)
- 中文分词与隐马尔科夫模型之一:机械分词
- android学习之Service初学
- Ubuntu Linux的虚拟机安装Windows86X虚拟机安装(Ubuntu ISO64Bit)
- 递归及递归的效率问题
- orale性能调优工具--EXPLAIN PLAN和DBMS_XPLAN
- c# webserice 返回DataTable部分数据被截取 显示不全的问题
- 中文分词与马尔科夫模型之二:隐马尔科夫模型与维特比
- Ubuntu Linux访问小米手机存储卡
- 通过graph-tool学习BGL
- Tomcat通过Haproxy获取访问的源IP
- 第11周项目5-当年第几天
- 归并排序求逆序对
- 【原创】《Linux高级程序设计》杨宗德著 - 进程管理与程序开发 - 信号应用实例