performance tuning concepts.

[coolboy98699]

Hi Sir,

I am newbie to performance tuning concepts.

Sir if the query is running slow then i will check the explain plan..

In that what parameters should i check..

Thank you

[Tim...]

Hi.

Whole books have been written on this subject, so I'm not sure what I can impart that is of use in a forum post.

1) Check the cardinality of the operations. That is, how many rows Oracle is processing for each step.

What is important here is the estimated number of rows match the actual number of rows. When Oracle generates an execution plan it uses database statistics to decide how expensive operations will be. If the stats are not representative of the data, then it may pick the wrong execution plan. A single way to check this is to compare the estimated cardinality with the actual cardinality. There is an example of this here:

http://www.oracle-base.com/articles/9i/ ... stics_hint

If the actual and expected cardinalities do not match (or are close), then Oracle is using unrepresentative stats to make its decisions, which is not good.

http://www.oracle-base.com/articles/mis ... istics.php

2) The next step is to sense check the operations. Do the execution plan seem sensible. For example, if you are expecting to return a single row, is a full table scan of 1 billion rows sensible? Likewise, if you are expecting to visit 1 billion rows, using an index to access them is probably not a great idea. This sense check may identify obvious issues, like you are trying to use a full table scan, where an index scan would be more appropriate, or vice versa.

Cheers

Tim...

[coolboy98699]

Sir

Thank you very much for your reply........

As you said in these two cases

"
1.if you are expecting to return a single row, is a full table scan of 1 billion rows sensible? Likewise,
2. if you are expecting to visit 1 billion rows, using an index to access them is probably not a great idea. "

what should we do to overcome please explain...

Thank you very much

[Tim...]

Hi.

1) It might mean you need to add an index to the table. It might be an index is present, but not being used because of the way the query is written.

2) This could be because of bad stats, so Oracle things the index is more efficient.

Cheers

Tim...

[coolboy98699]

Thank you sir.....

I am clear about those things now...

[Tim...]