可以通過這個列，找到存過中最消耗資源的SQL，或者DDL遞歸調用中，最慢得SQL語句 SELECT sql_id,count(*) FROM v$active_session_history WHERE TOP_LEVEL_SQL_ID='5w6mc35fa18tk' GROUP BY sql_id ORDER BY 2 DESC;
可以通過這些列，找到SQL語句最慢得地方，就需要優化這個 SELECT A.SQL_PLAN_HASH_VALUE, A.SQL_PLAN_LINE_ID, A.SQL_PLAN_OPERATION, A.SQL_PLAN_OPTIONS, B.OWNER || '.' || B.OBJECT_NAME OBJECT_NAME, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY A, DBA_OBJECTS B WHERE A.SQL_ID = '11jpuymjh9vsc' AND A.CURRENT_OBJ# = B.OBJECT_ID(+) GROUP BY A.SQL_PLAN_HASH_VALUE, A.SQL_PLAN_LINE_ID, A.SQL_PLAN_OPERATION, A.SQL_PLAN_OPTIONS, B.OWNER || '.' || B.OBJECT_NAME ORDER BY COUNT(*) DESC;
判斷一下一個SQL，有沒有出現執行的很慢的時候，比如平時1s，有段時間，執行超過12s SELECT SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY A WHERE A.SQL_ID = '11jpuymjh9vsc' GROUP BY SQL_ID, SQL_EXEC_START, SQL_EXEC_ID ORDER BY COUNT(*) DESC
可以檢查TX等待柱塞的行，通過拼裝ROWID可以找到柱塞的行。 SELECT A.SQL_ID, A.CURRENT_OBJ#, A.CURRENT_FILE#, A.CURRENT_BLOCK#, A.CURRENT_ROW#, COUNT(*) FROM dba_hist_active_sess_history A WHERE A.EVENT = 'enq: TX - row lock contention' GROUP BY A.SQL_ID, A.CURRENT_OBJ#, A.CURRENT_FILE#, A.CURRENT_BLOCK#, A.CURRENT_ROW# ORDER BY COUNT(*) DESC
10.Time Mobel TIME_MODEL 后面的IN,按照二進制組合起來的值，在這次采樣間隔內，會話做了那些操作 IN_CONNECTION_MGMT connection management call elapsed time IN_PARSE parse time elapsed IN_HARD_PARSE hard parse elapsed time IN_SQL_EXECUTION sql execute elapsed time IN_PLSQL_EXECUTION PL/SQL execution elapsed time IN_PLSQL_RPC inbound PL/SQL rpc elapsed time IN_PLSQL_COMPILATION PL/SQL compilation elapsed time IN_JAVA_EXECUTION Java execution elapsed time IN_BIND repeated bind elapsed time IN_CURSOR_CLOSE IN_SEQUENCE_LOAD sequence load elapsed time
當AWR中顯示某個TM存在問題時，通過這些列，找到TOP 進程或者SQL 存在硬解析的SQL，結果應該和v$sql去比較下 SELECT SQL_PLAN_HASH_VALUE, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY WHERE IN_HARD_PARSE = 'Y' GROUP BY SQL_PLAN_HASH_VALUE ORDER BY 2 DESC
統計時間內，物理讀/寫/心跳流量高的SQL SELECT SQL_ID, SUM(DELTA_READ_IO_REQUESTS), SUM(DELTA_WRITE_IO_REQUESTS), SUM(DELTA_READ_IO_BYTES), SUM(DELTA_WRITE_IO_BYTES), SUM(DELTA_INTERCONNECT_IO_BYTES) FROM V$ACTIVE_SESSION_HISTORY GROUP BY SQL_ID ORDER BY 2 DESC
select * from ( select sample_time,session_id,sql_id,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED from v$active_session_history where TEMP_SPACE_ALLOCATED is not null order by TEMP_SPACE_ALLOCATED desc ) where rownum<=20
