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  |
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  |
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.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 18:02:28 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
