No Tamil, It is not BITAND. I see no difference by eliminating it.Quote:
Originally Posted by tamilselvan
Printable View
No Tamil, It is not BITAND. I see no difference by eliminating it.Quote:
Originally Posted by tamilselvan
r u sure .. ur CPU is going to 100% when you fire this query? or is that ur CPU is already busy with other processes?
I am sure. I am testing this in a dedicated stress environment with production data and stats. There is no other process running at this time.Quote:
Originally Posted by abhaysk
First of all the sql can be re-written as below, the join on CORR.content_id with ci.LOCATOR, ci.ancestor_locator (resp sub queries) have been removed and have been added in column select logic to avoid scanning of tables twice. I assume the index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.
Code:SELECT SUM(assignments_mapped) assignments_mapped FROM
(
SELECT /*+ index(cbs pk_cb_standard) */
CASE WHEN CORR.content_id IN (ci.LOCATOR, ci.ancestor_locator) THEN
CASE WHEN ci.LOCATOR = ci.ancestor_locator THEN
2
ELSE
1
END
ELSE
0
END 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.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
);
The gets should go down by 2 or even more.. check the CPU now.
Rgds
Abhay
Abhaysk, Thanks for the suggestion. I like the way you have written the new query - unfortunately this one is running for more than 50 secs(and it is still running) with appr. 100% CPU usage throughout.
There are about 3004 corr.content_id and 49034 ci.locator. So when you do a cartesian join between these two, it is producing 14,72,98,136 rows to which we are applying the CASE equation.
Here is the explain plan for your query :
Yes you are right in assuming that index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.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 4 820 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 28 980 4
well actually i removed that substr on cbs.locator and again it started using merge cartisian.. well try this..
Code:SELECT SUM(assignments_mapped) assignments_mapped FROM
(
SELECT /*+ index(cbs pk_cb_standard) */
CASE WHEN CORR.content_id IN (ci.LOCATOR, ci.ancestor_locator) THEN
CASE WHEN ci.LOCATOR = ci.ancestor_locator THEN
2
ELSE
1
END
ELSE
0
END 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.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
);
Hi
Can you please collect stats on all the tables using dbms_stats and then post the plan here without any changes to the sql and without any hints.
regards
Hrishy
Quote:
Originally Posted by hrishy
Hrishy,
I gathered stats on the schema, and ran the simplified version of the query without any hints. This is still using 90% CPU. Here are the details from the trace file.
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.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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.01 0.01 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 5.44 5.31 0 2585652 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 5.45 5.33 0 2585652 0 4
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 127
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
38620 NESTED LOOPS
38620 NESTED LOOPS
49034 NESTED LOOPS
2870 NESTED LOOPS
61129 NESTED LOOPS
61129 TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_RESULT
61318 INDEX RANGE SCAN IDX_ASMNTRES_COMPDATE (object id 54920)
61129 TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_INSTANCE
61129 INDEX UNIQUE SCAN PK_ASSESSMENT_INSTANCE (object id 54912)
2870 TABLE ACCESS BY INDEX ROWID CLG_USER
61129 INDEX UNIQUE SCAN PK_USER (object id 55127)
49034 TABLE ACCESS BY INDEX ROWID CLG_ITEM_RESULT
49034 INDEX RANGE SCAN PK_ITEM_RESULT (object id 55033)
38620 TABLE ACCESS BY INDEX ROWID CLG_ITEM
49034 INDEX UNIQUE SCAN PK_ITEM (object id 55017)
38620 INDEX RANGE SCAN IDX_CORR_ASSET_CONTENT_MLO (object id 54995)
0 TABLE ACCESS BY INDEX ROWID CB_STANDARD
38620 INDEX UNIQUE SCAN PK_CB_STANDARD (object id 54866)
********************************************************************************
Strange behaviour. The fetch line shows 4 rows returned, where as ROW Source says 0 rows.Quote:
Fetch 4 5.44 5.31 0 2585652 0 4
------- ---- -------- ---------- -------- ---------- ---------- -----
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
The row source shows that joining with CB_STANDARD returns 0 rows.
Why don't you start this table, CB_STANDARD as a driving table. Use leading hint.
Do you have an index on LOCATOR column?
Also, change SUBSTR(cbs.LOCATOR,1,6) to LOCATOR like '1.1026%'
It's worth to try.
Tamil
Been there.Quote:
Originally Posted by tamilselvan
Is that column actually text, or is it a number?
Also, use this method for getting the explain plan ...
It may reveal column constraints that are also being applied as filters -- that can be responsible for high CPU usage and a poor execution plan due to poorly estimated statistics.Code:
set autotrace off
explain plan for select ...
/
select * from table(dbms_xplan.display)
/