Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, 'Abdur-Razzaaq.

Asked: August 14, 2001 - 10:13 pm UTC

Last updated: November 05, 2012 - 9:44 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,

The value specified by SORT_AREA_SIZE is assigned on a per user basis. Therefore, if it is set to 1M, and 25 users connect to the database, each user's Server Process will consume 1M (25M in total).

The questions are:

1) The memory consumed for the sort, is it within the SGA memory or the Server memory (outside SGA)?

2) What will be the case if 25 physical users (different sessions) are connected using the same database user?

3) Let's assume, SORT_AREA_SIZE is set to 1M and temporary tablespace is locally managed with uniform size of 1M. If SORT_AREA_RETAINED_SIZE is set to a higher value other than 1M, what will be the impact on performance?

Thanks,
'Abdur-Razzaaq

and Tom said...

Well, your basic assumption is wrong.

sort_area_size is an upper bound on the memory we dynamically allocate for a sort.

A session may have more then one sort going at the same time (more then one sort area size)

sort_area_sizes are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over.

So, if it is 1m and there are 25 users connected to the database you will have somewhere between 0 and many megs of sort space at any point in time (if all 25 do a single sort AT THE SAME exact instant -- you'll have 25m. If they are not sorting, you'll have 0 meg)


1) sort_area_size space is allocated in the PGA, outside of the SGA (retained can be in the UGA with MTS)

2) it is done by the SESSION not by name and a single session can have more then one going at any time

3) SORT_AREA_RETAINED_SIZE is bounded by sort_area_size, hence this hypothetical situation won't happen.


See
</code> http://www.ixora.com.au/newsletter/2000_11.htm
for a good discussion on this.



<b>followup to comment one</b>

It is definitely an inaccurate statement on their part (eg: its *wrong* plain and simple)

See
http://www.sybex.com/erratatracking.nsf/weberrataform?OpenForm&ISBN=2684 <code>
there is an errata sheet for this book -- you might want to drop sybex a line and have them add this to the sheet.


followup to comment two

Nope -- MTS won't affect this, sort_area_size is a per process thingy -- its in the shared_server under MTS (so using MTS the max sort area memory would be a function of the number of shared servers you have). The sort area retained comes from the UGA in MTS which is in the SGA -- and sort area spaces are always freed after the sort (sort area retained is freed right after the last row is processed)

sort areas are transient, they come and go -- they are not always there.


Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reason for wrong basic assumption

'Abdur-Razzaaq, August 15, 2001 - 10:23 pm UTC

Tom,
In the book "OCP Oracle8i DBA Performance Tuning and Network Administration Study Guide" by Joseph C. Johnson and Matthew Weishan from Sybex publishing, it is quoted on page 263 as follows:
"... The value specified by SORT_AREA_SIZE is assigned on a per user basis. Therefore, if SORT_AREA_SIZE is set to 1MB, and 20 users connect to the database, each user's Server Process will consume 1MB of server memory, 20MB total, whether they are actively sorting or not." Is this a documentation bug?


AB, August 16, 2001 - 3:54 am UTC

I think the OCP book might be referring to MTS. It was my understanding that MTS allocated all SORT_AREA_SIZE to a user at sign-on, whereas non-MTS provides the SORT_AREA_SIZE as and when needed by a session up to the limit specified.

Here is what the author of the book has to say

'Abdur-Razzaaq, August 16, 2001 - 9:48 pm UTC

Tom Kyte is correct. The amount of memory specified by the
SORT_AREA_SIZE parameter is not allocated to a user's process until they perform their first sort. This was an erroneous statement and I thank you for bringing it to my attention.

However, they does seem to be some conflicting information regarding whether the space is released *after* the user's sort is complete. I think this was the cause of my own
confusion on this issue. Specifically, page 2-16 of the "Oracle8i Designing and Tuning for Performance" reference manual states:

"...the user had set SORT_AREA_SIZE to 10MB on a machine with 64 MB of memory serving 20 users. When users logged on to the system, the first time they executed a sort, their
sessions were assigned to a sort area. Each session held the sort area for the duration of the session."

Additionally, page 19-42 of that same manual also states:

"Large sort areas can be used effectively if you combine a large SORT_AREA_SIZE with a minimal SORT_AREA_RETAINED_SIZE. If memory is not released until the
user disconnects from the database, then large sort work areas could cause problems."

However, the Oracle8i Reference on page 1-112 seems to contradict the "sort area is not released until the user disconnects" idea illustrated above by stating:

"After the sort is complete, but before the rows are returned, Oracle releases memory down to the size specified by the SORT_AREA_RETAINED_SIZE parameter. After the last
row is returned, Oracle releases the remainder of the memory."

After further testing and research it appears that the last statement is correct. Therefore, the corrected statement on page 263 on the Sybex review guide, section "Make it Bigger", starting at the second sentence should be:

"Care must be taken when increasing this parameter, however. While the amount of memory specified by SORT_AREA_SIZE is not allocated to a user's session until they initiate a sort, the server's available memory must be sufficient to accommodate the user's sort area while the sort is being processed. In particular, if SORT_AREA_SIZE is set to a large value and many users are sorting
simultaneously, the demands placed on the server's memory may adversely impact performance until the memory allocated to those sort areas is released when the sorts are
completed."

Cordially,
Joe Johnson, OCP

A reader, July 18, 2002 - 2:50 pm UTC

Can you show us some examples. Used the script provided at
</code> http://www.ixora.com.au/scripts/io_opt.htm#disk_sorts <code>
to monitor disk sorts.

Steps.

1. Restarted database with Sort_area_size = 64k.

connect as my application user in SQL*Plus and ran a query three times.

checked disk sorts with above script

DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
---------- ------------ ---------------
3 70K 1

did an alter session and set sort_area_size = 1M for my application user and ran the query 3 more times

checked disk sorts with above script

DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
---------- ------------ ---------------
6 68K 1

I would think that if the user alters his sort area to 1M it would not have a disk sort. I tried with 10M sort with the same results.

Can you show an example of this using the alter session?

Thanks






Tom Kyte
July 18, 2002 - 5:07 pm UTC

not knowing what you queried with -- not having your script from end to end -- I cannot really comment. I don't know what you are looking for exactly and how you were testing.

A reader, July 19, 2002 - 10:24 am UTC

Ok, Let me try to explain a little better what I am asking.
Database 8.1.7.3
DB_Block = 8192
sort_area_size = 65500
sort_area_retained_size = 65500

I spent some more time making up a better test script.

---------------------------------
create table t as (select 'SORTING OF LENGTH 20' TEST from all_objects);

DESC T;

