Oracle获取执行计划的方法（六脉神剑）

一、explain plan for

1、explain plan for &sql_text； 2、select * from table(dbms_xplan.display());

SQL> set line222 SQL> set pagesize1000 SQL> explain plan for select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 1404 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 12 | 1404 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 12 | 1044 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 3 - filter("SAL">1000) Note ----- - dynamic sampling used for this statement (level=2) 20 rows selected.

_优点：_无需真正执行，方便快捷，与PLSQL工具的F5一样

_缺点：_没有相关统计信息输出；无法判断处理多少行；无法判断表被访问多少次。

二、set autotrace on

1、set autotrace traceonly 2、执行sql 3、set autotrace off

SQL> set autotrace -h Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式 SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告 SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息 SET AUTOTRACE ON ----------------- 包含执行计划和统计信息 SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 SQL> set autotrace traceonly SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; 12 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 1404 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 12 | 1404 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 12 | 1044 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 3 - filter("SAL">1000) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 1935 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed

_优点：_可以输出相关统计信息；可以通过traceonly来控制执行结果是否输出。

_缺点：_必须等到sql语句执行完毕；无法看到表被访问次数。

三、statistics_level=all

两种方式： 1、alter session set statistics_level=all; 2、执行sql 3、select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); 或者执行sql时加上hint 'gather_plan_statistics' 1、select /*+ gather_plan_statistics */ * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; 2、select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> alter session set statistics_level=all; SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string ALL SQL> SELECT statistics_name 2 ,session_status 3 ,system_status 4 ,activation_level 5 ,session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES ---------------------------------------------------------------- -------- -------- ------- --- Active Session History ENABLED ENABLED TYPICAL NO Adaptive Thresholds Enabled ENABLED ENABLED TYPICAL NO Automated Maintenance Tasks ENABLED ENABLED TYPICAL NO Bind Data Capture ENABLED ENABLED TYPICAL NO Buffer Cache Advice ENABLED ENABLED TYPICAL NO Global Cache CPU Statistics DISABLED DISABLED ALL NO Global Cache Statistics ENABLED ENABLED TYPICAL NO Longops Statistics ENABLED ENABLED TYPICAL NO MTTR Advice ENABLED ENABLED TYPICAL NO Modification Monitoring ENABLED ENABLED TYPICAL NO PGA Advice ENABLED ENABLED TYPICAL NO Plan Execution Sampling ENABLED ENABLED TYPICAL YES Plan Execution Statistics ENABLED DISABLED ALL YES SQL Monitoring ENABLED ENABLED TYPICAL YES Segment Level Statistics ENABLED ENABLED TYPICAL NO Shared Pool Advice ENABLED ENABLED TYPICAL NO Streams Pool Advice ENABLED ENABLED TYPICAL NO Threshold-based Alerts ENABLED ENABLED TYPICAL NO Time Model Events ENABLED ENABLED TYPICAL YES Timed OS Statistics ENABLED DISABLED ALL YES Timed Statistics ENABLED ENABLED TYPICAL YES Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO V$IOSTAT_* statistics ENABLED ENABLED TYPICAL NO 24 rows selected. SQL> alter session set statistics_level=all; Session altered. SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- ------------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 12 rows selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 3264d6n6xacac, child number 1 ------------------------------------- select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000 Plan hash value: 615168685 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 15 | | | | |* 1 | HASH JOIN | | 1 | 12 | 12 |00:00:00.01 | 15 | 1321K| 1321K| 750K (0)| | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | | |* 3 | TABLE ACCESS FULL| EMP | 1 | 12 | 12 |00:00:00.01 | 8 | | | | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 3 - filter("SAL">1000) Note ----- - dynamic sampling used for this statement (level=2) 25 rows selected. --通过hint ’gather_plan_statistics'可以不需要设置ALL SQL> select /*+ gather_plan_statistics */ * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- ------------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 12 rows selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 1kxydxbgh08q2, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000 Plan hash value: 615168685 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 15 | | | | |* 1 | HASH JOIN | | 1 | 12 | 12 |00:00:00.01 | 15 | 1321K| 1321K| 765K (0)| | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | | |* 3 | TABLE ACCESS FULL| EMP | 1 | 12 | 12 |00:00:00.01 | 8 | | | | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 3 - filter("SAL">1000) Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected.

