可以通过这个列，找到存过中最消耗资源的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
