Script:List SORT ACTIVITY监控临时空间的使用

以下脚本可以用于列出数据库内的排序活跃性能信息并监控临时空间的使用:

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
/