Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: selects on EXT#, LENGTH from SYS.UET$ still slow even in 10g isn't it ?

Re: selects on EXT#, LENGTH from SYS.UET$ still slow even in 10g isn't it ?

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 11 Apr 2007 02:53:54 -0700
Message-ID: <1176285234.584201.258100@y5g2000hsa.googlegroups.com>


On Apr 11, 10:23 am, "Sébastien de Mapias" <sglrig..._at_gmail.com> wrote:
> Hi,
> I've read this Metalink note 273468.1 stating that queries on
> BYTES, BLOCKS or EXTENTS columns of DBA_SEGMENTS
> or DBA_EXTENTS are very slow on versions 8.1.7.0 to 9.2.0.4.
>
> But obviously in 64-bit 10.2.0.2 on Sun/Solaris it's the same:
> SQL> select bytes
> 2 from dba_extents i
> 3 where i.owner='QSDF'
> 4 and i.segment_name='MLKQJSDF'
> 5* and i.extent_id=0
> SQL> /
>
> BYTES
> ------------------
> 131072
>
> Elapsed: 00:01:21.03
>
> SQL> select distinct segment_type
> 2 from dba_extents i
> 3 where i.owner='QSDF'
> 4* and i.segment_name='MLKQJSDF'
> SQL> /
>
> SEGMENT_TYPE
> ------------------
> INDEX
>
> Elapsed: 00:00:01.04
>
> (more than one minute to give me the size of the first extent
> of this index in a local. managed tbsp, whereas I get a sorted
> result in one second)
>
> Is there any way to get a better response time in 10g ?
>
> Thanks.
> SR

Interesting, i've used user_extents to estimate space occupation of a segment, but i never noticed particular slowness. Anyway i think it depends if you use Locally managed Tablespaces o Dictionary managed tablespaces, in first case that is what i have always used (i've started working with oracle 8i) probably Oracle has to go into the file and get segment blocks that contains bitmaps. I've always thought that was the problem. i usually use a query like this
select sum(bytes) from dba_extents i where i.owner='XY' AND i.SEGMENT_NAME='AB';
and timing are similar to those of your query. I've noticed high CPU usage of the session and using
autotrace i've notice an excution plan not very simple and a huge amount o logical reads (10.1.0.5.0)
With /*+ RULE */ hint it seem a bit faster (i refuse to analize execution plan to understand if it is different)

Bye
Cristian Cudizio
http://cristiancudizio.wordpress.com/ Received on Wed Apr 11 2007 - 04:53:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US