Oracle查看SQL执行计划的方式

Oracle查看sql执行计划的方式@H_404_5@@H_404_5@@H_404_5@

@H_404_5@@H_404_5@

获取Oracle sql执行计划并查看执行计划，是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式：@H_404_5@@H_404_5@

@H_404_5@@H_404_5@

基本有以下几种方式：@H_404_5@@H_404_5@

1、通过sql_trace初始化参数@H_404_5@@H_404_5@

2、通过Autotrace@H_404_5@@H_404_5@

3、通过explain plan@H_404_5@@H_404_5@

4、通过dbms_xplan.display_cursor@H_404_5@@H_404_5@

5、通过dbms_xplan.display_awr@H_404_5@@H_404_5@

6、通过10046事件@H_404_5@@H_404_5@

@H_404_5@@H_404_5@

1、通过explain plan 工具@H_404_5@@H_404_5@

12:24:00 SCOTT@ prod>explain plan for@H_404_5@

12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788;@H_404_5@

Explained.@H_404_5@

Elapsed: 00:00:00.22@H_404_5@

12:24:16 SCOTT@ prod>select * from table(dbms_xplan.display);@H_404_5@

PLAN_TABLE_OUTPUT@H_404_5@

------------------------------------------------------------------------------------------------------@H_404_5@

Plan hash value: 2949544139@H_404_5@

--------------------------------------------------------------------------------------@H_404_5@

| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |@H_404_5@

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |@H_404_5@

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |@H_404_5@

Predicate Information (identified by operation id):@H_404_5@

---------------------------------------------------@H_404_5@

2 - access("EMPNO"=7788)@H_404_5@

14 rows selected.@H_404_5@

Elapsed: 00:00:01.14@H_404_5@

2、通过DBMS_XPLAN.display_cursor查看@H_404_5@@H_404_5@

12:52:37 SCOTT@ prod>desc dbms_xplan@H_404_5@

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE@H_404_5@

Argument Name Type In/Out Default?@H_404_5@

------------------------------ ----------------------- ------ --------@H_404_5@

sql_ID VARCHAR2 IN DEFAULT@H_404_5@

CURSOR_CHILD_NO NUMBER(38) IN DEFAULT@H_404_5@

FORMAT VARCHAR2 IN DEFAULT@H_404_5@

如果以scott用户访问需要进行授权：@H_404_5@

12:31:44 SYS@ prod>select * from dict where upper(table_name)='V$SESSION';@H_404_5@

TABLE_NAME COMMENTS@H_404_5@

------------------------------ ----------------------------------------@H_404_5@

V$SESSION Synonym for V_$SESSION@H_404_5@

Elapsed: 00:00:00.09@H_404_5@

12:31:09 SYS@ prod>grant select on V_$SESSION to scott;@H_404_5@

Grant succeeded.@H_404_5@

Elapsed: 00:00:00.10@H_404_5@

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));@H_404_5@

User has no SELECT privilege on V$sql_PLAN@H_404_5@

解决权限不足：@H_404_5@@H_404_5@

12:42:33 SYS@ prod>grant select any table to scott;@H_404_5@

12:43:46 SYS@ prod>show parameter o7@H_404_5@

NAME TYPE VALUE@H_404_5@

------------------------------------ ----------- ------------------------------@H_404_5@

O7_DICTIONARY_ACCESSIBILITY boolean TRUE@H_404_5@

12:44:54 SYS@ prod>@H_404_5@

案例：dbms_xplan.display_cursor@H_404_5@@H_404_5@

12:42:45 SCOTT@ prod>select empno,serif; font-size:14px"> EMPNO ENAME SAL DEPTNO@H_404_5@

---------- ---------- ---------- ----------@H_404_5@

7788 SCOTT 3000 20@H_404_5@

Elapsed: 00:00:00.08@H_404_5@

@H_404_5@

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,'all'));@H_404_5@@H_404_5@

sql_ID bqz9ujgnn4jzu,child number 0@H_404_5@

-------------------------------------@H_404_5@

select empno,deptno from emp where empno=7788@H_404_5@

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

| 1 | TABLE ACCESS BY INDEX ROWID@H_404_5@| EMP | 1 | 46 | 2 (0)| 00:00:01 |@H_404_5@

|* 2 | INDEX UNIQUE SCAN @H_404_5@ | PK_EMP | 1 | | 1 (0)| 00:00:01 |@H_404_5@

Query Block Name / Object Alias (identified by operation id):@H_404_5@

-------------------------------------------------------------@H_404_5@

