Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Slow Query DBA_SEGMENTS
Hi,
I need to get storage for lob columns and the only way I can think of doing
this is by joining dba_lobs with dba_segments.
I am doing this to generate CREATE statements from the Data Dic and also to
calculate storage requirements.
The query needs to run on both 8i and 9i.
I need to do this a lot as I have many lob columns on my DB.
Is there somthing I can do to improve the performance of this query. I am
thinking if I can somehow avoid DBA_SEGMENTS and hit the SYS tables
directly. OR is there some other way altogether. Maybe some sort of hiny
Any ideas would be apreciated.
The query looks something like this.
SELECT b.BLOCKS BLOCKS
, a.TABLE_NAME TABLE_NAME
, a.COLUMN_NAME COLUMN_NAME
, a.OWNER OWNER
, b.BYTES BYTES
, b.EXTENTS EXTENTS
, b.RELATIVE_FNO RELATIVE_FNO
, a.SEGMENT_NAME SEGMENT_NAME
, a.INDEX_NAME INDEX_NAME
, a.CHUNK CHUNK
, a.PCTVERSION PCTVERSION
, a.IN_ROW IN_ROW
, b.OWNER SEGMENT_OWNER
, b.PARTITION_NAME PARTITION_NAME
, b.SEGMENT_TYPE SEGMENT_TYPE
, b.TABLESPACE_NAME TABLESPACE_NAME
, b.HEADER_FILE HEADER_FILE
, b.HEADER_BLOCK HEADER_BLOCK
, b.FREELIST_GROUPS FREELIST_GROUPS
, b.BUFFER_POOL BUFFER_POOL
, a.LOGGING LOGGING
, b.INITIAL_EXTENT INITIAL_EXTENT
, b.NEXT_EXTENT NEXT_EXTENT
, b.MIN_EXTENTS MIN_EXTENTS
, b.MAX_EXTENTS MAX_EXTENTS
, b.PCT_INCREASE PCT_INCREASE
, b.FREELISTS FREELISTS
, a.CACHE CACHE from dba_lobs a, dba_segments b
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME (+) AND a.OWNER = ? and a.TABLE_NAME
= ? and a.COLUMN_NAME = ? order by OWNER, TABLE_NAME, COLUMN_NAME
Received on Wed Jun 05 2002 - 18:57:46 CDT