RE: How to find maximum number of concurrent sorts from instance star

From: Jon Walthour <jonw_at_fuse.net>
Date: Sat, 18 Aug 2001 10:44:15 -0700
Message-ID: <F001.003702DF.20010818104520@fatcity.com>

To find the maximum number of concurrent sorts from instance start, run the following query:

SELECT LEAST(disk_sorts, peak) AS peak_concurrent FROM (SELECT VALUE AS disk_sorts

          FROM sys.v_$sysstat
         WHERE name = 'sorts (disk)')
     , (SELECT /*+ ordered */
               SUM(l.max_utilization) peak
          FROM (SELECT          /*+ ordered */
                       DISTINCT t.contents$
                           FROM (SELECT DISTINCT tempts#
                                            FROM sys.user$
                                           WHERE type# = 1) u
                              , sys.ts$ t
                          WHERE t.ts# = u.tempts#) y
             , sys.v_$resource_limit l
         WHERE (    y.contents$ = 0
                AND l.resource_name = 'temporary_table_locks')
            OR (    y.contents$ = 1
                AND l.resource_name = 'sort_segment_locks'));

Jon Walthour

-----Original Message-----
Alex
Sent: Friday, August 17, 2001 7:01 PM
To: Multiple recipients of list ORACLE-L star

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hillman, Alex
INET: Alex.Hillman_at_usmint.treas.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jon Walthour
INET: jonw_at_fuse.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------