topic Execution Plan for SQL Statement in Application Development and Automation Discussions

Execution Plan for SQL Statement

Former Member — Thu, 11 Sep 2008 03:57:29 GMT

I just need to know what is consider a an expensive SQL when you do an SQL execution plan.

One of the SQL statement has an estimated cost of 25. Does this consider an expensive SQL statement?

I know 0 is a good value. But what is a guide here? lower than 10 is good, higher than 11 is bad?

Please guide?

Former Member — Thu, 11 Sep 2008 14:44:48 GMT

Hi,

What does the execution plan say? Does it use proper Indexes? Is it causing a full table scan?

Estimated cost looks good and I am not sure of any number which say good or bad.

Check wethere this exuction plan causing high disk reads/buffer gets.

Regards

KVR

pavan_kumargali — Fri, 12 Sep 2008 11:03:46 GMT

Hi Eida Hanafiah,

To Give you brief explonation about expensive SQL statements:

general threshold for an expensive SQL statement is:

If an SQL statement is having disk reads = (>2%) of total Reads

If an SQL statement is having buffer gets = (>5%) of logical Reads

But again depending on the database performance of the system, we need to analyze expensive SQL statements.

If database response time > 40% of Total response time, which means there would be existence of expensive SQL statements.

Coming to execution plan, the estimated costs depends on the access method to the index.

1. Full table scan

2. Index range scan

3. Index unique scan

Estimated costs more than 50 is really issue and need to analyze.

Hope this helps.

If you want more information, please feel free to reply me.

Thanks and Regards,

Pavan Kumar Gali.