Home » RDBMS Server » Performance Tuning » suggestions required to change the query to improve its performance (Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production,windows XP)
suggestions required to change the query to improve its performance [message #289283] Thu, 20 December 2007 09:37 Go to next message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
By using the following query I Created a View. If i query the view for the required data it is taking around 1 minute to fetch the data.

The output of the query gives all the time slots for which each seat is available on each day starting with the effective start date in the xxcgs_adm_seat_mst table
It ends with last CAL_DAY of the xxcgs_admin_calendar table.

The XXCGS_SEAT_MST table contains all the seats data and XXCGS_ADM_ALLOCATION table contains all the allocated seat data.


Tables used in this view

1)
CREATE TABLE XXCGS_ADM_ALLOCATION
(
SEAT_ID NUMBER,
ALLOCFROM_DATE DATE ,
ALLOCTO_DATE DATE ,
TIME_FROM VARCHAR2(11 BYTE),
TIME_TO VARCHAR2(11 BYTE),
Foreign Key(seat_id) references XXCGS_ADM_SEAT_MST(SEAT_ID));

The table is having the following sample data:

Insert into xxcgs_adm_allocation Values(1121,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation Values(1140,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation Values(1122,'12-DEC-2007','27-DEC-2007','04:30', '10:59');
Insert into xxcgs_adm_allocation Values(1054,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation Values(1141,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation values(1142,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation Values(1120,'12-DEC-2007','27-DEC-2007','00:00', '04:59');
COMMIT;


2) CREATE TABLE XXCGS_ADM_SEAT_MST
(
SEAT_ID NUMBER PRIMARY KEY,
EFFECTIVE_START DATE,
EFFECTIVE_END DATE);

Sample data:

Insert into xxcgs_adm_seat_mst Values (1053, '15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1049, '15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1054, '16-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1121, '12-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1120, '12-JAN-2007', NULL);
Insert into xxcgs_adm_seat_mst Values (1080, '11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1100, '11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1122, '01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1140, '01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1141, '01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1142, '01-DEC-2007',NULL);
COMMIT;


3)
CREATE TABLE XXCGS_ADMIN_CALENDAR
(
CAL_DAY DATE,
SL_NO NUMBER
);

This table contains 3 years data.

In this table I inserted data with this code:
declare
v_cal_day DATE ;
V_SL_NO NUMBER;
BEGIN
V_CAL_DAY := '01-JAN-2007';
V_SL_NO := 1;
LOOP
INSERT INTO XXCGS_ADMIN_CALENDAR VALUES (V_CAL_DAY,V_SL_NO);
V_CAL_DAY := V_CAL_DAY+1;
V_SL_NO := V_SL_NO+1;
EXIT WHEN V_CAL_DAY > '31-DEC-2009';
END LOOP;
END;


4)
CREATE TABLE TIME_SLOTS
(
S_NO NUMBER(2),
HOURS VARCHAR2(6 BYTE),
FROM_TIME VARCHAR2(6 BYTE),
TO_TIME VARCHAR2(6 BYTE)
);

Data in this Table:

insert into time_slots values(1, '00:30', '00:00', '00:29');
insert into time_slots values(2, '01:00', '00:30', '00:59');
insert into time_slots values(3, '01:30', '01:00', '01:29');
insert into time_slots values(4, '02:00', '01:30', '01:59');
insert into time_slots values(5, '02:30', '02:00', '02:29');
insert into time_slots values(6, '03:00', '02:30', '02:59');
insert into time_slots values(7, '03:30', '03:00', '03:29');
insert into time_slots values(8, '04:00', '03:30', '03:59');
insert into time_slots values(9, '04:30', '04:00', '04:29');
insert into time_slots values(10, '05:00', '04:30', '04:59');
insert into time_slots values(11, '05:30', '05:00', '05:29');
insert into time_slots values(12, '06:00', '05:30', '05:59');
insert into time_slots values(13, '06:30', '06:00', '06:29');
insert into time_slots values(14, '07:00', '06:30', '06:59');
insert into time_slots values(15, '07:30', '07:00', '07:29');
insert into time_slots values(16, '08:00', '07:30', '07:59');
insert into time_slots values(17, '08:30', '08:00', '08:29');
insert into time_slots values(18, '09:00', '08:30', '08:59');
insert into time_slots values(19, '09:30', '09:00', '09:29');
insert into time_slots values(20, '10:00', '09:30', '09:59');
insert into time_slots values(21, '10:30', '10:00', '10:29');
insert into time_slots values(22, '11:00', '10:30', '10:59');
insert into time_slots values(23, '11:30', '11:00', '11:29');
insert into time_slots values(24, '12:00', '11:30', '11:59');
insert into time_slots values(25, '12:30', '12:00', '12:29');
insert into time_slots values(26, '13:00', '12:30', '12:59');
insert into time_slots values(27, '13:30', '13:00', '13:29');
insert into time_slots values(28, '14:00', '13:30', '13:59');
insert into time_slots values(29, '14:30', '14:00', '14:29');
insert into time_slots values(30, '15:00', '14:30', '14:59');
insert into time_slots values(31, '15:30', '15:00', '15:29');
insert into time_slots values(32, '16:00', '15:30', '15:59');
insert into time_slots values(33, '16:30', '16:00', '16:29');
insert into time_slots values(34, '17:00', '16:30', '16:59');
insert into time_slots values(35, '17:30', '17:00', '17:29');
insert into time_slots values(36, '18:00', '17:30', '17:59');
insert into time_slots values(37, '18:30', '18:00', '18:29');
insert into time_slots values(38, '19:00', '18:30', '18:59');
insert into time_slots values(39, '19:30', '19:00', '19:29');
insert into time_slots values(40, '20:00', '19:30', '19:59');
insert into time_slots values(41, '20:30', '20:00', '20:29');
insert into time_slots values(42, '21:00', '20:30', '20:59');
insert into time_slots values(43, '21:30', '21:00', '21:29');
insert into time_slots values(44, '22:00', '21:30', '21:59');
insert into time_slots values(45, '22:30', '22:00', '22:29');
insert into time_slots values(46, '23:00', '22:30', '22:59');
insert into time_slots values(47, '23:30', '23:00', '23:29');
insert into time_slots values(48, '24:00', '23:30', '23:59');
commit;


The query

Part 1:

This Query maps the seat_id in the master table with each time slot and with effective start date between sysdate-60 and sysdate+365 assuming that a seat can be allocated not more than a year. so if there are 10 seats in this table then this table contains 10*48*425 records

SELECT mst.seat_id
, cal.sl_no
, cal.cal_day
, tm.s_no
, tm.from_time
, tm.to_time
FROM xxcgs_adm_seat_mst mst
, xxcgs_admin_calendar cal
, time_slots tm
WHERE cal.cal_day >= NVL (mst.effective_start,SYSDATE - 60)
AND cal.cal_day <= NVL (mst.effective_end, SYSDATE + 365)

I created a view TEST_MST_V on the above query


Part 2:

This query is based on the allocation table and it checks the allocated seats with all the time slots and between the allocated date range and marks the timeslot as 0 if it is not allocated and 1 if it is allocated

SELECT seat_id
, sl_no
, cal_day
, s_no
, from_time
, to_time
FROM
(SELECT alloc.seat_id
, cal.sl_no
, cal.cal_day
, tm.s_no
, tm.from_time
, tm.to_time
,SUM(DECODE(LEAST(alloc.time_from,tm.from_time),alloc.time_from,
DECODE(GREATESTalloc.time_to,tm.to_time),alloc.time_to, 1,0),0))AVAILABILITY
FROM
xxcgs_adm_allocation alloc
,xxcgs_admin_calendar cal
,time_slots tm
WHERE
cal.cal_day >= alloc.allocfrom_date
AND cal.cal_day <= alloc.allocto_date
GROUP BY alloc.seat_id
,cal.sl_no
,cal.cal_day
,tm.s_no
,tm.from_time
,tm.to_time
ORDER BY cal.sl_no, tm.s_no) a
WHERE a.AVAILABILITY = 1


I created TEST_ALLOC_V on the above query


Part 3:

Here i am substracting the 2nd query output from the 1st to get all the available time slots for all seats. In this i used the views created on the above 2 queries

SELECT seat_id
,sl_no
,cal_day
,s_no
,from_time
,to_time
FROM test_mst_v
MINUS
SELECT seat_id
, sl_no
, cal_day
, s_no
, from_time
, to_time
FROM test_alloc_v
WHERE AVAILABILITY = 1

Part 4:

I need to squeeze the time slots to get the available start time and available end time. For that i used the following query on the substracted data

SELECT seat_id
,sl_no
,cal_day
,MIN (s_no) min_s_no
,MAX (s_no) max_s_no
,MIN (from_time) min_time
,MAX (to_time) max_time
FROM
(SELECT
seat_id
,sl_no
,cal_day
,s_no
,prev_s_no
,from_time
,to_time
,COUNT (CASE WHEN prev_s_no IS NULL THEN 'x'
WHEN prev_s_no != s_no - 1 THEN 'x'
ELSE NULL
END)
OVER (PARTITION BY seat_id, sl_no ORDER BY s_no) grp
FROM (SELECT seat_id
,sl_no
,cal_day
,s_no
,from_time
,to_time,
LAG (s_no) OVER (PARTITION BY seat_id, sl_no ORDER BY s_no) prev_s_no
FROM(SELECT seat_id
,sl_no
,cal_day
,s_no
,from_time
,to_time
FROM test_mst_v
MINUS
SELECT seat_id
, sl_no
, cal_day
, s_no
, from_time
, to_time
FROM test_alloc_v
WHERE AVAILABILITY = 1)))
GROUP BY seat_id, sl_no, cal_day, grp
ORDER BY seat_id, cal_day


But this query is taking long time to fetch the data. Presently we have only 12 seats of data. For this it is taking 40 seconds to fetch the data. Once this project goes live we will have around 10000 seats in the XXCGS_ADM_SEAT_MST table.

I request any of the experts who can give me a solution based on the data i posted here to modify the query to give better performance.

Thanks and Regards
Deekshit

Report message to a moderator

Re: suggestions required to change the query to improve its performance [message #289286 is a reply to message #289283] Thu, 20 December 2007 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Try 1 or more of the suggestions in the following URL:
http://www.orafaq.com/forum/t/84315/74940/

Report message to a moderator

Re: suggestions required to change the query to improve its performance [message #289347 is a reply to message #289286] Thu, 20 December 2007 20:04 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There was a lot of unformatted code there, and you didn't post Explain Plans of TKProf output, so it's impossible to tell what is REALLY going on.

The problem is very probably with the RANGE JOINS - joins on >= / <= predicates.

There is an article here about that very problem. It might help.

If you post again, enclose your code in [CODE]...[/CODE] tags using the theme/orafaq/images/b_code.gif button and also include Explain Plan and TKProf output.
Ross Leishman

Report message to a moderator

Previous Topic: Changing Query Approach
Next Topic: Long Running SQL- CLOB vs TO_CHAR
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 05:48:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.