Re: Oracle REF CURSOR performance issue

Thanks, Jonathan for your quick reply.

I took your advice and used a SQL Hint /* INDEX(ep index_name) */ on my select statement and now the optimizer performs an index scan. My package performance is now equal to the sql statement performed directly in sql-plus.

Thanks again,

• Marco

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1011208705.6339.0.nnrp-01.9e984b29_at_news.demon.co.uk>...
> There are several possible problems. Without
> a costed execution plan it is hard to determine
> exactly why Oracle has taken a specific path.
>
> Most importantly, though, how accurate are your
> statistics. It looks as if Oracle thinks your extpat
> table is quite small.
>
> Secondly, bind variables with LIKE are always
> a bit of a problem.
>
> It is a bit of a puzzle, though, as Oracle 9 is
> supposed to peek inside bind variables on the
> first execution and derive a plan from the values
> it finds - so you would expect it to use the same
> plan in both cases/
>
> Quick and dirty fix - things to try:
>
> set the hint /*+ first_rows */
> set hint /*+ use_nl(ep) use_nl(p) */
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now running 3-day intensive seminars
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> Marco Casale wrote in message ...
> >Hi,
> >
> >I'm having some trouble with a performance problem using a REF CURSOR
> >I have an Oracle 9i database running on Solaris 8. The query I'm
> >trying to tune has 2 tables (Patient and ExtPat). These tables are
> >joined by a key called PID (a sequence). I used sql_trace on 2
> >different sessions.
> >Session 1: I typed in the sql directly in sqlplus
> >Here's tkprof output
> >
> >select ep.mrn,
> > p.LAST_NAME,
> > p.FIRST_NAME,
> > p.MIDDLE_NAME,
> > p.NAME_SUFFIX,
> > p.SSN,
> > p.BIRTH_DATE,
> > p.SEX_CD,
> > p.ADDRESS_LINE1,
> > p.ADDRESS_LINE2,
> > p.CITY,
> > p.STATE_CD,
> > p.ZIP,
> > p.HOME_PHONE,
> > ep.DEACT_DATE,
> > p.PID
> > from extpat ep, patient p
> > where p.last_name like ('SMITH' || '%')
> > and p.first_name like ('JOHN' || '%')
> > and ep.EXTAPP_CD = 'SMH'
> > and p.PID = ep.PID
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.01 0.00 0 0 0
> > 0
> >Execute 1 0.00 0.00 0 0 0
> > 0
> >Fetch 2 0.00 0.00 0 11 0
> > 1
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 4 0.01 0.01 0 11 0
> > 1
> >
> >Misses in library cache during parse: 1
> >Optimizer goal: CHOOSE
> >Parsing user id: 66
> >
> >Rows Row Source Operation
> >------- ---------------------------------------------------
> > 1 TABLE ACCESS BY INDEX ROWID EXTPAT
> > 4 NESTED LOOPS
> > 1 TABLE ACCESS BY INDEX ROWID PATIENT
> > 1 INDEX RANGE SCAN (object id 33005)
> > 2 INDEX RANGE SCAN (object id 32996)
> >
> >--------------------------------------------------------
> >
> >Next I created a Package to use a REF CURSOR so that I can provide the
> >Application Developers with a simple api to the database.
> >Unfortunately, my package is performing poorly.
> >
> >Here's the package.
> >
> >CREATE OR REPLACE PACKAGE MPI_LOOKUP
> >IS
> >
> >cursor c1 is
> > select ep.mrn,
> > p.LAST_NAME,
> > p.FIRST_NAME,
> > p.MIDDLE_NAME,
> > p.NAME_SUFFIX,
> > p.SSN,
> > p.BIRTH_DATE,
> > p.SEX_CD,
> > p.ADDRESS_LINE1,
> > p.ADDRESS_LINE2,
> > p.CITY,
> > p.STATE_CD,
> > p.ZIP,
> > p.HOME_PHONE,
> > ep.DEACT_DATE,
> > p.PID
> > from extpat ep, patient p;
> >
> >type mpiCur is ref cursor return c1%rowtype;
> >
> >procedure get_detail(p_cursor out mpiCur,
> > p_omegamrn in varchar2,
> > p_facility in varchar2);
> >
> >procedure get_detail(p_cursor out mpiCur,
> > p_lastname in varchar2,
> > p_firstname in varchar2,
> > p_facility in varchar2);
> >
> >END;
> >/
> >CREATE OR REPLACE PACKAGE BODY MPI_LOOKUP
> >
> >
> >is
> >
> >PROCEDURE get_detail
> >(
> > p_cursor out mpiCur,
> > p_omegamrn in varchar2,
> > p_facility in varchar2 )
> >AS
> >
> >
> >BEGIN
> > open p_cursor for
> > select ep.mrn,
> > p.LAST_NAME,
> > p.FIRST_NAME,
> > p.MIDDLE_NAME,
> > p.NAME_SUFFIX,
> > p.SSN,
> > p.BIRTH_DATE,
> > p.SEX_CD,
> > p.ADDRESS_LINE1,
> > p.ADDRESS_LINE2,
> > p.CITY,
> > p.STATE_CD,
> > p.ZIP,
> > p.HOME_PHONE,
> > ep.DEACT_DATE,
> > p.PID
> > from extpat ep, patient p
> > where ep.mrn = LPAD(p_omegamrn,12,0)
> > and ep.extapp_cd = p_facility
> > and p.PID = ep.PID;
> >
> >END get_detail;
> >
> >PROCEDURE get_detail
> >(
> > p_cursor out mpiCur,
> > p_lastname in varchar2,
> > p_firstname in varchar2,
> > p_facility in varchar2 )
> >AS
> > v_last varchar2(40);
> > v_first varchar2(40);
> >
> >BEGIN
> > select UPPER(p_lastname) || '%'
> > into v_last
> > from dual;
> >
> > select UPPER(p_firstname) || '%'
> > into v_first
> > from dual;
> >
> > open p_cursor for
> > select ep.mrn,
> > p.LAST_NAME,
> > p.FIRST_NAME,
> > p.MIDDLE_NAME,
> > p.NAME_SUFFIX,
> > p.SSN,
> > p.BIRTH_DATE,
> > p.SEX_CD,
> > p.ADDRESS_LINE1,
> > p.ADDRESS_LINE2,
> > p.CITY,
> > p.STATE_CD,
> > p.ZIP,
> > p.HOME_PHONE,
> > ep.DEACT_DATE,
> > p.PID
> > from extpat ep, patient p
> > where p.last_name like v_last
> > and p.first_name like v_first
> > and ep.EXTAPP_CD = p_facility
> > and p.PID = ep.PID;
> >
> >END get_detail;
> >
> >end mpi_lookup;
> >------------------------------------------------------------
> >Here's the output from tkprof
> >
> >
> >BEGIN mpi_lookup.get_detail(:b,'SMITH','JOHN','SMH'); END;
> >
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.00 0.00 0 0 0
> > 0
> >Execute 1 0.00 0.00 0 0 0
> > 1
> >Fetch 0 0.00 0.00 0 0 0
> > 0
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 2 0.00 0.01 0 0 0
> > 1
> >
> >Misses in library cache during parse: 1
> >Optimizer goal: CHOOSE
> >Parsing user id: 66
> >***************************************************************************
> *****
> >
> >select user#
> >from
> > sys.user$ where name = 'OUTLN'
> >
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.00 0.00 0 0 0
> > 0
> >Execute 1 0.00 0.00 0 0 0
> > 0
> >Fetch 1 0.00 0.00 0 2 0
> > 1
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 3 0.00 0.00 0 2 0
> > 1
> >
> >Misses in library cache during parse: 1
> >Optimizer goal: CHOOSE
> >Parsing user id: SYS (recursive depth: 2)
> >
> >Rows Row Source Operation
> >------- ---------------------------------------------------
> > 1 TABLE ACCESS BY INDEX ROWID USER$
> > 1 INDEX UNIQUE SCAN (object id 44)
> >
> >***************************************************************************
> *****
> >
> >SELECT UPPER(:b1) || '%'
> > from dual
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 2 0.01 0.00 0 0 0
> > 0
> >Execute 2 0.00 0.00 0 0 0
> > 0
> >Fetch 2 0.00 0.00 0 2 4
> > 2
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 6 0.01 0.00 0 2 4
> > 2
> >
> >Misses in library cache during parse: 1
> >Optimizer goal: CHOOSE
> >Parsing user id: 66 (recursive depth: 1)
> >
> >Rows Row Source Operation
> >------- ---------------------------------------------------
> > 1 TABLE ACCESS FULL DUAL
> >
> >***************************************************************************
> *****
> >
> >SELECT ep.mrn,
> > p.LAST_NAME,
> > p.FIRST_NAME,
> > p.MIDDLE_NAME,
> > p.NAME_SUFFIX,
> > p.SSN,
> > p.BIRTH_DATE,
> > p.SEX_CD,
> > p.ADDRESS_LINE1,
> > p.ADDRESS_LINE2,
> > p.CITY,
> > p.STATE_CD,
> > p.ZIP,
> > p.HOME_PHONE,
> > ep.DEACT_DATE,
> > p.PID
> > from extpat ep, patient p
> > where p.last_name like :b3
> > and p.first_name like :b2
> > and ep.EXTAPP_CD = :b1
> > and p.PID = ep.PID
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.00 0.00 0 0 0
> > 0
> >Execute 1 0.01 0.00 0 0 0
> > 0
> >Fetch 1 10.60 10.59 0 15133 0
> > 1
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 3 10.61 10.60 0 15133 0
> > 1
> >
> >Misses in library cache during parse: 1
> >Optimizer goal: CHOOSE
> >Parsing user id: 66 (recursive depth: 1)
> >
> >Rows Row Source Operation
> >------- ---------------------------------------------------
> > 1 MERGE JOIN
> > 963386 TABLE ACCESS BY INDEX ROWID EXTPAT
> >1928434 INDEX FULL SCAN (object id 32996)
> > 1 SORT JOIN
> > 1 TABLE ACCESS BY INDEX ROWID PATIENT
> > 1 INDEX RANGE SCAN (object id 33005)
> >----------------------------------------------------------------
> >
> >It is taking over 10 seconds to perform the same query.
> >It ends up performing a full table scan on my ExtPat table but I'm not
> >sure. I've joined the Patient and ExtPat table by PID which is an
> >index in both tables.
> >
> >Is there a better way in Oracle 9i to send a result set back to an
> >application?
> >
> >Thank you,
> >
> >- Marco
Received on Thu Jan 17 2002 - 11:19:11 CST