Memory Sort Ratio Analysis (vsortratio.sql)

What This Script Does

This script analyzes the ratio of memory-based sorts versus disk-based sorts to help DBAs optimize PGA memory allocation and improve query performance. A high memory sort ratio indicates efficient memory usage, while a low ratio suggests potential for PGA tuning to reduce disk I/O.

Script

rem
tti 'Memory Sort Ratio'
rem
set lines 80
rem
col sorts_memory     format 999,999,990 heading 'Sorts|(memory)'
col sorts_disk   format 999,999,990 heading 'Sorts|(disk)'
col memory_sort_ratio  format 990.0 heading 'Memory|Hit Ratio'
rem
select
      sum(decode(name,'sorts (memory)', value, 0)) sorts_memory,
      sum(decode(name,'sorts (disk)', value, 0)) sorts_disk,
      round(sum(decode(name,'sorts (memory)', value, 0))
            /
            sum(decode(name,'sorts (memory)', value, 0)
                + decode(name,'sorts (disk)', value, 0))
           * 100,1) memory_sort_ratio
from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)')
/

Usage

-- Run the script in SQL*Plus or SQLcl
@vsortratio.sql

-- No parameters required
-- Shows cumulative statistics since instance startup
-- Quick assessment of sort performance

Required Privileges

SELECT on V$SYSSTAT
Available to most database users

Sample Output

Memory Sort Ratio

    Sorts     Sorts Memory
 (memory)    (disk) Hit Ratio
---------- --------- ---------
 2,456,789    12,345      99.5

Key Output Columns

Sorts (memory): Total sorts performed in memory
Sorts (disk): Total sorts requiring disk storage
Memory Hit Ratio: Percentage of sorts completed in memory

Understanding Sort Performance

Memory Sort Ratio Thresholds

> 95%: Excellent - sorts mostly in memory
90-95%: Good - acceptable performance
85-90%: Fair - some optimization potential
< 85%: Poor - significant disk sorting, needs tuning

Factors Affecting Sort Performance

PGA_AGGREGATE_TARGET: Total PGA memory available
WORKAREA_SIZE_POLICY: AUTO vs MANUAL memory management
SORT_AREA_SIZE: Sort area size (manual mode)
Query Complexity: Large result sets, complex ORDER BY
Concurrent Sessions: Memory competition

Performance Analysis

Optimal Configuration

Target memory sort ratio > 95%
Minimal disk sorts for better performance
Proper PGA sizing for workload

Impact of Disk Sorts

Increased I/O: Disk reads/writes for temporary data
Response Time: Slower query execution
Resource Usage: Higher CPU and I/O utilization
Scalability: Reduced concurrent user capacity

Common Use Cases

PGA Tuning Assessment

-- Check current sort performance
@vsortratio.sql
-- If ratio < 95%, consider increasing PGA_AGGREGATE_TARGET

Performance Troubleshooting

-- During slow query investigations
@vsortratio.sql
-- Correlate with temp tablespace usage
-- Check if sorts are spilling to disk

Capacity Planning

-- Before workload increases
@vsortratio.sql
-- Establish baseline sort behavior
-- Plan PGA memory requirements

Troubleshooting Poor Sort Ratios

Check Current PGA Settings

-- Review PGA configuration
SELECT name, value, description
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'workarea_size_policy', 'sort_area_size');

-- Check PGA usage
SELECT * FROM v$pgastat;

Identify Sort-Heavy Queries

-- Find SQL with high disk sorts
SELECT sql_id, sql_text, sorts, disk_reads
FROM v$sql
WHERE sorts > 100
ORDER BY sorts DESC;

Monitor Temporary Tablespace Usage

-- Check temp space usage
SELECT tablespace_name,
       SUM(bytes_used)/1024/1024 mb_used,
       SUM(bytes_free)/1024/1024 mb_free
FROM v$temp_space_header
GROUP BY tablespace_name;

PGA Memory Optimization

Increase PGA_AGGREGATE_TARGET:

-- Gradually increase PGA memory
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

Enable automatic workarea management:

ALTER SYSTEM SET workarea_size_policy = AUTO SCOPE=BOTH;

Monitor after changes:

-- Check improvement in sort ratio
@vsortratio.sql

Advanced Analysis

Historical Sort Trends

-- Compare sort performance over time
SELECT h.end_interval_time,
       s1.value sorts_memory,
       s2.value sorts_disk,
       ROUND(s1.value / (s1.value + s2.value) * 100, 1) memory_ratio
FROM dba_hist_sysstat s1,
     dba_hist_sysstat s2,
     dba_hist_snapshot h
WHERE s1.stat_name = 'sorts (memory)'
AND s2.stat_name = 'sorts (disk)'
AND s1.snap_id = s2.snap_id
AND s1.instance_number = s2.instance_number
AND h.snap_id = s1.snap_id
AND h.instance_number = s1.instance_number
AND h.end_interval_time > SYSDATE - 7
ORDER BY h.end_interval_time DESC;

PGA Target Effectiveness

-- Evaluate PGA target vs actual usage
SELECT name,
       ROUND(value/1024/1024, 0) mb_value
FROM v$pgastat
WHERE name IN ('aggregate PGA target parameter',
               'aggregate PGA auto target',
               'total PGA allocated',
               'total PGA used for auto workareas',
               'total PGA used for manual workareas');

Session-Level Sort Analysis

-- Identify sessions with high sort activity
SELECT s.sid, s.username, s.program,
       pga.value pga_used_mb,
       sorts.value sorts_memory,
       temp.value temp_space_mb
FROM v$session s,
     v$sesstat sorts,
     v$sesstat pga,
     v$sesstat temp,
     v$statname sn1,
     v$statname sn2,
     v$statname sn3
WHERE s.sid = sorts.sid
AND s.sid = pga.sid
AND s.sid = temp.sid
AND sorts.statistic# = sn1.statistic#
AND pga.statistic# = sn2.statistic#
AND temp.statistic# = sn3.statistic#
AND sn1.name = 'sorts (memory)'
AND sn2.name = 'session pga memory'
AND sn3.name = 'session uga memory'
AND sorts.value > 100
ORDER BY sorts.value DESC;

Workarea Sizing Analysis

-- Analyze optimal vs actual workarea sizes
SELECT operation_type,
       policy,
       SUM(total_executions) executions,
       SUM(optimal_executions) optimal_execs,
       SUM(onepass_executions) onepass_execs,
       SUM(multipasses_executions) multipass_execs,
       ROUND(SUM(optimal_executions) / SUM(total_executions) * 100, 1) optimal_pct
FROM v$sql_workarea_histogram
WHERE total_executions > 0
GROUP BY operation_type, policy
ORDER BY total_executions DESC;

Sort Usage Analysis (vsortusage.sql) - Detailed sort space usage
PGA Memory Analysis (pga.sql) - PGA memory statistics
Temporary Space Analysis (temp.sql) - Temp tablespace usage
System Metrics (system_stats.sql) - Overall system performance