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
I am updating 80% of the rows in a 30-column (6 VC  and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput,...
We are starting to upgrade to Oracle 10g and support a PeopleSoft application on AIX5L. Which initialization parameters do you consider important for...
Can I install the Oracle 8i client and the Oracle 10g client on Windows XP?