Re: DBA_SEGMENTS and Full scan

Guitarator wrote:
> Hi
>
> Actually a single request on the Dba_segments have 7 Full_scan on
> SYS.TAB$, SYS.TABPARTS$, SYS.CLU$ ... . Is there a way to contourn this
> problem ?
>
> SELECT SUM(bytes)/1024/1024
> FROM DBA_SEGMENTS
> WHERE tablespace_name = 'TBDATA001'
> AND segment_type = 'TABLE'
> /
>
> Oracle version is 9.2.0.6
> O.S. AIX 5.3
Why do you consider this a problem? On my 9.2.0.6 system running on AIX 5.2 the response for you query on a 8G tablespace was 2.4 seconds.

Both TAB$ and CLU$ are stored in the same cluster so reading one of them will fetch the physical blocks for the other into memory if not alreay present.

HTH -- Mark D Powell -- Received on Fri Jun 16 2006 - 08:44:26 CDT