show parameter sort_area

alter session set sort_area_size = 65500;

set autotrace on stat

select count(1) from (select TEST from t
where rownum < 1331 order by 1);


select count(1) from (select TEST from t
where rownum < 1332 order by 1);


alter session set sort_area_size = 1024000;


select count(1) from (select TEST from t
where rownum < 1777 order by 1);


select count(1) from (select TEST from t
where rownum < 1778 order by 1);

drop table t;

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


I used this query in sql*plus to find the break point

select count(1) from (select TEST from t
where rownum < &rows order by 1);


Ok, I created a table of char(20) with fixed data. I set my sort_area_size to 65500. The most rows I can select and still do a memory sort is 1331. 1332 does a disk sort.

I then set my sort_area_size to 1024000 which is about 15 times larger that before. But the max number of rows I can sort in memory is 1777. 1778 does a disk sort.

I also looked at the sort_area_size and increasing it above 73800 returned same results and did not allow me to sort more that 1777 rows in memory.

So my questions would be?

1. Given a fixed about of data, What goes into the sort_area?
2. If you double your sort_area, shouldn't the amount of data you can sort double or is there an upper limit of how much sort area you can use?
3. Can you so an example showing how changing the sort area directly/indirectly affects how much data will be sorted in memory


Thanks


Tom Kyte
July 19, 2002 - 11:04 am UTC

My question to you is -- well, not really a question, rather statement of fact on my part - I cannot reproduce your findings.  Oh -- I can reproduce your finding of 1331 to 1332 just fine, but not eh 1700 example.  In fact, I went to 10k rows and didn't do a disk sort:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as (select 'SORTING OF LENGTH 20' TEST from all_objects);

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> DESC T;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------------------
 TEST                                                                             CHAR(20)

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> show parameter sort_area

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size              integer 0
sort_area_size                       integer 1024000
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 65500;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on stat
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(1) from (select TEST from t
  2  where  rownum < 1331 order by 1);

  COUNT(1)
----------
      1330


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
          9  consistent gets
         63  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(1) from (select TEST from t
  2  where  rownum < 1332 order by 1);

  COUNT(1)
----------
      1331


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
          9  consistent gets
          5  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 1024000;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(1) from (select TEST from t
  2  where  rownum < 1777 order by 1);

  COUNT(1)
----------
      1776


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(1) from (select TEST from t
  2  where  rownum < 1778 order by 1);

  COUNT(1)
----------
      1777


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(1) from (select TEST from t
  2  where  rownum < 10000 order by 1);

  COUNT(1)
----------
      9999


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         40  consistent gets
          0  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Productio


lets see a cut and paste just like that.

 

A reader, July 19, 2002 - 11:43 am UTC

Here you go.  What else would affect this?
-------------------------------

SQL> show parameter sort_area

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size              integer 65536
sort_area_size                       integer 1024000
SQL> 
SQL> create table t as (select 'SORTING OF LENGTH 20' TEST from all_objects);

Table created.

SQL> DESC T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST                                               CHAR(20)

SQL> 
SQL> show parameter sort_area 

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size              integer 65536
sort_area_size                       integer 1024000
SQL> 
SQL> alter session set sort_area_size = 65500;

Session altered.

SQL> 
SQL> set autotrace on stat
SQL> 
SQL> select count(1) from (select TEST from t
  2  where  rownum < 1331 order by 1);

  COUNT(1)
----------
      1330


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
          9  consistent gets
          7  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> 
SQL> select count(1) from (select TEST from t
  2  where  rownum < 1332 order by 1);

  COUNT(1)
----------
      1331


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
          9  consistent gets
          5  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL> 
SQL> 
SQL> alter session set sort_area_size = 1024000;

Session altered.

SQL> 
SQL> 
SQL> select count(1) from (select TEST from t
  2  where  rownum < 1777 order by 1);

  COUNT(1)
----------
      1776


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> 
SQL> select count(1) from (select TEST from t
  2  where  rownum < 1778 order by 1);

  COUNT(1)
----------
      1777


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
         11  consistent gets
          7  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL> 
SQL> 
SQL> drop table t;

Table dropped.

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


Thanks Tom for your time.

 

Tom Kyte
July 19, 2002 - 12:44 pm UTC

Interesting,

what platform
what version of that platform
what does select * from v$version show
what is your init.ora (not v$parameter, just show me what you are setting)

A reader, July 19, 2002 - 2:09 pm UTC

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE    8.1.7.2.1       Production
TNS for 32-bit Windows: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

OS is Win2000 Workstation SP2 256Meg Ram
Running Oracle in Dedicated Server Mode

init.ora has:

db_files = 1024
open_cursors = 500
max_enabled_roles = 30
db_file_multiblock_read_count = 32
db_block_buffers = 4000
shared_pool_size = 31457280
large_pool_size = 0 
JAVA_POOL_SIZE =  0 
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150
parallel_max_servers = 5
log_buffer = 32768
timed_statistics = true 
max_dump_file_size = 10240  
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
distributed_transactions = 500
compatible = 8.1.0
sort_area_size = 512000
sort_area_retained_size = 65536
job_queue_processes = 1


Thanks
 

Tom Kyte
July 19, 2002 - 5:07 pm UTC

DOH -- don't know why I didn't think of that before.

It is your sort_area_retained_size. It was not big enough. There were two things happening here -- the sort and the aggregation. In between the two, you give up your sort_area_size and shrink it down to sort_area_retained. Once we did that -- the intermediate result was bigger then fit in your sort_area_retained and we put it to disk. As soon as we put to disk -- there you go, sort disk.

If you change your retained size, you'll see the sort disk goes away. It didn't really SORT on disk, it sorted and then stuff some results on disk cause they wouldn't fit in the retained size.

I spotted it!

Mark J. Bobak, July 19, 2002 - 6:34 pm UTC

Heh, I'm pretty proud of myself, as I spotted the problem
right away, when the reader posted the first full cut and
paste. ;-)

I just wanted to mention to the reader:

The link to IxOra that Tom has in the reply to the original
question, Steve Adams does a nice discussion of sort_area_size
and sort_area_retained_size, and how they are used.


-Mark

PS Hey, I just noticed the spell checker icon, pretty cool.
Nice touch!

Do i have these parameters incorrectly set?

Yogeeraj, July 20, 2002 - 3:24 am UTC

Hello,

Reading this post, i became very curious to see the behaviour of the above test in my system. So i ran the same steps!

Oops! All these tests give me " 1  sorts (disk)" each time!!

Please advise on how i proceed in fixing the problem (if any).

Thank you for your precious time.

