RE: sizing sort_area_size

Hi,
Looking at v$sort_segment also can gve you a good idea of how much space you are using in your temporary tablespace, across the current uptime of the instance, assuming that it is a true temporary tablespace.

Regards

Pete

-----Original Message-----
Sent: 18 September 2003 23:35
To: Multiple recipients of list ORACLE-L

> I have been doing some alter sessions to improve performance of gigabyte
sized 'minus' operations. How do I tell if this is helping other than completion time?
>
> I believe that if LIOs to PIOs ratio improves with the SAME query then
sort_area_size has helped right?(notice Im not trying to improve my cache/hit ratio).

If you see less direct IO waits on your temporary tablespace, then increasing s_a_s has helped.

>
> I get this from v$sess_io.
>
> how do I tell how much temp tablespace my session uses? I cant find a
statistic for it? I see that I used up all my PGA since PGA size and PGA max size are equal in v$sesstat.

select * from v$sort_usage where sid = <sid>;

> anything else I can look at?

Activity on temp tablespace from v$tempstat.. but it's only good enough for comparing with baselines..

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

__________________________________________________________________

The information contained in this email is confidential and 
intended only for the use of the individual or entity named 
above. If the reader of this message is not the intended 
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly 
prohibited. Thomson Scientific will accept no responsibility 
or liability in respect to this email other than to the addressee. 
If you have received this communication in error, please 
notify us immediately via email: ITHelpdesk_at_derwent.co.uk
__________________________________________________________________
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hitchman, Peter
  INET: peter.hitchman_at_derwent.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).