Q
Problem solve Get help with specific problems with your technologies, process and projects.

Why doesn't a select statement speed a module run?

I am using Oracle 9i. I have a PL/SQL module that uses a local procedure for 20% of its time. The procedure consists of a SELECT INTO statement. Rewriting this statement using analytics gives a query plan costing, which is an order of magnitude less than the original version (I am using CBO). However, there is no speed improvement detected when running the module with the new SELECT statement in place. Is the query plan costing wrong, or is something else going on?

I am using Oracle 9i. I have a PL/SQL module that uses a local procedure for 20% of its time. The procedure consists of a SELECT INTO statement. Rewriting this statement using analytics gives a query plan costing, which is an order of magnitude less than the original version (I am using CBO). However, there is no speed improvement detected when running the module with the new SELECT statement in place. Is the query plan costing wrong, or is something else going on?
I don't rely on the query cost figures produced by "explain plan," nor should you. First, make sure that the new plan is being used at runtime, and not just produced by "explain plan." You can find the runtime plan in v$sql_plan. Then, trace the execution, using the original and new versions of the statement. Inspect the results (formatted with tkprof) to see what's going on.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close