Query Optimization

[OracleProgrammerwith]

I had a table with 32 columns out of which one is a composite primary key and have 10 foriegn keys.

I am writing a simple query with where clause having a composite primary key.

But still I am getting cost of the query as 37.

Can you please suggest how can i optimize the cost of the query.

select col4
from table
where col1 = x
and col2 = y
and col3 = z

Col1,Col2 and Col3 are the composite primary key.

Thanks in Advance.
Dhiren Shah

 

[Dagon]

Does the query plan show it using the index?

For Oracle-related work, contact me through Linked-In.

 

[OracleProgrammerwith]

I guess yes.But not sure how to read the plan.

This was the query fired :

select /*+ first_rows */
a.event_desc
from erm_rpr_risk_dtls_t a
where a.rmu_id= 1
and a.rpr_vrsn_no= 1
and a.risk_seq_no= 1

Below is the plan

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS Cost=37 Cardinality=2 Bytes=78 CPU cost=270943 Optimizer=HINT: FIRST_ROWS IO cost=37
TABLE ACCESS BY INDEX ROWID Object owner=ERM_DEV Object name=ERM_RPR_RISK_DTLS_T Cost=37 Cardinality=2 Bytes=78 CPU cost=270943 Optimizer=ANALYZED IO cost=37
INDEX SKIP SCAN Object owner=ERM_DEV Object name=ERM_RPR_RISK_DTLS_T_PK Cost=32 Cardinality=14 CPU cost=231586 Optimizer=ANALYZED Search columns=2 IO cost=32

 

[Dagon]

I'm a bit concerned about:

INDEX SKIP SCAN

That means it is reading the index by skipping some of the columns e.g. your index is on columns A, B and C but you are only specifying A and C so it is having to go through some process of working out all the possible values of B so that it can use the index as far down as C.

I've seen some very bad performance with plans which involve INDEX SKIP SCAN. I would try to avoid it by doing one of three things:

a) If possible, use all the columns of the concatenated index in the query.
b) If that is not possible, move the columns around so that those which you are using in the query are all at the front.
c) As a last resort, you could create another index on just the columns you are using in the query.


For Oracle-related work, contact me through Linked-In.

 

[OracleProgrammerwith]

Thanks a lot Sir ,

After passing all the columns in the where clause the cost has been reduced to 8,But the problem is I dont have all the values of the column. Out of the 10 foreign keys I have values of only 3 keys and that is a composite primary key.

Also does the below point means I will move all the forign keys positioning first and then the other keys ?

"If that is not possible, move the columns around so that those which you are using in the query are all at the front."

 

[Dagon]

It has nothing to do with your foreign keys. Foreign keys are different to primary keys and they may or may not appear in the primary key index.

The important thing is what columns appear in ERM_RPR_RISK_DTLS_T_PK and what order they appear in. Look at the definition of that index in TOAD or whatever SQL tool you use. Ideally, you need to be using all of the columns from that index or as many of the leading columns of the index as possible. The fact that your plan is showing skip scan indicates that there must be some columns in that index other than the three in the query.

For Oracle-related work, contact me through Linked-In.

 

[OracleProgrammerwith]

Thanks a lot sir,

The Cost has been drastically reduced to 3 now.

SELECT STATEMENT, GOAL = ALL_ROWS Cost=3 Cardinality=2 Bytes=78 CPU cost=83320 Optimizer=ALL_ROWS IO cost=3
INDEX FAST FULL SCAN Object owner=ERM_DEV Object name=ERM_RPR_RISK_EVT_DESC_IDX Cost=3 Cardinality=2 Bytes=78 CPU cost=83320 Optimizer=ANALYZED IO cost=3

 

[OracleProgrammerwith]

Can you please let me know how to avoid "Collection Iterator Pickler Fetch"

I had a PLSQL Function which returns more than 23 columns as Output.I am using 3 queries inside this PLSQL Function. When I am running this 3 queries separately I am getting the cost as 3,4 and 6 for the individual query but when I am using a pipelined function to incorporate this query in the same function I am getting cost as high.

SELECT STATEMENT, GOAL = ALL_ROWS Bytes=16336 Cardinality=8168 Cost=30 CPU cost=5694624 Depth=0 Id=0 IO cost=29 Operation=SELECT STATEMENT Optimizer=ALL_ROWS Plan id=1436 Position=30 Statement id=ERM_DEV Time=1 Timestamp=7/11/2011 2:50:43 PM
COLLECTION ITERATOR PICKLER FETCH Bytes=16336 Cardinality=8168 Cost=30 CPU cost=5694624 Depth=1 Id=1 IO cost=29 Object alias=KOKBF$@SEL$E112F6F0 Object name=ERM_RISK_MIT_NEW_REQ_ACT_FN Object type=PROCEDURE Operation=COLLECTION ITERATOR Options=PICKLER FETCH Parent id=0 Plan id=1436 Position=1 Projection=VALUE(A0)[4000], VALUE(A0)[22], VALUE(A0)[1], VALUE(A0)[22], VALUE(A0)[22], VALUE(A0)[22], VALUE(A0)[4000], VALUE(A0)[22], VALUE(A0)[4000], VALUE(A0)[22], VALUE(A0)[4000], VALUE(A0)[22], VALUE(A0)[4000], VALUE(A0)[500], VALUE(A0)[20], VALUE(A0)[22], VALUE(A0)[22], VALUE(A0)[150], VALUE(A0)[22], VALUE(A0)[22], VALUE(A0)[22], VALUE(A0)[100], VALUE(A0)[22], VALUE(A0)[4000] Qblock name=SEL$4A78348A Statement id=ERM_DEV Time=1 Timestamp=7/11/2011 2:50:43 PM

 

[Dagon]

Are you getting these figures from SQL*Trace or explain plan?

If it's just explain plan, I'm not sure how meaningful the cost will be because I don't see how Oracle can get any statistics for the number of rows returned by the pipeline function call. Therefore, it's probably using some meaningless guestimate to work out the cost and may give a higher figure than it actually is.

You'd be better off running the query with SQL*Trace turned on and then looking at the results in the trace file. That should give much more accurate figures about elapsed time, CPU time etc.

For Oracle-related work, contact me through Linked-In.

 

[sem]

+1. Oracle cost calculations are based on statistics and some initialization parameters describing hardware in used. E.g. optimizer_index_cost_adj, optimizer_index_caching. So the cost is based on some assumptions, not quite correct in some cases. Only tracing provides real numbers though only for specific cases.

Regards, Dima

http://seminihin.narod.ru