dbms_xplan

最新推荐文章于 2021-01-27 09:44:55 发布
最新推荐文章于 2021-01-27 09:44:55 发布
阅读量164 收藏
点赞数
 

10g

*****************************************

dbms_xplan包通过dbmsutil.sql脚本创建

■ DISPLAY - 从PLAN_TABLE中提取执行计划

■ DISPLAY_CURSOR - 从V$SQL_PLAN中提取执行计划，需要V$SQL_PLAN, V$SESSION，V$SQL_PLAN_STATISTICS_ALL上的权限

■ DISPLAY_AWR - 从AWR库中的SQL语句的提取执行计划，需要DBA_HIST_SQL_PLAN（WRH$_SQL_PLAN）, DBA_HIST_SQLTEXT（WRH$_SQLTEXT），V$DATABASE上的权限，

■ DISPLAY_SQLSET - 从SQL tuning set中的SQL语句的执行计划，需要ALL_SQLSET_STATEMENTS和ALL_SQLSET_PLANS上的权限（这两个视图是WRI$上建立的一个视图）

SQL> EXPLAIN PLAN FOR

2 select *

3 from regions a, countries b

4 where a.region_id = b.region_id

5 and a.region_name = 'Americas';

Explained.

SQL> SET LINESIZE 130

SQL> SET PAGESIZE 0

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 2128980668

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 168 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 6 | 168 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 350 | 1 (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("A"."REGION_NAME"='Americas')

4 - access("A"."REGION_ID"="B"."REGION_ID")

17 rows selected.

SQL> select count(*)

2 from regions a, countries b

3 where a.region_id = b.region_id

4 and a.region_name = 'Americas';

COUNT(*)

----------

5

SQL> SET PAGESIZE 0

SQL> SET LINESIZE 130

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

SQL_ID 59fkr5bn6q9kq, child number 0

-------------------------------------

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR)

Plan hash value: 3713220770

--------------------------------------------------------------------

| Id | Operation | Name | Cost |

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 24 |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | |

--------------------------------------------------------------------

Note

-----

- cpu costing is off (consider enabling it)

17 rows selected.

SQL> select /* ZHANGQIAOC */count(*)

2 from regions a, countries b

3 where a.region_id = b.region_id

4 and a.region_name = 'Americas';

COUNT(*)

----------

5

SQL> select sql_id, child_number, sql_text

2 from v$sql

3 where sql_text like '%ZHANGQIAOC%';

SQL_ID CHILD_NUMBER

------------- ------------

SQL_TEXT

---------------------------------------------------------------------------------------------------

bz0qkc2n9n614 0

select sql_id, child_number, sql_text from v$sql where sql_text like '%ZHANGQIAOC%'

4rwway8kx55j3 0

select /* ZHANGQIAOC */count(*) from regions a, countries b where a.region_id = b.region_id a

nd a.region_name = 'Americas'

5m4s3h69a1rgj 0

select /* ZHANGQIAOC */count(*) from regions a, countries b where a.region_id = b.region_id a

SQL> select * from table(dbms_xplan.display_cursor('4rwway8kx55j3',0));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

SQL_ID 4rwway8kx55j3, child number 0

-------------------------------------

select /* ZHANGQIAOC */count(*) from regions a, countries b where a.region_id =

b.region_id and a.region_name = 'Americas'

Plan hash value: 1080168104

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

| 1 | SORT AGGREGATE | | 1 | 17 | | |

| 2 | NESTED LOOPS | | 6 | 102 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 75 | 1 (0)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter("A"."REGION_NAME"='Americas')

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

5 - access("B"."REGION_ID"="A"."REGION_ID")

24 rows selected.

SQL> ALTER TABLE countries PARALLEL;

Table altered.

SQL> EXPLAIN PLAN for

2 select /*+ full(a) full(b) */*

3 from regions a, countries b

4 where a.region_id = b.region_id

5 and a.region_name = 'Americas'

6 order by b.country_id;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 2078065449

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 168 | 7 (29)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10002 | 6 | 168 | 7 (29)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | 6 | 168 | 7 (29)| 00:00:01 | Q1,02 | PCWP | |

