当前位置: 代码迷 >> SQL >> SQL执行计划-HIT、SQLPLAN
  详细解决方案

SQL执行计划-HIT、SQLPLAN

热度:76   发布时间:2016-05-05 10:34:12.0
SQL执行计划--HIT、SQLPLAN
方法1EXPLAIN FOR

EXPLIAN FOR SELECT * FROM XXXX;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

这种方式查询的是一种仅供参考性的执行计划,而真正的执行计划是存储在Liburary Cache中的。

?

方法2SET AUTOT ON

SET AUTOT ON EXP STATUS

完整语法:

SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

加上STATUS是查看实际执行的统计信息,如果不加则是预查看执行计划。

再执行SQL语句,就会打印出执行计划了。

?

方法3、从Library Cache中直接获取
这个方法效果最真实。它属于DBMS_XPLAN系列方法。其查询的视图包括:
V$SQL_PLAN

V$SQL_PLAN_STATISTICS

V$SQL_WORKAREA

V$SQL_PLAN_STATISTICS_ALL

另外相关视图还有:
V$SQL

V$SQLAREA

V$SQL_SHARED_CURSOR

V$SQL_PLAN
存储的就是Library Cache中的执行计划。其中的运行统计信息是在Parse阶段估算的
V$SQL_PLAN_STATISTICS
存储执行计划每一步运行时的统计信息,包括花费时间,处理数据行数。是真实统计结果。默认报流程,需要开启STATISTICS_LEVELALL才可以收集。或对SQL语句添加GATHER_PLAN_STATISTICS提示。
V$SQL_WORKAREA
提供SQL运行时的SQL Workarea内存的消耗。
V$SQL_PLAN_STATISTICS_ALL
是前三个视图的汇总,提用户完成了复杂的JOIN操作。
V$SESSION
记录会话信息,是性能调整时的最初入口。10g以后版本也整合了V$SESSION_WAIT的内容。

DBMS_XPLAN包有以下方法

方法

版本

DISPLAY

9i

DISPLAY_CURSOR

10g

DISPLAY_AWR

10g

DISPLAY_SQLSET

10g

DISPLAY_SQL_PLAN_BASELINE

11g

?

使用DISPLAY_CURSOR(sql_id, child?_number, format)查询

SQL>执行一个SQL语句

SQL> select SQL_TEXT, SQL_ID from v$sql where sql_text like 'xxxx';

SQL>select * from table(dbms_xplan.display_cursor('xxxxxxxxx', null, 'BASIC'));

--利用BASIC格式查看执行计划

SQL>select * from table(dbms_xplan.display_cursor('xxxxxxx',null, 'TYPICAL'));

--利用TYPICAL格式查看执行计划

SQL>select * from table(dbms_xplan.display_cursor('xxxxxxx',null, 'ALL'));

?

DBMS_XPLAN.DISPLAY_CURSOR参数介绍

FORMAT

格式

说明

BASIC

只有最基本的计划,不需要度量信息

TYPICAL

缺省值

SERIAL

TYPICAL基本相同,没有并行相关信息

ALL

TYPICAL增加语句过滤信息

?

FORMAT小粒度参数

格式

说明

ROWS

显示/隐藏ROWS

BYTES

显示/隐藏BYTES

COST

显示/隐藏COST

PARTITION

显示/隐藏分区信息

PARALLEL

显示/隐藏并行信息

PREDICATE

显示/隐藏谓词部分(即条件查询)

PROJECTION

显示/隐藏投影部分

ALIAS

显示/隐藏ALIAS部分

REMOTE

显示/隐藏远程SQL

NOTE

显示/隐藏NOTE部分

OUTLINE

显示/隐藏OUTLINE部分

?

FORMAT加项

格式

说明

IOSTATS

显示IO数据

MEMSTATS

如果PGA手动管理,会有相关内存内容

ALLSTATS

同时显示IOSTATSMEMSTATS

SQL> select * from table(dbms_xplan.display_cursor(‘xxxxxxx’,null,’ALL,+IOSTATS’));

SQL> select * from table(dbms_xplan.display_cursor(‘xxxxxxx’,null,’ALL, -ROWS, -COST’));

?

?

方法4、使用DISPLAY_AWR(sql_id, plan_hash_value, db_id, format)

获得AWR中的执行计划,信息来自于DBA_HIST_SQL_PLANDBA_HIST_SQLTEXT

SQL>select * from table(dbms_xplan.display_awr(‘xxx’)); --传入SQL ID

另外AWR还提供两个脚本用来分析某个SQL语句的执行计划是否发生都忙完以及性能变化信息,位置在$ORACLE_HOME/rdbms/adminawrsqrpt.sqlsprepsql,两个脚本。

?

执行计划分为5个部分。

SQL概要,执行计划,QB (Query Block)、数据过滤(谓词)、列投影。

?

SQL概要:

包括SQL_ID( Parent Cursor )Child Number( Child Cursor )、完整的SQL文本。

?

执行计划:

Hash值,表连接方式。

?

QB

对于每一个简单的SINGLE SQL语句(整个语句只有一层SELECT,并且查询对象都是真实的物理表),都有一个Query Block存在,形如SEL$1。这种格式是Prefix$n

Prefix种类如下:

Prefix

操作

CRI$

Create Index

DEL$

Delete

INS$

Insert

MEG$

Merge

SEL$

Select

SET$

集合操作

UPD$

Update

MISC$

其他,比如Lock Table

也可以自己对Prefix进行命名,要通过qb_name提示来做,

SQL>select * from table1, table2 where …

这样产生的QB部分中,SEL$1就变成了S_VIEW1.

?

复杂SQL包括视图、子查询、集合操作。

对于复杂SQL,首先做转换,变成等价简单SQL

对于视图(Inline ViewView),这个操作叫合并Merge

对于子查询,这个操作叫展开Unnest Subquery

10053事件可以跟踪SQL语句的Parse全过程。)

转换的过程将采用基于成本的查询转换(Cost Based Query Transformation),转换结束后取而代之的是一个新的简单SQL语句,这个新SQL对应一个Query Block。这个新Query Block的命名是SEL$????????

InExistsNot InNot Exists肯定会进行转换。

?

?

查询

alter system set statistics_level=all;

select* from TABLE(DBMS_XPLAN.diplay_cursor(null,null,"allstats +alias +outline"));

?