Home » RDBMS Server » Performance Tuning » Restructering (Oracle, 10g, Windows server 2003)
| Restructering [message #444027] |
Thu, 18 February 2010 06:56  |
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 #444147 is a reply to message #444033] |
Thu, 18 February 2010 21:24  |
rleishman
Messages: 3728
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 |
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 |
cookiemonster
Messages: 13922
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?
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 11 01:34:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