Best Regards
Yogeeraj
============================================================
SQL> show parameter sort_area

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size              integer 65536
sort_area_size                       integer 262144
SQL> create table t as (select 'SORTING OF LENGTH 20' TEST from all_objects);

Table created.

SQL> DESC T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST                                               CHAR(20)

SQL> alter session set sort_area_size = 65500;

Session altered.

SQL> set autotrace on stat

SQL> SQL> select count(1) from (select TEST from t
  2  where  rownum < 1331 order by 1);

  COUNT(1)
----------
      1330


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
          9  consistent gets
         12  physical reads
          0  redo size
        296  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL> select count(1) from (select TEST from t
  2  where  rownum < 1332 order by 1);

  COUNT(1)
----------
      1331


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
          9  consistent gets
          5  physical reads
          0  redo size
        296  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL>

SQL> alter session set sort_area_size = 1024000;

Session altered.

SQL> select count(1) from (select TEST from t
  2  where  rownum < 1777 order by 1);

  COUNT(1)
----------
      1776


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
         11  consistent gets
         15  physical reads
          0  redo size
        296  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL>

SQL> select count(1) from (select TEST from t
  2  where  rownum < 1778 order by 1);

  COUNT(1)
----------
      1777


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
         11  consistent gets
          7  physical reads
          0  redo size
        296  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL>

SQL> select * from v$version;

BANNER
------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

-------------------------
Parameters in my Init.ora
-------------------------
control_files = ("/d02/oradata/cmtdb/control01.ctl", "/d02/oradata/cmtdb/control02.ctl", "/d02/oradata/cmtdb/control03.ctl") 
open_cursors = 350
max_enabled_roles = 60
db_block_buffers = 10000
shared_pool_size = 157286400
large_pool_size = 20971520
java_pool_size = 20971520
log_checkpoint_interval = 100000
log_checkpoint_timeout = 1800
processes = 275
og_buffer = 262144
timed_statistics = true
log_archive_start = true
log_archive_dest_1 = "location=/d01/app/oracle/admin/cmtdb/arch"
log_archive_format = arch_%t_%s.arc
background_dump_dest = /d01/app/oracle/admin/cmtdb/bdump
core_dump_dest = /d01/app/oracle/admin/cmtdb/cdump
user_dump_dest = /d01/app/oracle/admin/cmtdb/udump
db_block_size = 8192
remote_login_passwordfile = exclusive
remote_os_authent=true
os_authent_prefix = "ops$"
job_queue_processes = 4
job_queue_interval = 60
distributed_transactions = 80
open_links = 4
local_listener = "listener_1"
compatible = "8.1.7.3"
sort_area_size = 262144
sort_area_retained_size = 65536
NLS_DATE_FORMAT=DD/MM/YYYY
utl_file_dir = * 
sessions=400
session_cached_cursors=100
_trace_files_public=true
db_file_multiblock_read_count=32   
resource_limit = true              
============================================================
 

Tom Kyte
July 20, 2002 - 10:51 am UTC

play with it (list the original guy did)

find your "break even" points.

A reader, October 17, 2002 - 2:54 pm UTC

Tom,
I tried to use the script disk_sorts.sql that is showed in the website that you recommended, but I am not able to run it because I don't have the table x_$kcctf,x_$ktfthc and sys.x_$kcftio.
How can I do in order to create these tables?
I checked all the scripts on /oracle/rdbms/admin (grep kcctf *)and none of them use these objects.
Is there any script that I can use in order to create them?

Thanks

Tom Kyte
October 17, 2002 - 3:13 pm UTC

that is the problem with x$ tables -- we change them (and they are undocumented).

You cannot create these tables, they are burned into the kernel itself. They are compiled into Oracle -- they are not real tables, they are data structures.

A reader, October 17, 2002 - 4:30 pm UTC

Tom,
Thanks for answering me.
My db is Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production. I saw that the "reader -07/19/02" who run the same script and also you had run the script on a 8.1.7.3.0 version. So I should be able to run the script too, I mean how can I do in order to create those table that script need.

Thanks for your help.

Regarding missing x_$kcctf etc.

Padders, October 17, 2002 - 5:51 pm UTC

Regarding missing x_$kcctf etc. I believe Steve Adams typically creates a series of views over x$ fixed tables and his scripts utilise these views. Script to create so called 'x_$ views' is also available on his site.

RE: ...in order to create those table...

Mark A. Williams, October 17, 2002 - 5:54 pm UTC

RE: ...in order to create those table...

PMFJI...

Review Tom's answer...

1) The "x$ tables" are not "real" tables, they are structures that are burned into the Oracle binary.

2) There is no script to create them.

3) They are not publicly documented for a reason...

4) Here's a small hint: They, in fact, are present, you just don't know how to access them... BUT you don't need to access them.

5) Ask yourself if there is a better way to get the information needed to tune disk sorts. For example, Statspack? Maybe v$sysstat? Or maybe chapters 19 and 20 of the "Oracle8i Designing and Tuning for Perfomance" guide? There are sections in those chapters dedicated to this exact topic.

If you really need to tune the sort_area_* parameters, you can get the information you need from sources other than the "mystical, magic, and all-powerful x$ tables"...

Anyway, just throwing a suggestion or two out...

- Mark

OK

Gerhard, February 11, 2004 - 11:08 am UTC

Dear Sir,
Regarding x$views,Is there a way to use them or query them?Please specify some of the tables you use.
Bye!

Tom Kyte
February 11, 2004 - 12:43 pm UTC

I do not use x$ views.

Any way to find out how much memory allocated for sort at any time

Belinda, March 11, 2004 - 2:16 pm UTC

Tom,

Is there any way to find out how many users concurrently doing sorting and being allocate memory for sorting at a particular moment? we would like to know the high watermark of the memory allocations for sort. our sort_area_size =16MB and sort_area_retained_size =4MB

The V$sort_usage and v$sort_segment only tell us the sorts being carried out in the temp tablespace, but we want to know physical memory allocated for sorts at a particular time.

Tom Kyte
March 11, 2004 - 2:26 pm UTC

it isn't there -- you'd want to use pga_aggregate_target in 9i and that'll give you this sort of detailed information.

the only thing you know in 8i is how many were done in memory (less then 16m), how many were not (> 16m) really....

A reader, March 11, 2004 - 3:46 pm UTC

Tom,

In 9.2, pga_aggregate_target is the upper bound for sort/hash memory, but how to find out the amount of memory actually being used at any given point of time by the processes?

Tom Kyte
March 11, 2004 - 3:49 pm UTC

look at the statistics that begin with workarea...

Sort_area_size and effect on "physical reads"

