| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle REF CURSOR performance issue
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 ...Received on Wed Jan 16 2002 - 13:19:36 CST
>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
 |
 |