DISK_SORT AND ROWS_SORT


set serveroutput on;
declare
     v_sort1   number;
     v_sort2   number;
     v_sort3   number;
     v_sort4   number;
     v_sort5   number;
begin
  select value into v_sort5 from V$PARAMETER where name='pga_aggregate_target';
  select value into v_sort1 from V$PARAMETER where name='sort_area_size';
  select sum(a.value) into v_sort2 from V$SYSSTAT a, V$STATNAME b
       where a.statistic#=b.statistic# and b.name like 'sorts (memory)';
  select sum(a.value) into v_sort3 from V$SYSSTAT a, V$STATNAME b
       where a.statistic#=b.statistic# and b.name like 'sorts (disk)';
  select sum(a.value) into v_sort4 from V$SYSSTAT a, V$STATNAME b
       where a.statistic#=b.statistic# and b.name like 'sorts (rows)';
  dbms_output.put_line('Sort Area Size: '||to_char(v_sort1,'99999999999'));
  dbms_output.put_line('PGA Aggregate : '||to_char(v_sort5,'99999999999'));
  dbms_output.put_line('Memory sorts  : '||to_char(v_sort2,'99999999999'));
  dbms_output.put_line('Disk sorts    : '||to_char(v_sort3,'99999999999'));
  dbms_output.put_line('Rows sorted   : '||to_char(v_sort4,'99999999999'));
  dbms_output.put_line('%sort to disk : '||to_char((v_sort3/(v_sort3+v_sort2))*100,'99999990D99'));
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: