does it really take 30 seconds (it seems every time I get this sort of question is it really that -- well, it took 30 seconds to get the first pagefull in toad -- they didn't wait for the LAST row to come back).

Sorting generally must get all rows -- then sort -- then return. I will guess you really are comparing the time to get the first row from each query -- and that is not comparable.


Show us a TKPROF of the two queries....


It is not "waiting" for space in temp, it is using it.

well, you have to think about configurations as well.

shared server -- sort_area_retained in the UGA in the SGA. sort area size - in the PGA in the dedicated server.

But -- is the sort_area_size not returned to the OS in dedicated server mode? Yes and no I say. If you have allocated memory, and then are not using it -- the OS can and will page it out - releasing the real physical memory for use by other processes.

I cannot comment on why your test did not show what you expected. I would need to see the test. I can say there is a good chance that the larger sort area would be useful. I can say that if I was using parallel query to rebuild -- I might want it smaller.

it is not tracked at the statement level -- you can find the sessions...

so that means that -- your "bottleneck" if any was not in any way related to using temp.

that is all that shows.

you actually have shown two important things:

o tuning blindly and using conventional wisdom to tune is silly.
o that Oracle actually used 16, 32, 64, 128 meg of ram (but obviously that was NOT the bottleneck)

DISK, writing 600meg to DISK and if you are in archive log mode, writing another X hundred meg there. Just means you were not cpu/memory bound, you were IO bound -- you had more then enough of those other resources.

At some point, all you are measuring anymore is "how long does it take to write this stuff".

So, you've done what is always best to do -- benchmarch, find the sweet spot

it is PER sort...

A single session may in fact have more than one sort going on at a time and in doing so may have N "sort area size" chunks of memory all going.

ok, uncle.

sort area retained size would be the more appropriate number here, if you negate the use of parallel query which does it by process or thread in child sessions.

sure -- it is OK, you would just be grabbing 2m for a sort of anything over 64k.

As long as that is OK with you, it is OK with everything -- and would cut down on the extent requests.


v$parameter has a set of columns that tell you if a parameter is modifiable system/session/etc.  

but for this, you would either change SAS

NUM                           : "612"
NAME                          : "sort_area_size"
TYPE                          : "3"
VALUE                         : "65536"
ISDEFAULT                     : "TRUE"
ISSES_MODIFIABLE              : "TRUE"
ISSYS_MODIFIABLE              : "DEFERRED"
ISMODIFIED                    : "FALSE"
ISADJUSTED                    : "FALSE"
DESCRIPTION                   : "size of in-memory sort work area"
UPDATE_COMMENT                : ""
-----------------

(it is modifiable only at the session level or at the system level "deferred"


ops$tkyte@ORA9IR2> alter system set sort_area_size = 64000 deferred;
 
System altered.


or you can rebuild temp -- create temporary tablespace.... and then alter your users to use the new temp and drop the old temp.


but 2m is fine -- I use 1m myself (for the extent size) with PGA_AGGREGATE_TARGET in 9i -- where I have no idea what the sort area size is...

beware of changing init.ora's in production -- be ready to change them back (eg: have some change mgmt in place so everyone knows what is going on, what has changed..) 

<quote>
When you set PGA_AGGREGATE_AREA then no need of setting these parameter,
</quote>

Let me expand that:

if ( version = 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
if ( connection is dedicated server )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set, shared server uses sort area size
end if
else
they do need to be set
end if
elsif (version > 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set
end if
end if




the _size parameters (sort, hash, bitmap) are all treated the same.

if you are using automatic pga sizing, they are dynamically allocated workareas that are sized based on the current load on the server.

if you are not, they are allocated in the PGA memory of the server process (except for the sort_area_retained_size which is allocated in the UGA memory which is in the PGA in dedicated server mode, and the SGA in shared server mode)

version 9i:
shared server -> sort_area_size is used regardless.
dedicated server AND workarea = auto -> pga_aggregate_target is used
dedicated server AND workarea = manual -> sort_area_size is used

version 10g
workarea=auto -> pga_aggregate_target
workarea= manual -> sort_area_size


you need not remove the sort_area_size, and since sessions could set workarea to manual - you might just want to leave it in with YOUR default.

well, that advice may or may not do a thing for you - the poster needed to provide a lot more information.

it really depends on what sort of memory management you are using.

Now, with a 200million row table - and 200mb of sort area size allowed (assuming manual memory management) that just means Oracle will use 200mb of memory BEFORE IT STARTS writing to disk. It will not prevent it, it might - but it might not. If you needed 1gb of memory - it will (in this case) obviously not avoid the write to temp.

consider:

a) parallel
b) nologging (don't forget to backup)

and set your sort_area_size appropriately OR the pga_aggregate_target appropriately.

<quote src=Expert Oracle Database Architecture>
Manual PGA Memory Management
In manual PGA memory management, the parameters that will have the largest impact on the size of your PGA, outside of the memory allocated by your session for PL/SQL tables and other variables, will be as follows:
* SORT_AREA_SIZE: The total amount of RAM that will be used to sort information before swapping out to disk.
* SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold sorted data after the sort is complete. That is, if SORT_AREA_SIZE was 512KB and SORT_AREA_RETAINED_SIZE was 256KB, then your server process would use up to 512KB of memory to sort data during the initial processing of the query. When the sort was complete, the sorting area would be “shrunk” down to 256KB, and any sorted data that did not fit in that 256KB would be written out to the temporary tablespace.
* HASH_AREA_SIZE: The amount of memory your server process would use to store hash tables in memory. These structures are used during a hash join, typically when joining a large set with another set. The smaller of the two sets would be hashed into memory and anything that didn’t fit in the hash area region of memory would be stored in the temporary tablespace by the join key.
These parameters control the amount of space Oracle will use to sort or hash data before writing (swapping) it to disk, and how much of that memory segment will be retained after the sort is done. The SORT_AREA_SIZE-SORT_AREA_RETAINED_SIZE is generally allocated out of your PGA, and the SORT_AREA_RETAINED_SIZE will be in your UGA. You can discover your current usage of PGA and UGA memory and monitor its size by querying special Oracle V$ views, also referred to as dynamic performance views.

.......


Automatic PGA Memory Management
Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to attempt to address a few issues:
* Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There was also much confusion over how those parameters actually worked and how memory was allocated.
* Manual allocation was a “one-size-fits-all” method: Typically as the number of users running similar applications against a database went up, the amount of memory used for sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used 10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size settings, everyone would pretty much use the same values all day long. Consider the previous example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we allowed ourselves to use went up. If you run that example for yourself, you will almost certainly see a decrease in response time as the amount of RAM available for sorting increases. Manual allocation fixes the amount of memory to be used for sorting at a more or less constant number, regardless of how much memory is actually available. Automatic memory management allows us to use the memory when it is available; it dynamically adjusts the amount of memory we use based on the workload.
* Memory control: As a result of the previous point, it was hard, if not impossible, to keep the Oracle instance inside a “box” memory-wise. You could not control the amount of memory the instance was going to use, as you had no real control over the number of simultaneous sorts/hashes taking place. It was far too easy to use more real memory (actual physical free memory) than was available on the machine.
Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a fixed-size piece of memory, so you can very accurately see how big it is, and that will be its total size (until and if you change that). You then tell Oracle, “This is how much memory you should try to limit yourself across all workareas—a new umbrella term for the sorting and hashing areas you use.” Now, you could in theory take a machine with 2GB of physical memory and allocate 768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close. Before I discuss why that is true, we’ll take a look at how to set up automatic PGA memory management and turn it on.
The process to set this up involves deciding on the proper values for two instance initialization parameters, namely
* WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort area and hash area size parameters to control the amount of memory allocated, or AUTO, in which case the amount of memory allocated will vary based on the current workload present in the database. The default and recommended value is AUTO.
* PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should allocate, in total, for all workareas used to sort/hash data. Its default value varies by version and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory management, you should explicitly set this parameter.
So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero value, you will be using the new automatic PGA memory management. You can “turn it on" in your session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
Note Bear in mind the previously discussed caveat that in Oracle9i, shared server connections will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10g and up, automatic PGA memory management is available to both connection types. It is important to properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections with Oracle9i.
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the same time not using more RAM than you want. Under manual memory management, this was virtually impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort operation that user would use up to 10MB for the sort workarea. If 100 users were doing the same, they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing a sort by himself could have used much more memory, and the 100 users should have used much less. That is what automatic PGA memory management was designed to do. Under a light workload, memory usage could be maximized as the load increases on the system, and as more users perform sort or hash operations, the amount of memory allocated to them would decrease—to obtain the goal of using all available RAM, but not attempting to use more than physically exists.
</quote>

.... Please advice me whether my query is correct. ....


I fell out of my chair when I read that, ouch, that hurts.


How in the world am I supposed to tell you if your query is correct????????

Only you know the question you are trying to answer - ONLY YOU.


Now, I look at your query, after putting it into a format human beings can read and understand:

select ...
  from ap_inv_hdr ,ap_inv_line,ap_pay_line,ap_dbcr_alloc_hdr
 where ap_inv_hdr.doc_type_code='APINV'
   and ap_inv_hdr.doc_num = ap_inv_line.doc_num
   and ap_inv_hdr.doc_num not in    (select ap_pay_line.doc_num
      from ap_inv_hdr,ap_dbcr_alloc_hdr,ap_pay_line
     where ap_inv_hdr.doc_type_code = ap_pay_line.org_doc_type_code
       and ap_inv_hdr.doc_num = ap_pay_line.doc_num
       and ap_inv_hdr.doc_type_code = ap_dbcr_alloc_hdr.org_doc_type_code
       And ap_inv_hdr.doc_num = ap_dbcr_alloc_hdr.org_doc_num)
   and ap_inv_hdr.doc_type_code not in
   (select ap_dbcr_alloc_hdr.org_doc_type_code
      from ap_inv_hdr,ap_dbcr_alloc_hdr,ap_pay_line
     where ap_inv_hdr.doc_type_code = ap_pay_line.org_doc_type_code
       and ap_inv_hdr.doc_num = ap_pay_line.doc_num
       and ap_inv_hdr.doc_type_code = ap_dbcr_alloc_hdr.org_doc_type_code
       And ap_inv_hdr.doc_num = ap_dbcr_alloc_hdr.org_doc_num)
/

I see you have four tables:

  from ap_inv_hdr ,ap_inv_line,ap_pay_line,ap_dbcr_alloc_hdr

but you only inner join two of them:

where ap_inv_hdr.doc_type_code='APINV'
   and ap_inv_hdr.doc_num = ap_inv_line.doc_num

and you cartesian join the other two

ap_pay_line,ap_dbcr_alloc_hdr

you do understand that if ap_pay_line has say 10,000 records and ap_dbcr_alloc_hdr has 10,000 records, that cartesian join will result in

100,000,000 records


and if the join between "ap_inv_hdr.doc_num = ap_inv_line.doc_num" results in 10,000 records after the NOT IN's are processed, your result set would be

1,000,000,000,000 records


I sort of am DOUBTFUL that this is the query you intended to code - but you are the only one that can answer that...

read about "not in" and "in"

select * from A
where (columns) not in (select from b)
  and (columns) not in (select from c)
  and (columns) in (select from d)

preferring big bulky straightforward SQL, I'll stick with not in and in.

and - your implementation in general is not correct, it would have to be:

from a, (select distinct doc_num, doc_type from d) d

no one specified anything about the relationship between A and D - in cannot be simply rewritten as a join (and not in cannot simply be rewritten as not exists)

our two queries return radically different results given the same data.

We'd need to actually know a lot more to write the correct query here - hence my psuedo code and the statement "read about...."


I suppose

select * from A
where (columns) not in (select from b union all select from c)
  and (columns) in (select from d)

might have advantages, and if the columns are nullable - we might add a is not null in the union query (then it would be like the not exists)

we already conversed about the approaches above.

We've seen a not in, a not in with a union all, a discussion about nulls....

did you read through that.

they are not for the same purpose.

the temporary tablespace will be used to swap the contents of PGA sort areas to disk in the even the PGA sort area is insufficient.

A temporary tablespace and PGA workareas work in conjunction with each other to sort data that would otherwise be unsortable if we were restricted to just memory.

I wrote (quote)

... shared server -- sort_area_retained in the UGA in the SGA. sort area size - in the PGA in the
dedicated server.
....

...
if you are not, they are allocated in the PGA memory of the server process (except for the
sort_area_retained_size which is allocated in the UGA memory which is in the PGA in dedicated
server mode, and the SGA in shared server mode)
....

...
The SORT_AREA_SIZE-SORT_AREA_RETAINED_SIZE is generally allocated out of your PGA, and the
SORT_AREA_RETAINED_SIZE will be in your UGA.
.....

I never said the sort area was in the UGA.

sort area retained size, is the amount of memory your session keeps rows in memory, your SESSION needs these rows, they have to be in memory you always have access to.

In shared server, you get the server for a CALL - a call is "open query", "fetch a row from query", "fetch another row from query", "close query" and so on. You might use 50 different shared servers to process a single select statement.

... The statement Id=5 (SORT ORDER BY) has used a temp space of 33M. ...

No, rather, it is that step 5 WAS ESTIMATED TO NEED 33mb, *estimated*


and you did all of the work in memory:

          1  sorts (memory)
          0  sorts (disk)

... Why oracle is still using the temp space and
try to sort "no-records"?
...

we didn't, again - EXPLAIN PLAN shows the GUESS, did you see the guess of rows there?

... I'm a bit bewildered by seeing the number 100 in "Rows" column of statement
Id=0. The query has returned no rows. And the final SELECT operation returned
100 rows. How?
....

an explain plan - the optimization process in general - works with imperfect information and attempts to GUESS the number of rows each step will return. It will almost NEVER be 100% correct.

See
https://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html
for more examples.

you understand an explain is a generic 'guess' at what will happen.

The plan is saying "at run time, I believe I will find 215,000 rows which I want to sort. Given that I don't know how much PGA I'll be allocated when I run (it could be a lot, it could be a little) - I think I'll take it safe and estimate a small pga. Given a small pga (what is small? depends on your pga_aggregate target... small to you is different than small to me), I think I'll need 5mb of temp space.


Reality is saying "at run time, when we ran this either we found far fewer than 215,000 rows to process OR we got a good sized pga, we didn't need that temp space on disk (but we might have used that temp space in MEMORY)".

so, you might have used that tempspace, but not on disk
or, you didn't need that much tempspace

with auto workarea_size_policy and 10g - sort_area_size is not relevant, it is not used. We allocate sort workareas based on the pga_aggregate_target and the current system workload (more users, less sort space for you, less uses, more sort space for you)

looks like you have hash joins disabled? is that true?

you would use tkprof to see where something is spending its time, enable tracing with waits (dbms_monitor) and use tkprof to format the trace file