數據庫進階：Oracle中SQL語句執行效率的查找與解決

Oracle中SQL語句執行效率問題的查找與解決:

　　一、識別占用資源較多的語句的方法(4種方法)

　　1.測試組和最終用戶反饋的與反應緩慢有關的問題。

　　2.利用V_$SQLAREA視圖提供了執行的細節。(執行、讀取磁盤和讀取緩沖區的次數)

　　• 數據列

　　EXECUTIONS：執行次數

　　DISK_READS：讀盤次數

　　COMMAND_TYPE：命令類型(3:select,2:insert;6:update;7delete;47:pl/sql程序單元)

　　OPTIMIZER_MODE：優化方式

　　SQL_TEXT：Sql語句

　　SHARABLE_MEM：占用shared pool的內存多少

　　BUFFER_GETS：讀取緩沖區的次數

　　• 用途

　　1、幫忙找出性能較差的SQL語句

　　2、幫忙找出最高頻率的SQL

　　3、幫忙分析是否需要索引或改善聯接

　　監控當前Oracle:family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">的session，如出現時鐘的標志，表示此進程中的sql運行時間較長。

　　4. Trace工具：

　　a)查看數據庫服務的初始參數：timed_statistics、user_dump_dest和max_dump_file_size

　　b)Step 1: alter session set sql_trace=true

　　c)Step 2: run sql;

　　d)Step 3: alter session set sql_trace=false

　　e)Step 4:使用 “TKPROF”轉換跟蹤文件

　　f)Parse，解析數量大通常表明需要增加數據庫服務器的共享池大小，

　　query或current提取數量大表明如果沒有索引，語句可能會運行得更有效，

　　disk提取數量表明索引有可能改進性能，

　　library cache中多于一次的錯過表明需要一個更大的共享池大小

　　二、如何管理語句處理和選項

　　•基于成本(Cost Based) 和基于規則(Rule Based) 兩種優化器， 簡稱為CBO 和RBO

　　•Optimizer Mode參數值:

　　Choose：如果存在訪問過的任何表的統計數據 ，則使用基于成本的Optimizer,目標是獲得最優的通過量。如果一些表沒有統計數據，則使用估計值。如果沒有可用的統計數據，則將使用基于規則的Optimizer。

　　All_rows：總是使用基于成本的Optimizer，目標是獲得最優的通過量。

　　First_rows_n：總是使用基于成本的Optimizer，目標是對返回前N行(“n”可以是1，10，100或者1000)獲得最優的響應時間。

　　First_rows：用于向后兼容。使用成本與試探性方法的結合，以便快速傳遞前幾行。

　　RULE：總是使用基于規則的Optimizer

　　三、使用數據庫特性來獲得有助于查看性能的處理統計信息(解釋計劃和AUTOTRACE)

　　No1: Explain Plan

　　A)使用Explain工具需要創建Explain_plan表，這必須先進入相關應用表、視圖和索引的所有者的帳戶內. (@D:\oracle\ora92\rdbms\admin\utlxplan)

　　B) 表結構：

　　STATEMENT_ID：為一條指定的SQL語句確定特定的執行計劃名稱。如果在EXPLAN PLAN語句中沒有使用SET STATEMENT_ID，那么此值會被設為NULL。

　　OPERATION：在計劃的某一步驟執行的操作名稱，例如：Filters，Index，Table，Marge Joins and Table等。

　　OPTION：對OPERATION操作的補充，例如：對一個表的操作，OPERATION可能是TABLE ACCESS，但OPTION可能為by ROWID或FULL。

　　Object_Owner：擁有此database Object的Schema名或Oracle帳戶名。

　　Object_name：Database Object名

　　Object_type：類型，例如：表、視圖、索引等等

原文轉自：http://www.anti-gravitydesign.com