**优点：**可以通过STRATS得出表被访问次数；可以通过E-Rows和A-Rows来判断预测行数和实际行数是否一致；可以通过Buffers来获取逻辑读数值。

_缺点：_需要sql语句执行完；必须将执行结果输出；看不出物理读数值。

四、dbms_xplan.display_cursor

1、获取sql_id 2、查看AWR和CURSOR中的执行计划 select * from table(dbms_xplan.display_awr('&sqlid')); select * from table(dbms_xplan.display_cursor('&sqlid'));

--查看AWR和CURSOR中的执行计划 select * from table(dbms_xplan.display_awr('&sqlid')); select * from table(dbms_xplan.display_cursor('&sqlid')); 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 SQL> select distinct sql_id,plan_hash_value from v$sql where sql_text like 'select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000%'; SQL_ID PLAN_HASH_VALUE ------------- --------------- 3264d6n6xacac 615168685 --获取AWR中的执行计划 SQL> select * from table(dbms_xplan.display_awr('3264d6n6xacac')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 3264d6n6xacac -------------------- select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000 Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | | 1 | HASH JOIN | | 12 | 1404 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 12 | 1044 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 19 rows selected. --获取共享池中的执行计划 SQL> select * from table(dbms_xplan.display_cursor('3264d6n6xacac')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 3264d6n6xacac, child number 0 ------------------------------------- select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000 Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN | | 12 | 1404 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 12 | 1044 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 3 - filter("SAL">1000) Note ----- - dynamic sampling used for this statement (level=2) 25 rows selected.

_优点：_通过sql_id可以立即获取执行计划，无需执行；可以得到真实执行过的执行计划。

_缺点：_没有输出相关统计信息；无法判断处理行数；无法判断表访问次数。

五、event 10046 trace

1、设置10046事件 alter session set events '10046 trace name context forever,level 12'; 2、tkprof格式化trace文件 tkprof /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc,/home/oracle/events_10046.txt 3、查看trace文件

SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- ------------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 12 rows selected. SQL> alter session set events '10046 trace name context off'; Session altered. SQL> select value from v$diag_info where name like 'Default Trace File%'; VALUE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc --通过tkprof格式化trace文件 SQL> !tkprof /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc,/home/oracle/events_10046.txt sys=no sort=prsela,exeela,fchela TKPROF: Release 11.2.0.4.0 - Development on Fri Apr 16 11:29:10 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. SQL> !cat /home/oracle/events_10046.txt Trace file: /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc Sort options: prsela exeela fchela ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: 3264d6n6xacac Plan Hash: 615168685 select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 2 18 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 15 0 12 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 2 33 0 12 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 12 12 12 HASH JOIN (cr=15 pr=0 pw=0 time=170 us cost=6 size=1404 card=12) 4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=12 us cost=3 size=120 card=4) 12 12 12 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=11 us cost=3 size=1044 card=12) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 34.46 34.46 ********************************************************************************

_优点：_可以查看SQL语句对应等待事件；函数调用的SQL将被列出；可以查看处理行数和物理逻辑读；可以看出解析时间和执行时间；可以跟踪整个程序包；

_缺点：_步骤较为繁琐；无法判断表被访问次数；

六、awrsqrpt.sql

1、@?/rdbms/admin/awrsqrpt.sql 2、输入begin snap和end snap 3、输入sql_id 4、查看sqlrpt报告

SQL> @?/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1176847559 ORCL11G 1 orcl11g Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1176847559 1 ORCL11G orcl11g orcl11g Using 1176847559 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- orcl11g ORCL11G 2 16 Apr 2021 10:05 1 3 16 Apr 2021 11:01 1 4 16 Apr 2021 11:48 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 3 Begin Snapshot Id specified: 3 Enter value for end_snap: 4 End Snapshot Id specified: 4 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: 3264d6n6xacac SQL ID specified: 3264d6n6xacac Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_3_4.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Using the report name awrsqlrpt_1_3_4.html

Oracle

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。