Tune SQL

[abhaysk]

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.