Re: Finding users who have dynamically set SORT_AREA_SIZE

Hi

 To me it does not seem to be reliable: Win2000, 8.1.7.0.0

Sysdba:

SQL> show parameter sort

NAME                                 TYPE    VALUE


------------------------------------ ------- ------------------------------

nls_sort                             string
sort_area_retained_size              integer 16384
sort_area_size                       integer 32768
sort_multiblock_read_count           integer 2

User sort_dump:

select * from all_tables
order by table_name;

Statistics

          8  recursive calls
          9  db block gets
      17254  consistent gets
         14  physical reads
          0  redo size
      22286  bytes sent via SQL*Net to client
       2090  bytes received via SQL*Net from client
         17  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
        238  rows processed

OK -> disk sort

SQL>
SQL> select sid from v$mystat
  2 where rownum =1;

       SID

         8

Sysdba:

SQL> oradebug setorapid 8
Statement processed.
SQL> oradebug dump global_area 4
Statement processed.
SQL> In trace:
stpdef stsustp_p [5ED8220, 5ED8248) = 0000C000 00004000 00000001 00000002 ...

Note: Oracle for sort_area_size shows more than specified

User sort_dump:

SQL> alter session set sort_area_size=1000000;

Session altered.

Statistics

          8  recursive calls
          6  db block gets
      17254  consistent gets
          7  physical reads
          0  redo size
      22284  bytes sent via SQL*Net to client
       2090  bytes received via SQL*Net from client
         17  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        238  rows processed

Good, bigger sort size -> sort in memory

Trace show the same: (old trace renamed)

stpdef stsustp_p [398220, 398248) = 0000C000 00004000 00000001 00000002 ...

Test repeated 15 times ( To avoid possibility of mistake when process doesn't sort )

Did I miss something?

Igor Received on Thu May 16 2002 - 12:53:32 CDT