| 4 | PX RECEIVE | | 6 | 168 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |

| 5 | PX SEND RANGE | :TQ10001 | 6 | 168 | 6 (17)| 00:00:01 | Q1,01 | P->P | RANGE |

|* 6 | HASH JOIN | | 6 | 168 | 6 (17)| 00:00:01 | Q1,01 | PCWP | |

| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |

| 8 | PX RECEIVE | | 1 | 14 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |

| 9 | PX SEND BROADCAST | :TQ10000 | 1 | 14 | 3 (0)| 00:00:01 | | S->P | BROADCAST |

|* 10 | TABLE ACCESS FULL | REGIONS | 1 | 14 | 3 (0)| 00:00:01 | | | |

| 11 | PX BLOCK ITERATOR | | 25 | 350 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |

| 12 | INDEX FAST FULL SCAN| COUNTRY_C_ID_PK | 25 | 350 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("A"."REGION_ID"="B"."REGION_ID")

10 - filter("A"."REGION_NAME"='Americas')

25 rows selected.

SQL>

SQL> CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

View created.

SQL> SELECT * FROM PLAN;

Plan hash value: 2078065449

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 168 | 7 (29)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10002 | 6 | 168 | 7 (29)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | 6 | 168 | 7 (29)| 00:00:01 | Q1,02 | PCWP | |

| 4 | PX RECEIVE | | 6 | 168 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |

| 5 | PX SEND RANGE | :TQ10001 | 6 | 168 | 6 (17)| 00:00:01 | Q1,01 | P->P | RANGE |

|* 6 | HASH JOIN | | 6 | 168 | 6 (17)| 00:00:01 | Q1,01 | PCWP | |

| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |

| 8 | PX RECEIVE | | 1 | 14 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |

| 9 | PX SEND BROADCAST | :TQ10000 | 1 | 14 | 3 (0)| 00:00:01 | | S->P | BROADCAST |

|* 10 | TABLE ACCESS FULL | REGIONS | 1 | 14 | 3 (0)| 00:00:01 | | | |

| 11 | PX BLOCK ITERATOR | | 25 | 350 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |

| 12 | INDEX FAST FULL SCAN| COUNTRY_C_ID_PK | 25 | 350 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("A"."REGION_ID"="B"."REGION_ID")

10 - filter("A"."REGION_NAME"='Americas')

25 rows selected.

DBMS_XPLAN.DISPLAY(TABLE_NAME IN VARCHAR2 DEFAULT 'PLAN_TABLE',

STATEMENT_ID IN VARCHAR2 DEFAULT NULL,

FORMAT IN VARCHAR2 DEFAULT 'TYPICAL',

FILTER_PREDS IN VARCHAR2 DEFAULT NULL); --9i没有此参数

9i无此方法

DBMS_XPLAN.DISPLAY_AWR(SQL_ID IN VARCHAR2,

PLAN_HASH_VALUE IN NUMBER DEFAULT NULL,

DB_ID IN NUMBER DEFAULT NULL,

FORMAT IN VARCHAR2 DEFAULT TYPICAL);

9i无此方法

DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID IN VARCHAR2 DEFAULT NULL,

CHILD_NUMBER IN NUMBER DEFAULT NULL,

FORMAT IN VARCHAR2 DEFAULT 'TYPICAL');

9i无此方法

DBMS_XPLAN.DISPLAY_SQLSET(SQLSET_NAME IN VARCHAR2,

SQL_ID IN VARCHAR2,

PLAN_HASH_VALUE IN NUMBER := NULL,

FORMAT IN VARCHAR2 := 'TYPICAL',

SQLSET_OWNER IN VARCHAR2 := NULL)

RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

