Oracle Sort Report

Oracle sort report

Oracle temp space report

prompt
prompt Report Current Sort Activity.
prompt

set linesize 150
set verify off
set pagesize 50

prompt SQL work area (current)
col sid for 99990
col operation_type for a20
col wsize  for 999,990     heading 'Current|W.Size(k)'
col esize  for 999,990     heading 'Expected|W.Size(k)'
col amsize for 999,999,990 heading 'Current|Mem.(k)'
col mmsize for 999,999,990 heading 'Maximum|Mem.(k)'
col tsize  for 999,999,990 heading 'Tmp. Seg.|Size (k)'
col passes for 999,990     heading 'Passes'
compute sum of wsize  on report
compute sum of wsize  on report
compute sum of esize  on report
compute sum of amsize on report
compute sum of mmsize on report
compute sum of tsize  on report
break on report
select SID
     , OPERATION_TYPE
     , WORK_AREA_SIZE/1024   as wsize
     , EXPECTED_SIZE/1024    as esize
     , ACTUAL_MEM_USED/1024  as amsize
     , MAX_MEM_USED/1024     as mmsize
     , TEMPSEG_SIZE/1024     as tsize
     , NUMBER_PASSES         as passes
from v$sql_workarea_active
order by sid
/

column max_space   format a11         heading 'Tablespace|Name'
column max_tot_mb  format 999,990     heading 'Max Total|Mbytes'
column max_used_mb format 999,990     heading 'Max Single|Use Mbytes'
column max_sort_mb format 999,990     heading 'Max Single|Sort Mbytes'
column max_file_mb format 999,990     heading 'Tablespace|Size (Mb)'
column ftype                          heading 'File|Type'
column extent_size format 99,999,999  heading 'Ext. Size|(Bytes)'

-- get the database blk size
column blk_size new_value _blk_size
set termout off
select to_number (value) as blk_size from  v$parameter
where  upper(name) = 'DB_BLOCK_SIZE';
set termout on

prompt sort segments:
select s.tablespace_Name                          as max_space
     , ftype                                      as ftype
     , (s.extent_size*&_blk_size)                 as extent_size
     , (file_bytes)/(1024*1024)                   as max_file_mb
     , (s.max_blocks*&_blk_size)/(1024*1024)      as max_tot_mb
     , (s.max_used_blocks*&_blk_size)/(1024*1024) as max_used_mb
     , (s.max_sort_blocks*&_blk_size)/(1024*1024) as max_sort_mb
from  v$sort_segment s
,(select tablespace_name
       , sum (bytes) as file_bytes
       , 'Data' as ftype
  from   dba_data_files
  group by tablespace_name
  union
  select tablespace_name
       , sum (bytes) as file_bytes
       , 'Temp' as ftype
  from   dba_temp_files
  group by tablespace_name
) f
where f.tablespace_name = s.tablespace_name
order by 1
/

compute sum of srt_mb  on srt_space
compute sum of srt_ext on srt_space
break on srt_space skip page

column srt_space           format a11     heading 'TSpace'
column srt_sid_serial      format A15     heading 'Sid serial'
column srt_osuser          format a25     heading 'OS User / Schema'
column srt_program         format a20     heading 'Program'
column srt_event           format a30     heading 'Wait Event'
column srt_mb              format 999,990 heading 'Mbytes'
column srt_ext             format 99,990  heading 'Extents'

prompt sort segment usage:
select u.tablespace                      as srt_space
     , s.sid||','||serial#               as srt_sid_serial
     , s.osuser||'/'||s.schemaname       as srt_osuser
     , substr (s.program,1,20)           as srt_program
     , u.segtype
     , u.extents                         as srt_ext
     , (u.blocks*&_blk_size)/(1024*1024) as srt_mb
     , w.event                           as srt_event
     , s.sql_id
from v$sort_usage u, v$session s, v$session_wait w
where u.session_addr = s.saddr (+)
and   s.sid = w.sid (+)
order by s.osuser, s.schemaname, s.program, s.sid, u.extents
/

clear breaks
clear computes

====

SQL> @dba_sort

Report Current Sort Activity.

SQL work area (current)

                              Current  Expected      Current      Maximum    Tmp. Seg.
   SID OPERATION_TYPE       W.Size(k) W.Size(k)      Mem.(k)      Mem.(k)     Size (k)   Passes
------ -------------------- --------- --------- ------------ ------------ ------------ --------
                            --------- --------- ------------ ------------ ------------
sum

no rows selected

sort segments:

Tablespace  File   Ext. Size Tablespace Max Total Max Single  Max Single
Name        Type     (Bytes)  Size (Mb)    Mbytes Use Mbytes Sort Mbytes
----------- ---- ----------- ---------- --------- ---------- -----------
TEMP        Temp   1,048,576        326       325         11           2

1 row selected.

sort segment usage:

TSpace      Sid serial      OS User / Schema          Program              SEGTYPE   Extents   Mbytes Wait Event                     SQL_ID
----------- --------------- ------------------------- -------------------- --------- ------- -------- ------------------------------ -------------
TEMP        43,7069         oracle/SYS                sqlplus@apt-amd-02 ( LOB_DATA        1        1 SQL*Net message from client
            47,12494        oracle/SYS                sqlplus@apt-amd-02 ( DATA            1        1 SQL*Net message from client
***********                                                                          ------- --------
sum                                                                                        2        2

2 rows selected.