RE: Querying DBA_EXTENTS

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 23 Oct 2015 18:29:54 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282AAC15_at_EXMBX01.thus.corp>

The effect may be vary with version - and it probably won't work in 10g - but you could add the following hints to the main query:

/*+

        leading(sl de)
        no_merge(sl)
        no_merge(de)
        push_pred(de)

*/

This should make the optimizer do a nested loop into dba_extents for each row in segment_list passing in the relevant values each time.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Deepak Sharma [dmarc-noreply_at_freelists.org] Sent: 23 October 2015 18:46
To: Oracle-L Group
Subject: Querying DBA_EXTENTS

If I run the below query using literals (red), it comes back immediately:

SELECT DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,        MAX(DE.BYTES) LARGEST_EXTENT_BYTES FROM dba_extents DE
WHERE 1=1

  AND DE.OWNER           = <owner>
  AND DE.SEGMENT_NAME    = <segment_name>
  AND DE.segment_type    = <segment_type>
  AND DE.tablespace_name = <tablespace_name>
  AND DE.partition_name  = <max_partition_name>

GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME However, if I want to iterate through using a driving WITH_AS list, it's a drag even if I'm selecting just 1 row in the driving section (rownum < 2)

I've tried a few hints, LEADING(SL DE), USE_NL(SL DE), but the dba_extents being a dictionary view, these hints don't seem to have any effect.

Thoughts?

WITH SEGMENT_LIST AS
(
  select * from (
   SELECT /*+ materialize */

           owner, segment_name, segment_type, tablespace_name,
           MAX(partition_name) MAX_PARTITION_NAME

   FROM <my_custom_table>
   GROUP BY owner, segment_name, segment_type, tablespace_name   ) where rownum < 2
)
SELECT DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,        MAX(DE.BYTES) LARGEST_EXTENT_BYTES FROM SEGMENT_LIST SL, dba_extents DE
WHERE 1=1

  AND DE.OWNER           = SL.OWNER
  AND DE.SEGMENT_NAME    = SL.SEGMENT_NAME
  AND DE.segment_type    = SL.segment_type
  AND DE.tablespace_name = SL.tablespace_name
  AND DE.partition_name  = SL.max_partition_name

GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 23 2015 - 20:29:54 CEST