共计 9559 个字符，预计需要花费 24 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下如何使用 dbms_xplan 查看执行计划，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
概述
dbms_xplan 包提供了多样的，预定义的方式去解释执行计划。sql 执行计划，执行时间等信息都存储与 v$sql_plan,v$sql_plan_statistics_all 视图中。此包运行的权限是执行用户，不是包的拥有者 sys。
当执行 diplay_awr 时，需要 DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE 的查询权限
当执行 display_cursor 时需要 V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL 的查询权限
当执行 display_sql_plan_baseline 时，需要 DBA_SQL_PLAN_BASELINES 的查询权限
当执行 display_sqlset 时，需要 ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS 的查询权限
以上的所有权限都在 SELECT_CATALOG 角色中，所以直接赋权 SELECT_CATALOG 就可以了。
dbms_xplan 中有许多功能，我这里着重研究其中几个常用的
SQL desc dbms_xplan;
…
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_PLAN RETURNS CLOB
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
…
dbms_xplan.display
展示执行计划内容
简单的执行一次 display
SQL explain plan for insert into scott.emp values(1111, xplan , , , , , ,
Explained.
SQL select * from table(dbms_xplan.display);
7 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2137789089
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
———————————————————————————————
Statistics
———————————————————-
1890 recursive calls
40 db block gets
3635 consistent gets
0 physical reads
0 redo size
1102 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
7 rows processed
SQL select * from scott.emp where empno=1111;
no rows selected
—explain plan for，dbms_xplan.display 都没有执行 sql。explain plan for 是产生 sql 的执行计划并保存在 PLAN_TABLE 表中，dbms_xplan.display 从 PLAN_TABLE 中提取计划并展示出来。
定义
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);
table_name：指定的是计划的存储表表名（不是执行表的表名）。默认是 PLAN_TABLE。
statement_id：指定计划的 statement_id，如果没有指定，则该值为 explain plan 的 statement_id , 如果没有 expain plan，则该值为最近一次解释的执行计划。
format：解释计划的 level
? BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option. 展示最少的信息
? TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below). 默认值
? SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. 没有并发信息
? ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed). 展示最多的信息，包含了分布式操作的远程 sql 执行信息
filter_preds：sql 过滤，限制从计划表中返回的行。
dbms_xplan.display_awr
展示在 awr 中存储的 sql 的执行计划
定义
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);
sql_id: 在 dba_hist_sqltext 中可以找到 sql_id
plan_hash_value:sql 执行计划的 hash 值。如果该值被忽略，则函数返回 sql_id 中的所有执行计划
db_id：database_id。如果不知道则该值为 V$DATABASE 视图中的 database_id，也就是本地数据库。
format：与 display 类似。总共有 4 个 level：BASIC，TYPICAL,SERIAL,ALL。
SQL select * from scott.emp where rownum
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20
—手动生成快照
SQL EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
SQL select sql_id,sql_text from dba_hist_sqltext where sql_text like %rownum%
SQL_ID
————-
SQL_TEXT
——————————————————————————–
6yzbcy3x0yr1j
insert into wrh$_dispatcher (snap_id, dbid, instance_number, name, serial#,
fsbqktj5vw6n9
select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags,
cv959u044n88s
select 1 from sys.aq$_subscriber_table where rownum 2 and subscriber_id 0 a
…
SQL_ID
————-
SQL_TEXT
——————————————————————————–
bd3tcy3ar02px
select * from scott.emp where rownum =1
7 rows selected.
SQL
—指定 awr 中存在 sql
SQL select * from table(dbms_xplan.display_awr( bd3tcy3ar02px
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID bd3tcy3ar02px
——————–
select * from scott.emp where rownum =1
Plan hash value: 1973284518
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | COUNT STOPKEY | | | | | |
PLAN_TABLE_OUTPUT
——————————————————————————–
| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |
—————————————————————————
Note
—–
– dynamic sampling used for this statement (level=2)
18 rows selected.
SQL
dbms_xplan.display_cursor
展示 cursor 中的执行计划
定义
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT ‘TYPICAL
sql_id
cursor_child_no: 子游标标志。如果没有指定，则展示 sql_id 下的所有执行计划
format
默认值执行
SQL select * from scott.emp where rownum
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
—默认查询了当前 session 最后一次执行 sql
SQL select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 90ud69jbjz75c, child number 0
————————————-
select * from scott.emp where rownum =2
Plan hash value: 1973284518
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
PLAN_TABLE_OUTPUT
——————————————————————————–
| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(ROWNUM =2)
Note
—–
– dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
——————————————————————————–
23 rows selected.
SQL
指定 sql_id 查找 plan，首先要找到 sql_id
SQL select sql_id,sql_text from v$sqlarea where sql_text like %rownum =2%
SQL_ID
————-
SQL_TEXT
——————————————————————————–
75gpskbx0uk8w
select sql_id,sql_text from v$sqlarea where sql_text like %rownum =2%
90ud69jbjz75c
select * from scott.emp where rownum =2
SQL select * from table(dbms_xplan.display_cursor( 90ud69jbjz75c
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 90ud69jbjz75c, child number 0
————————————-
select * from scott.emp where rownum =2
Plan hash value: 1973284518
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
PLAN_TABLE_OUTPUT
——————————————————————————–
| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(ROWNUM =2)
Note
—–
– dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
——————————————————————————–
23 rows selected.
SQL
如果 sql 不在内存中，则不能通过 cursor 方式查询
SQL select * from table(dbms_xplan.display_cursor( 90ud69jbjz75c
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID: 90ud69jbjz75c, child number: 0 cannot be found
这时就可以通过生成快照的方式从 awr 中查询 plan
以上是“如何使用 dbms_xplan 查看执行计划”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
