Home » RDBMS Server » Performance Tuning » Optimizing Hierachical query
Optimizing Hierachical query [message #178061] Mon, 19 June 2006 05:07 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
SQL> SELECT * FROM V$Version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

Elapsed: 00:00:00.08
SQL> 


Here is the explain plan of the query. Improving the performance of this query is the one I am lookin into.

SQL>  SELECT PARENT_PK, CONTENTITEM_PK, FOLDER_PK, MDK, LEVEL FROM CMF_CONTENTITEM 
  2   START WITH CONTENTITEM_PK = 1 CONNECT BY PRIOR CONTENTITEM_PK=PARENT_PK 
  3   ORDER BY LEVEL DESC;

no rows selected

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4407 Card=732073 Byt
          es=17569752)

   1    0   SORT (ORDER BY) (Cost=4407 Card=732073 Bytes=17569752)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         INDEX (UNIQUE SCAN) OF 'PK_CMF_CONTENTITEM' (UNIQUE)
           (Cost=2 Card=1 Bytes=6)

   5    3         TABLE ACCESS (BY USER ROWID) OF 'CMF_CONTENTITEM'
   6    2       HASH JOIN
   7    6         CONNECT BY PUMP
   8    6         TABLE ACCESS (FULL) OF 'CMF_CONTENTITEM' (Cost=975 Card=732073 Bytes=17569752)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        255  bytes sent via SQL*Net to client
        235  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed


--The count of records
SELECT COUNT(DISTINCT PARENT_PK ) FROM VIGN_DATA.CMF_CONTENTITEM  --6684 

SELECT COUNT(DISTINCT CONTENTITEM_PK) FROM VIGN_DATA.CMF_CONTENTITEM--732083 

CONTENTITEM_PK is the primary key. 


Is there any hint I can apply or anything I can do to improve the performance of this query



Thanks



[Updated on: Mon, 19 June 2006 08:18] by Moderator

Report message to a moderator

Re: Optimizing Hierachical query [message #178124 is a reply to message #178061] Mon, 19 June 2006 08:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Improve from 3 seconds to how much?
this query returns no data. Post something that will return data.
Re: Optimizing Hierachical query [message #178199 is a reply to message #178061] Tue, 20 June 2006 02:01 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Here is the query which returns 1 row. To my surprise I could not find a single row which has more than 1 level. Most likely we will change this functionality to a simple where clause.

But would like to know, if in future any such situation comes to tune a Hierachical query how do one proceed.

Thank you very much

SQL> SELECT PARENT_PK, CONTENTITEM_PK, FOLDER_PK, MDK, LEVEL FROM CMF
2 START WITH CONTENTITEM_PK = 10831
3 CONNECT BY PRIOR CONTENTITEM_PK=PARENT_PK ORDER BY LEVEL DESC;

Elapsed: 00:00:04.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4223 Card=732097 Byt
es=16106134)

1 0 SORT (ORDER BY) (Cost=4223 Card=732097 Bytes=16106134)
2 1 CONNECT BY (WITH FILTERING)
3 2 NESTED LOOPS
4 3 INDEX (UNIQUE SCAN) OF 'PK_CMF_CONTENTITEM' (UNIQUE)
(Cost=2 Card=1 Bytes=5)

5 3 TABLE ACCESS (BY USER ROWID) OF 'CMF_CONTENTITEM'
6 2 HASH JOIN
7 6 CONNECT BY PUMP
8 6 TABLE ACCESS (FULL) OF 'CMF_CONTENTITEM' (Cost=975 C
ard=732097 Bytes=16106134)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15979 consistent gets
0 physical reads
0 redo size
348 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
Re: Optimizing Hierachical query [message #184666 is a reply to message #178199] Thu, 27 July 2006 07:07 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

Look at following link http://www.jlcomp.demon.co.uk/faq/tuning_explain.html

It deals with plan_table but I think you can use it.

HTH.

Michael

Previous Topic: Pl/sql procedure experience a high parse count using oracle 9i db-link.
Next Topic: How to get the CBO to recognize a FBI
Goto Forum:
  


Current Time: Tue Dec 06 02:24:39 CST 2016

Total time taken to generate the page: 0.11772 seconds