OraFAQ Forum: Performance Tuning » Hier - Query Perfomance (3 Merged)

Home » RDBMS Server » Performance Tuning » Hier - Query Perfomance (3 Merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Hier - Query Perfomance (3 Merged) [message #502140]

Mon, 04 April 2011 11:59

mnafehm
Messages: 3
Registered: April 2011

Junior Member

Hi All, new to the board and trying to see if someone can assist me with the query performance below - I am yielding results but in 15-20 secs, which in this day and age will be considered unacceptable as this will tie back to an UI (User interface)

SELECT   DISTINCT
         rg.area_name RG,
         ed.area_name EDMKT,
         cnq.area_name CNQMKT,
         RNCNM,
         RANVND,
         DECODE (site_cnt_rnc, NULL, 0, site_cnt_rnc) site_cnt_rnc,
         DECODE (NB_CNT, NULL, 0, NB_CNT) NB_CNT,
         DECODE (site_cnt_msn, NULL, 0, site_cnt_msn) site_cnt_msn,
         DECODE (MNB_CNT, NULL, 0, MNB_CNT) MNB_CNT,
         DECODE (CRITICAL, NULL, 0, CRITICAL) CRITICAL,
         DECODE (MAJOR, NULL, 0, MAJOR) MAJOR,
         DECODE (MINOR, NULL, 0, MINOR) MINOR,
         DECODE (WARNING, NULL, 0, WARNING) WARNING,
         DECODE (SGL_SITES, NULL, 0, SGL_SITES) SGL_SITES,
         DECODE (NEW_SITES, NULL, 0, NEW_SITES) NEW_SITES,
         PROC_DATE
FROM   VCC_ERI_NODEB M, VCC_RNCS R, vcc_mismatch_summary A, nrs.nodebs N,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   end_date > SYSDATE + 10 AND LEVEL = 4 --CNQMKT level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) cnq,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   LEVEL = 3                              --EDMKT level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) ed,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   LEVEL = 2                             --region level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) rg
WHERE       rg.area_key = ed.parea_key
        AND ed.area_key = cnq.parea_key
        AND N.MARKET_KEY = cnq.AREA_KEY
        AND m.rnc_key = R.rnc_key
        AND m.nodeb_id = n.cds_id
        AND A.RNCNM = R.RNC_NAME
        AND A.Proc_Date = (SELECT   TRUNC (MAX (Proc_Date))
                           FROM   VCC_MISMATCH_SUMMARY
                          WHERE   REGION = A.Region)
        AND A.Region <> 'CENTRAL'
ORDER BY  1,
          2,
          3,
          4;

[EDITED by LF: applied [code] tags (and removed a duplicate message that followed this one)]

[Updated on: Tue, 05 April 2011 02:37] by Moderator

Report message to a moderator

Re: Hier - Query Perfomance [message #502143 is a reply to message #502140]

Mon, 04 April 2011 12:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Hier - Query Perfomance [message #502148 is a reply to message #502143]

Mon, 04 April 2011 12:46

mnafehm
Messages: 3
Registered: April 2011

Junior Member

Hope this helps

SQL

SELECT   DISTINCT
         rg.area_name RG,
         ed.area_name EDMKT,
         cnq.area_name CNQMKT,
         RNCNM,
         RANVND,
         DECODE (site_cnt_rnc, NULL, 0, site_cnt_rnc) site_cnt_rnc,
         DECODE (NB_CNT, NULL, 0, NB_CNT) NB_CNT,
         DECODE (site_cnt_msn, NULL, 0, site_cnt_msn) site_cnt_msn,
         DECODE (MNB_CNT, NULL, 0, MNB_CNT) MNB_CNT,
         DECODE (CRITICAL, NULL, 0, CRITICAL) CRITICAL,
         DECODE (MAJOR, NULL, 0, MAJOR) MAJOR,
         DECODE (MINOR, NULL, 0, MINOR) MINOR,
         DECODE (WARNING, NULL, 0, WARNING) WARNING,
         DECODE (SGL_SITES, NULL, 0, SGL_SITES) SGL_SITES,
         DECODE (NEW_SITES, NULL, 0, NEW_SITES) NEW_SITES,
         PROC_DATE
FROM   VCC_ERI_NODEB M, VCC_RNCS R, vcc_mismatch_summary A, nrs.nodebs N,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   end_date > SYSDATE + 10 AND LEVEL = 4 --CNQMKT level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) cnq,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   LEVEL = 3                              --EDMKT level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) ed,
            (SELECT   area_key, parea_key, area_name
                   FROM   NRS.HIER_AREAS
                  WHERE   LEVEL = 2                             --region level
             START WITH   parea_key IS NULL
             CONNECT BY   parea_key = PRIOR area_key) rg