AR, August 12, 2004 - 8:29 pm UTC

Tom,
I’m trying to simulate the effects of increasing sort_area_size based on the example from your book. Strangely, my “physical reads” actually increase considerably after upping the sort_area_size. Could you please explain what might be going on?

Note : This is on 7.3.4, AIX 4.3

schaix:/app/oracle> cat a.sql
select * from t t1,t t2 where t1.object_id=t2.object_id;
..
SVRMGR> show parameter sort
NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
nls_sort                            string
sort_area_retained_size             integer 65536
sort_area_size                      integer 512000
sort_direct_writes                  string  AUTO
sort_read_fac                       integer 5
sort_spacemap_size                  integer 512
sort_write_buffer_size              integer 32768
sort_write_buffers                  integer 2
..
schaix:/app/oracle> sqlplus sys
Connected to:
Oracle7 Server Release 7.3.4.0.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.0.0 - Production

SQL> create table t as select * from all_objects;

Table created.

SQL> set autotrace traceonly
SQL> alter session set sort_area_size=512000;

Session altered.

SQL> @a.sql

DELETED RESULTS – since it was a COLD cache..am running it again

SQL> @a.sql

8012 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
          0  recursive calls
       1462  db block gets
        364  consistent gets
          0  physical reads
          0  redo size
    1561787  bytes sent via SQL*Net to client
       6407  bytes received via SQL*Net from client
        537  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
       8012  rows processed

Now to increase sort_area_size..cache still warm

SQL> @a.sql

8012 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
          0  recursive calls
        376  db block gets
        364  consistent gets
        356  physical reads
          0  redo size
    1561787  bytes sent via SQL*Net to client
       6407  bytes received via SQL*Net from client
        537  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
       8012  rows processed


Notice how my physical reads went up from 0 to 356, when I upped the sort_area_size from 500K to 1M. 

Then I tried upping the sort_area_retained_size. (cache still warm)
SQL> alter session set sort_area_retained_size=512000;

Session altered.

SQL>  alter session set sort_area_size=512000;

Session altered.

SQL> @a.sql

8012 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
          0  recursive calls
       1104  db block gets
        364  consistent gets
          0  physical reads
          0  redo size
    1561787  bytes sent via SQL*Net to client
       6407  bytes received via SQL*Net from client
        537  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
       8012  rows processed

SQL> alter session set sort_area_retained_size=512000;

Session altered.

SQL> alter session set sort_area_size=1024000;

Session altered.

SQL> @a.sql

8012 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
          0  recursive calls
        376  db block gets
        364  consistent gets
        356  physical reads
          0  redo size
    1561787  bytes sent via SQL*Net to client
       6407  bytes received via SQL*Net from client
        537  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
       8012  rows processed

Again, my physical reads shot up from 0 to 356. Why is this happening?
 

Tom Kyte
August 13, 2004 - 9:56 am UTC

set sort area retained to 0 or sort area size. this is 7.3, really really really (like really) old...... like really really old.

Re: Sort_area_size and effect on "physical reads"

AR, August 12, 2004 - 11:43 pm UTC

Tom,
As for my above long post - once I upped the sort_area_retained_size all the way to 1M (and the sort_area_size to 1M), I was able to get the "physical reads" down to 0.

I don't understand why I had to up the sort_area_retained_size considerably in order to see any difference in "physical reads". Why didn't upping the sort_area_retained_size from 65536 to 512000 (with a sort_area_size of 1M) have ANY effect on the physical reads value? But once I upped it to 1M, they disappeared. Could you please explain?

The way I interpret the above results - merely increasing the sort_area_size won't automatically mean better performance during sorts (atleast with an RBO). One ought to increase the sort_area_retained_size as well. Test, test ofcourse :).

Re: Sort_area_size and effect on "physical reads"

AR, August 13, 2004 - 10:53 am UTC

Tom,
It'll be great if you could answer the above followup question. Why didn't increasing the sort area retained from a small value to 0.5m have NO effect on physical reads. Why did I have to up it all the way to sort area size?

Thanks for your time.
PS : I realize that I'm testing on an ancient version. I was under the assumption that besides the fact that 9i allows for "automatic" PGA management, there wasn't a change in the underlying technology. Yes, I will test on 9i as well.

Tom Kyte
August 13, 2004 - 5:41 pm UTC

you are missing out on all of the IO done to/from temp here. not seeing all of the information. a 9ir2 trace with IO's associated with the steps in the plan would make this really easy to look at.

you might want to query up v$sesstat to see the true io's done in each case (i don't have easy access to a 7.3 release from where I'm at right now -- only over telnet via sattellite and if you've ever tried to do telnet over a high latency link, well, you'll understand why I'm not eager to provide scads of examples right now :)

PGA work areas not reclaimed

SFT, February 28, 2005 - 3:37 am UTC

Hello Tom,

One of our applications generates SELECT statements that depending on user input
may result in large sort/hash operations.
Therefore I hard-coded
ALTER SESSION SET SORT_AREA_SIZE=<40M>;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=<2M>;
The app runs on both Ora8 and Ora9.
Then I noticed that sometimes PGA memory size jumps to very high values and
stays there until the session is finished.
It looks like it is so on Ora8 only.
I run simple test that shows (?) that Ora8 does not free SQL work area down to
SORT_AREA_RETAINED_SIZE while Ora9 seemingly frees all sort area
regardless of SORT_AREA_RETAINED_SIZE.
Can you please interpret these results? How can I 'force' Ora8 to return
memory to OS?
Here is the test:

--*********************************************************
-- Oracle8i Enterprise Edition Release 8.1.7.4.1
TEST8> CREATE TABLE T AS (SELECT * FROM USER_OBJECTS);
Table created.
TEST8> DECLARE
2 num_rows PLS_INTEGER := 0;
3 BEGIN
4 WHILE num_rows < 100000 LOOP
5 INSERT INTO T SELECT * FROM USER_OBJECTS;
6 SELECT COUNT(*) INTO num_rows FROM T;
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.

TEST8> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed.
TEST8> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'T';
NUM_ROWS
--------------------
102284

TEST8> ALTER SESSION SET SORT_AREA_SIZE=20000000;
Session altered.
TEST8> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=1000000;
Session altered.

TEST8> @mystat "session pga memory"
NAME VALUE
------------------------------ -------------
session pga memory 1009576

TEST8> SET AUTOTRACE TRACEONLY
TEST8> SELECT * FROM T ORDER BY OBJECT_ID;
102284 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1310 Card=102284 Bytes=6853028)
1 0 SORT (ORDER BY) (Cost=1310 Card=102284 Bytes=6853028)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=174 Card=102284 Bytes=6853028)


