The execution time is now 2 secs but CPU usage is almost 100%. Here is the updated Query, Explain Plan, Trace Stats and Wait events. Any idea why ?
The output is returning only 0 rows.Code:SELECT (SELECT /*+ index(cbs pk_cb_standard) */ 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 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) + (SELECT /*+ index(cbs pk_cb_standard) */ 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.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) assignments_mapped FROM DUAL; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 6.10 28.79 32279 470910 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 6.11 28.79 32279 470910 0 1 Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 1 2 SORT AGGREGATE 1 163 NESTED LOOPS 1 163 33 NESTED LOOPS 1 139 32 NESTED LOOPS 3 312 26 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 INDEX UNIQUE SCAN CSI.PK_ITEM 1 25 1 INDEX RANGE SCAN CSI.IDX_CORR_ASSET_CONTENT_MLO 1 35 2 TABLE ACCESS BY INDEX ROWID CSI.CB_STANDARD 1 24 1 INDEX UNIQUE SCAN CSI.PK_CB_STANDARD 1 SORT AGGREGATE 1 205 NESTED LOOPS 1 205 38 NESTED LOOPS 3 543 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 INDEX RANGE SCAN CSI.IDX_CORR_ASSET_CONTENT_MLO 1 35 2 TABLE ACCESS BY INDEX ROWID CSI.CB_STANDARD 1 24 1 INDEX UNIQUE SCAN CSI.PK_CB_STANDARD 1 TABLE ACCESS FULL SYS.DUAL 1 2 Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net more data from client 1 0.00 0.00 SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.03 0.04 db file scattered read 2715 0.01 0.63 db file sequential read 1839 0.00 0.32 direct path write 2295 0.00 0.00 direct path read 2295 0.00 0.00
Reply With Quote