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小時內刪除侵權內容。