Statistics
----------------------------------------------------------
8 recursive calls
7 db block gets
1142 consistent gets
1175 physical reads
0 redo size
13301026 bytes sent via SQL*Net to client
112994 bytes received via SQL*Net from client
10233 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
102284 rows processed

TEST8> SET AUTOTRACE OFF

TEST8> @mystat2
NAME V DIFF
------------------------------ ------------- -------------
session pga memory 13555904 12546328

-- So we have about 12M PGA still hanging around

--*********************************************************
-- Oracle9i Release 9.2.0.6.0 - Production

-- Table create statement etc. is the same as for Ora8:
........................

TEST9> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'T';
NUM_ROWS
--------------------
100080

TEST9> select name, value from v$parameter where name like 'workarea%';
NAME VALUE
---------------------------------------- ------
workarea_size_policy MANUAL

TEST9> ALTER SESSION SET SORT_AREA_SIZE=20000000;
Session altered.
TEST9> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=1000000;
Session altered.

TEST9> @mystat "session pga memory"
NAME VALUE
------------------------------ -------------
session pga memory 344600

TEST9> SET AUTOTRACE TRACEONLY
TEST9> SELECT * FROM T ORDER BY OBJECT_ID;

100080 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1364 Card=100080 Bytes=7706160
1 0 SORT (ORDER BY) (Cost=1364 Card=100080 Bytes=7706160)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=119 Card=100080 Bytes=7706160)

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
1226 consistent gets
1248 physical reads
0 redo size
13763615 bytes sent via SQL*Net to client
110574 bytes received via SQL*Net from client
10013 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100080 rows processed

TEST9> SET AUTOTRACE OFF
TEST9> @mystat2
NAME V DIFF
------------------------------ ------------- -------------
session pga memory 344600 0

-- PGA work areas are fully freed (?)
-- During SELECT execution I ran:
SELECT * FROM V$PGASTAT
.............
total PGA used for manual workareas 1999872 bytes
.............

Thanks in advance


Tom Kyte
February 28, 2005 - 7:49 am UTC

do you have access to "Expert one on one Oracle"? If so, I work through this. pga memory is handled very differently in 9i and above -- this is true. But even in 8i, the pga memory was "freed", just for reuse in that process. In 9i, memory can be memmapped and unmapped in certain cases/OS's


so the pga memory is free, it is assocated with that process however, you can call
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/index.htm <code>

but it most likely won't really free anything unless you are really luck since memory is allocated in a heap and a single byte of memory allocated AFTER the sort area was allocated but still in use would prevent the pga from shrinking.

How to identify session changed parameters in a session?

Mathew Butler, July 06, 2005 - 5:50 am UTC

I have a logon trigger that alters session parameters for performance reasons. These changes only occur for a specific user.

I need to verify that these session changes are correctly restricted to this specific user.

Is there a simple way of doing this? A V$ view maybe? All I can think of is to carry out a 10053 trace of a query for a session that should be unaffected by this change, and then view the trace header where it lists the optimiser paramaters and their current sessions.

Is there a better way?

Thanks as always.

Tom Kyte
July 06, 2005 - 8:01 am UTC

in 10g, you can peek at the optimizer environment from another session, in 9i and before, there really isn't any technique for doing so.

IF your login trigger has:

if (user='bob')
then
do this
end if;

it would be fairly safe to assume it happens for bob only. Further, unless performance tanks for these other users - "so what"?

but you would see child cursors for this special user (different optimization parameters - child cursor) so looking for the existence of a child cursor for the affected queries will work as well -- to indicate there are two versions of it, two environments (v$sql_shared_cursor would tell you why there are child cursors)

THanks

Mathew Butler, July 06, 2005 - 11:07 am UTC

Sounds like a 10053 is the definitive way tro do this before 10G.

We have something like;

if (user='bob')
then
do this
end if;

and I agree that this code is well behaved and well understood. However, I've been asked to demonstrate that these changes won't impact other users.

Can you point me to doco on the 10G peeking at other sessions parameter settings? Either doco or a demo :o)

Many Thanks.


Tom Kyte
July 06, 2005 - 12:28 pm UTC

ops$tkyte-ORA10G> desc v$SES_OPTIMIZER_ENV
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 SID                                               NUMBER
 ID                                                NUMBER
 NAME                                              VARCHAR2(40)
 ISDEFAULT                                         VARCHAR2(3)
 VALUE                                             VARCHAR2(25)
 
ops$tkyte-ORA10G> select name, value from v$SES_OPTIMIZER_ENV where sid = ( select sid from v$mystat where rownum=1 );
 
NAME                           VALUE
------------------------------ -------------------------
parallel_execution_enabled     true
optimizer_features_enable      10.1.0.4
cpu_count                      1
active_instance_count          1
parallel_threads_per_cpu       2
hash_area_size                 131072
bitmap_merge_area_size         1048576
sort_area_size                 65536
sort_area_retained_size        0
db_file_multiblock_read_count  8
pga_aggregate_target           24576 KB
parallel_query_mode            enabled
parallel_dml_mode              disabled
parallel_ddl_mode              enabled
optimizer_mode                 all_rows
cursor_sharing                 exact
star_transformation_enabled    false
optimizer_index_cost_adj       100
optimizer_index_caching        0
query_rewrite_enabled          true
query_rewrite_integrity        enforced
workarea_size_policy           auto
optimizer_dynamic_sampling     2
statistics_level               typical
skip_unusable_indexes          true
 
25 rows selected.

ops$tkyte-ORA10G> select sid, count(*) from v$SES_OPTIMIZER_ENV group by sid;
 
       SID   COUNT(*)
---------- ----------
       266         25
       267         25
       268         25
       269         25
       270         25
       271         25
       272         25
       273         25
       274         25
       275         25
       276         25
       277         25
       278         25
       279         25
       280         25
 
15 rows selected.
 

To increase sort_area_size

Sean, October 26, 2005 - 11:06 am UTC

Tom,
We have the message in alert.log of the database yesterday as we experienced the performance issue.

Tue Oct 25 14:59:22 2005
WARNING: aiowait timed out 4 times
Tue Oct 25 14:59:27 2005
WARNING: aiowait timed out 1 times
Tue Oct 25 14:59:31 2005
WARNING: aiowait timed out 3 times
Tue Oct 25 14:59:38 2005
WARNING: aiowait timed out 4 times
Tue Oct 25 15:00:20 2005

I looked up Metalink for the issue, and found
Note:163530.1
Database Hangs With Aiowait Time Out Warning if Async IO Is True

