上一篇中讲了如何使用EXPLAIN PLAN方法来获取sql执行计划,这篇继续讲另两种方法:使用动态性能视图和AWR报告。
一、使用动态性能视图
查询动态性能视图我们可以获取丰富的信息,包括执行计划与游标信息等等。下面罗列几个常用的v$视图。
① v$sql_plan
②v$sql_plan_statistics
③v$sql_workarea
④v$sql_plan_statistics_all
其中v$sql_plan_statistics_all包含了其他三个视图的信息,所以常用这个视图得到的信息最多。包括执行计划信息、游标信息、运行时统计信息(比如:执行时间、产生记录数量)、sql内存使用情况等等。
1.获取游标相关信息
试想有一个场景,客户说他运行一个业务从提交后一直在等待状态,此时你想看下后台运行的是什么sql,可以这样:
SELECT STATUS, SQL_ID, SQL_CHILD_NUMBER
FROM V$SESSION
WHERE USERNAME = 'TEST';
STATUS SQL_ID SQL_CHILD_NUMBER
-------- ------------- ----------------
INACTIVE
INACTIVE
ACTIVE bzn1vszukbh3a 0
INACTIVE
由此抓出这个用户下正在运行的sql_id与sql_child_number,从而有针对性地分析相关sql语句。
第二个场景是,如果有测试人员给你提交了一个报告,里面有一些sql_text你知道,但你想分析关于这个sql的详细信息,可以这样:
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT STATUS%'
AND SQL_TEXT NOT LIKE '%v$sql%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------
6d9xsg8gsjn7c 0 SELECT STATUS, SQL_ID, SQL_CHI
LD_NUMBER FROM V$SESSION WH
ERE USERNAME = 'TEST'
2.获取执行计划
比如现在已经抓取到sql_id=’ 6d9xsg8gsjn7c’的语句很慢,那么为了得到它的执行计划,可以这样:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6d9xsg8gsjn7c', 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID 6d9xsg8gsjn7c, child number 0
-------------------------------------
SELECT STATUS, SQL_ID, SQL_CHILD_NUMBER FROM V$SESSION WHERE USERNAME
= 'TEST'
Plan hash value: 3733760267
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 116 | 0 (0)|
|* 2 | FIXED TABLE FULL | X$KSUSE | 1 | 103 | 0 (0)|
|* 3 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
二、使用AWR或Statspack
相信接触过Oracle管理的人对AWR和Statspack都不陌生,前者是后者的改进,只有10g以后的版本才有。其实存储在AWR报告中的执行计划还是可以通过数据字典中的视图查询得出的,这个视图是:dba_hist_sql_plan。
现在我从测试库中生成AWR报告,我们通过分析AWR报告中的SQLStatistics部分,可以找出一些有问题的sql语句,比如响应时间长的(SQL ordered by Elapsed Time)语句,消耗CPU高的语句(SQLordered by CPU Time)等等。抓取到有问题语句的sql_id后,就可以这样:
SELECT * FROM table(dbms_xplan.display_awr('3mxd8jarkr9g1'));
SQL_ID 3mxd8jarkr9g1
--------------------
Select * from table(dbms_workload_repository.awr_diff_report_html(
360794861, 1, 22799, 22800,
360794861, 1, 23255, 23256))
Plan hash value: 1675984159
--------------------------------------------------------------------------
| Id | Operation | Name | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| AWR_DIFF_REPORT_HTML | |
--------------------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)