Tune SQL

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.