The Note segguested to increase sort_area_size.

We have sort_area_size 1M.

How much could we increase it? and what's the negative impact if it becomes very large?

Tom Kyte
October 27, 2005 - 3:17 am UTC

I'm not sure I agree with that note

You are having a problem with AIO, the suggestion is "increase sort area size" (which in 9i and above might not even be used!) to avoid sorting to disk so as to not do AIO.

I would be looking to fix the problem with AIO, not trying to avoid doing AIO (since you can never be sure you have avoided doing AIO in all cases!!)

I'd be more inclined to look at note Note 222989.1

I have added a comment to that note for the author with my inputs.

WORKAREA_SIZE_POLICY in DW application

Suvendu, October 30, 2005 - 10:53 am UTC

Hi Tom,
Most of cases you pointed to use PGA_AGGREGATE_TARGET instead of *_AREA_SIZE parameter. But I was going thru one DW article where author telling to avoid this and to use WORKAREA_SIZE_POLICY=MANUAL mode in DW application. Could you please comment on this?

</code> http://www.evdbt.com/SuperChargingStarTransformations.doc <code>

"In Oracle 9.0, automatic PGA memory management was introduced using the pga_aggregate_target parameter. When this feature is used, the above parameters are overridden. In larger data warehouse environments, this results in signifcantly impaired performance of longer-running queries. The fundamental problem with automatic PGA memory management is that it simply doesnÂ’t use the high amounts of available memory. It instead chooses to use large amounts of temporary disk space, which results in very long execution times. For optimal star transformation performance, manual PGA memory management should be used along with the parameters discussed above."

Thanking you a lot for taking my question and for your time too.

Regards,
Suvendu


Tom Kyte
October 31, 2005 - 2:54 am UTC

All things relating to "automatic" features follow the 80/20 rule - they work perfectly 80 percent of the time and not so in other cases.


I'm not sure I agree 100% with the premise of the paper. If there are lots of concurrent data warehouse users - the ability to the system to dynamically change the amount of memory available to each session as the concurrent workload goes up and down - could be crucial.

The sort_area/hash_area_size parameters are sort of "one size fits all". 100 users - 1 users - you would get to use the same amount of the resource, which could be really bad.


I think you need to think about a couple more variables before writing off the automatic pga feature - it is designed to share the available ram amongst all concurrent sessions without using more memory than you actually have.

So, if you were a system with a) a ton of available memory and b) a known maximum concurrent workload and c) the known workloads concurrent use of memory (sort/hash areas) would not exceed physical ram - they may have a point.



Large sort area size potentially detrimental?

Andrew, November 10, 2005 - 11:21 pm UTC

Hi Tom

While reading about the fancy new Hash based GROUP BYs I stumbled upon this piece of documentation

www.oracle.com/technology/products/bi/ db/10g/pdf/twp_general_sort_performance_10gr2_0605.pdf

What puzzled me is that results on large row sets can be significantly worse when you set sort_area_size to 700Mb instead of 10Mb (both for 10gR1 and 10gR2) by a factor of 2! In other words, if disk spillage occurs, it seems that it's better to have a small remainder of data/sort_area_size rather than a large sort_area_size?

Thanks

Tom Kyte
November 12, 2005 - 8:11 am UTC

yes, the old sort was designed to page - memory used to be "not as available".

hash memory, hash disks?

A reader, April 27, 2006 - 2:55 am UTC

I have the following query for cheking
how many sorts were in memory and how many used
disk sort in 9i Release 2.

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');

But how can I see that how man hash were in memory
and how many hash in disk.

Thanks




Tom Kyte
April 27, 2006 - 2:59 pm UTC

don't know that you can.

response from metalink

A reader, May 02, 2006 - 2:04 am UTC

I posted the same question to metalink and here is
there response.

"
There is no easy way to measure the usage of HASH JOINS on your instance. You could have ne
ed to trace all of your user sessions to identify which Explain Plan you are usi
ng on your user queries. By using a grep Unix function, you could locate the usa
ge of Hash Joins as much as possible.

No built in way ( like v$sysstat - sort ) to locate Hash Joins otherwise.
"

Sorting

A reader, March 08, 2007 - 8:52 am UTC

Hi Tom
To sort a 16G table in memory; what parameters would you recommend?
The box has 8 sparc CPUs; 24G real memory; only instance; everything is available to reduce the time.

This table with single CPU (no parallel) 200M sort_area_size (workarea_policy= manual) takes
1 memory sort, and
1 disk sort and completes in 49 mins.

With parallelism 100M sort_area_size (workarea_policy=manual) takes
1 memory sort
14 disk sorts and completes in 50 mins.

Thanks for your extremly valuable time.
Tom Kyte
March 08, 2007 - 11:02 am UTC

well, you could start by using more memory? why not do that?

Sort AreaSize

Emmanuel, November 06, 2008 - 3:08 pm UTC

Hi tom,

I've been trying to understand about the parameter sort_area_size therefore I am reviewing your book in Chapter 4 Section Manual PGA Memory so I decided to implement the exercises that you propose.

So in session 158
SQL> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     1048576


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

       SID
----------
       158

SQL> alter session set workarea_size_policy=manual;

Session altered.

--I modify the sort_area_size to 200 mb.

SQL> alter session set sort_area_size = 209715200;

Session altered.

SQL> sta memory_test3





--Another Session
SQL> sta reset_stat

Table dropped.


Table created.

Enter value for 1: 158

PL/SQL procedure successfully completed.




So after I run the test and monitor the UGA and PGA from another session meeting that the amendment of the sort size had no effect

SQL> sta watch_stat

7 rows merged.

--Before running the test

NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0
physical reads direct temporary tablespace                                0
physical writes direct temporary tablespace                               0
session pga memory                                                   454228
session pga memory max                                               454228
session uga memory                                                   156628
session uga memory max                                               156628

7 rows selected.



--After running the test

NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                            34150      34150
physical writes direct temporary tablespace                           34150      34150
session pga memory                                                   519764      65536
session pga memory max                                              1896020    1441792
session uga memory                                                   222092      65464
session uga memory max                                              1596836    1440208


Despite varying the sort_area_size of my session it only took 1 mb.


The only way to take the changes in the session was running the instruction alter session set sort_area_size = 209715200 2 times

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

       SID
----------
       158

SQL>  alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size = 209715200;

Session altered.

SQL> alter session set sort_area_size = 209715200;

Session altered.

SQL> sta memory_test3 


So this was the result in another session

--Before running the test
NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0
physical reads direct temporary tablespace                                0
physical writes direct temporary tablespace                               0
session pga memory                                                   519764
session pga memory max                                               519764
session uga memory                                                   222092
session uga memory max                                               222092

