Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    9

    Talking Unanswered: REF CURSOR SLOW but same query is fast on SQL*PLUS

    Hi

    We are getting some weird response from Oracle 8.1.6 on solaris 2.6.
    We are running a query from Oracle SQL*PLUS and it is pretty fast.

    We plug this query in PL/SQL and get a ref cursor back.
    Once we try to get the data from REF cursor it is very slow.

    Does someone experienced same problem?

    I ran trace analyzer and found that the execution of the query is getting
    changed in PL/SQL. I ran the same query from the SQL*PLUS I get a different execution plan.

    MMD

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I read somewhere that the default optimiser goal for for PL/SQL is ALL_ROWS, even if the database setting is FIRST_ROWS (although I've been unable to find any other info on it in the Oracle docs).

    This would give the impression of it being slower, where the PL/SQL is trying to complete the whole job in the quickest time but the SqlPlus is trying to give the quickest response.

    That might explain the differences in execution plan?

    HTH
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •