-
Tune SQL
Hi, Here is my SQL that is executing in 3.7mins and is using 100%CPU. Any suggestions to tune it ?
Code:
SELECT COUNT (cai.assessment_instance_id) AS assignments_mapped
FROM CLG_ASSESSMENT_RESULT car,
CLG_ITEM_RESULT cir,
CLG_ITEM ci,
CLG_CORRELATION CORR,
CLG_ASSESSMENT_INSTANCE cai,
CB_STANDARD cbs,
CLG_USER u
WHERE car.assessment_result_id = cir.assessment_result_id
AND car.status = 20
AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
AND car.assessment_instance_id = cai.assessment_instance_id
AND cai.assignor_id = u.user_id
AND u.org_unit_id = 21296
AND cir.item_locator = ci.LOCATOR
AND (CORR.content_id = ci.LOCATOR OR CORR.content_id = ci.ancestor_locator)
AND CORR.asset_type_id = 113
AND CORR.cbs_id = cbs.cbs_id
AND SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026' -- INPUT: subject Writing; grade 6
AND BITAND (cbs.internal_grade_levels, 2) > 0
Explain Plan
***********************
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 61
SORT AGGREGATE 1 205
NESTED LOOPS 1 205 61
MERGE JOIN CARTESIAN 2 340 53
NESTED LOOPS 3 438 29
NESTED LOOPS 3 237 23
NESTED LOOPS 1 38 20
NESTED LOOPS 3 84 14
TABLE ACCESS BY INDEX ROWID CSI_PROD.CLG_ASSESSMENT_RESULT 3 54 11
INDEX RANGE SCAN CSI_PROD.IDX_ASMNTRES_COMPDATE 38 3
TABLE ACCESS BY INDEX ROWID CSI_PROD.CLG_ASSESSMENT_INSTANCE 1 10 1
INDEX UNIQUE SCAN CSI_PROD.PK_ASSESSMENT_INSTANCE 1
TABLE ACCESS BY INDEX ROWID CSI_PROD.CLG_USER 1 10 2
INDEX UNIQUE SCAN CSI_PROD.PK_USER 1 1
TABLE ACCESS BY INDEX ROWID CSI_PROD.CLG_ITEM_RESULT 13 533 3
INDEX RANGE SCAN CSI_PROD.PK_ITEM_RESULT 13 2
TABLE ACCESS BY INDEX ROWID CSI_PROD.CLG_ITEM 1 67 2
INDEX UNIQUE SCAN CSI_PROD.PK_ITEM 1 1
BUFFER SORT 1 24 51
TABLE ACCESS FULL CSI_PROD.CB_STANDARD 1 24 8
INDEX RANGE SCAN CSI_PROD.IDX_CORR_MLO_CONTENT_ASSET 1 35 4
Thanks.
-
I think " MERGE JOIN CARTESIAN" is causing problem.
Change the driving table to CLG_USER.
Select /*+ LEADING(U) */
With the hint, you can trace sql with level 8.
Tamil
-
Making either CLG_USER or CLG_ASSESSMENT_RESULT as leading tables is increasing the execution time to 4.1mins.
-
You could try changing ...
SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026'
... to ...
cbs.LOCATOR like '1.1026%'
-
Originally Posted by rshivagami
Making either CLG_USER or CLG_ASSESSMENT_RESULT as leading tables is increasing the execution time to 4.1mins.
Do you see MERGE JOIN CARTESIAN operation in the plan?
Otherwise, try another hint:
USE_NL(U, cai, car, cir, ci, corr, cbs)
Tamil
-
The execution time is 4.1mins by replacing SUBSTR with LIKE. Here is the plan :
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 39
SORT AGGREGATE 1 205
NESTED LOOPS 1 205 39
MERGE JOIN CARTESIAN 1 170 35
NESTED LOOPS 3 438 29
NESTED LOOPS 3 237 23
NESTED LOOPS 1 38 20
NESTED LOOPS 3 84 14
TABLE ACCESS BY INDEX ROWID CSI.CLG_ASSESSMENT_RESULT 3 54 11
INDEX RANGE SCAN CSI.IDX_ASMNTRES_COMPDATE 38 3
TABLE ACCESS BY INDEX ROWID CSI.CLG_ASSESSMENT_INSTANCE 1 10 1
INDEX UNIQUE SCAN CSI.PK_ASSESSMENT_INSTANCE 1
TABLE ACCESS BY INDEX ROWID CSI.CLG_USER 1 10 2
INDEX UNIQUE SCAN CSI.PK_USER 1 1
TABLE ACCESS BY INDEX ROWID CSI.CLG_ITEM_RESULT 13 533 3
INDEX RANGE SCAN CSI.PK_ITEM_RESULT 13 2
TABLE ACCESS BY INDEX ROWID CSI.CLG_ITEM 1 67 2
INDEX UNIQUE SCAN CSI.PK_ITEM 1 1
BUFFER SORT 1 24 33
TABLE ACCESS BY INDEX ROWID CSI.CB_STANDARD 1 24 2
INDEX RANGE SCAN CSI.UK_CB_STANDARD_LOCATOR 1 1
INDEX RANGE SCAN CSI.IDX_CORR_MLO_CONTENT_ASSET 1 35 4
The "merge cartesian join" is still there..
Thanks.
-
What's the execution plan for USE_NL(U, cai, car, cir, ci, corr, cbs)?
Tamil
-
Originally Posted by tamilselvan
Do you see MERGE JOIN CARTESIAN operation in the plan?
Otherwise, try another hint:
USE_NL(U, cai, car, cir, ci, corr, cbs)
Tamil
Here is the expalin plan with the above hint.
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 39
SORT AGGREGATE 1 205
NESTED LOOPS 1 205 39
NESTED LOOPS 1 170 35
NESTED LOOPS 3 438 29
NESTED LOOPS 3 237 23
NESTED LOOPS 1 38 20
NESTED LOOPS 3 84 14
TABLE ACCESS BY INDEX ROWID CSI.CLG_ASSESSMENT_RESULT 3 54 11
INDEX RANGE SCAN CSI.IDX_ASMNTRES_COMPDATE 38 3
TABLE ACCESS BY INDEX ROWID CSI.CLG_ASSESSMENT_INSTANCE 1 10 1
INDEX UNIQUE SCAN CSI.PK_ASSESSMENT_INSTANCE 1
TABLE ACCESS BY INDEX ROWID CSI.CLG_USER 1 10 2
INDEX UNIQUE SCAN CSI.PK_USER 1 1
TABLE ACCESS BY INDEX ROWID CSI.CLG_ITEM_RESULT 13 533 3
INDEX RANGE SCAN CSI.PK_ITEM_RESULT 13 2
TABLE ACCESS BY INDEX ROWID CSI.CLG_ITEM 1 67 2
INDEX UNIQUE SCAN CSI.PK_ITEM 1 1
TABLE ACCESS BY INDEX ROWID CSI.CB_STANDARD 1 24 2
INDEX RANGE SCAN CSI.UK_CB_STANDARD_LOCATOR 1 1
INDEX RANGE SCAN CSI.IDX_CORR_MLO_CONTENT_ASSET 1 35 4
The Merge Cartesian Join is gone but the execution time has increased to 4.4 mins.
Thanks.
-
Do you have an index on ORG_UNIT_ID col?
If not. create it and see the plan.
Tamil
-
Try with optimizer_mode=ALL_ROWS
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
|