| 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 -> Oracle REF CURSOR performance issue
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
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
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 )
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
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
and p.first_name like v_first and ep.EXTAPP_CD = p_facility and p.PID = ep.PID;
END get_detail;
end mpi_lookup;
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
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
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,
 |
 |