why wouldn't you just create space_values as a clone of dba_segments with an
additional datum clause. then you could index as you have done and include
an index for the reading date?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Frank Zaum" <frankzaum_at_yahoo.de> wrote in message
news:4c1e5bbe.0205070748.3b6607d5_at_posting.google.com...
> Dear all,
>
> I am running Oracle 8.1.7 on an AIX system and i
> created a table space_values to contain used and free space
> information for objects in the db. the table contains all the out
> parameters of dbms_space:
>
> TABELLE NOT NULL
> VARCHAR2(40)
> OWNER NOT NULL
> VARCHAR2(30)
> DATUM NOT NULL DATE
> TOTAL_BLOCKS
> NUMBER(11)
> TOTAL_BYTES
> NUMBER(11,2)
> UNUSED_BLOCKS
> NUMBER(11)
> UNUSED_BYTES
> NUMBER(11,2)
> LAST_USED_EFID
> NUMBER(11)
> LAST_USED_EBID
> NUMBER(11)
> LAST_USED_BLOCK
> NUMBER(11)
> HWM
> NUMBER(11)
>
> the PK is (tabelle,owner,datum) using index. others
> constraints/indexes do not exist.
> when i run this SQL, it takes a lot of time, and even though i
> analyzed the table right before, it would not be faster:
>
> select c.segment_name ,
> c.segment_name ,
> c.owner ,
> c.tablespace_name ,
> c.segment_type ,
> b.total_blocks ,
> b.unused_blocks ,
> b.total_bytes ,
> b.unused_bytes ,
> b.last_used_efid ,
> b.last_used_ebid ,
> b.last_used_block ,
> round((total_blocks-unused_blocks)/total_blocks*100,2) ,
> b.hwm
> from space_values b,
> dba_segments c
> where b.owner = 'Frank'
> and c.segment_type = 'TABLE PARTITION'
> and b.datum = '07.05.2002'
> and b.owner = c.owner
> and b.tabelle = c.segment_name
> order by b.tabelle
>
> this is the explain plan, and i dont understand it.
> kann anybody explain this to me or give me a hint how i can tune that
> query?
>
> Explain plan:
>
> Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop
>
> SELECT STATEMENT
> SORT ORDER BY
> NESTED LOOPS
> VIEW SYS_DBA_SEGS
> UNION-ALL
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> VIEW SYS_OBJECTS
> UNION-ALL
> TABLE ACCESS FULL TAB$
> FILTER
> TABLE ACCESS FULL TABPART$
> FILTER
> TABLE ACCESS FULL CLU$
> TABLE ACCESS FULL IND$
> FILTER
> TABLE ACCESS FULL INDPART$
> FILTER
> TABLE ACCESS FULL LOB$
> FILTER
> TABLE ACCESS FULL TABSUBPART$
> FILTER
> TABLE ACCESS FULL INDSUBPART$
> TABLE ACCESS FULL LOBFRAG$
> TABLE ACCESS BY INDEX ROWID OBJ$
> INDEX UNIQUE SCAN I_OBJ1
> TABLE ACCESS CLUSTER SEG$
> INDEX UNIQUE SCAN I_FILE#_BLOCK#
> INDEX UNIQUE SCAN I_FILE2
> TABLE ACCESS CLUSTER TS$
> INDEX UNIQUE SCAN I_TS#
> TABLE ACCESS CLUSTER USER$
> INDEX UNIQUE SCAN I_USER#
> FILTER
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL UNDO$
> INDEX UNIQUE SCAN I_FILE2
> TABLE ACCESS CLUSTER SEG$
> INDEX UNIQUE SCAN I_FILE#_BLOCK#
> TABLE ACCESS CLUSTER TS$
> INDEX UNIQUE SCAN I_TS#
> TABLE ACCESS CLUSTER USER$
> INDEX UNIQUE SCAN I_USER#
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL FILE$
> TABLE ACCESS CLUSTER SEG$
> INDEX RANGE SCAN I_FILE#_BLOCK#
> TABLE ACCESS CLUSTER TS$
> INDEX UNIQUE SCAN I_TS#
> TABLE ACCESS CLUSTER USER$
> INDEX UNIQUE SCAN I_USER#
> TABLE ACCESS BY INDEX ROWID SPACE_VALUES
> INDEX UNIQUE SCAN SPACE_VALUES_PK
>
>
> TIA, Frank Zaum.
Received on Tue May 07 2002 - 10:58:06 CDT
