oracle 監控SQL 效能

2024-03-05 工作雜記 Oracle 系統調校

這幾天在調校系統校能，想說從資料庫快取中看看能不能找出花費系統高的系統資源，後來從網路拼拼湊湊加上過去的經驗拼湊下面的監控SQL。拼湊好後，會透過kettle 搭配系統排程匯出查詢結果。

SELECT * FROM (
SELECT
1 QUERY_STATUS,
SA.SQL_ID,
SA.SQL_TEXT,
SA.SQL_FULLTEXT,
SA.EXECUTIONS EXECUTIONS, --執行次數
ROUND(SA.ELAPSED_TIME / 1000000, 2) ELAPSED_TIME, --總共執行時間(秒)
ROUND(SA.ELAPSED_TIME / 1000000 / SA.EXECUTIONS, 2) ELAPSED_TIME_AVERAGE, --平均執行時間(秒)
ROUND(SA.CPU_TIME / 1000000, 2) CPU_TIME,  --CPU 執行時間
SA.DISK_READS,
ROUND(SA.PLSQL_EXEC_TIME / 1000000, 2) PLSQL_EXEC_TIME, --(秒)
ROUND(SA.USER_IO_WAIT_TIME / 1000000, 2) USER_IO_WAIT_TIME, --(秒)
SA.COMMAND_TYPE,
SA.HASH_VALUE,
SA.PARSING_USER_ID PARSING_USER_ID,
U.USERNAME USERNAME --帳號名稱
FROM V$SQLAREA SA
LEFT JOIN ALL_USERS U ON SA.PARSING_USER_ID = U.USER_ID
WHERE SA.EXECUTIONS > 0
ORDER BY (SA.ELAPSED_TIME / SA.EXECUTIONS) DESC
) WHERE ROWNUM <= 50;

V$SQLAREA table 欄位說明

Name                                      Null?    Type
----------------------------------------- -------- --------------
SQL_TEXT                                           VARCHAR2(1000)
SHARABLE_MEM                                       NUMBER
PERSISTENT_MEM                                     NUMBER
RUNTIME_MEM                                        NUMBER
SORTS                                              NUMBER
VERSION_COUNT                                      NUMBER
LOADED_VERSIONS                                    NUMBER
OPEN_VERSIONS                                      NUMBER
USERS_OPENING                                      NUMBER
EXECUTIONS                                         NUMBER
USERS_EXECUTING                                    NUMBER
LOADS                                              NUMBER
FIRST_LOAD_TIME                                    VARCHAR2(19)
INVALIDATIONS                                      NUMBER
PARSE_CALLS                                        NUMBER
DISK_READS                                         NUMBER
BUFFER_GETS                                        NUMBER
ROWS_PROCESSED                                     NUMBER
COMMAND_TYPE                                       NUMBER
OPTIMIZER_MODE                                     VARCHAR2(25)
PARSING_USER_ID                                    NUMBER
PARSING_SCHEMA_ID                                  NUMBER
KEPT_VERSIONS                                      NUMBER
ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
MODULE                                             VARCHAR2(64)
MODULE_HASH                                        NUMBER
ACTION                                             VARCHAR2(64)
ACTION_HASH                                        NUMBER
SERIALIZABLE_ABORTS                                NUMBER
CPU_TIME                                           NUMBER
ELAPSED_TIME                                       NUMBER
IS_OBSOLETE                                        VARCHAR2(1)
PLSQL_EXEC_TIME                                    NUMBER
USER_IO_WAIT_TIME                                  NUMBER
  • SHARABLE_MEM: Amount o sharable memory used by the SQL statement
  • PERSISTENT_MEM: Amount of persistent memory used by the statement
  • RUNTIME_MEM: Amount of runtime memory used by the statement
  • SORTS: Total sorts across all executions
  • EXECUTIONS: Total executions(執行次數)
  • PARSE_CALLS: Total parse calls across all executions
  • DISK_READS: Total disk reads across all executions
  • BUFFER_GETS: Total buffer gets across all executions
  • ROWS_PROCESSED: Total rows processed over all executions
  • OPTIMIZER_MODE: Optimizer mode used by statement
  • SERIALIZABLE_ABORTS: Number of serializable aborts over all executions
  • CPU_TIME: Total CPU time used for all executions
  • ELAPSED_TIME: Total elapsed time for all executions
  • PLSQL_EXEC_TIME: PL/SQL 執行時間
  • USER_IO_WAIT_TIME: I/O 等待的時間
    • 曾經被 dblaod 讀檔讀不到檔案拖垮資料庫效能

參考資料

Oracle SQL response time monitoring

9.69 V$SQLAREA

Relevant Topics


Recent Posts