oracle 監控SQL 效能

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

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