489 lines
20 KiB
MySQL
489 lines
20 KiB
MySQL
/*
|
|
**********************************************
|
|
AWR Queries
|
|
**********************************************
|
|
Contents
|
|
- AWR SQL Stats Query
|
|
- AWR SQL Statistics (Daily/Weekly/Monthly Totals)
|
|
- AWR Segment Statistics Query
|
|
- AWR Active Session History (ASH) queries
|
|
- AWR ASH Largest TEMP and PGA Users
|
|
|
|
*/
|
|
|
|
--**************************************************
|
|
-- AWR SQL Stats Query
|
|
--**************************************************
|
|
SELECT s.end_interval_time
|
|
, ss.instance_number
|
|
, ss.sql_id
|
|
, (ss.elapsed_time_total/1000000) "ElapsedTime (Total)"
|
|
, (ss.elapsed_time_total/1000000) / ss.executions_total "ElapsedTime/Exec"
|
|
, ss.executions_total
|
|
, ss.parsing_schema_name
|
|
, ss.*
|
|
FROM dba_hist_sqlstat ss
|
|
JOIN dba_hist_snapshot s ON s.dbid = ss.dbid AND s.instance_number = ss.instance_number AND s.snap_id = ss.snap_id
|
|
JOIN v$database d ON d.dbid = s.dbid
|
|
WHERE 1=1
|
|
-- AND s.end_interval_time BETWEEN TO_TIMESTAMP('2012-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2012-10-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
|
|
-- AND ss.sql_id = ''
|
|
-- AND upper(ss.module) like upper('bum%')
|
|
-- AND upper(ss.action) like upper('%%')
|
|
-- AND ss.sql_profile IS NOT NULL
|
|
-- AND ss.parsing_schema_name LIKE '%%'
|
|
-- AND (ss.elapsed_time_total/1000000) / ss.executions_total > 10 -- ElapsedTime/Exec > 10s
|
|
-- AND ss.executions_delta > 0
|
|
-- AND (ss.elapsed_time_total/1000000) > 100
|
|
--ORDER BY (ss.elapsed_time_total/1000000) / ss.executions_total DESC
|
|
ORDER BY (ss.elapsed_time_total/1000000) DESC
|
|
;
|
|
|
|
|
|
-- **********************************************
|
|
-- AWR SQL Statistics (Daily/Weekly/Monthly Totals)
|
|
-- **********************************************
|
|
|
|
SELECT TO_CHAR(s.end_interval_time,'YYYY-MM-DD') "Day" /* Daily */
|
|
--SELECT TO_CHAR(s.end_interval_time,'IYYY-IW') "Week" /* Weekly */
|
|
--SELECT TO_CHAR(s.end_interval_time,'YYYY-MM') "Month" /* Monthly */
|
|
, ss.sql_id "SQLId"
|
|
, MIN(s.end_interval_time) "StartTimestamp"
|
|
, MAX(s.end_interval_time) "EndTimeStamp"
|
|
, ROUND(SUM(ss.executions_delta)) "Executions"
|
|
, ROUND(SUM(ss.iowait_delta/1000000)) "IO Wait (Sec)"
|
|
, ROUND(SUM(ss.apwait_delta/1000000)) "App Wait (Sec)"
|
|
, ROUND(SUM(ss.ccwait_delta/1000000)) "Concurrency Wait (Sec)"
|
|
, ROUND(SUM(ss.clwait_delta/1000000)) "Cluster Wait (Sec)"
|
|
, ROUND(SUM(ss.plsexec_time_delta/1000000)) "PL/SQL Exec (Sec)"
|
|
, ROUND(SUM(ss.cpu_time_delta/1000000)) "CPU Time (Sec)"
|
|
, ROUND(SUM(ss.elapsed_time_delta/1000000)) "Elapsed Time (Sec)"
|
|
, ROUND(SUM(ss.rows_processed_delta)) "RowsProcessed (#)"
|
|
, ROUND(SUM(ss.sorts_delta)) "Sorts"
|
|
, ROUND(SUM(ss.buffer_gets_delta)) "Buffer Gets"
|
|
, ROUND(SUM(ss.direct_writes_delta)/1024/1024/1024) "DirectWrite (GB)"
|
|
, ROUND(SUM(ss.physical_read_bytes_delta)/1024/1024/1024) "PhyReads (GB)"
|
|
, ROUND(SUM(ss.physical_write_bytes_delta)/1024/1024/1024) "PhyWrites (GB)"
|
|
, ROUND(SUM(ss.optimized_physical_reads_delta)/1024/1024/1024) "PhyReads Optimized (GB)"
|
|
, ROUND(SUM(ss.io_offload_elig_bytes_delta)/1024/1024/1024) "PhyReads IO Offload Elig (GB)"
|
|
, ROUND(SUM(ss.io_offload_return_bytes_delta)/1024/1024/1024) "PhyReads Offload Return (GB)"
|
|
, ROUND(SUM(ss.io_interconnect_bytes_delta)/1024/1024/1024) "IO Interconnect (GB)"
|
|
FROM dba_hist_sqlstat ss
|
|
JOIN dba_hist_snapshot s ON s.dbid = ss.dbid AND s.instance_number = ss.instance_number AND s.snap_id = ss.snap_id
|
|
WHERE 1=1
|
|
-- AND ss.sql_id = '1awt7p3ua5tnz'
|
|
-- AND TO_CHAR(s.end_interval_time,'Dy') NOT IN ('Sat','Sun')
|
|
-- AND s.end_interval_time > sysdate - 30
|
|
GROUP BY TO_CHAR(s.end_interval_time,'YYYY-MM-DD') /* Daily */
|
|
--- GROUP BY TO_CHAR(s.end_interval_time,'IYYY-IW') /* Weekly */
|
|
--GROUP BY TO_CHAR(s.end_interval_time,'YYYY-MM') /* Monthly */
|
|
, ss.sql_id
|
|
ORDER BY 1 DESC
|
|
;
|
|
|
|
|
|
--**************************************************
|
|
-- AWR Segment Statistics Query
|
|
-- - Captures only top segment's statistics.
|
|
--**************************************************
|
|
SELECT s.end_interval_time
|
|
, ss.instance_number
|
|
, o.owner
|
|
, o.object_name
|
|
, o.subobject_name
|
|
, o.object_type
|
|
, o.tablespace_name
|
|
, o.partition_type
|
|
, ss.space_allocated_delta
|
|
, ss.space_used_delta
|
|
, ss.physical_reads_delta
|
|
, ss.physical_reads_direct_delta
|
|
, ss.physical_read_requests_delta
|
|
, ss.optimized_physical_reads_delta
|
|
, ss.physical_writes_delta
|
|
, ss.physical_writes_direct_delta
|
|
, ss.physical_write_requests_delta
|
|
, ss.itl_waits_delta
|
|
, ss.row_lock_waits_delta
|
|
, ss.gc_buffer_busy_delta
|
|
, ss.gc_cr_blocks_served_delta
|
|
, ss.gc_cu_blocks_served_delta
|
|
, ss.gc_cr_blocks_received_delta
|
|
, ss.gc_cu_blocks_received_delta
|
|
, ss.buffer_busy_waits_delta
|
|
, ss.db_block_changes_delta
|
|
, ss.table_scans_delta
|
|
, ss.chain_row_excess_delta
|
|
FROM v$database d
|
|
JOIN dba_hist_snapshot s USING (dbid)
|
|
JOIN dba_hist_seg_stat ss USING (dbid, instance_number,
|
|
, dba_hist_snapshot s
|
|
, dba_hist_seg_stat_obj o
|
|
WHERE d.dbid = s.dbid
|
|
AND ss.dbid = s.dbid
|
|
AND ss.instance_number = s.instance_number
|
|
AND ss.snap_id = s.snap_id
|
|
AND o.dbid = o.dbid
|
|
AND o.obj# = o.obj#
|
|
AND o.dataobj# = o.dataobj#
|
|
AND o.owner like 'PM_OWN'
|
|
AND o.object_name LIKE 'PMA_A'
|
|
AND o.subobject_name LIKE '%%'
|
|
AND s.end_interval_time > systimestamp - 30
|
|
ORDER BY s.end_interval_time desc
|
|
, ss.instance_number
|
|
;
|
|
|
|
|
|
SELECT MAX(s.end_interval_time) end_interval_time
|
|
, o.owner
|
|
, o.object_name
|
|
, o.subobject_name
|
|
, o.object_type
|
|
, o.tablespace_name
|
|
, o.partition_type
|
|
, SUM(ss.space_allocated_delta)
|
|
, SUM(ss.space_used_delta)
|
|
, SUM(ss.physical_reads_delta)
|
|
, SUM(ss.physical_reads_direct_delta)
|
|
, SUM(ss.physical_read_requests_delta)
|
|
, SUM(ss.optimized_physical_reads_delta)
|
|
, SUM(ss.physical_writes_delta)
|
|
, SUM(ss.physical_writes_direct_delta)
|
|
, SUM(ss.physical_write_requests_delta)
|
|
, SUM(ss.itl_waits_delta)
|
|
, SUM(ss.row_lock_waits_delta)
|
|
, SUM(ss.gc_buffer_busy_delta)
|
|
, SUM(ss.gc_cr_blocks_served_delta)
|
|
, SUM(ss.gc_cu_blocks_served_delta)
|
|
, SUM(ss.gc_cr_blocks_received_delta)
|
|
, SUM(ss.gc_cu_blocks_received_delta)
|
|
, SUM(ss.buffer_busy_waits_delta)
|
|
, SUM(ss.db_block_changes_delta)
|
|
, SUM(ss.table_scans_delta)
|
|
, SUM(ss.chain_row_excess_delta)
|
|
FROM v$database d
|
|
, dba_hist_seg_stat ss
|
|
, dba_hist_snapshot s
|
|
, dba_hist_seg_stat_obj o
|
|
WHERE d.dbid = s.dbid
|
|
AND s.dbid = ss.dbid
|
|
AND s.instance_number = ss.instance_number
|
|
AND s.snap_id = ss.snap_id
|
|
AND ss.dbid = o.dbid
|
|
AND ss.obj# = o.obj#
|
|
AND ss.dataobj# = o.dataobj#
|
|
AND o.owner like 'PM_OWN'
|
|
AND o.object_name LIKE 'PMA_A'
|
|
AND o.subobject_name LIKE '%%'
|
|
AND s.end_interval_time > systimestamp - 30
|
|
GROUP BY o.owner
|
|
, o.object_name
|
|
, o.subobject_name
|
|
, o.object_type
|
|
, o.tablespace_name
|
|
, o.partition_type
|
|
ORDER BY end_interval_time desc
|
|
--ORDER BY SUM(ss.space_allocated_delta) desc
|
|
--ORDER BY SUM(ss.space_used_delta) desc
|
|
--ORDER BY SUM(ss.physical_reads_delta) desc
|
|
--ORDER BY SUM(ss.physical_reads_direct_delta) desc
|
|
--ORDER BY SUM(ss.physical_read_requests_delta) desc
|
|
--ORDER BY SUM(ss.optimized_physical_reads_delta) desc
|
|
--ORDER BY SUM(ss.physical_writes_delta) desc
|
|
--ORDER BY SUM(ss.physical_writes_direct_delta) desc
|
|
--ORDER BY SUM(ss.physical_write_requests_delta) desc
|
|
--ORDER BY SUM(ss.itl_waits_delta) desc
|
|
--ORDER BY SUM(ss.row_lock_waits_delta) desc
|
|
--ORDER BY SUM(ss.gc_buffer_busy_delta) desc
|
|
--ORDER BY SUM(ss.gc_cr_blocks_served_delta) desc
|
|
--ORDER BY SUM(ss.gc_cu_blocks_served_delta) desc
|
|
--ORDER BY SUM(ss.gc_cr_blocks_received_delta) desc
|
|
--ORDER BY SUM(ss.gc_cu_blocks_received_delta) desc
|
|
--ORDER BY SUM(ss.buffer_busy_waits_delta) desc
|
|
--ORDER BY SUM(ss.db_block_changes_delta) desc
|
|
--ORDER BY SUM(ss.table_scans_delta) desc
|
|
--ORDER BY SUM(ss.chain_row_excess_delta) desc
|
|
;
|
|
|
|
|
|
|
|
|
|
--**************************************************
|
|
-- AWR Active Session History (ASH) queries
|
|
--**************************************************
|
|
|
|
SELECT ash.sample_time
|
|
, ash.instance_number I#
|
|
, ash.session_id
|
|
, ash.session_serial#
|
|
, ash.session_type
|
|
, sn.service_name
|
|
, u.username
|
|
, ash.machine
|
|
, ash.PROGRAM
|
|
, ash.client_id
|
|
, ash.sql_exec_start
|
|
, ash.force_matching_signature
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_plan_hash_value
|
|
, ash.top_level_sql_id
|
|
-- , ash.plsql_entry_object_id
|
|
-- , ash.plsql_entry_subprogram_id
|
|
-- , ash.plsql_object_id
|
|
-- , ash.plsql_subprogram_id
|
|
, NVL2(p2.owner,p2.owner || '.' || p2.object_name || nvl2(p2.procedure_name,'.' || p2.procedure_name, ''),'') plsql_program
|
|
, NVL2(p1.owner, p1.owner || '.' || p1.object_name || nvl2(p1.procedure_name,'.' || p1.procedure_name, ''),'') plsql_entry_program
|
|
, ash.module
|
|
, ash.action
|
|
, ash.event
|
|
, ash.blocking_session_status
|
|
, ash.blocking_session
|
|
, ash.blocking_session_serial#
|
|
, ash.blocking_inst_id
|
|
, ash.blocking_hangchain_info
|
|
, ash.tm_delta_time
|
|
, ash.tm_delta_cpu_time
|
|
, ash.tm_delta_db_time
|
|
, ash.delta_time
|
|
, ash.delta_read_io_requests
|
|
, ash.delta_read_io_bytes
|
|
, ash.delta_write_io_requests
|
|
, ash.delta_write_io_bytes
|
|
, ash.delta_interconnect_io_bytes
|
|
, ash.pga_allocated
|
|
, ash.temp_space_allocated
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_active_sess_history ash
|
|
, dba_users u
|
|
, dba_hist_service_name sn
|
|
, v$database d
|
|
, dba_procedures p1
|
|
, dba_procedures p2
|
|
WHERE d.dbid = s.dbid
|
|
AND s.dbid = ash.dbid
|
|
AND s.instance_number = ash.instance_number
|
|
and s.snap_id = ash.snap_id
|
|
AND ash.dbid = sn.dbid
|
|
and ash.service_hash = sn.service_name_hash
|
|
AND ash.user_id = u.user_id
|
|
AND ash.plsql_entry_object_id = p1.object_id (+)
|
|
AND ash.plsql_entry_subprogram_id = p1.subprogram_id (+)
|
|
AND ash.plsql_object_id = p2.object_id (+)
|
|
AND ash.plsql_subprogram_id = p2.subprogram_id (+)
|
|
-- and s.end_interval_time between TO_TIMESTAMP('2012-06-09 17:00:00','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2012-06-09 23:00:07','YYYY-MM-DD HH24:MI:SS')
|
|
--and ash.sample_time between TO_TIMESTAMP('2013-02-19 17:00:00','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2013-02-19 17:51:00','YYYY-MM-DD HH24:MI:SS')
|
|
AND ash.sample_time > SYSTIMESTAMP - 2/24
|
|
-- AND ash.instance_number = '7'
|
|
-- AND ash.session_id = '1923'
|
|
-- AND ash.session_serial# = '12265'
|
|
-- and ash.service_hash = '1859414386'
|
|
-- and ash.sql_id like '1u585mt959ub3'
|
|
-- AND ash.top_level_sql_id LIKE '%'
|
|
-- and u.username like 'KQIU'
|
|
-- and upper(ash.machine) like upper('%beta%')
|
|
-- AND upper(sn.service_name) like upper('SYS$USERS')
|
|
-- and upper(ash.program) like upper('%SQL%')
|
|
-- and upper(ash.module) like upper('%PiNT%')
|
|
-- ORDER BY s.end_interval_time
|
|
ORDER BY ash.sample_time
|
|
;
|
|
|
|
|
|
|
|
SELECT ash.sql_id
|
|
, ROUND(SUM(ash.tm_delta_time)/1000000) "Total Time (sec)"
|
|
, ROUND(SUM(ash.tm_delta_cpu_time)/1000000) "CPU Time (sec)"
|
|
, ROUND(SUM(ash.tm_delta_db_time)/1000000) "DB Time (sec)"
|
|
, ROUND(SUM(ash.delta_time)/1000000) "Time (sec)"
|
|
, SUM(ash.delta_read_io_requests) "PhyRead Requests"
|
|
, ROUND(SUM(ash.delta_read_io_bytes)/1024/1024/1024) "PhyRead (GB)"
|
|
, SUM(ash.delta_write_io_requests) "PhyWrite Requests"
|
|
, ROUND(SUM(ash.delta_write_io_bytes)/1024/1024/1024) "PhyWrite (GB)"
|
|
, ROUND(SUM(ash.delta_interconnect_io_bytes)/1024/1024/1024) "InterConnectIO (GB)"
|
|
, ROUND(SUM(ash.pga_allocated)/1024/1024) "PGA (MB)"
|
|
, ROUND(SUM(ash.temp_space_allocated)/1024/1024) "TempSpace (MB)"
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_active_sess_history ash
|
|
, dba_users u
|
|
, dba_hist_service_name sn
|
|
, v$database d
|
|
WHERE d.dbid = s.dbid
|
|
AND s.dbid = ash.dbid
|
|
AND s.instance_number = ash.instance_number
|
|
and s.snap_id = ash.snap_id
|
|
AND ash.dbid = sn.dbid
|
|
and ash.service_hash = sn.service_name_hash
|
|
AND ash.user_id = u.user_id
|
|
-- and s.end_interval_time between TO_TIMESTAMP('2012-06-09 17:00:00','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2012-06-09 23:00:07','YYYY-MM-DD HH24:MI:SS')
|
|
--and ash.sample_time between TO_TIMESTAMP('2013-02-19 17:00:00','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2013-02-19 17:51:00','YYYY-MM-DD HH24:MI:SS')
|
|
and ash.sample_time > systimestamp - 10
|
|
-- AND ash.instance_number = '5'
|
|
-- AND ash.session_id = '2471'
|
|
-- AND ash.session_serial# = '38281'
|
|
-- and ash.service_hash = '1859414386'
|
|
-- and ash.sql_id like '9m4qqj5uzzn3k'
|
|
-- AND ash.top_level_sql_id LIKE '%'
|
|
and u.username like 'username'
|
|
-- and upper(ash.machine) like upper('%beta%')
|
|
-- AND upper(sn.service_name) like upper('SYS$USERS')
|
|
-- and upper(ash.program) like upper('%SQL%')
|
|
-- and upper(ash.module) like upper('%PiNT%')
|
|
GROUP BY ash.sql_id
|
|
-- ORDER BY SUM(ash.tm_delta_time) nulls last
|
|
-- ORDER BY SUM(ash.tm_delta_cpu_time) nulls last
|
|
-- ORDER BY SUM(ash.tm_delta_db_time) nulls last
|
|
-- ORDER BY SUM(ash.delta_time) nulls last
|
|
-- ORDER BY SUM(ash.delta_read_io_requests) nulls last
|
|
-- ORDER BY SUM(ash.delta_read_io_bytes) nulls last
|
|
-- ORDER BY SUM(ash.delta_write_io_requests) nulls last
|
|
-- ORDER BY SUM(ash.delta_write_io_bytes) nulls last
|
|
-- ORDER BY SUM(ash.delta_interconnect_io_bytes) nulls last
|
|
-- ORDER BY SUM(ash.pga_allocated) nulls last
|
|
ORDER BY SUM(ash.temp_space_allocated) desc nulls last
|
|
;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT ash.sample_time
|
|
, ash.inst_id I#
|
|
, ash.session_id
|
|
, ash.session_serial#
|
|
, ash.session_type
|
|
, sn.name service_name
|
|
, u.username
|
|
, ash.machine
|
|
, ash.program
|
|
, ash.sql_opname
|
|
, ash.force_matching_signature
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_plan_hash_value
|
|
, ash.top_level_sql_id
|
|
, ash.sql_exec_start
|
|
-- , ash.plsql_entry_object_id
|
|
-- , ash.plsql_entry_subprogram_id
|
|
-- , ash.plsql_object_id
|
|
-- , ash.plsql_subprogram_id
|
|
, NVL2(p2.owner,p2.owner || '.' || p2.object_name || nvl2(p2.procedure_name,'.' || p2.procedure_name, ''),'') plsql_program
|
|
, NVL2(p1.owner, p1.owner || '.' || p1.object_name || nvl2(p1.procedure_name,'.' || p1.procedure_name, ''),'') plsql_entry_program
|
|
, ash.module
|
|
, ash.action
|
|
, ash.event
|
|
, ash.blocking_session_status
|
|
, ash.blocking_session
|
|
, ash.blocking_session_serial#
|
|
, ash.blocking_inst_id
|
|
, ash.blocking_hangchain_info
|
|
, ash.TM_DELTA_TIME
|
|
, ash.TM_DELTA_CPU_TIME
|
|
, ash.TM_DELTA_DB_TIME
|
|
, ash.DELTA_TIME
|
|
, ash.DELTA_READ_IO_REQUESTS
|
|
, ash.DELTA_WRITE_IO_REQUESTS
|
|
, ash.DELTA_READ_IO_BYTES
|
|
, ash.DELTA_WRITE_IO_BYTES
|
|
, ash.DELTA_INTERCONNECT_IO_BYTES
|
|
, ash.PGA_ALLOCATED
|
|
, ash.TEMP_SPACE_ALLOCATED
|
|
FROM gv$active_session_history ash
|
|
, dba_users u
|
|
, dba_services sn
|
|
, dba_procedures p1
|
|
, dba_procedures p2
|
|
WHERE ash.service_hash = sn.name_hash
|
|
AND ash.user_id = u.user_id
|
|
AND ash.plsql_entry_object_id = p1.object_id (+)
|
|
AND ash.plsql_entry_subprogram_id = p1.subprogram_id (+)
|
|
AND ash.plsql_object_id = p2.object_id (+)
|
|
AND ash.plsql_subprogram_id = p2.subprogram_id (+)
|
|
--and ash.sample_time between TO_TIMESTAMP('2012-09-18 23:00:00','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2012-09-19 15:00:00','YYYY-MM-DD HH24:MI:SS')
|
|
and ash.sample_time > systimestamp - 5/24
|
|
-- AND ash.inst_id = '2'
|
|
-- AND ash.session_id = '1781'
|
|
-- AND ash.session_serial# = '12265'
|
|
-- and ash.service_hash = '1859414386'
|
|
-- and ash.sql_id like '9m4qqj5uzzn3k'
|
|
-- and ash.sql_id IS NOT NULL
|
|
-- AND ash.top_level_sql_id LIKE '0bxc9a7r0vczs'
|
|
-- and u.username like '%ATTRIB%'
|
|
-- and upper(ash.machine) like upper('%beta%')
|
|
-- AND upper(sn.name) like upper('SYS$USERS') -- service_name
|
|
-- and upper(ash.program) like upper('%SQL%')
|
|
-- and upper(ash.module) like upper('%PiNT%')
|
|
-- AND ash.inst_id = 3
|
|
-- AND ash.SESSION_ID = 1057
|
|
-- AND ash.SESSION_SERIAL# = 10989
|
|
-- ORDER BY s.end_interval_time
|
|
ORDER BY ash.sample_time
|
|
;
|
|
|
|
|
|
------------------------------------------
|
|
-- AWR ASH Largest TEMP and PGA Users
|
|
------------------------------------------
|
|
SELECT service_name, username, max(temp_space_allocated), max(PGA_ALLOCATED)
|
|
FROM
|
|
(
|
|
SELECT ash.instance_number, ash.session_id, session_serial#, sn.service_name, u.username, max(NVL(temp_space_allocated,0)) temp_space_allocated, max(NVL(PGA_ALLOCATED,0)) PGA_ALLOCATED
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_active_sess_history ash
|
|
, dba_users u
|
|
, dba_hist_service_name sn
|
|
, v$database d
|
|
WHERE d.dbid = s.dbid
|
|
AND s.dbid = ash.dbid
|
|
AND s.instance_number = ash.instance_number
|
|
and s.snap_id = ash.snap_id
|
|
and s.end_interval_time between TO_TIMESTAMP('2012-04-01 00:04:25','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2012-05-03 00:49:30','YYYY-MM-DD HH24:MI:SS')
|
|
AND ash.dbid = sn.dbid
|
|
and ash.service_hash = sn.service_name_hash
|
|
AND ash.user_id = u.user_id
|
|
-- AND sn.service_name LIKE '%%'
|
|
-- and ash.sql_id = '7c3kd9m29awf8'
|
|
-- and ash.program = 'BumPlusCacheJob.exe'
|
|
group by ash.instance_number, ash.session_id,session_serial#, sn.service_name, u.username
|
|
)
|
|
GROUP BY service_name, username
|
|
order by max(temp_space_allocated) desc nulls last
|
|
--order by max(PGA_ALLOCATED) desc nulls last
|
|
;
|
|
|
|
|
|
select ash.instance_number
|
|
, ash.session_id
|
|
, ash.sql_id
|
|
, ash.SQL_PLAN_HASH_VALUE
|
|
, ash.SQL_EXEC_ID
|
|
, ash.sql_exec_start
|
|
, max(ash.sample_time) end_time
|
|
--, ROUNd(max(ash.wait_time)/100/60) "dur(min)"
|
|
, ( max(ash.sample_time) - ash.sql_exec_start) "Duration (D hh:mm:ss.fff)"
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_active_sess_history ash
|
|
, dba_users u
|
|
, dba_services sn
|
|
WHERE s.dbid = ash.dbid
|
|
AND s.instance_number = ash.instance_number
|
|
and s.snap_id = ash.snap_id
|
|
and s.end_interval_time between TO_TIMESTAMP('2012-04-01 00:04:25','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('2012-05-03 00:49:30','YYYY-MM-DD HH24:MI:SS')
|
|
and ash.service_hash = '1859414386'
|
|
and ash.sql_id = '7c3kd9m29awf8'
|
|
and ash.program = 'BumPlusCacheJob.exe'
|
|
--ORDER BY ash.WAIT_TIME desc
|
|
group by ash.instance_number, ash.session_id
|
|
,ash.sql_id,ash.SQL_PLAN_HASH_VALUE, ash.SQL_EXEC_ID, ash.sql_exec_start
|
|
order by ( max(ash.sample_time) - ash.sql_exec_start) desc nulls last
|
|
, ash.sql_exec_start desc nulls last
|
|
;
|
|
|
|
|