以下脚本可以用于列出数据库内的排序活跃性能信息并监控临时空间的使用:
REM SORT ACTIVITY set linesize 150 pagesize 1400; SELECT d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)", TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " , TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' / alter session set nls_date_format='dd-mon-yy'; set lines 160 pages 1000 echo off feedback off col stat_name for a25 col date_time for a40 col BEGIN_INTERVAL_TIME for a20 col END_INTERVAL_TIME for a20 prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc." WITH sysstat AS (select sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.stat_name stat_name, ss.value e_value, lag(ss.value, 1) over(order by ss.snap_id) b_value from dba_hist_sysstat ss, dba_hist_snapshot sn where trunc(sn.begin_interval_time) >= sysdate-7 and ss.snap_id = sn.snap_id and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and ss.dbid = (select dbid from v$database) and ss.instance_number = (select instance_number from v$instance) and ss.stat_name = 'sorts (disk)') select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time, stat_name, round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0 / select temp_space/1024/1024,SQL_ID from DBA_HIST_SQL_PLAN where temp_space>0 order by 1 asc; --For 8.1.7 to 9.2: SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks; --For 10.1 and above: SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks; SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, P.program, TBS.block_size, T.tablespace ORDER BY sid_serial; SELECT * FROM (SELECT matching_criteria, TO_CHAR(force_matching_signature) force_matching_signature, sql_id, child_number, sql_text, executions, elapsed_time / 1000, operation_type, policy, estimated_optimal_size, last_memory_used, last_execution, active_time / 1000, num_sort_operations, tot_active_time / 1000, tot_optimal_executions, tot_onepass_executions, tot_multipasses_executions, all_tot_active_time / 1000, max_tempseg_size, parsing_schema_name FROM (SELECT force_matching_signature, sql_id, child_number, sql_text, matching_criteria, SUM(executions) OVER(PARTITION BY matching_criteria) executions, SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time, operation_type, policy, estimated_optimal_size, last_memory_used, last_execution, active_time, num_sort_operations, SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time, SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions, SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions, SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions, MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size, SUM(tot_active_time) OVER() all_tot_active_time, ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum, parsing_schema_name FROM (SELECT s.sql_id, s.child_number, s.sql_text, s.executions, s.elapsed_time, s.force_matching_signature, CASE WHEN s.force_matching_signature > 0 THEN TO_CHAR(s.force_matching_signature) ELSE s.sql_id END matching_criteria, ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum, sw.operation_type, sw.policy, sw.estimated_optimal_size, sw.last_memory_used, sw.last_execution, MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size, sw.active_time * sw.total_executions active_time, SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time, COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations, SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions, SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions, SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions, NVL(u.username, s.parsing_schema_name) parsing_schema_name FROM v$sql s, v$sql_workarea sw, all_users u WHERE sw.sql_id = s.sql_id AND sw.child_number = s.child_number AND u.user_id(+) = s.parsing_user_id) WHERE rnum = 1) WHERE rnum = 1 ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) WHERE ROWNUM <= 200 / SELECT * FROM (SELECT s.sid, s.machine, s.program, s.module, s.osuser, NVL(DECODE(TYPE, 'BACKGROUND', 'SYS (' || b.ksbdpnam || ')', s.username), SUBSTR(p.program, INSTR(p.program, '('))) username, NVL(SUM(CASE WHEN sn.name = 'sorts (memory)' THEN ss.VALUE ELSE 0 END), 0) sorts_memory, NVL(SUM(CASE WHEN sn.name = 'sorts (disk)' THEN ss.VALUE ELSE 0 END), 0) sorts_disk, NVL(SUM(CASE WHEN sn.name = 'sorts (rows)' THEN ss.VALUE ELSE 0 END), 0) sorts_rows, NVL(SUM(CASE WHEN sn.name = 'physical reads direct temporary tablespace' THEN ss.VALUE ELSE 0 END), 0) reads_direct_temp, NVL(SUM(CASE WHEN sn.name = 'physical writes direct temporary tablespace' THEN ss.VALUE ELSE 0 END), 0) writes_direct_temp, NVL(SUM(CASE WHEN sn.name = 'workarea executions - optimal' THEN ss.VALUE ELSE 0 END), 0) workarea_exec_optimal, NVL(SUM(CASE WHEN sn.name = 'workarea executions - onepass' THEN ss.VALUE ELSE 0 END), 0) workarea_exec_onepass, NVL(SUM(CASE WHEN sn.name = 'workarea executions - multipass' THEN ss.VALUE ELSE 0 END), 0) workarea_exec_multipass FROM v$session s, v$sesstat ss, v$statname sn, v$process p, x$ksbdp b WHERE s.paddr = p.addr AND b.inst_id(+) = USERENV('INSTANCE') AND p.addr = b.ksbdppro(+) AND s.TYPE = 'USER' AND s.sid = ss.sid AND ss.statistic# = sn.statistic# AND sn.name IN ('sorts (memory)', 'sorts (disk)', 'sorts (rows)', 'physical reads direct temporary tablespace', 'physical writes direct temporary tablespace', 'workarea executions - optimal', 'workarea executions - onepass', 'workarea executions - multipass') GROUP BY s.sid, s.machine, s.program, s.module, s.osuser, NVL(DECODE(TYPE, 'BACKGROUND', 'SYS (' || b.ksbdpnam || ')', s.username), SUBSTR(p.program, INSTR(p.program, '('))) ORDER BY workarea_exec_multipass DESC, workarea_exec_onepass DESC, reads_direct_temp + writes_direct_temp DESC, sorts_rows DESC) WHERE ROWNUM <= 200 / SELECT rawtohex(workarea_address) workarea_address, sql_id, sql_text, operation_type, policy, sid, active_time, work_area_size, expected_size, actual_mem_used, max_mem_used, number_passes, tempseg_size, tablespace, complete_ratio, elapsed, time_remaining, opname, machine, program, module, osuser, username FROM (SELECT swa.workarea_address, swa.sql_id, sa.sql_text, swa.operation_type, swa.policy, swa.sid, swa.active_time / 1000 active_time, swa.work_area_size, swa.expected_size, swa.actual_mem_used, swa.max_mem_used, swa.number_passes, swa.tempseg_size, swa.tablespace, (CASE WHEN sl.totalwork <> 0 THEN sl.sofar / sl.totalwork ELSE NULL END) complete_ratio, sl.elapsed_seconds * 1000 elapsed, sl.time_remaining * 1000 time_remaining, sl.opname, s.machine, s.program, s.module, s.osuser, NVL(DECODE(TYPE, 'BACKGROUND', 'SYS (' || b.ksbdpnam || ')', s.username), SUBSTR(p.program, INSTR(p.program, '('))) username, ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum FROM v$sql_workarea_active swa, v$sqlarea sa, (SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl, v$session s, v$process p, x$ksbdp b WHERE sl.sid(+) = swa.sid AND sl.sql_id(+) = swa.sql_id AND swa.sid <> USERENV('sid') AND sa.sql_id = swa.sql_id AND s.sid = swa.sid AND s.paddr = p.addr AND b.inst_id(+) = USERENV('INSTANCE') AND p.addr = b.ksbdppro(+) ORDER BY swa.number_passes DESC, swa.work_area_size DESC) WHERE rnum = 1 /
Leave a Reply