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: SQL-Tuning Problem

Re: SQL-Tuning Problem

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 07 May 2002 23:10:30 +0200
Message-ID: <rgggduc42eisv9mu03rceu2o9nu5as0ibe@4ax.com>


On 7 May 2002 08:48:03 -0700, frankzaum_at_yahoo.de (Frank Zaum) wrote:

>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.

You didn't ANALYZE the dictionary did you. Please run the following select
select table_name, num_rows
from dba_tables
where owner = 'SYS'
and num_rows is not null
/

If you have statistics on any tables this query is going to provide results.
If there are statistics, delete them ASAP, which basically means NOW. by exec dbms_utility.analyze_schema('SYS','DELETE')

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue May 07 2002 - 16:10:30 CDT

Original text of this message

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