Sort Segment Usage Analysis (vsortusage.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides real-time monitoring of temporary tablespace usage by analyzing sessions that are actively using sort segments. It helps DBAs:

• Track Sort Operations: Identifies sessions performing sorts that spill to disk
• Monitor Temp Usage: Shows how much temporary space each session consumes
• Identify Heavy Users: Highlights sessions using excessive temporary space
• Analyze Patterns: Groups usage by tablespace and provides summary statistics
• Link to SQL: Associates sort usage with specific SQL hash values for troubleshooting

Script

rem vsortusage.sql
rem
set linesize 132
rem
ttitle 'Sort Segment Usage'
rem
col tablespace format a10
col username format a10
col osuser format a10
col sid format 9999
col extents format 99,999,999
col module form a18
col blocks format 9,999,999,999
col kbytes format 9,999,999,999 heading 'KBYTES'
col sql_hash_value format 99999999999 heading 'HASH VALUE'
col sql_text format a54 trunc
rem
break on report on tablespace skip 1 on sid on username on osuser
compute sum avg max of extents kbytes on report tablespace
rem
select  /*+ ordered use_nl (p, su, s) */ /*, st) */
       su.tablespace,
       s.sid,
       s.username,
       s.osuser,
       s.module,
       sum(su.extents) extents,
       sum(su.blocks) * p.value /1024 kbytes,
       s.sql_hash_value
       -- , st.sql_text
from
     v$parameter p,
     v$sort_usage su,
     v$session s
     --, v$sqltext st
where s.saddr = su.session_addr
  and s.serial# = su.session_num
  -- and st.address(+) = s.sql_address
  -- and st.hash_value(+) = s.sql_hash_value
  -- and st.piece(+) = 0
  and p.name='db_block_size'
group by
       su.tablespace,
       s.sid,
       s.username,
       s.osuser,
       s.module,
       s.sql_hash_value,
       -- st.sql_text,
       p.value
order by
       su.tablespace,
       6 desc,
       s.sid
/

Usage

SQL> @vsortusage

The script requires no parameters and displays current sort segment usage across all temporary tablespaces.

Parameters

This script has no input parameters.

Required Privileges

• SELECT on V$SORT_USAGE
• SELECT on V$SESSION
• SELECT on V$PARAMETER

Sample Output

                                           Sort Segment Usage

TABLESPACE SID  USERNAME   OSUSER     MODULE               EXTENTS         KBYTES HASH VALUE
---------- ---- ---------- ---------- ------------------ ---------- -------------- -----------
TEMP       1234 SCOTT      oracle     SQL Developer             128         16,384  3423456789
           2345 HR         oracle     JDBC Thin Client          256         32,768  1234567890
           3456 BATCH_USER batch      Batch Job                 512         65,536  9876543210
********** ---- ---------- ----------                    ---------- --------------
avg                                                             299         38,229
max                                                             512         65,536
sum                                                             896        114,688

TEMP2      4567 ANALYTICS  analyst    BI Tool                    64          8,192  5678901234
********** ---- ---------- ----------                    ---------- --------------
avg                                                              64          8,192
max                                                              64          8,192
sum                                                              64          8,192

Key Output Columns

Column	Description
TABLESPACE	Name of the temporary tablespace being used
SID	Session identifier of the process using sort space
USERNAME	Oracle username performing the sort operation
OSUSER	Operating system username of the connected user
MODULE	Application module name (set by DBMS_APPLICATION_INFO)
EXTENTS	Number of temporary extents allocated for this session
KBYTES	Total kilobytes of temporary space used
HASH VALUE	SQL hash value of the statement causing the sort

Understanding the Metrics

Sort Segment Basics

• In-Memory Sorts: Operations that fit within PGA_AGGREGATE_TARGET
• Disk Sorts: Operations that spill to temporary tablespace
• One-Pass Sorts: Read data once from temp (more efficient)
• Multi-Pass Sorts: Read data multiple times from temp (less efficient)

Key Indicators

• High EXTENTS: Indicates fragmented or growing sort operations
• Large KBYTES: Shows memory-intensive operations
• Multiple Sessions: May indicate temp tablespace contention
• Persistent Usage: Could suggest runaway queries or insufficient PGA

Performance Thresholds

• < 10MB: Normal for small sorts
• 10-100MB: Moderate usage, monitor frequency
• 100MB-1GB: Heavy usage, investigate optimization
• > 1GB: Critical usage, immediate attention needed

Common Use Cases

1. Troubleshooting ORA-01652 (Unable to extend temp segment)

-- Check current usage before error occurs
@vsortusage

-- Identify the largest consumers
-- Look for sessions with KBYTES > 1000000 (1GB)

2. Capacity Planning for Temporary Tablespace

-- Run during peak hours to understand requirements
-- Note the sum totals for each tablespace
-- Plan for 2-3x peak usage for safety

3. Identifying Inefficient SQL

-- Use the HASH VALUE to find problematic SQL
SELECT sql_text
FROM v$sqlarea
WHERE hash_value = &hash_value_from_output;

4. Monitoring Batch Jobs

-- Filter by specific modules or users
-- Track sort usage patterns over time
-- Identify jobs that need PGA tuning

Performance Analysis

PGA vs Temporary Tablespace

The relationship between PGA settings and temp usage:

• Optimal: All sorts complete in memory (PGA)
• One-Pass: Single read from temp tablespace
• Multi-Pass: Multiple reads from temp tablespace

Tuning Recommendations

1. Increase PGA_AGGREGATE_TARGET

   -- Check current setting
   SELECT name, value/1024/1024 MB
   FROM v$parameter
   WHERE name = 'pga_aggregate_target';
   
   -- Consider increasing if seeing frequent disk sorts
   ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;

2. Enable Automatic PGA Management

   -- Ensure workarea_size_policy is AUTO
   ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=BOTH;

3. Monitor Sort Statistics

   -- Check sort performance metrics
   SELECT name, value
   FROM v$sysstat
   WHERE name LIKE 'sorts%';

Query Optimization Tips

• Add appropriate indexes to avoid sorts
• Use FIRST_ROWS optimization for OLTP
• Consider materialized views for complex aggregations
• Partition large tables to reduce sort volumes

Troubleshooting

Common Issues

1. No Rows Returned

   • No active sort operations using temp space
   • All sorts fitting in PGA (good situation)
   • Check V$SYSSTAT for ‘sorts (disk)’ counter

2. Extremely High Usage

   • Cartesian joins creating massive result sets
   • Missing WHERE clauses
   • Inefficient GROUP BY or ORDER BY operations

3. Temp Tablespace Full

   -- Add more space
   ALTER TABLESPACE temp ADD TEMPFILE '/path/temp02.dbf' SIZE 10G;
   
   -- Or resize existing file
   ALTER DATABASE TEMPFILE '/path/temp01.dbf' RESIZE 20G;

Investigation Steps

1. Identify the Session

   -- Get session details
   SELECT sid, serial#, username, program, machine
   FROM v$session
   WHERE sid = &sid_from_output;

2. Find the SQL Statement

   -- Get full SQL text
   SELECT sql_fulltext
   FROM v$sqlarea
   WHERE hash_value = &hash_value_from_output;

3. Check Execution Plan

   -- Look for SORT operations
   SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

Related Scripts

• vtemp.sql - Detailed temporary segment usage
• vpga.sql - PGA memory usage analysis
• vsessev.sql - Session event analysis
• vsqltopu.sql - Top SQL by various metrics
• vsgastat.sql - SGA statistics including sorts