查看sql执行计划

来源：互联网发布：两个系统数据库对接编辑：程序博客网时间：2024/04/29 08:24

1.dbms_xplan.display

dbms_xplan由9i中引入,用来显示explan plan里的执行计划信息,explan
plan的结果存储在plan_table表中,9i里plan_table是个物理表,而在10g
plan_table是个全局临时表,如果从9i升级到10g里的应用,可以drop掉
plan_table以显示更全的执行计划信息.

用法:

explain">SYS@oracle10g>explainplan for select count(*)
2 from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name
3 /

已解释。

SYS@oracle10g>select* from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 420753894

--------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |   145 |    59   (4)| 00:00:01 |
|   1 | SORT AGGREGATE     |          |     1 |   145 |            |          |
|   2 |   NESTED LOOPS      |          |    12 | 1740 |    59   (4)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| TEST_2   |     3 |   237 |    56   (4)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN | I_TEST_1 |     5 |   330 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T2"."OBJECT_ID"<10)
4 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

Note
-----
- dynamic sampling used for this statement

已选择21行。

rows:预测返回的行数
cost:某个返回路径的成本
bytes:预测返回的字节说
time :预测执行时间

Predicate Information (identified by operation id):
operation id对应的谓词信息
(9i里引入,9i只有explain plan显示谓词信息,10g里set autot trace也能显示谓词信息)

Note:

显示是否使用动态采样,outline和profile等

display的一些option:

BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

http://www.psoug.org/reference/dbms_xplan.html

explain plan里应该重点关注的部分:

1.rows 评估的行数很重要,大部分的sql性能问题都是因为rows评估错误导致
2.cost 是cbo选择执行计划的标准
3.谓词信息 ,谓词信息可以查看一些谓词的转换,如隐式转换等,同时结合谓词
信息和rows来判断oracle为什么会评估错rows,然后再采取解决方式,同时注意
filter和access谓词

2.autotrace

explain plan的同胞兄弟,oracle间接调用的explan plan(10046就能看出),但是autotrace的一个
好处是能够显示sql的物理读,逻辑读,递归调用,排序等信息,因此autotrace可以作为单条sql语句
效率的基准测试工具

常见选项:

set autotrace explain
set autotrace traceonly
set autotrace on
set autotrace statistics

3.v$sql_plan

v$sql_plan在9i中引入,对sql tuning带来了巨大的帮助,上面的2个方法不能查看sql运行时刻的
执行计划,只能看到评估出来的执行计划,因为sql在解析时刻和真正执行时刻的执行计划可能不一样
(如使用绑定变量),因此真实的执行计划非常重要

例如:

select child_number,
       '[' || ltrim(to_char(depth, '00')) || ']' "ID",
       lpad(' ', 3 * (depth - 1)) || operation ||
       decode(options, null, '', ' ' || options) "Operation",
       OBJECT_NAME "OBJECT_NAME",
       COST "COST",
       CARDINALITY "CARD",
       BYTES,
       ACCESS_PREDICATES,
       FILTER_PREDICATES
from V$SQL_PLAN
where hash_value = &hash_value;

4. 10046 and sql_trace

10046是一个比较强大的工具,在troubleshooting和sql tuning中的帮助很大,可以查看绑定变量的信息
以及wait event的信息,同时可以查看执行计划中的每个步骤消耗的逻辑读信息,物理读信息,以及消耗的
时间等

select count(*)
from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.12          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        252          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.03       0.13          0        255          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=252 pr=0 pw=0 time=2306 us)
      8   NESTED LOOPS (cr=252 pr=0 pw=0 time=450 us)
      8    TABLE ACCESS FULL TEST_2 (cr=242 pr=0 pw=0 time=112 us)
      8    INDEX RANGE SCAN I_TEST_1 (cr=10 pr=0 pw=0 time=196 us)(object id 53804)

可以看到逻辑读基本都消耗在test_2的全表扫描上,因此可能可以在这个字段上创建个索引来减少
逻辑读情况

创建索引后的情况:

