Oracle 獲取SQL執行計劃方法
Oracle 獲取SQL執行計劃方法
方法一：DBMS_XPLAN.DISPLAY_CURSOR
方法二：explain plan for ......
方法三：Tuning SQL*Plus(autotrace)
方法四：*+ gather_plan_statistics *
方法五：10046
方法六: 10053
方法七：awrsqrpt.sql
方法八：statistics_level=all
方法一：DBMS_XPLAN.DISPLAY_CURSOR
(1)從共享池裡獲取
SELECT * FROM TABLE(dbms_xplan.display_cursor('SQL_ID'));
By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session.
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.
In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Example:
SYS@orcl> SELECT /* TOTO */ ename, dname FROM scott.dept d join scott.emp e USING (deptno);
14 rows selected.
SYS@orcl> SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%TOTO%';
--9dpnjdgna1k40
SYS@orcl> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9dpnjdgna1k40',0));
SQL_ID 9dpnjdgna1k40, child number 0
-------------------------------------
SELECT /* TOTO */ ename, dname FROM scott.dept d join scott.emp e
USING (deptno)
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
24 rows selected.
或者直接查詢：
SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';
優點：
(1)知道sql_id就可以得到執行計劃，和explain plan for一樣無須執行；
(2)可以得到真實執行計劃；
缺點:
(1)沒有輸出執行時的相關統計資訊(例如:邏輯讀，遞迴呼叫次數，物理讀等)
(2)無法判斷處理多少上；
(3)無法判斷表訪問多少次；
適用：
一個SQL對應多個執行計劃；
方法二： explain plan for獲取
(1)EXPLAIN PLAN FOR XXX;
(2)select * from table(DBMS_XPLAN.DISPLAY);
(3)從awr效能檢視裡獲取
SELECT * FROM TABLE(dbms_xplan.display_awr('SQL_ID'));
DISPLAY Function
This table function displays the contents of the plan table.
In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included).
You can apply a predicate on the specified table to select rows of the plan to display.
Syntax
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);
Example:
(1)EXPLAIN PLAN FOR XXX;
(2)select * from table(DBMS_XPLAN.DISPLAY);
SYS@orcl>
EXPLAIN PLAN FOR
SELECT *
FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno
AND e.ename = 'CLARK';
Explained.
SYS@orcl> SET LINESIZE 130
SYS@orcl> SET PAGESIZE 0
SYS@orcl> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='CLARK')
4 - access("E"."DEPTNO"="D"."DEPTNO")
18 rows selected.
---如果執行計劃下方出現'PLAN_TABLE' is old version提示，需要重新生成plan_table;
SQL> drop table PLAN_TABLE;
SQL> @?/rdbms/admin/utlxplan.sql
優點：
(1)無需真正的執行，快捷方便；
缺點：
(1)沒有輸出執行時的相關統計資訊
(產生多少邏輯讀，多少次遞迴呼叫，多少次物理讀等情況)
(2)無法判斷處理多少行；
(3)無法判斷表被訪問多少次；
適用於:
SQL執行很長時間才出結果或返回不了結果;
方法三：Tuning SQL*Plus(autotrace)
Tracing Statements
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
Controlling the Autotrace Report
You can control the report by setting the AUTOTRACE system variable.
(1)SET AUTOTRACE OFF:
No AUTOTRACE report is generated. This is the default.
(2)SET AUTOTRACE ON EXPLAIN:
The AUTOTRACE report shows only the optimizer execution path
(3)SET AUTOTRACE ON STATISTICS:
The AUTOTRACE report shows only the SQL statement execution statistics.
(4)SET AUTOTRACE ON:
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
(5)SET AUTOTRACE TRACEONLY:
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.
If STATISTICS is enabled, query data is still fetched, but not printed.
To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you.
DBA privileges are required to grant the PLUSTRACE role.
(1)Creating a PLAN_TABLE
CONNECT HR
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
(2)Creating the PLUSTRACE Role
CONNECT / AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
(3)Granting the PLUSTRACE Role
CONNECT / AS SYSDBA
GRANT PLUSTRACE TO HR;
Example:
SYS@orcl> set autotrace on
SYS@orcl>
SELECT /* TOTO */ ename, dname FROM scott.dept d join scott.emp e USING (deptno);
......
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
819 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
報錯SP2-0618需要執行plustrce.sql
SQL> conn scott/tiger
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;
優點：
(1)可以輸出執行時的相關統計資訊(產生多少邏輯讀，多少次遞迴呼叫，多少次物理讀)
(2)traceonly可以控制返回結果不輸出；
缺點：
(1)必須等到語句真正執行完畢後，才可以出結果；
(2)無法看到表被訪問多少次；
方法四：*+ gather_plan_statistics *
SYS@orcl> set linesize 130
SYS@orcl> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SYS@orcl> SELECT /*+ gather_plan_statistics */
ename, dname
FROM scott.dept d
join scott.emp e
USING (deptno);
......
14 rows selected.
SYS@orcl> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4y8f4mhhn471d, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ ename, dname FROM scott.dept d
join scott.emp e USING (deptno)
Plan hash value: 844388907
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 7 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 14 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
filter("D"."DEPTNO"="E"."DEPTNO")
24 rows selected.
(1)starts為該SQL執行的次數；
(2)E-Rows為執行計劃預計的行數；
(3)A-Rows為實際返回的行數。A-Rows和E-Rows做比較，就可以確定哪一步執行計劃出了問題。
(4)A-Time為每一步實際執行的時間(HH:MM:SS.FF),根據這一行可以知道該SQL耗時在哪個地方；
(5)buffers為每一步實際執行的邏輯讀或一致性讀；
(6)Reads為物理讀；
優點:
(1)可以清晰的從STARTS得出表被訪問多少次；
(2)可以清晰的從E-ROWS和A-ROWS中得到預測的行數和真實的行數，從而可以準確判斷Oracle評估是否準確；
(3)BUFFER顯示真實的邏輯讀數量；
缺點:
(1)必須要等到語句真正執行完畢後，才能出結果；
(2)無法控制記錄不打屏輸出，沒有類似autotrace的traceonly的功能；
(3)看不出遞迴呼叫的次數，看不出物理讀；
適用：
獲取表被訪問的次數；
方法五：10046
(1)
設定追蹤識別符號
alter session set tracefile_identifier='10046';
開啟跟蹤
alter session set events '10046 trace name context forever, level 12';
執行語句
select count(*) from all_objects;
關閉10046追蹤
alter session set events '10046 trace name context off';
(2)
oradebug setmypid;
oradebug unlimit;
oradebug event 10046 trace name context forever ,level 12;
SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno);
oradebug tracefile_name;
Oradebug event 10046 trace name context off;
Example 1:
SYS@orcl> alter session set tracefile_identifier='10046';
SYS@orcl> alter session set events '10046 trace name context forever, level 12';
SYS@orcl> SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno);
SYS@orcl> alter session set events '10046 trace name context off';
[oracle@uf2010813 trace]$ pwd
/opt/oracle/app/diag/rdbms/orcl/orcl/trace
[oracle@uf2010813 trace]$ ll -rth *10046*
-rw-r-----. 1 oracle oinstall 135 Jul 31 21:55 orcl_ora_29418_10046.trm
-rw-r-----. 1 oracle oinstall 3.8K Jul 31 21:55 orcl_ora_29418_10046.trc
[oracle@uf2010813 trace]$ tkprof orcl_ora_29418_10046.trc 10046.log sys=no
Example 2:
SYS@orcl> oradebug setmypid;
SYS@orcl> oradebug unlimit;
SYS@orcl> oradebug event 10046 trace name context forever ,level 12;
SYS@orcl> SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno);
SYS@orcl> oradebug tracefile_name;
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_31388.trc
SYS@orcl> Oradebug event 10046 trace name context off;
[oracle@uf2010813 trace]$ tkprof orcl_ora_31388.trc 10046.log1 sys=no
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 6.17 17.25
Disk file operations I/O 2 0.00 0.00
db file sequential read 4 0.02 0.04
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 23 0.01 0.12
asynch descriptor resize 1 0.00 0.00
1 user SQL statements in session.
66 internal SQL statements in session.
67 SQL statements in session.
********************************************************************************
Trace file: orcl_ora_31388.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
66 internal SQL statements in trace file.
67 SQL statements in trace file.
13 unique SQL statements in trace file.
1390 lines in trace file.
0 elapsed seconds in trace file.
優點:
(1)可以看出SQL語句對應的等待事件；
(2)如果SQL語句中有函式呼叫，函式中又有SQL，將會被列出；
(3)可以方便的看出處理的行數，產生的物理讀，邏輯讀；
(4)可以方便的看出解析時間和執行計劃；
(5)可以跟蹤整個程式包；
缺點：
(1)步驟繁瑣；
(2)無法判斷表被訪問多少次；
(3)執行計劃中謂詞部分不能清晰展現出來；
適用：如果SQL中含函式，函式中又套SQL等，即存在多層呼叫，想準確分析只能用方法；
方法六:10053
(1)
alter session set tracefile_identifier='10053';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
select count(*) from dba_users;
ALTER SESSION SET EVENTS '10053 trace name context off';
(2)
oradebug setmypid;
oradebug unlimit;
oradebug event 10053 trace name context forever, level 1;
SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno);
oradebug tracefile_name;
Oradebug event 10053 trace name context off;
Example 1:
SYS@orcl> alter session set tracefile_identifier='10053';
SYS@orcl> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
SYS@orcl> select count(*) from dba_users;
SYS@orcl> ALTER SESSION SET EVENTS '10053 trace name context off';
[oracle@uf2010813 trace]$ ls -l *10053*
-rw-r-----. 1 oracle oinstall 566260 Aug 1 00:27 orcl_ora_13125_10053.trc
-rw-r-----. 1 oracle oinstall 119517 Aug 1 00:27 orcl_ora_13125_10053.trm
Example 2:
SYS@orcl> oradebug setmypid;
SYS@orcl> oradebug unlimit;
SYS@orcl> oradebug event 10053 trace name context forever, level 1;
SYS@orcl> SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno);
SYS@orcl> oradebug tracefile_name;
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11907_10053.trc
SYS@orcl> Oradebug event 10053 trace name context off;
方法七： awrsqrpt.sql
(1)@?/rdbms/admin/awrsqrpt.sql
Type Specified: html
Enter value for num_days: 1
Enter value for begin_snap: 21034
Enter value for end_snap: 21035
Enter value for sql_id: 3ag4uwm6ypra1
Enter value for report_name: awrsqlrpt_0731.html
[oracle@uf2010813 ~]$ ls -l awrsqlrpt_0731.html
-rw-r--r--. 1 oracle oinstall 13773 Aug 1 03:03 awrsqlrpt_0731.html
---Exec dbms_workload_repository.create_snapshot;
方法八： statistics_level=all
(1)alter session set statistics_level=all;
(2)執行SQL;
(3)檢視執行計劃；
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
Example:
SYS@orcl> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SYS@orcl> alter session set statistics_level=all;
SYS@orcl> set linesize 130
SYS@orcl> SELECT /* TOTO */ ename, dname FROM scott.dept d join scott.emp e USING (deptno);
SYS@orcl> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 72zuupmcd922s, child number 0
-------------------------------------
SELECT /* TOTO */ ename, dname FROM scott.dept d join scott.emp e
USING (deptno)
Plan hash value: 844388907
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 7 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 14 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
filter("D"."DEPTNO"="E"."DEPTNO")
24 rows selected.
參考至:《循規蹈矩：快速讀懂SQL執行計劃的套路與工具》---樑敬彬
更多資訊見官方文件：
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm#ARPLS378
DBMS_XPLAN
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views. Finally, it displays plans from a SQL plan baseline.
Overview
The DBMS_XPLAN package supplies five table functions:
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037231
Tuning SQL*Plus
SQL*Plus User's Guide and Reference
Statistics
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.
(1)recursive calls
Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
(2)db block gets
Number of times a CURRENT block was requested.
(3)consistent gets
Number of times a consistent read was requested for a block
(4)physical reads
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
(5)redo size
Total amount of redo generated in bytes
(6)bytes sent through SQL*Net to client
Total number of bytes sent to the client from the foreground processes.
(7)bytes received through SQL*Net from client
Total number of bytes received from the client over Oracle Net.
(8)SQL*Net round-trips to/from client
Total number of Oracle Net messages sent to and received from the client
(9)sorts (memory)
Number of sort operations that were performed completely in memory and did not require any disk writes
(10)sorts (disk)
Number of sort operations that required at least one disk write
(11)rows processed
Number of rows processed during the operation
STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:
(1)Automatic Workload Repository (AWR) Snapshots
(2)Automatic Database Diagnostic Monitor (ADDM)
(3)All server-generated alerts
(4)Automatic SGA Memory Management
(5)Automatic optimizer statistics collection
(6)Object level statistics
(7)End to End Application Tracing (V$CLIENT_STATS)
(8)Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
(9)Service level statistics
(10)Buffer cache advisory
(11)MTTR advisory
(12)Shared pool sizing advisory
(13)Segment level statistics
(14)PGA Target advisory
(15)Timed statistics
(16)Monitoring of statistics
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2142873/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle獲取執行計劃的方法Oracle
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 獲取SQL執行計劃SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle 獲取執行計劃的幾種方法Oracle
- 獲取執行計劃的方法
- 獲取SQL執行計劃的方式：SQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- 執行計劃-1：獲取執行計劃
- 介紹幾種獲取SQL執行計劃的方法（上）SQL
- oracle 9i 獲取sql執行計劃(書寫長的sql)OracleSQL
- 獲取執行計劃的6種方法
- oracle dbms_xplan獲取執行計劃Oracle
- Oracle10g如何獲取執行計劃Oracle
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- Oracle sql執行計劃OracleSQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle中跟蹤sql執行計劃的方法OracleSQL
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 獲取執行計劃之Autotrace
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- 獲得目標SQL語句執行計劃的方法SQL
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- 檢視Oracle SQL執行計劃方法比較、分析OracleSQL
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- 如何獲取真實的執行計劃
- 檢視SQL的執行計劃方法SQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle 檢視SQL的執行計劃OracleSQL
- Oracle手動固定SQL執行計劃OracleSQL
- 利用statspack來獲取生成環境中top SQL及其執行計劃SQL
- sql 執行計劃SQL
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 檢視sql執行計劃方法彙總SQL