Home » RDBMS Server » Performance Tuning » URGENT-Query not performing
URGENT-Query not performing [message #167567] Thu, 13 April 2006 22:01 Go to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hi Gurus,

I have this query which is taking about 1 hour to execute.
I need your suggestions for improvement. Please help me.

select 
      ED.DI_ID Di_Id,
      DP.PARTICIPANT_ID Participant_Id,
      X.CUSTOMER_OWNING_TEAM_ID Customer_Owning_Team_ID,
      X.CUSTOMER_ID Customer_ID,
      X.CUSTOMER_NAME Customer_Name,
      COUNT(DP.PARTICIPANT_ID) Recipients_Total
from 
      ARV_ENTITY_DI ED,
      ARV_ENTITY E,
      ARV_DI_PARTICIPANT DP,
      (
        select    i.individual_id,
         (select CA.SERVICE_TEAM_ID
          from av_customer_assignment CA
          where CA.CUSTOMER_ID = I.CUSTOMER_ID and CA.PRIMARY_OWNER_I = 'Y' and
                CA.END_DATE is null and rownum = 1) as CUSTOMER_OWNING_TEAM_ID,
         (select CA.CUSTOMER_ID
          from av_customer_assignment CA
          where CA.CUSTOMER_ID = I.CUSTOMER_ID and CA.PRIMARY_OWNER_I = 'Y' and
                CA.END_DATE is null and rownum = 1) as CUSTOMER_ID,
                trim(i.first_name) || ' ' || trim(i.middle_initial) || ' ' || trim(i.last_name)  CUSTOMER_NAME
        from   av_any_individual i
     ) x
where     
      ED.ENTITY_ID = E.ENTITY_ID and      
      DP.DI_ID = ED.DI_ID and
      ED.DI_TYPE_CD = 11004 and
      X.individual_id = DP.PARTICIPANT_ID      
group by 
      ED.DI_ID, DP.PARTICIPANT_ID, Customer_Owning_Team_ID, Customer_ID, Customer_Name
order by 
      count(DP.PARTICIPANT_ID) desc;


Explain plan:-
SELECT STATEMENT, GOAL = CHOOSE			44548	145662	40202712
 COUNT STOPKEY					
  NESTED LOOPS			10	1	89
   NESTED LOOPS OUTER			9	1	73
    NESTED LOOPS			8	1	65
     NESTED LOOPS			7	1	58
      NESTED LOOPS			6	1	51
       NESTED LOOPS			5	1	41
        NESTED LOOPS			4	1	33
         NESTED LOOPS			3	1	25
          TABLE ACCESS BY INDEX ROWID	GDS	TCUSTOMER_KEYS	2	1	9
           INDEX UNIQUE SCAN	GDS	TCUSTOMER_KEYS_PK	2	1	
          INLIST ITERATOR					
           TABLE ACCESS BY INDEX ROWID	JDEDTA	F55540	2	1	16
            INDEX RANGE SCAN	ARROW	F55540_TEMP_PNDX	1	6	
         INDEX RANGE SCAN	GDS	TORGANIZATIONAL_UNIT_KEYS_UX1	1	1	8
        TABLE ACCESS BY INDEX ROWID	GDS	TORGANIZATIONAL_UNIT_KEYS	2	1	8
         INDEX UNIQUE SCAN	GDS	TORGANIZATIONAL_UNIT_KEYS_PK		1	
       TABLE ACCESS BY INDEX ROWID	JDEDTA	F0101	2	1	10
        INDEX UNIQUE SCAN	JDEDTA	F0101_UX1		1	
      INDEX UNIQUE SCAN	JDEDTA	F550101_PK		1	7
     INDEX UNIQUE SCAN	JDEDTA	F0116_PK		1	7
    TABLE ACCESS BY INDEX ROWID	JDEDTA	F0150	2	1	8
     INDEX RANGE SCAN	JDEDTA	F0150_UX1	1	1	
   INDEX RANGE SCAN	JDEDTA	F0005_PK	1	1	16
 COUNT STOPKEY					
  NESTED LOOPS			10	1	89
   NESTED LOOPS OUTER			9	1	73
    NESTED LOOPS			8	1	65
     NESTED LOOPS			7	1	58
      NESTED LOOPS			6	1	51
       NESTED LOOPS			5	1	41
        NESTED LOOPS			4	1	33
         NESTED LOOPS			3	1	25
          TABLE ACCESS BY INDEX ROWID	GDS	TCUSTOMER_KEYS	2	1	9
           INDEX UNIQUE SCAN	GDS	TCUSTOMER_KEYS_PK	2	1	
          INLIST ITERATOR					
           TABLE ACCESS BY INDEX ROWID	JDEDTA	F55540	2	1	16
            INDEX RANGE SCAN	ARROW	F55540_TEMP_PNDX	1	6	
         INDEX RANGE SCAN	GDS	TORGANIZATIONAL_UNIT_KEYS_UX1	1	1	8
        TABLE ACCESS BY INDEX ROWID	GDS	TORGANIZATIONAL_UNIT_KEYS	2	1	8
         INDEX UNIQUE SCAN	GDS	TORGANIZATIONAL_UNIT_KEYS_PK		1	
       TABLE ACCESS BY INDEX ROWID	JDEDTA	F0101	2	1	10
        INDEX UNIQUE SCAN	JDEDTA	F0101_UX1		1	
      INDEX UNIQUE SCAN	JDEDTA	F550101_PK		1	7
     INDEX UNIQUE SCAN	JDEDTA	F0116_PK		1	7
    TABLE ACCESS BY INDEX ROWID	JDEDTA	F0150	2	1	8
     INDEX RANGE SCAN	JDEDTA	F0150_UX1	1	1	
   INDEX RANGE SCAN	JDEDTA	F0005_PK	1	1	16
 SORT ORDER BY			44548	145662	40202712
  SORT GROUP BY			44548	145662	40202712
   NESTED LOOPS			23298	370412	102233712
    NESTED LOOPS			13377	49605	1984200
     NESTED LOOPS			3702	48378	1451340
      VIEW	ARROW	ARV_ENTITY_DI	3700	48378	1161072
       UNION-ALL					
        INDEX FAST FULL SCAN	ARROW	DI_USAGE_COMPOSE_PNDX	1400	321107	5458819
        HASH JOIN			2029	2167378	60686584
         INDEX FAST FULL SCAN	ARROW	DI_USAGE_COMPOSE_PNDX	1400	321107	5458819
         INDEX FAST FULL SCAN	ARROW	SUBGROUP_MEM_SGID_PNDX	629	1977624	21753864
      INDEX UNIQUE SCAN	ARROW	ENTITY_PK		1	6
     TABLE ACCESS BY INDEX ROWID	ARROW	TDI_PARTICIPANT	2	1	10
      INDEX RANGE SCAN	ARROW	DI_PARTICIPANT_DIID_PNDX		1	
    VIEW	GDS	AV_ANY_INDIVIDUAL	2	7	1652
     UNION-ALL PARTITION					
      TABLE ACCESS BY INDEX ROWID	GDS	TINDIVIDUAL_KEYS	2	1	28
       INDEX UNIQUE SCAN	GDS	TINDIVIDUAL_KEYS_PK	2	1	
      NESTED LOOPS			6	1	134
       NESTED LOOPS			5	1	125
        NESTED LOOPS			4	1	115
         NESTED LOOPS			3	1	33
          TABLE ACCESS BY INDEX ROWID	GDS	TINDIVIDUAL_KEYS	2	1	28
           INDEX UNIQUE SCAN	GDS	TINDIVIDUAL_KEYS_PK	2	1	
          INDEX UNIQUE SCAN	JDEDTA	F0101_PK		1	5
         TABLE ACCESS BY INDEX ROWID	JDEDTA	F0111	2	1	82
          INDEX UNIQUE SCAN	JDEDTA	F0111_PK		1	
        INDEX RANGE SCAN	JDEDTA	F0150_UX1	1	1	10
       INDEX RANGE SCAN	GDS	TCUSTOMER_KEYS_UX3	1	1	9
      NESTED LOOPS			3	1	37
       INDEX RANGE SCAN	GDS	TCUSTOMER_KEYS_UX3	1	1	9
       TABLE ACCESS BY INDEX ROWID	GDS	TINDIVIDUAL_KEYS	2	1	28
        INDEX UNIQUE SCAN	GDS	TINDIVIDUAL_KEYS_PK	2	1	


O.S. :- Windows 2003 DB version:- Oracle 9i

Please help me as this is urgent.

Milind.

Re: URGENT-Query not performing [message #167626 is a reply to message #167567] Fri, 14 April 2006 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>from
> ARV_ENTITY_DI ED,
> ARV_ENTITY E,

ARV_ENTITY should not be in the FROM clause; since you are not selecting any field from ARV_ENTITY table.
Re: URGENT-Query not performing [message #168459 is a reply to message #167567] Thu, 20 April 2006 09:48 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Does this SQL produce the same result?
SELECT   ed.di_id                           di_id
,        dp.participant_id                  participant_id
,        x.customer_owning_team_id          customer_owning_team_id
,        x.customer_id                      customer_id
,        x.customer_name                    customer_name
,        COUNT(dp.participant_id)           recipients_total
FROM     arv_entity_di                      ed
,        arv_di_participant                 dp
,       (SELECT    i.individual_id
         ,         ca.service_team_id       customer_owning_team_id
         ,         ca.customer_id           customer_id
         ,         ROW_NUMBER()
                   OVER (PARTITION BY ca.customer_id
                         ORDER BY     NULL) r
         ,         TRIM(i.first_name)
                   || ' '
                   || TRIM(i.middle_initial)
                   || ' ' 
                   || TRIM(i.last_name)     customer_name
         FROM      av_any_individual        i
         ,         av_customer_assignment   ca
         WHERE     i.customer_id = ca.customer_id (+)
         AND       'Y'           = ca.primary_owner_i (+)
         AND       ca.end_date (+) IS NULL) x
WHERE    dp.di_id        = ed.di_id
AND      ed.di_type_cd   = 11004
AND      x.individual_id = dp.participant_id
AND      x.r             = 1
GROUP BY ed.di_id
,        dp.participant_id
,        x.customer_owning_team_id
,        x.customer_id
,        x.customer_name
ORDER BY COUNT(dp.participant_id) DESC
/
Re: URGENT-Query not performing [message #168477 is a reply to message #168459] Thu, 20 April 2006 11:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Too late Art, it was URGENT!
Re: URGENT-Query not performing [message #168487 is a reply to message #168477] Thu, 20 April 2006 12:46 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Quote from Frank:

Too late Art, it was URGENT!
*shrug*

Then I guess I won't get paid, then....

Oh, wait a minute....
Previous Topic: How can I improve the performance of LOWER (col) LIKE ‘%name%’
Next Topic: Index Order and Question
Goto Forum:
  


Current Time: Wed May 22 19:28:35 CDT 2024