SQL Tuning problem

From: Frank Zaum <frankzaum_at_yahoo.de>
Date: 7 May 2002 08:49:48 -0700
Message-ID: <4c1e5bbe.0205070749.4df1fc2_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 - 17:49:48 CEST