Database Tuning (MOSC)

MOSC Banner

Poor Performance of Select in Explicit Cursor

edited Jan 13, 2010 2:06AM in Database Tuning (MOSC) 13 commentsAnswered
 I have introduced some function-based indexes to improve the performance of a cursor declared inside a package for a customer.  When I tested the changes from SQLPlus I found that these indexes are not used when the cursor is called, and there is no performance improvement.

However, if I execute the SELECT statement directly (i.e. not within the cursor), the indexes are used, and performance significantly improved.

The cursor is declared within a package :

        CURSOR bearer_pvc(bearerid      IN      VARCHAR2)
        RETURN pvcsum_rec
        is (
                select .......
        );

and I call it from my test harness using an OPEN, LOOP, FETCH, END LOOP, CLOSE sequence.  Using the 'sql_trace' and 'timed_statistics' session settings I can find an execution plan in a trace file, as follows :

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center