您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle SQL 效能問題查找與解決方法

編輯：Oracle 資料庫基礎

一、識別佔用資源較多 SQL 陳述式的方法（四種）

1. 測試組與最終用戶回饋

收集關於回應緩慢的問題回饋。

2. 利用 V$SQLAREA 檢視表

提供執行細節：執行次數、讀取磁碟與緩衝區次數。

欄位	說明
EXECUTIONS	執行次數
DISK_READS	讀盤次數
COMMAND_TYPE	命令型別（3:SELECT, 2:INSERT, 6:UPDATE, 7:DELETE, 47:PL/SQL 程式單元）
OPTIMIZER_MODE	優化方式
SQL_TEXT	SQL 陳述式
SHARABLE_MEM	佔用 shared pool 記憶體大小
BUFFER_GETS	讀取緩衝區次數

用途：找出效能較差的 SQL、最高頻率的 SQL、分析是否需索引或改善聯結。

3. 監控目前 Oracle Session

若出現時鐘圖示，表示該程序中 SQL 執行時間較長。

4. Trace 工具

查看資料庫服務初始參數：timed_statistics、user_dump_dest、max_dump_file_size。
Step 1: ALTER SESSION SET SQL_TRACE=TRUE
Step 2: 執行 SQL
Step 3: ALTER SESSION SET SQL_TRACE=FALSE
Step 4: 使用 TKPROF 轉換追蹤檔案

TKPROF 分析重點：

Parse（解析）數量大 → 需增加共用池大小
Query 或 Current 擷取數量大 → 若無索引，陳述式可能執行更有效
Disk 擷取數量大 → 索引可能改善效能
Library cache 多於一次錯失 → 需較大共用池

二、管理陳述式處理與選項

優化器模式

參數值	說明
CHOOSE	若存在任何存取過資料表的統計資料，使用成本基礎優化器（CBO），目標為最佳吞吐量；若部分資料表無統計資料，使用估計值；若無可用統計資料，使用規則基礎優化器（RBO）
ALL_ROWS	總是使用 CBO，目標為最佳吞吐量
FIRST_ROWS_n	總是使用 CBO，目標為前 N 列（n=1,10,100,1000）最佳回應時間
FIRST_ROWS	向後相容，結合成本與試探方法快速傳遞前幾列
RULE	總是使用 RBO

三、使用資料庫特性取得處理統計資訊（執行計畫與 AUTOTRACE）

1. Explain Plan

A. 建立 Explain Plan 表：@ORACLE_HOME/rdbms/admin/utlxplan.sql

B. 表結構：STATEMENT_ID、OPERATION、OPTION、OBJECT_OWNER、OBJECT_NAME、OBJECT_TYPE、ID、PARENT_ID

C. 執行計畫解讀：

全表掃描：無可用索引、大量資料、小表、HWM、Rowid 掃描
索引掃描：唯一、範圍、降序範圍、跳躍、全索引、快速全索引、索引聯結、位圖聯結
選擇存取路徑：CBO 檢查 WHERE 與 FROM 子句，產生可能執行計畫，評估成本，選擇最低成本
表聯結方式：Nested Loops、Sort-Merge Join、Hash Join、索引聯結

2. AUTOTRACE

使用步驟：

以 SYSTEM 登入
建立 PLUSTRACE 角色：@ORACLE_HOME/sqlplus/admin/plustrce.sql
授權給一般使用者：GRANT PLUSTRACE TO <user>
若無 PLAN_TABLE 亦需建立

選項：

SET AUTOTRACE ON：顯示查詢結果、執行計畫、統計資料
ON STATISTICS：顯示查詢結果、統計資料，不顯示執行計畫
ON EXPLAIN：顯示查詢結果、執行計畫，不顯示統計資料
TRACEONLY：顯示執行計畫和統計結果，不含查詢結果

關鍵統計指標：recursive calls、db block gets、consistent gets、physical reads、redo size、bytes sent/received via SQL*Net、SQL*Net roundtrips、sorts (memory/disk)、rows processed