WHERE       rg.area_key = ed.parea_key
        AND ed.area_key = cnq.parea_key
        AND N.MARKET_KEY = cnq.AREA_KEY
        AND m.rnc_key = R.rnc_key
        AND m.nodeb_id = n.cds_id
        AND A.RNCNM = R.RNC_NAME
        AND A.Proc_Date = (SELECT   TRUNC (MAX (Proc_Date))
                           FROM   VCC_MISMATCH_SUMMARY
                          WHERE   REGION = A.Region)
        AND A.Region <> 'CENTRAL'
ORDER BY  1,
          2,
          3,
          4;

EXPLAIN PLAN

Plan
SELECT STATEMENT  ALL_ROWSCost: 1,132  Bytes: 9,282  Cardinality: 51  													
	32 SORT UNIQUE  Cost: 1,131  Bytes: 9,282  Cardinality: 51  												
		31 HASH JOIN  Cost: 1,130  Bytes: 9,282  Cardinality: 51  											
			3 VIEW VIEW SYS.VW_SQ_1 Cost: 324  Bytes: 104  Cardinality: 8  										
				2 SORT GROUP BY  Cost: 324  Bytes: 176  Cardinality: 8  									
					1 TABLE ACCESS FULL TABLE VCC.VCC_MISMATCH_SUMMARY Cost: 314  Bytes: 2,883,320  Cardinality: 131,060  								
			30 HASH JOIN  Cost: 806  Bytes: 4,308,486  Cardinality: 25,494  										
				28 HASH JOIN  Cost: 489  Bytes: 10,780  Cardinality: 110  									
					26 HASH JOIN  Cost: 484  Bytes: 8,690  Cardinality: 110  								
						23 HASH JOIN  Cost: 450  Bytes: 30,225  Cardinality: 465  							
							20 HASH JOIN  Cost: 10  Bytes: 98  Cardinality: 2  						
								15 HASH JOIN  Cost: 7  Bytes: 72  Cardinality: 2  					
									10 VIEW VCC. Cost: 3  Bytes: 54  Cardinality: 3  				
										9 FILTER  			
											8 CONNECT BY WITH FILTERING  		
												4 TABLE ACCESS FULL TABLE NRS.HIER_AREAS Cost: 3  Bytes: 3,612  Cardinality: 86  	
												7 HASH JOIN  	
													5 CONNECT BY PUMP  
													6 TABLE ACCESS FULL TABLE NRS.HIER_AREAS Cost: 3  Bytes: 78  Cardinality: 3  
									14 VIEW VCC. Cost: 3  Bytes: 7,074  Cardinality: 393  				
										13 FILTER  			
											12 CONNECT BY NO FILTERING WITH START-WITH  		
												11 TABLE ACCESS FULL TABLE NRS.HIER_AREAS Cost: 3  Bytes: 7,074  Cardinality: 393  	
								19 VIEW VCC. Cost: 3  Bytes: 5,109  Cardinality: 393  					
									18 FILTER  				
										17 CONNECT BY NO FILTERING WITH START-WITH  			
											16 TABLE ACCESS FULL TABLE NRS.HIER_AREAS Cost: 3  Bytes: 7,074  Cardinality: 393  		
							22 PARTITION LIST ALL  Cost: 439  Bytes: 1,894,416  Cardinality: 118,401  Partition #: 27  Partitions accessed #1 - #6						
								21 TABLE ACCESS FULL TABLE NRS.NODEBS Cost: 439  Bytes: 1,894,416  Cardinality: 118,401  Partition #: 27  Partitions accessed #1 - #6					
						25 PARTITION LIST ALL  Cost: 32  Bytes: 389,200  Cardinality: 27,800  Partition #: 29  Partitions accessed #1 - #12							
							24 INDEX FAST FULL SCAN INDEX (UNIQUE) VCC.VCC_ERI_NODEB_UK1 Cost: 32  Bytes: 389,200  Cardinality: 27,800  Partition #: 29  Partitions accessed #1 - #12						
					27 TABLE ACCESS FULL TABLE VCC.VCC_RNCS Cost: 5  Bytes: 10,564  Cardinality: 556  								
				29 TABLE ACCESS FULL TABLE VCC.VCC_MISMATCH_SUMMARY Cost: 315  Bytes: 9,126,695  Cardinality: 128,545

