Home » RDBMS Server » Performance Tuning » difference between these 2 execution methods
difference between these 2 execution methods [message #145655] Fri, 04 November 2005 11:10 Go to next message
kals_kk
Messages: 61
Registered: August 2005
Member
Hi,
when I am calling the schedule_util.get_resource_blocks_schedule table function through a select statement, it is taking less than a second to execute.

But when I am calling "schedule_util.get_schedules procedure" through "procedure test_get_schedules" which in turn calls schedule_util.get_resource_blocks_schedule table function execution time is nearly 10secs.

Can somebody tell me how to improve the performance using the wrapper procedure ?
Here are the required procedures and functions given

select *
from table(
schedule_util.get_resource_blocks_schedule(
null,
equipment_array('CT ROOM1'),
to_date('11/04/2005 09:00', 'MM/DD/RRRR HH24:MI'),
to_date('11/04/2006 09:00', 'MM/DD/RRRR HH24:MI'),
'forwards',
'OP',
'available',
null,
null,
'byTime'
)
) where rownum <= 10
/
---The above statement takes less than a second

CREATE OR REPLACE procedure test_get_schedules
(
out_resource_blocks out sys_refcursor
)
as
begin
schedule_util.get_schedules
(
null, -- in_org_obj_aguid
'01FA409FDE1A4BB4A71E526343B30DFD', -- in_equipment_obj_aguid
'11/10/2005 05:00', -- in_start_dt
null, -- in_end_dt
'forwards', -- in_date_direction ('forwards' or 'backwards')
'OP', -- in_patient_type
'available', -- in_category -- null, Available, or Unavailable
null, -- in_procedure_aguid
null, -- in_duration -- in minutes (overrides equip.duration & proc.duration)
'byTime', -- in_layout_style
3, -- in_max_rowcount
out_resource_blocks -- sys_refcursor returning data
);

end;
/

-- This is taking nearly 10sec

Here is the schedule_util.get_schedules procedure

procedure get_schedules(
in_org_obj_aguid in equipment_t.org_obj_aguid%type
,in_equipment_obj_aguid in equipment_t.obj_aguid%type
,in_start_date in varchar2
,in_end_date in varchar2
,in_date_direction in varchar2
,in_patient_type in top_proc_dates_t.patient_type_code%type
,in_category in varchar2 -- null, Available, or Unavailable
,in_procedure_aguid in procedure_t.obj_aguid%type
,in_duration in pls_integer -- in minutes
,in_layout_style in varchar2 -- subsort beneath byDay; valid values: constants.kScheduleLayoutByTime or constants.kScheduleLayoutByEquipment, see Constants
,in_max_rowcount in pls_integer
,out_resource_blocks out sys_refcursor
) is
the_equipments equipment_array := null;
the_date_direction varchar2(255);
the_start_date date;
the_end_date date;
the_tmp_date date;
begin
if(in_equipment_obj_aguid is not null) then
the_equipments := equipment_array(in_equipment_obj_aguid);
end if;

-- initialize date range to non-null values
the_start_date := to_date(in_start_date, 'MM/DD/RRRR HH24:MI');
the_end_date := to_date(in_end_date, 'MM/DD/RRRR HH24:MI');
if(the_start_date is null) then
the_start_date := sysdate;
end if;
if(the_end_date is null) then
the_end_date := the_start_date + 365;
end if;

open out_resource_blocks
for select
resource_obj_aguid
,start_date
,start_time
,end_time
,status
from table(get_resource_blocks_schedule(in_org_obj_aguid
,the_equipments
,the_start_date
,the_end_date
,in_date_direction
,in_patient_type
,in_category
,in_procedure_aguid
,in_duration
,in_layout_style)) x
where (in_max_rowcount <= 0 or rownum <= in_max_rowcount);
end get_schedules;


Re: difference between these 2 execution methods [message #145780 is a reply to message #145655] Sun, 06 November 2005 16:55 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ALTER SESSION SET SQLTRACE = TRUE;


Run something...

ALTER SESSION SET SQLTRACE = FALSE;


Do the same thing for both executions IN SEPARATE SESSIONS, and use TK*Prof to analyze the results. The difference should be fairly obvious from the tk_prof output.
Previous Topic: rebuilding indexes
Next Topic: enq: TM - contention
Goto Forum:
  


Current Time: Thu Mar 28 18:52:59 CDT 2024