Oracle學習系列—資料庫優化—效能優化工具
Oracle中對於SQL的執行計劃和跟蹤功能是很強大的,其中包括EXPLAIN PLAN,TKPROF和SQL Trace, Auto Trace.
EXPLAIN PLAN
Explain Plan語句能夠顯示優化器對SELECT,UPDATE,INSERT,DELETE等語句分析的執行計劃.一條語句的執行計劃是Oracle執行SQL的順序.行源樹是執行計劃的核心,主要包含下列資訊:
Ø 參考表的順序
Ø 每個表的訪問方式
Ø 表的連線方式
Ø 最佳化:成本和基數
Ø 分割槽
Ø 並行化
建立PLAN_TABLE表
@C:UserDefineoracleora92rdbmsadminutlxplan.sql; |
執行PLAN_TABLE
SQL> explain plan 2 set statement_id='test' for 3 select * from testindex where object_type='JAVA CLASS'; Explained |
顯示PLAN_TABLE表輸出
SQL> @C:UserDefineoracleora92rdbmsadminutlxpls.sql; SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 895 | 76075 | 27 | 1 | TABLE ACCESS BY INDEX ROWID| TESTINDEX | 895 | 76075 | 27 |* 2 | INDEX RANGE SCAN | OBJECTTYPEINDEX | 895 | | 2 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TESTINDEX"."OBJECT_TYPE"='JAVA CLASS')
13 rows selected |
自定義PLAN_TABLE表輸出
SELECT lpad('
',level-1)||operation||' '||options||' '||object_name "Plan" |
PLAN_TABLE表輸出
Plan -------------------------------------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID TESTINDEX INDEX RANGE SCAN OBJECTTYPEINDEX |
SQL Trace
SQL Trace提供了每一條SQL語句的效能資訊,產生下列統計資訊:
Ø 分析,執行和fetch數量
Ø CPU和消耗時間
Ø 物理讀和邏輯讀
Ø 處理的記錄數
Ø 庫快取的缺失數
你可以在會話或者例項級別上增強SQL Trace跟蹤.
TKPROF
你能夠使用TKPROF程式格式化跟蹤檔案的內容,把輸出寫入一個易於理解的檔案中,此外,TKPROF也能夠完成以下:
Ø 決定SQL語句的執行計劃
Ø 在資料庫中建立一個儲存統計的SQL指令碼
SQL Trace和TKPROF的步驟
1. 設定跟蹤檔案管理的初始化引數
TIMED_STATISTICS 啟用或遮蔽時間統計的收集,例如CPU和消耗時間
MAX_DUMP_FILE_SIZE 檔案的最大行數,預設為500
USER_DUMP_DEST 跟蹤檔案的目錄
2. 增強SQL Trace跟蹤,執行應用程式
使用DBMS_SESSION.SET_SQL_TRACE 儲存過程
ALTER SESSION SET SQL_TRACE=TRUE;
3. 執行TKPROF轉換SQL Trace檔案到一個易於閱讀的輸出檔案中.
TKPROF filename1 filename2
4. 解譯輸出檔案
5. 執行SQL script把統計資訊儲存到資料庫中.
SQL Trace和TKPROF全過程
顯示系統設定引數併產生Trace檔案
SQL> show parameters TIMED_STATISTICS;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ timed_statistics boolean TRUE
SQL> show parameters MAX_DUMP_FILE_SIZE;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string UNLIMITED
SQL> show parameters USER_DUMP_DEST;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string C:UserDefineoracleadminwbqudump
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered
SQL> select owner,object_name from testindex where object_type='CONTEXT';
OWNER OBJECT_NAME ------------------------------ -------------------------------------------------------------------------------- SYS LT_CTX SYS WK$CONTEXT |
使用TKPROF格式化輸出檔案
C:>CD C:UserDefineoracleadminwbqudump
C:UserDefineoracleadminwbqudump>TKPROF wbq_ora_4868.trc output.txt
TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
C:UserDefineoracleadminwbqudump>more |output.txt |
閱讀並分析格式化報表
TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: wbq_ora_4868.trc Sort options: default
******************************************************************************** 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 ******************************************************************************** select * from testindex where object_type='CONTEXT'
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 8 0 4
Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61
Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID TESTINDEX 2 INDEX RANGE SCAN OBJECTTYPEINDEX (object id 30527)
|
Autotrace Report
Autotrace Setting |
結果 |
SET AUTOTRACE OFF |
沒有自動跟蹤報告(系統預設) |
SET AUTOTRACE ON EXPLAIN |
輸出執行結果和優化器執行計劃 |
SET AUTOTRACE ON STATISTICS |
輸出執行結果和SQL語句統計資訊 |
SET AUTOTRACE ON |
輸出執行結果,優化器執行計劃和SQL語句統計資訊 |
SET AUTOTRACE TRACEONLY |
僅僅輸出優化器執行計劃和SQL語句統計資訊,不輸出結果 |
樣例
SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 12 02:16:18 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect wbq/wbq;
Connected.
SQL> SET AUTOTRACE OFF;
SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME
2 FROM TESTINDEX
3 WHERE OBJECT_TYPE='CLUSTER';
OWNER OBJECT_NAME
------------------------------ --------------------
SYS C_COBJ#
SYS C_FILE#_BLOCK#
…
10 rows selected.
SQL> SET AUTOTRACE ON;
SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME
2 FROM TESTINDEX
3 WHERE OBJECT_TYPE='CLUSTER';
OWNER OBJECT_NAME
------------------------------ --------------------
SYS C_COBJ#
SYS C_FILE#_BLOCK#
…
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=895 Bytes=32220)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=27 Card=895 Bytes=32220)
2 1 INDEX (RANGE SCAN) OF 'OBJECTTYPEINDEX' (NON-UNIQUE) (Cost=2 Card=895)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
614 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME
2 FROM TESTINDEX
3 WHERE OBJECT_TYPE='CLUSTER';
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=895 Bytes=32220)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=27 Card=895 Bytes=32220)
2 1 INDEX (RANGE SCAN) OF 'OBJECTTYPEINDEX' (NON-UNIQUE) (Cost=2 Card=895)
Statistics
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6517/viewspace-145534/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 資料庫效能優化2資料庫優化
- Oracle效能優化-資料庫CPU使用率100%Oracle優化資料庫
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- oracle 效能優化Oracle優化
- 資料庫優化 - SQL優化資料庫優化SQL
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Spark效能優化:優化資料結構Spark優化資料結構
- 資料庫效能優化有哪些方式資料庫優化
- MySQL系列:效能優化MySql優化
- Oracle 效能優化-expdp備份速度優化02Oracle優化
- Oracle 效能優化-expdp備份速度優化03Oracle優化
- 《java學習三》jvm效能優化-------調優JavaJVM優化
- 資料庫優化資料庫優化
- 資料庫優化之臨時表優化資料庫優化
- 掌握Oracle資料庫效能調優方法Oracle資料庫
- 效能優化的過程學習優化
- 1.2.9. 任務9:資料庫效能優化資料庫優化
- Part II 診斷和優化資料庫效能優化資料庫
- 資料庫優化SQL資料庫優化SQL
- MySQL資料庫優化MySql資料庫優化
- 最新IP資料庫 儲存優化 查詢效能優化 每秒解析上千萬資料庫優化
- 【前端效能優化】vue效能優化前端優化Vue
- 【資料庫】查詢優化之子連線優化資料庫優化
- iOS效能優化系列篇之“列表流暢度優化”iOS優化
- iOS效能優化系列篇之“優化總體原則”iOS優化
- 《java學習三》jvm效能優化------jconsulJavaJVM優化
- 資料庫的這些效能優化,你做了嗎?資料庫優化
- 後端思維之資料庫效能優化方案後端資料庫優化
- Django資料庫效能優化之 - 使用Python集合操作Django資料庫優化Python
- 資料庫優化建議資料庫優化
- 百萬級資料庫優化資料庫優化
- 資料庫查詢優化資料庫優化
- MYSQL資料庫------SQL優化MySql資料庫優化
- 前端效能優化(JS/CSS優化,SEO優化)前端優化JSCSS
- 效能優化篇 - Performance(工具 & api)優化ORMAPI
- 使用資源管理器優化Oracle效能AQ優化Oracle