Home » RDBMS Server » Performance Tuning » Restructering (Oracle, 10g, Windows server 2003)
Restructering [message #444027] Thu, 18 February 2010 06:56 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi

Below given is the query which takes too much time to execute
, it takes more than 6 mins to execute

Our client for which are doing thios work does not us to
create indexes on tables.

Can anyone please help me to restructure the following query so that performs well


SELECT C.OID,C.CUSTOMER_ID AS  CAS,
			       N.NAME        AS  CUSTOMER_NAME,
			       U.CLIENT_UCN  AS  UCN,
			       G.OBG         AS  OG,
			       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
			       R.CCF_CD      AS  CREDIT_FLAG,
			       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
									  FROM   SAM.INT_GRADES
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT
		       FROM    SAM.CLIENTUCN     U,
			       SAM.INT_GRADES       G,                   
			       SAM.CRIT_FLAG        R,
			       SAM.UNIQUE_ULT P,
			       SAM.CLIENT             C,
			       SAM.CIT_CLIENTME    N,
			       SAM.TEAM_PA3       T
			WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
			AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
			AND    C.OID = N.CLT_OID
			AND    N.CLT_OID = U.CLIENT_OID
			AND    U.CLIENT_OID = G.CLT_OID
			AND    G.CLT_OID = P.CHILD_CLT_OID
			AND    P.CHILD_CLT_OID = R.CLT_OID
			AND    R.CLT_OID = T.CLT_OID
			AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
			AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
			AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
			AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
			AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
			AND    R.CCF_CD = 'C1'
			AND    C.OID = '1256120'
			GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD 


I restructured it as follows but with no effect

WITH
Q2 AS
( SELECT CLT_OID,CODE_VALUE,ID_EMAIL_INTERNAL
  FROM SAM.TEAM_PA3
  WHERE  CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
  AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
  AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
  AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
  AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
  AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT),
Q1 AS
(SELECT OID,CUSTOMER_ID
 FROM SAM.CLIENT
 WHERE SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT)
SELECT           Q1.OID,Q1.CUSTOMER_ID AS  CAS,
					       N.NAME        AS  CUSTOMER_NAME,
					       U.CLIENT_UCN  AS  UCN,
					       G.OBG         AS  OG,
					       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
					       R.CCF_CD      AS  CREDIT_FLAG,
					       Max(Decode(Q2.CODE_VALUE,'C1',Substr(Q2.ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
					       Max(Decode(Q2.CODE_VALUE,'C3',Substr(Q2.ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
					       Max(Decode(Q2.CODE_VALUE,'CORP BKER',Substr(Q2.ID_EMAIL_INTERNAL,1,Instr(Q2.ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
					       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
							WHEN Q1.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
							WHEN Q1.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
											  FROM   SAM.INT_GRADES
											  WHERE  CLT_OID = P.ULT_PAR_OID
											  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT
				       FROM    REFDBO.CRIT_FLAG        R,                   
                       SAM.CLIENTUCN     U,
                       SAM.INT_GRADES         G,                 
                       SAM.CIT_CLIENTME    N,
                       SAM.UNIQUE_ULT_P P,
                       Q1,                 
                       Q2
					WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
					AND    Q1.OID = N.CLT_OID
					AND    N.CLT_OID = U.CLIENT_OID
					AND    U.CLIENT_OID = G.CLT_OID
					AND    G.CLT_OID = P.CHILD_CLT_OID
					AND    P.CHILD_CLT_OID = R.CLT_OID
					AND    R.CLT_OID = Q2.CLT_OID
					--AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
					--AND    C.OID = '1256120'
					GROUP BY Q1.OID,Q1.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD;        

Re: Restructering [message #444033 is a reply to message #444027] Thu, 18 February 2010 07:13 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post explain plans for both queries
Re: Restructering [message #444147 is a reply to message #444033] Thu, 18 February 2010 21:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That scalar subquery on SAM.INT_GRADES in the SELECT clause is most probably the problem.

To confirm, replace the entire subquery with a fixed value (like SYSDATE) and run it again. If it is 50% faster, then you know that at least 50% of the time is spent in the scalar subquery.

See this article (seach for "Correlated sub-query expressions in the SELECT clause") for more information why you should never use a scalar subquery in the SELECT clause.

Ross Leishman
Re: Restructering [message #445075 is a reply to message #444027] Thu, 25 February 2010 11:13 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi cookiemonster,
following is the explain for first query, can u please give some helpful suggestions by looking at the plan

The tables in FROM clause of the first query are not tables, they are views

Also i cannot provide the plan for second query due to
some problem


Explain plan


SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 4104044567                                                      -------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |  3333K|   960M|       |   659K  (3)| 01:24:13 |       |       |
|   1 |  SORT AGGREGATE                          |                        |     1 |   194 |       |            |          |       |       |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID      | WORKFORCE_JPMC_V3      |     2 |   152 |       |     4   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |                        |     1 |   194 |       |    19   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                         |                        |     1 |   118 |       |    15   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                        |                        |     1 |    90 |       |    13   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                       |                        |     1 |    53 |       |    10   (0)| 00:00:01 |       |       |
|   7 |        PARTITION RANGE ITERATOR          |                        |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM            |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|*  9 |          INDEX RANGE SCAN                | CLIENT_TEAM_I3         |     5 |       |       |     3   (0)| 00:00:01 |   KEY |     2 |
|  10 |        PARTITION RANGE ITERATOR          |                        |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 11 |         TABLE ACCESS BY LOCAL INDEX ROWID| TEAM                   |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 12 |          INDEX RANGE SCAN                | TEAM_I1                |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  13 |       PARTITION RANGE ITERATOR           |                        |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 14 |        TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER            |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 15 |         INDEX RANGE SCAN                 | TEAM_MEMBER_I3         |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 16 |      TABLE ACCESS BY INDEX ROWID         | CODE_VALUE             |     1 |    28 |       |     2   (0)| 00:00:01 |       |       |
|* 17 |       INDEX RANGE SCAN                   | CODE_VALUE_I1          |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  18 |     PARTITION RANGE ITERATOR             |                        |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 19 |      INDEX RANGE SCAN                    | WORKFORCE_JPMC_V3_I1   |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  20 |  SORT AGGREGATE                          |                        |     1 |   194 |       |            |          |       |       |
|* 21 |   TABLE ACCESS BY LOCAL INDEX ROWID      | WORKFORCE_JPMC_V3      |     2 |   152 |       |     4   (0)| 00:00:01 |     1 |     1 |
|  22 |    NESTED LOOPS                          |                        |     1 |   194 |       |    19   (0)| 00:00:01 |       |       |
|  23 |     NESTED LOOPS                         |                        |     1 |   118 |       |    15   (0)| 00:00:01 |       |       |
|  24 |      NESTED LOOPS                        |                        |     1 |    90 |       |    13   (0)| 00:00:01 |       |       |
|  25 |       NESTED LOOPS                       |                        |     1 |    53 |       |    10   (0)| 00:00:01 |       |       |
|  26 |        PARTITION RANGE ITERATOR          |                        |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|* 27 |         TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM            |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|* 28 |          INDEX RANGE SCAN                | CLIENT_TEAM_I3         |     5 |       |       |     3   (0)| 00:00:01 |   KEY |     2 |
|  29 |        PARTITION RANGE ITERATOR          |                        |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 30 |         TABLE ACCESS BY LOCAL INDEX ROWID| TEAM                   |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 31 |          INDEX RANGE SCAN                | TEAM_I1                |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  32 |       PARTITION RANGE ITERATOR           |                        |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 33 |        TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER            |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 34 |         INDEX RANGE SCAN                 | TEAM_MEMBER_I3         |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 35 |      TABLE ACCESS BY INDEX ROWID         | CODE_VALUE             |     1 |    28 |       |     2   (0)| 00:00:01 |       |       |
|* 36 |       INDEX RANGE SCAN                   | CODE_VALUE_I1          |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  37 |     PARTITION RANGE ITERATOR             |                        |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 38 |      INDEX RANGE SCAN                    | WORKFORCE_JPMC_V3_I1   |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  39 |  SORT AGGREGATE                          |                        |     1 |   194 |       |            |          |       |       |
|* 40 |   TABLE ACCESS BY LOCAL INDEX ROWID      | WORKFORCE_JPMC_V3      |     2 |   152 |       |     4   (0)| 00:00:01 |     1 |     1 |
|  41 |    NESTED LOOPS                          |                        |     1 |   194 |       |    19   (0)| 00:00:01 |       |       |
|  42 |     NESTED LOOPS                         |                        |     1 |   118 |       |    15   (0)| 00:00:01 |       |       |
|  43 |      NESTED LOOPS                        |                        |     1 |    90 |       |    13   (0)| 00:00:01 |       |       |
|  44 |       NESTED LOOPS                       |                        |     1 |    53 |       |    10   (0)| 00:00:01 |       |       |
|  45 |        PARTITION RANGE ITERATOR          |                        |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|* 46 |         TABLE ACCESS BY LOCAL INDEX ROWID| CLIENT_TEAM            |     1 |    30 |       |     7   (0)| 00:00:01 |   KEY |     2 |
|* 47 |          INDEX RANGE SCAN                | CLIENT_TEAM_I3         |     5 |       |       |     3   (0)| 00:00:01 |   KEY |     2 |
|  48 |        PARTITION RANGE ITERATOR          |                        |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 49 |         TABLE ACCESS BY LOCAL INDEX ROWID| TEAM                   |     1 |    23 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 50 |          INDEX RANGE SCAN                | TEAM_I1                |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  51 |       PARTITION RANGE ITERATOR           |                        |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 52 |        TABLE ACCESS BY LOCAL INDEX ROWID | TEAM_MEMBER            |     1 |    37 |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 53 |         INDEX RANGE SCAN                 | TEAM_MEMBER_I3         |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 54 |      TABLE ACCESS BY INDEX ROWID         | CODE_VALUE             |     1 |    28 |       |     2   (0)| 00:00:01 |       |       |
|* 55 |       INDEX RANGE SCAN                   | CODE_VALUE_I1          |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  56 |     PARTITION RANGE ITERATOR             |                        |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 57 |      INDEX RANGE SCAN                    | WORKFORCE_JPMC_V3_I1   |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  58 |  PARTITION RANGE ITERATOR                |                        |     1 |    47 |       |     4   (0)| 00:00:01 |   KEY |     2 |
|* 59 |   TABLE ACCESS BY LOCAL INDEX ROWID      | CIT_CLIENT_NAME        |     1 |    47 |       |     4   (0)| 00:00:01 |   KEY |     2 |
|* 60 |    INDEX RANGE SCAN                      | CIT_CLIENT_NAME_I3     |     1 |       |       |     3   (0)| 00:00:01 |   KEY |     2 |
|* 61 |  TABLE ACCESS BY INDEX ROWID             | INT_GRADES             |     3 |    90 |       |     7   (0)| 00:00:01 |       |       |
|* 62 |   INDEX RANGE SCAN                       | INT_GRADES_PK          |     3 |       |       |     3   (0)| 00:00:01 |       |       |
|  63 |  SORT UNIQUE                             |                        |  3333K|   960M|  2003M|   659K  (3)| 01:24:13 |       |       |
|* 64 |   HASH JOIN                              |                        |  3333K|   960M|       |   442K  (4)| 00:56:32 |       |       |
|* 65 |    TABLE ACCESS FULL                     | GES_GCR_REFERENCE      | 21693 |   529K|       |    70   (5)| 00:00:01 |       |       |
|* 66 |    HASH JOIN                             |                        |  2288K|   604M|       |   442K  (4)| 00:56:31 |       |       |
|  67 |     PARTITION RANGE ITERATOR             |                        |   704 | 23232 |       |  3378   (3)| 00:00:26 |   KEY |     2 |
|* 68 |      TABLE ACCESS FULL                   | CREDIT_GCR             |   704 | 23232 |       |  3378   (3)| 00:00:26 |   KEY |     2 |
|  69 |     MERGE JOIN CARTESIAN                 |                        |  2121K|   493M|       |   438K  (4)| 00:56:05 |       |       |
|* 70 |      TABLE ACCESS BY LOCAL INDEX ROWID   | CLIENT                 |     1 |    34 |       |     4   (0)| 00:00:01 |     1 |     1 |
|  71 |       NESTED LOOPS                       |                        |  6928 |  1454K|       | 50641   (3)| 00:06:29 |       |       |
|* 72 |        HASH JOIN                         |                        |  4697 |   830K|  7480K| 32609   (5)| 00:04:11 |       |       |
|  73 |         PARTITION RANGE ITERATOR         |                        |   129K|  5956K|       |  4203   (5)| 00:00:33 |   KEY |     2 |
|* 74 |          TABLE ACCESS FULL               | CIT_CLIENT_NAME        |   129K|  5956K|       |  4203   (5)| 00:00:33 |   KEY |     2 |
|* 75 |         HASH JOIN                        |                        | 53379 |  6985K|  3696K| 27670   (5)| 00:03:33 |       |       |
|* 76 |          HASH JOIN                       |                        | 37459 |  3255K|       | 17331   (5)| 00:02:13 |       |       |
|* 77 |           HASH JOIN                      |                        | 11437 |   625K|       |  5963   (5)| 00:00:46 |       |       |
|* 78 |            TABLE ACCESS FULL             | CIT_CREDIT_FLAG        | 11059 |   269K|       |  1824   (6)| 00:00:14 |       |       |
|  79 |            PARTITION RANGE ITERATOR      |                        | 68224 |  2065K|       |  4137   (5)| 00:00:32 |   KEY |     2 |
|* 80 |             TABLE ACCESS FULL            | CIT_RPT_ULT_ALL_CREDIT | 68224 |  2065K|       |  4137   (5)| 00:00:32 |   KEY |     2 |
|* 81 |           TABLE ACCESS FULL              | INT_GRADES             |  2638K|    83M|       | 11335   (5)| 00:01:27 |       |       |
|  82 |          PARTITION LIST SINGLE           |                        |  2101K|    90M|       |  4437   (7)| 00:00:35 |   KEY |   KEY |
|* 83 |           TABLE ACCESS FULL              | CIT_CLIENT_ALIAS       |  2101K|    90M|       |  4437   (7)| 00:00:35 |     1 |     1 |
|  84 |        PARTITION RANGE ITERATOR          |                        |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|* 85 |         INDEX RANGE SCAN                 | CLIENT_PK              |     1 |       |       |     2   (0)| 00:00:01 |   KEY |     2 |
|  86 |      BUFFER SORT                         |                        |   306 |  8874 |       |   438K  (4)| 00:56:05 |       |       |
|  87 |       SORT UNIQUE                        |                        |   306 |  8874 |       |    56   (4)| 00:00:01 |       |       |
|* 88 |        TABLE ACCESS FULL                 | BC_BDU_CAU             |   306 |  8874 |       |    56   (4)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                  
   2 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                       
   8 - filter("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                           
   9 - access("CT"."CLT_OID"=:B1)                                                                                                 
  11 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                  
  12 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNTIL_DT" IS NOT NULL)           
  14 - filter(("TMB"."TMR_CD"='C1' OR "TMB"."TMR_CD"='C3' OR "TMB"."TMR_CD"='CORP BKER' OR "TMB"."TMR_CD"='CRD ADM') AND          
              "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                         
  15 - access("TM"."OID"="TMB"."TEM_OID")                                                                                         
  16 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                                                 
  17 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                         
       filter("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR "CV"."CODE_VALUE"='CRD ADM')   
  19 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)                 
  21 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                       
  27 - filter("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                           
  28 - access("CT"."CLT_OID"=:B1)                                                                                                 
  30 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                  
  31 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNTIL_DT" IS NOT NULL)           
  33 - filter(("TMB"."TMR_CD"='C1' OR "TMB"."TMR_CD"='C3' OR "TMB"."TMR_CD"='CORP BKER' OR "TMB"."TMR_CD"='CRD ADM') AND          
              "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                         
  34 - access("TM"."OID"="TMB"."TEM_OID")                                                                                         
  35 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                                                 
  36 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                         
       filter("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR "CV"."CODE_VALUE"='CRD ADM')   
  38 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)                 
  40 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                       
  46 - filter("CT"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CT"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                           
  47 - access("CT"."CLT_OID"=:B1)                                                                                                 
  49 - filter("TM"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                  
  50 - access("CT"."TEM_OID"="TM"."OID" AND "TM"."CIT_EFF_UNTIL_DT">=SYSDATE@! AND "TM"."CIT_EFF_UNTIL_DT" IS NOT NULL)           
  52 - filter(("TMB"."TMR_CD"='C1' OR "TMB"."TMR_CD"='C3' OR "TMB"."TMR_CD"='CORP BKER' OR "TMB"."TMR_CD"='CRD ADM') AND          
              "TMB"."CIT_EFF_ASOF_DT"<=SYSDATE@! AND "TMB"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                         
  53 - access("TM"."OID"="TMB"."TEM_OID")                                                                                         
  54 - filter("CV"."CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                                                 
  55 - access("CV"."CODE_VALUE"="TMB"."TMR_CD" AND "CV"."CT"='TMR' AND "CV"."CIT_EFF_ASOF_DT"<=SYSDATE@!)                         
       filter("CV"."CODE_VALUE"='C1' OR "CV"."CODE_VALUE"='C3' OR "CV"."CODE_VALUE"='CORP BKER' OR "CV"."CODE_VALUE"='CRD ADM')   
  57 - access("TMB"."EMPL_ID"="ID_STANDARD" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)                 
  59 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                       
  60 - access("CLT_OID"=:B1 AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)                                 
  61 - filter("CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                                                      
  62 - access("CLT_OID"=:B1 AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                     
  64 - access("GCR"="ID_GCR")                                                                                                     
  65 - filter("CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                     
  66 - access("BUS_DEV_UNIT_ID"="GCR")                                                                                            
  68 - filter("GCR_CD"='PRI' AND "CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!)                                  
  70 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                                                       
  72 - access("CLT_OID"="CLT_OID")                                                                                                
  74 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                     
  75 - access("CLT_OID"="CLT_OID")                                                                                                
  76 - access("CLT_OID"="CHILD_CLT_OID")                                                                                          
  77 - access("CHILD_CLT_OID"="CLT_OID")                                                                                          
  78 - filter(SUBSTR("CCF_CD",1,1)='C' AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)                        
  80 - filter("CIT_EFF_ASOF_DT"<=SYSDATE@! AND "CIT_EFF_UNTIL_DT">=SYSDATE@!)                                                     
  81 - filter("CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                     
  83 - filter(("CAS_CD"<>'CLO' OR "CAS_CD" IS NULL) AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)           
  85 - access("OID"="CLT_OID" AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_UNTIL_DT" IS NOT NULL)                               
  88 - filter(("CR_ADM_UNIT_NO"=39 OR "CR_ADM_UNIT_NO"=62 OR "CR_ADM_UNIT_NO"=704 OR "CR_ADM_UNIT_NO"=705 OR "CR_ADM_UNIT_NO"=711)
              AND "CIT_EFF_UNTIL_DT">=SYSDATE@! AND "CIT_EFF_ASOF_DT"<=SYSDATE@!)                                                 

156 rows selected.

SQL> 
SQL> spool off


Regards,
Ritesh



CM: Removed a ton of trailing spaces. Do us a favour - next time you use spool make sure you use trimspool.

[Updated on: Thu, 25 February 2010 11:28] by Moderator

Report message to a moderator

Re: Restructering [message #445077 is a reply to message #444027] Thu, 25 February 2010 11:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
And that's why you shouldn't write queries that join views. They are an absolute nightmare when it comes to fixing performance problems.

At a glance the biggest single problem is this:
-------------------------------------------------------------------------------------------------------------------------------------------                                         
| Id  | Operation                                | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                              
-------------------------------------------------------------------------------------------------------------------------------------------
|  63 |  SORT UNIQUE                             |                        |  3333K|   960M|  2003M|   659K  (3)| 01:24:13 |       |       |  


You've got a sort operation that's having to write 2G of data to temp.
That'll be due to something in one of the views, but since I have no idea what the views do I really couldn't say what.

Can you rewrite your query against the base tables directly and bypass the views?
Re: Restructering [message #445155 is a reply to message #445077] Fri, 26 February 2010 04:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or rewrite the views to remove the ORDER BY
Previous Topic: ORACLE OPTIMIZER
Next Topic: Performance regression from 8i to 10G
Goto Forum:
  


Current Time: Mon Dec 05 18:56:27 CST 2016

Total time taken to generate the page: 0.09018 seconds