(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.s...
trunc(sum(st.elapsed_time_delta) / 1000000 / 60) ela_mins from dba_hist_sqlstat st where st.sql_id in (‘ &1′) group by st.sql_id, st.plan_hash_value order by st.sql_id, cpu_mins; 5.分析对比sql语句在不同执行计划中的执行情况 select st2.sql_id, st2.plan_hash_value, st_...
6.54 DBA_HIST_SGA 6.55 DBA_HIST_SGA_TARGET_ADVICE 6.56 DBA_HIST_SGASTAT 6.57 DBA_HIST_SHARED_POOL_ADVICE 6.58 DBA_HIST_SHARED_SERVER_SUMMARY 6.59 DBA_HIST_SNAP_ERROR 6.60 DBA_HIST_SNAPSHOT 6.61 DBA_HIST_SQL_BIND_METADATA 6.62 DBA_HIST_SQL_PLAN 6.63 DBA_HIST_SQL_SUMMARY 6.64 DBA_HIST_...
TRUNC(SUM(st.CPU_TIME_DELTA) /1000000/60) CPU_MINS , DECODE( SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW , TRUNC(SUM(st.ELAPSED_TIME_DELTA) /1000000/60) ELA_MINS FROM DBA_HIST_SQLSTAT st WHERE ...
trunc(sum(st.CPU_TIME_DELTA)/1000000/60) CPU_MINS, trunc(sum(st.ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS from DBA_HIST_SQLSTAT st where st.SQL_ID in (‘&1′) group by st.SQL_ID , st.PLAN_HASH_VALUE order by st.SQL_ID, CPU_MINS; 分析对比SQL语句在不同执行计划中的执...
dba_hist_sqlstat是一个宝库,很多的sql执行统计信息都会在其中,可以基于这个数据字典分析很多的特性,比如查看某条sql语句的性能历史,分析执行计划是否稳定等等,这些功能在分析sql语句的时候是相当实用的,毕竟一个awr报告中的sql问题可能只是一个表象,如果结合历史来看就会分析出更多的因素来。 这个脚本在<<Oracle SQL高...
内存中sql执行次数、行数、平均执行时间统计 --查找某条sql_id set line 300 pages 999 col sql_text for a280 select sql_id,sql_text from gv$sql where sql_text like 'xxxxx%'; select sql_id,EXECUTIONS,ROWS_PROCESSED,ELAPSED_TIME ,round(ELAPSED_TIME/EXECUTIONS/1000,3) "每次平均时间(毫秒)" ...
DBA_HIST_SQLSTATdisplays historical information about SQL statistics. This view captures the top SQL statements based on a set of criteria and captures the statistics information fromV$SQL. The total value is the value of the statistics since instance startup. The delta value is the value of ...
sql_textfromv$sort_usage su, v$parameter p, v$session se, v$sql swherep.name='db_block_size'andsu.session_addr=se.saddrands.hash_value=su.sqlhashands.address=su.sqladdrorderbyse.username, se.sid # 查看当前临时表空间使用大小与正在占用临时表空间的sql语句 ...
TM_DELTA_CPU_TIMENUMBERYES过去TM_DELTA_TIME时间段内在 CPU 上花费的时间量 说明 该字段从 V4.2.2 版本开始引入。 TM_DELTA_DB_TIMENUMBERYES过去TM_DELTA_TIME时间段内在数据库调用中花费的时间量 说明 该字段从 V4.2.2 版本开始引入。 TOP_LEVEL_SQL_IDCHAR(32)YES顶层 SQL ID ...