SELECT * FROM table (

DBMS_XPLAN.DISPLAY_SQLSET(

'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));

SELECT * FROM table (

DBMS_XPLAN.DISPLAY_SQLSET(

'OLTP_optimization_0405','gwp663cqh5qbf'));

SELECT * FROM table (

DBMS_XPLAN.DISPLAY_SQLSET(

'OLTP_optimization_0405', 'gwp663cqh5qbf', NULL, 'ALLSTATS LAST');

另外，调优SQL的常用语句

--SQL文本

select * from v$sqltext where sql_id='4pf5z8cnggkrs';

--SGA中的执行计划

select * from v$sql_plan where sql_id='4pf5z8cnggkrs';

--AWR中捕获的执行计划

SELECT * FROM TABLE(dbms_xplan.display_awr('f2zymmdhuzvxj'));

--绑定变量的值

select b.SQL_TEXT,value_string,child_number,name,datatype_string

from v$sql_bind_capture a,v$sqlarea b

where a.SQL_ID = b.SQL_ID

and A.SQL_ID='4pf5z8cnggkrs'

order by CHILD_NUMBER,POSITION;

--其他信息

select child_number,executions,optimizer_mode,optimizer_cost,parsing_user_id,

parsing_schema_id,address,hash_value

from v$sql

where sql_id = '7d5xrwggyz1hp';


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/611609/viewspace-743710/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/611609/viewspace-743710/

csdn20142
关注
dbmsxplan oracle_Oracle dbms_xplan包简介
weixin_39609423的博客
12-29 252
dbms_xplan包用于展示执行计划。执行计划存储在计划表中，库缓存中和AWR中，dbms_xplan包能从这些地方将执行计划取出，格式化之后展示给我们。二. dbms_xplan输出dbms_xplan的输出就是执行计划的信息，从上往下看一般是这样的：第一部分：SQL_ID：识别父游标child number：这个sql_id子游标序号，识别子游标SQL文本内容：展示的是该执行计划是哪条SQ...
dbmsxplan oracle_DBMS_XPLAN.DISPLAY_AWR
weixin_35600177的博客
12-29 740
之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划，于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询，但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录，一般来说这些语句已经从shared pool共享池中被替换出去了。这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Ora...
dbmsxplan oracle_Oracle dbms_xplan
weixin_39953102的博客
12-20 150
DBMS_XPLAN 包关于执行计划的功能如下：(每个函数的参数可以desc dbms_xplan查看)•DISPLAY - to format and display the contents of a plan table.•DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQ...
DBMS_XPLAN
guxueliang的专栏
06-24 758
与手工执行计划表相比，使用DBMS_XPLAN通常可以获得更好的结果，因为他的语法更加加单，还提供多种有用的数据如格式，并且可以利用 缓存的执行计划统计信息。 调用DBMS_XPLAN函数嘴简单的方法是使用SELECT * FROM TABLE()语法，如下面的语句： SELECT * FROM TABLE(DBMS_XPLAN.FUNCTION(OPTIONS));   最常用的两
Oracle dbms_xplan包简介
liwenshui322的专栏
01-24 2003
一. 用途介绍        dbms_xplan包用于展示执行计划。执行计划存储在计划表中，库缓存中和AWR中，dbms_xplan包能从这些地方将执行计划取出，格式化之后展示给我们。         二. dbms_xplan输出         dbms_xplan的输出就是执行计划的信息，从上往下看一般是这样的：         第一部分：
dbms xplan 详解
cuizhu0832的博客
07-16 199
http://www.orafaq.com/wiki/DBMS_XPLANDBMS_XPLAN (DBMS Explain Plan) is a PL/SQL p...
Oracle中使用DBMS_XPLAN处理执行计划详解
12-16
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是使用set autotrace命令，不过现在看来，DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
Re-post: DBMS_XPLAN : Display Oracle Execution Plans
04-23
标题“DBMS_XPLAN: 显示Oracle执行计划”是一个关于Oracle数据库系统中用于分析SQL查询执行性能的重要工具的讨论。这个工具允许用户查看SQL语句的执行计划，从而理解数据库如何处理这些语句，以及哪些操作可能影响...
Oracle DBMS_XPLAN深度解析：执行与解释计划
"Oracle中使用DBMS_XPLAN处理执行计划详解，通过DBMS_XPLAN包查看SQL计划，包括执行计划和解释计划，提供更简化的方法，替代set autotrace命令。DBMS_XPLAN包的5个核心函数用于不同场景，如DISPLAY、DISPLAY_AWR、...
dbms_xplan.display_awr方式获取执行计划的实验和之前的误导
bisal的专栏
10-28 9549
《查看Oracle执行计划的几种常用方法-系列1》(http://blog.youkuaiyun.com/bisal/article/details/38919181)这篇博文中曾提到一个隐藏问题： “隐藏问题2： 实验这部分内容发现使用select * from table(dbms_xplan.display_awr('sql_id'));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，
Oracle中使用DBMS_XPLAN查看执行计划的方法
weixiaohuai的博客
06-03 4548
一、简介 DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包.DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 DBMS_XPLAN包不仅可以获取解释计划，还可以用来输出存储在AWR、SQL调试集、缓存的SQL游标，以及SQL基线中的语句计划。下面将重点讨论关于DBMS_XPLAN包在解释计划和执行计划上的应用。 首先看一个经常使用的查看某条语句的解释计划的示例： //登录oracle sqlplus / as sysdba //查看执行计划 ex
MySQL中xplan_dbms_xplan之display_cursor函数的使用
weixin_34827675的博客
01-27 317
DBMS_XPLAN包中display_cursor函数不同于display函数，display_cursor用于显示SQL语句的真实的执行计划，在大多数情况下，显示真实的执行计划有助于更好的分析SQL语句的全过程，尤其是运行此SQL语句实时的I/O开销。通过对比预估的I/O与真实的I/O开销来判断SQL语句所存在问题，如缺少统计信息，SQL语句执行的次数，根据实际中间结果集的大小来选择合适的连接...
dbmsxplan oracle_dbms_xplan.display_awr方式获取执行计划的实验和之前的误导，dbmsxplan.display...
weixin_42513365的博客
01-12 275
dbms_xplan.display_awr方式获取执行计划的实验和之前的误导，dbmsxplan.display《查看Oracle执行计划的几种常用方法-系列1》(http://blog.youkuaiyun.com/bisal/article/details/38919181)这篇博文中曾提到一个隐藏问题：“隐藏问题2：实验这部分内容发现使用select * from table(dbms_xplan.d...
dbms_xplan.display/dbms_xplan.display_cursor/autotrace
about database article
04-11 487
dbms_xplan.display/dbms_xplan.display_cursor/autotrace 这3个都可以显示sql语句的执行计划，那么这3者有什么区别那？ 1. Explain plan Explain plan以SQL语句作为输入，得到这条SQL语句的执行计划，并将执行计划输出存储到计划表中，方法如下： explain plan for select * from
使用dbms_xplan工具查看执行计划
ORACLE专栏
09-10 919
使用dbms_xplan工具查看执行计划 9i有一个新的包 dbms_xplan，对查询plan_table表是一个很有用的工具，相对于以前写一个复杂的SQL语句，然后从plan_table看执行计划，不如调用 dbms_xplan 包，还可以显示格式，这个工具的使用也非常方便。调用的语法类似select * from table(dbms_xplan.display(format=>BASIC
oracle 11G dbms_xplan包的使用
11-23 708
一、dbms_xplan.display的使用     1.  explain  plan for    一定要加上这个执行计划         select count(*)  from dual;    2.select 　＊　from  table(dbms_xplan.display);  之后就可以看到刚才那个语句的执行计划。      dbms_xplan.display的格
dbms_xplan函数包
kiwi的专栏
01-20 676
dbms_xplan 函数包 DISPLAY函数用户显示存储在PLAN_TABLE中的执行计划。此外，如果从视图v$sql_plan_statistics_all 可以获得该执行计划的相关统计数据。DISPLAY可以格式化输出这些数据 参数描述： TABLE_NAME：存储查询计划的表名（不区分大小写），默认值为PLAN_TABLE STATEMENT_ID:SQL语句的ID。在PLAN_TABL
[Oracle] 获取执行计划的几种方法
runfeel
06-14 382
1. 预估执行计划 - Explain Plan Explain plan以SQL语句作为输入，得到这条SQL语句的执行计划，并将执行计划输出存储到计划表中。 首先，在你要执行的SQL语句前加explain plan for，此时将生成的执行计划存储到计划表中，语句如下： explain plan for SQL语句 然后，在计划表中查询刚刚生成的执行计划，语句如下： sel...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值