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%'

Quote:

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

Quote:

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

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.

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) */

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

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

Quote:

Originally Posted by tamilselvan

Is it because of BITAND operation?

Tamil

No Tamil, It is not BITAND. I see no difference by eliminating it.

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?

Quote:

Originally Posted by abhaysk

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.

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 :

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

Yes you are right in assuming that index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.

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

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

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) ********************************************************************************

Quote:

Fetch 4 5.44 5.31 0 2585652 0 4
------- ---- -------- ---------- -------- ---------- ---------- -----

Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS

Strange behaviour. The fetch line shows 4 rows returned, where as ROW Source says 0 rows.

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

Quote:

Originally Posted by tamilselvan

Also, change SUBSTR(cbs.LOCATOR,1,6) to LOCATOR like '1.1026%'

Been there.

Is that column actually text, or is it a number?

Also, use this method for getting the explain plan ...

Code:

set autotrace off explain plan for select ... / select * from table(dbms_xplan.display) /

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.

Locator column is actually a text. Here are the Explain Plan data.

Code:

PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 205 | 40 | | 1 | SORT AGGREGATE | | 1 | 205 | | | 2 | NESTED LOOPS | | 1 | 205 | 40 | | 3 | NESTED LOOPS | | 3 | 543 | 37 | | 4 | NESTED LOOPS | | 3 | 438 | 31 | | 5 | NESTED LOOPS | | 3 | 237 | 25 | | 6 | NESTED LOOPS | | 1 | 38 | 22 | | 7 | NESTED LOOPS | | 3 | 84 | 16 | |* 8 | TABLE ACCESS BY INDEX ROWID| CLG_ASSESSMENT_RESULT | 3 | 54 | 13 | |* 9 | INDEX RANGE SCAN | IDX_ASMNTRES_COMPDATE | 38 | | 3 | | 10 | TABLE ACCESS BY INDEX ROWID| CLG_ASSESSMENT_INSTANCE | 1 | 10 | 1 | |* 11 | INDEX UNIQUE SCAN | PK_ASSESSMENT_INSTANCE | 1 | | | |* 12 | TABLE ACCESS BY INDEX ROWID | CLG_USER | 1 | 10 | 2 | |* 13 | INDEX UNIQUE SCAN | PK_USER | 1 | | 1 | | 14 | TABLE ACCESS BY INDEX ROWID | CLG_ITEM_RESULT | 13 | 533 | 3 | |* 15 | INDEX RANGE SCAN | PK_ITEM_RESULT | 13 | | 2 | |* 16 | TABLE ACCESS BY INDEX ROWID | CLG_ITEM | 1 | 67 | 2 | |* 17 | INDEX UNIQUE SCAN | PK_ITEM | 1 | | 1 | |* 18 | INDEX RANGE SCAN | IDX_CORR_ASSET_CONTENT_MLO | 1 | 35 | 2 | |* 19 | TABLE ACCESS BY INDEX ROWID | CB_STANDARD | 1 | 24 | 1 | |* 20 | INDEX UNIQUE SCAN | PK_CB_STANDARD | 1 | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter("CAR"."STATUS"=20) 9 - access("CAR"."COMPLETED_DATE">=TO_DATE('2006-09-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "CAR"."COMPLETED_DATE"<=TO_DATE('2006-10-13 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 11 - access("CAR"."ASSESSMENT_INSTANCE_ID"="CAI"."ASSESSMENT_INSTANCE_ID") 12 - filter("U"."ORG_UNIT_ID"=21296) 13 - access("CAI"."ASSIGNOR_ID"="U"."USER_ID") 15 - access("CAR"."ASSESSMENT_RESULT_ID"="CIR"."ASSESSMENT_RESULT_ID") 16 - filter("CI"."ANCESTOR_LOCATOR" IS NOT NULL) 17 - access("CIR"."ITEM_LOCATOR"="CI"."LOCATOR") 18 - access("CORR"."ASSET_TYPE_ID"=113 AND "CORR"."CONTENT_ID"="CI"."ANCESTOR_LOCATOR") 19 - filter(SUBSTR("CBS"."LOCATOR",1,6)='1.1026' AND BITAND("CBS"."INTERNAL_GRADE_LEVELS",2)>0) 20 - access("CORR"."CBS_ID"="CBS"."CBS_ID") Note: cpu costing is off

One more thing BITAND operator would return integer only.
See below:
SQL> select bitand(89292.2929292928, 992.227277272) from dual;

BITAND(89292.2929292928,992.227277272)
--------------------------------------
192

But the LOCATOR is checked with 1.1026. Some logic problem?

Tamil

Tamil,

1. We are doing a SUBSTR of Locator where Locator values are like '1', '1.1', '1.1.1', ....
2. We are doing a BITAND for an integer column where for anything above 1st grade "BITAND (cbs.internal_grade_levels, 2) > 0" is true, for anything above 2nd grade,
"BITAND(cbs.internal_grade_levels, 4) > 0" is true and so on..

Ok. I got it.

Code:

Now look at the tkprof output: 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

The SQL was parsed 4 times and executed 4 times. The CPU time was 5.33. So the average CPU time was 1.33 seconds and it did 2.5 Miilion LIOs.

Second, there is a huge difference between autotrace explain plan and tkprof output row source. Why? Either statistics are missing on tables and indexes or some init parameters are not set correctly. What's the value for optimizer_mode you set?

As I said earlier, the least number of rows was returned by CB_STANDARD table. Make this as driving table.
Use LEADING hint. Post the autotrace plan and tkprof output. Attach the entire trace file.

Tamil

Quote:

Originally Posted by tamilselvan

Ok. I got it.
The SQL was parsed 4 times and executed 4 times. The CPU time was 5.33. So the average CPU time was 1.33 seconds and it did 2.5 Miilion LIOs.

As I said earlier, the least number of rows was returned by CB_STANDARD table. Make this as driving table.
Use LEADING hint. Post the autotrace plan and tkprof output. Attach the entire trace file.

Tamil

2 things..

1) average cpu is 1.33 fare.. but average lio is not 2.5 million, but its 0.6~ million (though its too high a value).

2) how will it help making CB_STANDARD as driving???

PS below conditions...

AND CORR.cbs_id = cbs.cbs_id
AND SUBSTR(cbs.LOCATOR,1,6)= '1.1026'-- INPUT: subject Writing; grade 6

Or even

AND CORR.cbs_id = cbs.cbs_id
AND cbs.LOCATOR like '1.1026%'-- INPUT: subject Writing; grade 6

by making this driving, it will use locator as filter and we dont know how many rows it will return..

"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. "

Well your suggestion is based CB_STANDARD returning 0 rows when joined.. yes but that because it first joins with "CORR.cbs_id = cbs.cbs_id" and then looks for locator like '1.1026%' which its not finding any.

Abhay.