-
instead of OR clause.. split it as union or better use concat.. and also collect histograms on all tables and all indexed columns with appropriate bucket size of around 100.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Tamil, There already is an index on u.org_unit_id column.
Abhaysk, Using an UNION ALL Clause brought down the execution time to 37secs but the CPU usage is still 99%. Here is a part of the query in the UNION that is using the maximum resources.
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
AND CORR.asset_type_id = 113
AND CORR.cbs_id = cbs.cbs_id
AND cbs.LOCATOR LIKE '1.1026%' -- INPUT: subject Writing; grade 6
AND BITAND (cbs.internal_grade_levels, 2) > 0
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 30
SORT AGGREGATE 1 163
TABLE ACCESS BY INDEX ROWID CSI.CLG_ITEM_RESULT 1 41 3
NESTED LOOPS 1 163 30
NESTED LOOPS 1 122 27
NESTED LOOPS 1 97 26
MERGE JOIN CARTESIAN 1 62 22
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
BUFFER SORT 1 24 20
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 28 980 4
INDEX UNIQUE SCAN CSI.PK_ITEM 1 25 1
INDEX RANGE SCAN CSI.PK_ITEM_RESULT 13 2
-
I still believe "MERGE JOIN CARTESIAN 1 62 " is causing for slow poor performance. Try HASH Join.
Tamil
-
its due to merge join.. try this hint if u have index on cbs.cbs_id and it sounds to me like its a unique key?..
/*+ use_nl(cbs) index(cbs index_on_cbs_id) */
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Abhaysk, Adding /*+ index(cbs index_on_cbs_id) */ hint has solved the problem. It is now executing in 2 secs and here is the explain plan.
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 33
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
Tamil, you are right - eliminating "Merge Join Cartesian" solved the problem.
Thanks everyone.
-
Oracle and merge join do not work well.
Tamil
-
merge cartesian doesnt work with any flavour.. unless one of the tables joined return a single row..
-
Ah !! this was a very informative thrread.
Special thanks to abhaysk and Tamil
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
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 ?
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
The output is returning only 0 rows.
-
Is it because of BITAND operation?
Tamil
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
|