Report message to a moderator

Re: Hier - Query Perfomance [message #502796 is a reply to message #502148]

Sat, 09 April 2011 21:04

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I'm not really sure why you have all those CONNECT BY inline views. You're only selecting one level from each of them, and then joining them back together in hierarchical order.
Why not just use the raw tables instead of the inline views - you should get the same result providing you ensure RG is at Level 2 by adding another join to the same table.

SELECT   DISTINCT
         rg.area_name RG,
         ed.area_name EDMKT,
         cnq.area_name CNQMKT,
         RNCNM,
         RANVND,
         DECODE (site_cnt_rnc, NULL, 0, site_cnt_rnc) site_cnt_rnc,
         DECODE (NB_CNT, NULL, 0, NB_CNT) NB_CNT,
         DECODE (site_cnt_msn, NULL, 0, site_cnt_msn) site_cnt_msn,
         DECODE (MNB_CNT, NULL, 0, MNB_CNT) MNB_CNT,
         DECODE (CRITICAL, NULL, 0, CRITICAL) CRITICAL,
         DECODE (MAJOR, NULL, 0, MAJOR) MAJOR,
         DECODE (MINOR, NULL, 0, MINOR) MINOR,
         DECODE (WARNING, NULL, 0, WARNING) WARNING,
         DECODE (SGL_SITES, NULL, 0, SGL_SITES) SGL_SITES,
         DECODE (NEW_SITES, NULL, 0, NEW_SITES) NEW_SITES,
         PROC_DATE
FROM   VCC_ERI_NODEB M, VCC_RNCS R, vcc_mismatch_summary A, nrs.nodebs N,
--            (SELECT   area_key, parea_key, area_name
--                   FROM   NRS.HIER_AREAS
--                  WHERE   end_date > SYSDATE + 10 AND LEVEL = 4 --CNQMKT level
--             START WITH   parea_key IS NULL
--             CONNECT BY   parea_key = PRIOR area_key) cnq,
NRS.HIER_AREAS cnq,
--            (SELECT   area_key, parea_key, area_name
--                   FROM   NRS.HIER_AREAS
--                  WHERE   LEVEL = 3                              --EDMKT level
--             START WITH   parea_key IS NULL
--             CONNECT BY   parea_key = PRIOR area_key) ed,
NRS.HIER_AREAS ed,
--            (SELECT   area_key, parea_key, area_name
--                   FROM   NRS.HIER_AREAS
--                  WHERE   LEVEL = 2                             --region level
--             START WITH   parea_key IS NULL
--             CONNECT BY   parea_key = PRIOR area_key) rg
NRS.HIER_AREAS rg,
NRS.HIER_AREAS theroot
WHERE       theroot.parea_key IS NULL
        AND cnq.end_date > SYSDATE + 10
        AND rg.area_key = ed.parea_key
        AND ed.area_key = cnq.parea_key
        AND N.MARKET_KEY = cnq.AREA_KEY
        AND m.rnc_key = R.rnc_key
        AND m.nodeb_id = n.cds_id
        AND A.RNCNM = R.RNC_NAME
        AND A.Proc_Date = (SELECT   TRUNC (MAX (Proc_Date))
                           FROM   VCC_MISMATCH_SUMMARY
                          WHERE   REGION = A.Region)
        AND A.Region <> 'CENTRAL'
ORDER BY  1,
          2,
          3,
          4;

Ross Leishman

Report message to a moderator

Previous Topic:	How to index 15 columns when you cannot predict what columns will be used in a where clause?
Next Topic:	Shall I kill these process...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 16:19:32 CDT 2024