select count(*)
from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.00       0.00          0         13          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.01          0         13          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=13 pr=0 pw=0 time=682 us)
      8   NESTED LOOPS (cr=13 pr=0 pw=0 time=921 us)
      8    TABLE ACCESS BY INDEX ROWID TEST_2 (cr=3 pr=0 pw=0 time=276 us)
      8     INDEX RANGE SCAN I_TEST_2 (cr=2 pr=0 pw=0 time=146 us)(object id 53807)
      8    INDEX RANGE SCAN I_TEST_1 (cr=10 pr=0 pw=0 time=312 us)(object id 53804)

ps:sql_trace会产生一个新的解析环境(sql_trace是optimizer参数的一个选项),因此可能产生一个
新的version_count,所以经常看到在bind_peeking发生的情况下,走sql_trace执行很快,而不打开sql_trace
的时候又执行的很慢

5.dbms_xplan.display_cursor

10g r2后oracle新提供的方法,实际上是对v$sql_plan以及相关视图的封装,可以通过10046就能
发现,display_cursor显示的也是真实的执行计划,oracle推出这个方法后,v$sql_plan和10046
将慢慢退出舞台(通过gather_plan_statistics 的hint)

display_cursor语法:

select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','options'));

display_cursor的一些比较强大的options:

a. 'All'
b. 'advanced'
c. 'peeked_binds'
d. 'outline'
e. 'allstats last'

一般比较常用的是'advanced','peeked_binds','allstats last'

更多介绍参考:

http://www.psoug.org/reference/dbms_xplan.html

gather_plan_statistics和dbms_xplan.display_cursor连用的例子:

SYS@oracle10g>select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 766bq2cgr9byq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_1 t1,test_2 t2 where
t2.object_id<10 and t1.object_name=t2.object_name

Plan hash value: 2539381227

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
A-Time | Buffers |

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:
00:00.01 | 13 |

| 2 | NESTED LOOPS | | 1 | 12 | 8 |00:
00:00.01 | 13 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_2 | 1 | 8 | 8 |00:
00:00.01 | 3 |

|* 4 | INDEX RANGE SCAN | I_TEST_2 | 1 | 8 | 8 |00:
00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | I_TEST_1 | 8 | 2 | 8 |00:
00:00.01 | 10 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T2"."OBJECT_ID"<10)
5 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

已选择23行。

重点介绍:

e-rows:表示预测返回的行数,explain plan里的数据
a-rows:实际返回的行数
a-times:表示实际消耗的时间
buffers:表示逻辑读情况
starts表示循环次数,一般情况下都是1,但是在nest loops和filter的情况下可能大于1,表示执行次数,
一般是驱动表的a-rows.

6.display_awr

在10g以前的版本中,诊断历史发生的性能问题比较难,只能借助statspack或者自己收集历史信息,oracle
10g里在这方面有了极大的增强,包括awr,ash,以及dba_hist相关的视图,为dba做诊断提供了很大的帮助

display_awr可以显示awr里sql的执行信息,和display_cursor类似,只是语法有些不同

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
DB_ID                          NUMBER(38)              IN     DEFAULT
FORMAT                         VARCHAR2                IN     DEFAULT

a.sql_id可以从awr报告或者ash报告里得到
b.sql_id可以从dba_hist_sqltext里查询sql语句得到
c.可以通过dba_hist_sqlstat查看sql的消耗情况

例如:

SYS@oracle10g>selectsql_id,sql_text from dba_hist_sqltext where sql_text
2 like 'select count(*) from t%';

SQL_ID SQL_TEXT
-------------------------------------------------------------------------------
a2gxctfkz4z5h select count(*) from test_peek where name=:a and id=:b

SYS@oracle10g>select* from table(dbms_xplan.display_awr('a2gxctfkz4z5h',null,null,'peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a2gxctfkz4z5h
--------------------
select count(*) from test_peek where name=:a and id=:b

Plan hash value: 2988370418

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| TEST_PEEK | 25282 | 98K| 41 (13)| 00:00:01 |
--------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :A (VARCHAR2(30), CSID=852): 'a'
2 - :B (NUMBER): 1

已选择20行。

From:http://space.itpub.net/8984272/viewspace-619812