-
We have migrated to Oracle ver 8.1.7 from 8.0.6 .
We have done this migration through full export .
I have one sql query which was performaing well in 8.0.6 but the same query is not performing well in 8.1.7.
SQL plus hangs. The explain plan fpr the query shows NESTED LOOP AND NESTED LOOP OUTER..But this query was performing very well in 8.0.6.ALL the indexes are also same.Other resources are not a problem .
OS IS SUN SOLARIS
Can any body help me to resolve this problem ?
Thanks.
Sanjay
-
Do you have the tables and indices analyzed?
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Yes !
Tables and indexes both are analyzed.
-
What is the optimizer mode?
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Optimizer_mode is CHOOSE..This is peoplesoft application
Sanjay
-
Could you post both the query and the explain plan for 8.0.6 and 8.1.7
-Sam
Thanx
Sam
Life is a journey, not a destination!
-
SELECT B.IESG_BROKER_ID, B.NAME, K.DESCR, L.DESCR, ' ', ' ', SUM( decode( A.ANALYSIS_TYPE,'RJE', A.RESOURCE_AMOUNT,0)), D.IESG_RANK, D.RESOURCE_AMOUNT, F.IESG_RANK, F.RESOURCE_AMOUNT, H.IESG_RANK, H.RESOURCE_AMOUNT, J.IESG_RANK, J.RESOURCE_AMOUNT
FROM PS_PROJ_RESOURCE A, PS_IESG_BROKER_TBL B, PS_IESG_BROKER_TBL C, PS_IESG_RANK_TBL D, PS_IESG_BROKER_TBL E, PS_IESG_RANK_TBL F, PS_IESG_BROKER_TBL G, PS_IESG_RANK_TBL H, PS_IESG_BROKER_TBL I, PS_IESG_RANK_TBL J, PS_IESG_OFFICE_TBL K, PS_IESG_BROK_TITLE L
WHERE A.ANALYSIS_TYPE(+) = 'RJE'
AND A.ACCOUNTING_DT(+) BETWEEN TO_DATE('2001-01-01','YYYY-MM-DD') AND TO_DATE('2001-11-08','YYYY-MM-DD')
AND A.IESG_BROKER_ID(+) = B.IESG_BROKER_ID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_IESG_BROKER_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.IESG_BROKER_ID = B_ED.IESG_BROKER_ID
AND B_ED.EFFDT <= SYSDATE)
AND EXISTS (SELECT 'X' FROM PSTREESELECT06 B3_4 WHERE B3_4.SELECTOR_NUM=6380 AND B3_4.TREE_NODE_NUM BETWEEN 894736817 AND 905263132 AND B3_4.RANGE_FROM_06 = B.IESG_OFFICE_ID)
AND B.SETID = C.SETID
AND B.IESG_BROKER_ID = C.IESG_BROKER_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_IESG_BROKER_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.IESG_BROKER_ID = C_ED.IESG_BROKER_ID
AND C_ED.EFFDT <= B.EFFDT)
AND C.IESG_BROKER_ID = D.IESG_BROKER_ID(+)
AND D.BUSINESS_UNIT(+) = 'IESG'
AND D.FISCAL_YEAR(+) = '2000'
AND C.SETID = E.SETID
AND C.IESG_BROKER_ID = E.IESG_BROKER_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_IESG_BROKER_TBL E_ED
WHERE E.SETID = E_ED.SETID
AND E.IESG_BROKER_ID = E_ED.IESG_BROKER_ID
AND E_ED.EFFDT <= C.EFFDT)
AND E.IESG_BROKER_ID = F.IESG_BROKER_ID(+)
AND E.SETID = G.SETID
AND E.IESG_BROKER_ID = G.IESG_BROKER_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_IESG_BROKER_TBL G_ED
WHERE G.SETID = G_ED.SETID
AND G.IESG_BROKER_ID = G_ED.IESG_BROKER_ID
AND G_ED.EFFDT <= E.EFFDT)
AND G.IESG_BROKER_ID = H.IESG_BROKER_ID(+)
AND G.SETID = I.SETID
AND G.IESG_BROKER_ID = I.IESG_BROKER_ID
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_IESG_BROKER_TBL I_ED
WHERE I.SETID = I_ED.SETID
AND I.IESG_BROKER_ID = I_ED.IESG_BROKER_ID
AND I_ED.EFFDT <= G.EFFDT)
AND I.IESG_BROKER_ID = J.IESG_BROKER_ID(+)
AND F.BUSINESS_UNIT(+) = 'IESG'
AND H.BUSINESS_UNIT(+) = 'IESG'
AND J.BUSINESS_UNIT(+) = 'IESG'
AND F.FISCAL_YEAR(+) = '1999'
AND H.FISCAL_YEAR(+) = '1998'
AND J.FISCAL_YEAR(+) = '1997'
AND B.SETID = K.SETID
AND B.IESG_OFFICE_ID = K.IESG_OFFICE_ID
AND K.EFFDT =
(SELECT MAX(K_ED.EFFDT) FROM PS_IESG_OFFICE_TBL K_ED
WHERE K.SETID = K_ED.SETID
AND K.IESG_OFFICE_ID = K_ED.IESG_OFFICE_ID
AND K_ED.EFFDT <= SYSDATE)
AND B.SETID = L.SETID
AND L.EFFDT =
(SELECT MAX(L_ED.EFFDT) FROM PS_IESG_BROK_TITLE L_ED
WHERE L.SETID = L_ED.SETID
AND L.IESG_BROKER_TITLE = L_ED.IESG_BROKER_TITLE
AND L_ED.EFFDT <= SYSDATE)
AND B.IESG_BROKER_TITLE = L.IESG_BROKER_TITLE
GROUP BY B.IESG_BROKER_ID, B.NAME, K.DESCR, L.DESCR, ' ', ' ', D.IESG_RANK, D.RESOURCE_AMOUNT, F.IESG_RANK, F.RESOURCE_AMOUNT, H.IESG_RANK, H.RESOURCE_AMOUNT, J.IESG_RANK, J.RESOURCE_AMOUNT
ORDER BY 7 DESC;
Explain Plan
Query Plan
----------------------------------------------------------------------------------------------------
SORT ORDER BY
SORT GROUP BY
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL PS_IESG_BROKER_TBL
TABLE ACCESS FULL PS_IESG_BROK_TITLE
TABLE ACCESS FULL PS_IESG_OFFICE_TBL
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
INDEX UNIQUE SCAN PS_IESG_RANK_TBL
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
INDEX UNIQUE SCAN PS_IESG_RANK_TBL
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
INDEX UNIQUE SCAN PS_IESG_RANK_TBL
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
INDEX UNIQUE SCAN PS_IESG_RANK_TBL
TABLE ACCESS BY INDEX ROWID PS_PROJ_RESOURCE
INDEX RANGE SCAN PSYPROJ_RESOURCE
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
INDEX RANGE SCAN PS_PSTREESELECT06
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
SORT AGGREGATE
INDEX FAST FULL SCAN PS_IESG_OFFICE_TBL
SORT AGGREGATE
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) PS_IESG_BROK_TITLE
-
First of all, could you try these parameters:
Code:
ALTER SESSION/SYSTEM SET OPTIMIZER_FEATURES_ENABLED=8.0.6
Try the quey. If it doesn't perform well, then set
Code:
ALTER SYSTEM/SESSION SET OPTIMIZER_MODE=FIRST_ROWS
Also please check your sort area size too...
-Sam
Thanx
Sam
Life is a journey, not a destination!
-
SQL> alter system set optimizer_features_enable=8.0.6;
alter system set optimizer_features_enable=8.0.6
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
optimizer_mode = first_row is also not working.
Sort_area_size is 25600 which is same as an 8.0.6 database.
The problem is This query is working very well oracle 8.0.6.
Very strange ..Pl help......
Sanjay
-
Oops that was a wrong suggestion. Its that you have to specify the parameter in the init.ora file and bounce the instance ....
Sorry about that.
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|