7 rows selected.


--After running the test

SQL> sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                                0          0
physical writes direct temporary tablespace                               0          0
session pga memory                                                   519764          0
session pga memory max                                            186773076  186253312
session uga memory                                                   222092          0
session uga memory max                                            186270780  186048688

7 rows selected.


This time the session took 177 mb as I wanted


I'am in Oracle 10.2.0.4


Why this happens?
Tom Kyte
November 11, 2008 - 2:29 pm UTC

I cannot explain that. There should obviously be no special effect by calling the alter session twice.

share with me all of your non-default init.ora settings.


select name || ' = ' || value from v$parameter where isdefault = 'FALSE';

Parameters

Emmanuel, November 12, 2008 - 12:39 pm UTC

This is the result of the query
NAME||'='||VALUE
-------------------------------------------------------------------------------------------------------------------------------------
processes = 150
sga_max_size = 734003200
shared_pool_size = 159383552
streams_pool_size = 264241152
nls_length_semantics = BYTE
resource_manager_plan =
sga_target = 734003200
control_files = /u01/app/oracle/oradata/nirvana/control01.ctl, /u01/app/oracle/oradata/nirvana/control02.ctl, /u01/app/oracle/oradata
/nirvana/control03.ctl

db_block_size = 8192
db_16k_cache_size = 12582912
compatible = 10.2.0.1.0
log_archive_dest_1 = LOCATION=/home/oracle/u02/app/oracle/oradata/nirvana/archive_logs
db_file_multiblock_read_count = 16
db_recovery_file_dest = /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size = 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 3600
remote_login_passwordfile = EXCLUSIVE
db_domain =
global_names = TRUE
dispatchers = (PROTOCOL=TCP) (SERVICE=nirvanaXDB)
job_queue_processes = 30
_job_queue_interval = 1
parallel_max_servers = 40
background_dump_dest = /u01/app/oracle/admin/nirvana/bdump
user_dump_dest = /u01/app/oracle/admin/nirvana/udump
core_dump_dest = /u01/app/oracle/admin/nirvana/cdump
audit_file_dest = /u01/app/oracle/admin/nirvana/adump
sort_area_size = 1048576
db_name = nirvana
open_cursors = 300
pga_aggregate_target = 200278016
aq_tm_processes = 0

34 rows selected.



Thank you very much for your invaluable help
Tom Kyte
November 13, 2008 - 4:33 pm UTC

create table t as select * from all_objects where 1=0;
exec dbms_stats.set_table_stats( user, 'T', numrows=> 1000000 );

column now new_val now
select 'tag_'||to_char( sysdate,'yyyymmddhh24miss' ) now from dual;

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1048576;
select * from t &now order by 1, 2, 3, 4;
alter session set sort_area_size = 2097152;
select * from t &now order by 1, 2, 3, 4;
alter session set sort_area_size = 3145728;
select * from t &now order by 1, 2, 3, 4;
alter session set sort_area_size = 4194304;
select * from t &now order by 1, 2, 3, 4;

select sql_text from v$sql where sql_text like 'select * from t &now order by 1, 2, 3, 4%';



if you run that, do you see:

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like 'select * from t &now order by 1, 2, 3, 4%';
old   1: select sql_text from v$sql where sql_text like 'select * from t &now order by 1, 2, 3, 4%'
new   1: select sql_text from v$sql where sql_text like 'select * from t tag_20081113151005 order by 1, 2, 3, 4%'

SQL_TEXT
-------------------------------------------------------------------------------
select * from t tag_20081113151005 order by 1, 2, 3, 4
select * from t tag_20081113151005 order by 1, 2, 3, 4
select * from t tag_20081113151005 order by 1, 2, 3, 4
select * from t tag_20081113151005 order by 1, 2, 3, 4



four copies in the shared pool?

Sort AreaSize

Emmanuel, November 13, 2008 - 8:01 pm UTC

Yes I see four copies in the shared pool.

emmanuel@nirvana> column now new_val now
emmanuel@nirvana> select 'tag_'||to_char( sysdate,'yyyymmddhh24miss' ) now from dual;

NOW
------------------
tag_20081113172505

emmanuel@nirvana> alter session set workarea_size_policy = manual;

Session altered.

emmanuel@nirvana> alter session set sort_area_size = 1048576;

Session altered.

emmanuel@nirvana> select * from t &now order by 1, 2, 3, 4;
old   1: select * from t &now order by 1, 2, 3, 4
new   1: select * from t tag_20081113172505 order by 1, 2, 3, 4

no rows selected

emmanuel@nirvana> alter session set sort_area_size = 2097152;

Session altered.

emmanuel@nirvana> select * from t &now order by 1, 2, 3, 4;
old   1: select * from t &now order by 1, 2, 3, 4
new   1: select * from t tag_20081113172505 order by 1, 2, 3, 4

no rows selected

emmanuel@nirvana> alter session set sort_area_size = 3145728;

Session altered.

emmanuel@nirvana> select * from t &now order by 1, 2, 3, 4;
old   1: select * from t &now order by 1, 2, 3, 4
new   1: select * from t tag_20081113172505 order by 1, 2, 3, 4

no rows selected

emmanuel@nirvana> alter session set sort_area_size = 4194304;

Session altered.

emmanuel@nirvana> select * from t &now order by 1, 2, 3, 4;
old   1: select * from t &now order by 1, 2, 3, 4
new   1: select * from t tag_20081113172505 order by 1, 2, 3, 4

no rows selected

emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from t &now order by 1, 2, 3, 4%';
old   1: select sql_text from v$sql where sql_text like 'select * from t &now order by 1, 2, 3, 4%'
new   1: select sql_text from v$sql where sql_text like 'select * from t tag_20081113172505 order by 1, 2, 3, 4%'

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from t tag_20081113172505 order by 1, 2, 3, 4
select * from t tag_20081113172505 order by 1, 2, 3, 4
select * from t tag_20081113172505 order by 1, 2, 3, 4
select * from t tag_20081113172505 order by 1, 2, 3, 4


Tom Kyte
November 18, 2008 - 5:36 pm UTC

I cannot reproduce, I cannot explain why you see what you say you saw.

As long as differing plans were built for each - it should not take "two alter sessions"

would you be willing to run the test and verify that a new child cursor was created with just ONE alter session? We know it must be true for two - since you observed the change.

sort_area_size

Emmanuel, November 19, 2008 - 4:28 pm UTC

Ok I'm going to do the test one more time and this time I will verify that a new child cursor has been created
emmanuel@nirvana>  select sid from v$mystat where rownum<=1;

       SID
