Execution Plan for SQL Statement
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?
Pavan Kumar Gali replied
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.