1 - SEL$1 / EMP@SEL$1@H_404_5@

2 - SEL$1 / EMP@SEL$1@H_404_5@

Column Projection Information (identified by operation id):@H_404_5@

-----------------------------------------------------------@H_404_5@

1 - "EMPNO"[NUMBER,22],"ENAME"[VARCHAR2,10],"SAL"[NUMBER,@H_404_5@

"DEPTNO"[NUMBER,22]@H_404_5@

2 - "EMP".ROWID[ROWID,"EMPNO"[NUMBER,serif; font-size:14px"> 32 rows selected.@H_404_5@

Elapsed: 00:00:00.05@H_404_5@

案例：@H_404_5@@H_404_5@

12:49:10 SCOTT@ prod>select empno,serif; font-size:14px"> Elapsed: 00:00:00.00@H_404_5@

12:50:06 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,serif; font-size:14px"> | 0 | SELECT STATEMENT | | | | 2 (100)| |@H_404_5@

Outline Data@H_404_5@

-------------@H_404_5@

/*+@H_404_5@

BEGIN_OUTLINE_DATA@H_404_5@

IGNORE_OPTIM_EMBEDDED_HINTS@H_404_5@

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')@H_404_5@

DB_VERSION('11.2.0.1')@H_404_5@

ALL_ROWS@H_404_5@

OUTLINE_LEAF(@"SEL$1")@H_404_5@

INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))@H_404_5@

END_OUTLINE_DATA@H_404_5@

*/@H_404_5@

46 rows selected.@H_404_5@

Elapsed: 00:00:00.06@H_404_5@

12:50:21 SCOTT@ prod>@H_404_5@

@H_404_5@@H_404_5@这种方法在 sqlPLUS中查看刚执行过的 sql sql sql的执行计划。@H_404_5@@H_404_5@

-- dbms_xplan.display_cursor传入的前两个参数值均为 null,null第三个参数是 "advanced"第三个参数也可以是 "all"得到的显示结果，@H_404_5@少了 "Outline data"部分的内容。@H_404_5@

sql>select sql_text,sql_id,hash_value,child_number from v$sql@H_404_5@

2* where sql_text like 'select empno,sal%'@H_404_5@

sql_TEXT sql_ID HASH_VALUE CHILD_NUMBER@H_404_5@

-------------------------------------------------- ------------- ---------- ------------@H_404_5@

bqz9ujgnn4jzu 3913435130 0@H_404_5@@H_404_5@@H_404_5@

=7788@H_404_5@

Elapsed: 00:00:00.04@H_404_5@

13:00:25 SCOTT@ prod>select * from table(dbms_xplan.display_cursor('bqz9ujgnn4jzu',serif; font-size:14px"> Elapsed: 00:00:00.14@H_404_5@

3、通过DBMS_XPLAN.display_awr@H_404_5@@H_404_5@@H_404_5@

使用方法@H_404_5@dbms_xplan.display_cursor@H_404_5@@H_404_5@能够得到sql执行计划的前提条件是该sql还在共享池中，而如果执行计划的前提条件是该sql还在共享池中，@H_404_5@@H_404_5@而如果执行计划的前提条件是该还在共享池中，而如果sql sql sql的执行计划已经被刷出共享池，那么只要该sql的执行计划@H_404_5@被ORACLE采集到 AWR Repository中，就可以用该方法来查看。@H_404_5@

@H_404_5@

12:24:00 SCOTT@ prod>select empno,deptno from emp where empno=7788;@H_404_5@@H_404_5@

13:10:56 SYS@ prod>exec dbms_workload_repository.create_snapshot;@H_404_5@

PL/sql procedure successfully completed.@H_404_5@

13:11:37 SYS@ prod>alter system flush shared_poo@H_404_5@l;@H_404_5@

System altered.@H_404_5@

Elapsed: 00:00:00.31@H_404_5@

13:16:28 SYS@ prod>select * from table(dbms_xplan.display_cursor('bqz9ujgnn4jzu',0)">sql_ID: bqz9ujgnn4jzu,child number: 0 cannot be found@H_404_5@

13:21:53 SYS@ prod>desc dbms_xplan@H_404_5@

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE@H_404_5@

sql_ID VARCHAR2 IN@H_404_5@

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT@H_404_5@

DB_ID NUMBER(38) IN DEFAULT@H_404_5@

13:30:15 SCOTT@ prod>select * from table(dbms_xplan.display_awr('bqz9ujgnn4jzu'));@H_404_5@

sql_ID bqz9ujgnn4jzu@H_404_5@

--------------------@H_404_5@

select empno,serif; font-size:14px"> | 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |@H_404_5@

Elapsed: 00:00:00.30@H_404_5@

4、通过10046 事件查看@H_404_5@@H_404_5@@H_404_5@

1）查看当前session：@H_404_5@@H_404_5@

13:29:52 SYS@ prod>grant alter session to scott;@H_404_5@

13:44:31 SCOTT@ prod>alter session set events '10046 trace name context forever,level 12';@H_404_5@

Session altered.@H_404_5@

13:44:53 SCOTT@ prod>select empno,serif; font-size:14px"> 13:45:51 SCOTT@ prod>alter session set events '10046 trace name context off'@H_404_5@;@H_404_5@

Elapsed: 00:00:00.03@H_404_5@

[oracle@rh6 ~]$ ls -lt /u01/app/oracle/diag/rdbms/prod/prod/trace/|more@H_404_5@

total 1256@H_404_5@

-rw-r----- 1 oracle oinstall 27801 May 16 13:46 prod_ora_4995.trc@H_404_5@

-rw-r----- 1 oracle oinstall 177 May 16 13:46 prod_ora_4995.trm@H_404_5@

-rw-r----- 1 oracle oinstall 1122 May 16 13:34 prod_j000_5188.trc@H_404_5@

-rw-r----- 1 oracle oinstall 59 May 16 13:34 prod_j000_5188.trm@H_404_5@

......@H_404_5@

[oracle@rh6 ~]$ tkprof /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_4995.trc /home/oracle/emp_0416.txt sys=no@H_404_5@

TKPROF: Release 11.2.0.1.0 - Development on Fri May 16 13:47:41 2014@H_404_5@

[oracle@rh6 ~]$ cat emp_0416.txt@H_404_5@

sql ID: bqz9ujgnn4jzu@H_404_5@

Plan Hash: 2949544139@H_404_5@

404_5@

from@H_404_5@

emp where empno=7788@H_404_5@

call count cpu elapsed disk query current rows@H_404_5@

------- ------ -------- ---------- ---------- ---------- ---------- ----------@H_404_5@

Parse 1 0.07 0.09 1 66 0 0@H_404_5@

Execute 1 0.00 0.00 0 0 0 0@H_404_5@

Fetch 2 0.00 0.00 0 2 0 1@H_404_5@

total 4 0.07 0.09 1 68 0 1@H_404_5@

Misses in library cache during parse: 1@H_404_5@

Optimizer mode: ALL_ROWS@H_404_5@

Parsing user id: 84 @H_404_5@

Rows Row Source Operation@H_404_5@

------- ---------------------------------------------------@H_404_5@

1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)@H_404_5@

1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)@H_404_5@

Elapsed times include waiting on following events:@H_404_5@

Event waited on Times Max. Wait Total Waited@H_404_5@

---------------------------------------- Waited ---------- ------------@H_404_5@

db file sequential read 1 0.00 0.00@H_404_5@

sql*Net message to client 2 0.00 0.00@H_404_5@

sql*Net message from client 2 19.13 19.13@H_404_5@

********************************************************************************@H_404_5@

2）查看其它session：@H_404_5@@H_404_5@

14:12:23 SYS@ prod>select sid,serial#,username from v$session where username is not null;@H_404_5@

SID SERIAL# USERNAME@H_404_5@

---------- ---------- ------------------------------@H_404_5@

1 5 SYS@H_404_5@

42 9@H_404_5@SCOTT@H_404_5@

6 rows selected.@H_404_5@

14:12:47 SYS@ prod>desc dbms_monitor@H_404_5@

PROCEDURE SESSION_TRACE_DISABLE@H_404_5@

SESSION_ID BINARY_INTEGER IN DEFAULT@H_404_5@

SERIAL_NUM BINARY_INTEGER IN DEFAULT@H_404_5@

PROCEDURE SESSION_TRACE_ENABLE@H_404_5@

WAITS BOOLEAN IN DEFAULT@H_404_5@

BINDS BOOLEAN IN DEFAULT@H_404_5@

PLAN_STAT VARCHAR2 IN DEFAULT@H_404_5@

14:13:11 SCOTT@ prod>select sid from v$mystat where rownum=1;@H_404_5@

SID@H_404_5@

----------@H_404_5@

42@H_404_5@

14:13:41 SYS@ prod>exec dbms_monitor.SESSION_TRACE_ENABLE(42,9,waits=>true,binds=>true);@H_404_5@

14:13:25 SCOTT@ prod>select empno,deptno from emp where empno=7369;@H_404_5@

7369 SMITH 800 20@H_404_5@

14:14:29 SYS@ prod>exec dbms_monitor.SESSION_TRACE_disable(42,9);@H_404_5@

Elapsed: 00:00:00.02@H_404_5@

sql ID: fyydvbdw2uq6q@H_404_5@

emp where empno=7369@H_404_5@

Parse 1 0.00 0.00 0 0 0 0@H_404_5@

total 4 0.00 0.00 0 2 0 1@H_404_5@

------- ---------------------------------------------------@H_404_5@

1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)@H_404_5@

1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)@H_404_5@

Elapsed times include waiting on following events:@H_404_5@

Event waited on Times Max. Wait Total Waited@H_404_5@

---------------------------------------- Waited ---------- ------------@H_404_5@

sql*Net message to client 2 0.00 0.00@H_404_5@

sql*Net message from client 1 0.00 0.00@H_404_5@

5、通过autotrace查看@H_404_5@@H_404_5@@H_404_5@

SET AUTOTRACE ON@H_404_5@
SET AUTOTRACE TRACEONLY@H_404_5@
SET AUTOTRACE TRACEONLY EXPLAIN@H_404_5@
--使用SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY时，目标sql已经被执行过，所以在SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY 的情况下能看到目标sql@H_404_5@
的实际消耗情况。@H_404_5@
--使用SET AUTOTRACE TRACEONLY EXPLAIN时，如果执行的是SELECT语句，则该sql并没有被执行，但如果执行的是DML语句，情况就不一样了，此时的DML语句是会@H_404_5@
被ORACLE执行的。@H_404_5@
--需要特别说明的是，虽然使用SET AUTOTRACE命令所得到的执行计划可能是不准确的，因为SET AUTOTRACE命令所显示的执行计划都是源自于explain plan 命令。@H_404_5@@H_404_5@

案例：@H_404_5@@H_404_5@@H_404_5@

@H_404_5@@H_404_5@@H_404_5@

15:32:11 SYS@ prod>conn scott/tiger@H_404_5@

Connected.@H_404_5@

15:32:17 SCOTT@ prod>set autotrace on@H_404_5@@H_404_5@

15:32:21 SCOTT@ prod>select empno,simsun"> EMPNO ENAME SAL DEPTNO@H_404_5@

---------- ---------- ---------- ----------@H_404_5@

7788 SCOTT 3000 20@H_404_5@

Elapsed: 00:00:00.03@H_404_5@

Execution Plan@H_404_5@

----------------------------------------------------------@H_404_5@

Plan hash value: 2949544139@H_404_5@

--------------------------------------------------------------------------------------@H_404_5@

| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |@H_404_5@

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |@H_404_5@

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |@H_404_5@

Predicate Information (identified by operation id):@H_404_5@

---------------------------------------------------@H_404_5@

2 - access("EMPNO"=7788)@H_404_5@

Statistics@H_404_5@

168 recursive calls@H_404_5@

0 db block gets@H_404_5@

38 consistent gets@H_404_5@

0 physical reads@H_404_5@

0 redo size@H_404_5@

736 bytes sent via sql*Net to client@H_404_5@

523 bytes received via sql*Net from client@H_404_5@

2 sql*Net roundtrips to/from client@H_404_5@

5 sorts (memory)@H_404_5@

0 sorts (disk)@H_404_5@

1 rows processed@H_404_5@

15:32:26 SCOTT@ prod>set autotrace trace@H_404_5@@H_404_5@

15:32:49 SCOTT@ prod>select empno,simsun">Elapsed: 00:00:00.02@H_404_5@

0 recursive calls@H_404_5@

2 consistent gets@H_404_5@

0 sorts (memory)@H_404_5@

15:34:59 SCOTT@ prod>set autotrace on exp@H_404_5@@H_404_5@

15:35:04 SCOTT@ prod>select empno,simsun">15:35:12 SCOTT@ prod>set autotrace on statis@H_404_5@@H_404_5@

15:35:20 SCOTT@ prod>select empno,simsun">15:35:26 SCOTT@ prod>@H_404_5@

@H_404_5@@H_404_5@@至此，Oracle下查看sql执行计划的几种方式介绍完毕，大家可根据自己的爱好来使用！@H_404_5@@H_404_5@

Oracle查看SQL执行计划的方式

猜你在找的Oracle相关文章