----------
       132

emmanuel@nirvana> alter session set workarea_size_policy = manual;

Session altered.

emmanuel@nirvana> alter session set sort_area_size = 4194304;

Session altered.

emmanuel@nirvana> sta memory_test3


--Another Session
--Before run the test

emmanuel@nirvana> sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0
physical reads direct temporary tablespace                                0
physical writes direct temporary tablespace                               0
session pga memory                                                   716372
session pga memory max                                               716372
session uga memory                                                   222092
session uga memory max                                               222092

7 rows selected.

--Another Session
--After run the test

NAME                                                                  VALUE       DIFF

---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                            34011      34011
physical writes direct temporary tablespace                           34011      34011
session pga memory                                                   585300    -131072
session pga memory max                                              2551380    1835008
session uga memory                                                   287556      65464
session uga memory max                                              1975684    1753592

7 rows selected.

emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from mul_log_objeto tag_20081119105528 order by 2,3,4';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from mul_log_objeto tag_20081119105528 order by 2,3,4

It only took 1 mb and just one child cursor was created.


--Same session 132

emmanuel@nirvana> alter session set sort_area_size = 6291456;

Session altered.

emmanuel@nirvana> sta memory_test3


--Another Session After run the test
emmanuel@nirvana> sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                            54281      20270
physical writes direct temporary tablespace                           54281      20270
session pga memory                                                   585300          0
session pga memory max                                              7335508    4784128
session uga memory                                                   287556          0
session uga memory max                                              4559092    2583408

7 rows selected.

emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from mul_log_objeto tag_20081119105528 order by 2,3,4';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4


This time dispite I've asigned 6 mb It took 4 mb. Seems that Oracle took the memory previously asigned by the last alter session. 
Two child cursor were created this time.


--Same session 132
emmanuel@nirvana> alter session set sort_area_size = 8388608;

Session altered.

emmanuel@nirvana> sta memory_test3


--Another Session after run the test
emmanuel@nirvana> sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                            74543      20262
physical writes direct temporary tablespace                           74543      20262
session pga memory                                                   585300          0
session pga memory max                                              7335508          0
session uga memory                                                   287556          0
session uga memory max                                              6588964    2029872

7 rows selected.


emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from mul_log_objeto tag_20081119105528 order by 2,3,4';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4

This time It took 6 mb and I just asigned 8 mb. Again Oracle took the memory previously asigned by the last alter session.
3 child cursor were created this time.
one for 4mb
one for 6mb
and the last for 8mb


--Same session 132
emmanuel@nirvana> alter session set sort_area_size =10485760;

Session altered.

emmanuel@nirvana> sta memory_test3 


--Another session after the test.

emmanuel@nirvana> sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                            94801      20258
physical writes direct temporary tablespace                           94801      20258
session pga memory                                                   585300          0
session pga memory max                                             10481236    3145728
session uga memory                                                   287556          0
session uga memory max                                              8558820    1969856

7 rows selected.

emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from mul_log_objeto tag_20081119105528 order by 2,3,4';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4

--Same history It took 8mb when I was allocated 10mb and 4 child cursors

4 child cursor were created this time.
one for 4mb
one for 6mb
one for 8mb
and the last for 10mb


This time I returned to perform the test without altering the sort area size, hoping that Oracle takes the memory allocated to 10MB.

emmanuel@nirvana> sta memory_test3

sta watch_stat

7 rows merged.


NAME                                                                  VALUE       DIFF
---------------------------------------------------------------- ---------- ----------
calls to kcmgas                                                           0          0
physical reads direct temporary tablespace                           115059      20258
physical writes direct temporary tablespace                          115059      20258
session pga memory                                                   585300          0
session pga memory max                                             10481236          0
session uga memory                                                   287556          0
session uga memory max                                              8558820          0


emmanuel@nirvana> select sql_text from v$sql where sql_text like 'select * from mul_log_objeto tag_20081119105528 order by 2,3,4';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4
select * from mul_log_objeto tag_20081119105528 order by 2,3,4

Oracle did not take



 
I have tested it on Oracle for windows and linux 32 bits and the efect it's the same

This is the test on Windows

Sort_area_size      Report of memory allocated
  4mb                          2mb
  6mb                          5.5mb
  8mb                          6.6mb
  10mb                         8.2mb
  200mb                        10mb


After execute alter session two times

alter session set sort_area_size = 209715200;
alter session set sort_area_size = 209715200;

Sort_area_size      Report of memory allocated
    200mb                  178 mb 



Thanks for your time

I really appreciate your help
Tom Kyte
November 24, 2008 - 11:06 am UTC

I think I found a bug related to this (not filed yet) in 10.2.0.4 and 11.1.0.6 with sort area size. I found it in another example - but it only takes a single session to see it so it'll be easy to file.


sort_area_size

Emmanuel, November 19, 2008 - 4:40 pm UTC

Sorry I forgot to mention that both tests were carried out in version 10.2.0.4

Where is the twp_general_sort_performance_10gr2_0605.pdf

A reader, May 18, 2011 - 10:10 am UTC

Tom,

The twp_general_sort_performance_10gr2_0605.pdf mentioned in this thread earlier can't be found. Do you know the new link on Oracle site?

Thanks.

Tom Kyte
May 18, 2011 - 10:47 am UTC

Where is the twp_general_sort_performance_10gr2_0605.pdf

A reader, May 20, 2011 - 3:26 pm UTC

Thanks Tom.

It's a paid site and you have to give them your credit card number before you know if they actually have the document.
Tom Kyte
May 23, 2011 - 11:40 am UTC

I just used google to find it, you can do the same. When whitepapers become "old" we retire them.

I just clicked that link and read the paper however, not sure what issue you ran into - I certainly didn't pay for anything.

In fact, they even let me download it for free. It is on asktom now, click on files tab, look for it there.

twp_general_sort_performance_10gr2_0605.pdf

A reader, May 24, 2011 - 8:29 am UTC

Tom,

Thank you so much!

sorting taking too much time.

A reader, November 04, 2012 - 11:52 pm UTC

hi tom,

i have 20,30k rows to sort and it seems to take up quite awhile for results to return

1st) how do i know if too much time have been spend sorting data for a particular sql

2nd) how do i improve sorting speed ?

3rd) how/when do i know, i have to increase my sort_area_size ? or when do i know my sort_area_size is insufficient ?

Regards,
Noob
Tom Kyte
November 05, 2012 - 9:44 am UTC

show us the row source operation part of the tkprof (show us the entire section of the tkprof report for this particular query actually)