Hi, Here is my SQL that is executing in 3.7mins and is using 100%CPU. Any suggestions to tune it ?
Thanks.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