That gives you only the current use of sort segments. The same applies
to reasoning based on v$sort_usage and V$TEMP_SPACE_HEADER. The only
True Way(TM) of tuning sorts is by using v$sysstat and v$sesstat values,
That gives you an overview of the sort statistics since the instance (or
the session) was started. If you need a lot of memory quickly, you just
set SORT_AREA_SIZE to some reasonable value for a short period of time,
then lower it again and hope for the best.
--
Mladen Gogala
Oracle DBA
> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of Hitchman, Peter
> Sent: Friday, September 19, 2003 11:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 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).
>
Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mladen_at_wangtrading.com
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).
Received on Fri Sep 19 2003 - 11:39:51 CDT
