Home » RDBMS Server » Performance Tuning » Query taking long time to execute (oracle 9i)
Query taking long time to execute [message #287757] Thu, 13 December 2007 06: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.


The following is the query

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) AS 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) AS pRev_s_No
                  FROM   (SELECT   Seat_Id,
                                   sl_No,
                                   cal_Day,
                                   s_No,
                                   MIN(From_Time) From_Time,
                                   MAX(To_Time) To_Time
                          FROM     (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)
                                    MINUS 
                                    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(Greatest(Alloc.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)
                          GROUP BY Seat_Id,
                                   sl_No,
                                   cal_Day,
                                   s_No)))
GROUP BY Seat_Id,
         sl_No,
         cal_Day,
         grp
ORDER BY Seat_Id,
         cal_Day



Tables used in this view

1)
CREATE TABLE XXCGS_ADM_ALLOCATION
(
SEAT_ID NUMBER,
SEAT VARCHAR2(250 BYTE),
LOCATION VARCHAR2(50 BYTE),
CATEGORY VARCHAR2(50 BYTE),
ALLOCFROM_DATE DATE ,
ALLOCTO_DATE DATE ,
TIME_FROM VARCHAR2(11 BYTE),
TIME_TO VARCHAR2(11 BYTE) );

The table is having the following sample data:

Insert into xxcgs_adm_allocation
Values(1121, 'NI-F1-SW-074', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1140, 'NI-F1-SW-076', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1122,'NI-F1-SW-075', 'MUMB.NIRL.I.A', 'VOICE.SOE', '12-DEC-2007', '27-12-2007', '04:30', '10:59');
Insert into xxcgs_adm_allocation
Values(1054,'NI-F1-SW-072', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1141,'NI-F1-SW-077', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
values(1142,'NI-F1-SW-078', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1120,'NI-F1-SW-073', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
COMMIT;



2) CREATE TABLE XXCGS_ADM_SEAT_MST
(
SEAT_ID NUMBER,
LOCATION_ID NUMBER,
CATEGORY_ID NUMBER,
EFFECTIVE_START DATE,
EFFECTIVE_END DATE);

Sample data:

Insert into xxcgs_adm_seat_mst Values (1053, 1028, 1028,'15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1049, 1028, 1028,'15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1054, 1037, 1036,'16-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1121, 1028, 1028,'12-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1120, 1028, 1028,'12-JAN-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1080, 1040, 1038,'11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1100, 1040, 1028, 11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1122, 1028, 1038,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1140, 1028, 1028,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1141, 1037, 1036,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1142, 1037, 1036,'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;


I dont have any knowledge of tuning the sql queries

can anybody give me a solution to speed up the query

[Formatted the OP's code with http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl
Please format your code next time]

[Updated on: Mon, 17 December 2007 20:55] by Moderator

Report message to a moderator

Re: Query taking long time to execute [message #287758 is a reply to message #287757] Thu, 13 December 2007 06:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>I dont have any knowledge of tuning the sql queries
http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sql_1016.htm#35171
http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sql_1016.htm#30972

Please read and post tkprof / execution plan or autotrace output with code tage with format.
Re: Query taking long time to execute [message #288327 is a reply to message #287757] Mon, 17 December 2007 01:55 Go to previous messageGo to next message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
Hi I attached File which contains Trace ouptut and Explain plan .


Regards,

Deekshit
Re: Query taking long time to execute [message #288328 is a reply to message #287757] Mon, 17 December 2007 01:59 Go to previous messageGo to next message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
The Trace File in word document

regards,

deekshit
Re: Query taking long time to execute [message #288330 is a reply to message #288328] Mon, 17 December 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From OraFAQ Forum Guide:
Quote:

Do not describe your problem and/or solution in MS Office Word, Excel or similar files and attach them to your message. Some people can not, some do not want to download them. Plain TXT files are acceptable.

Regards
Michel
Re: Query taking long time to execute [message #288510 is a reply to message #288330] Mon, 17 December 2007 21:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your SQL has 4 layers of aggregation, 2 layers of analytics, and a MINUS.

This can only be descibed as SPAGHETTI CODE.

I acknowlege that this may not be your fault. Someone else probably wrote the code and you are just trying to fix it. This is probably just the product of dozens of iterations of problems and fixes - surely nobody ever set out to write code like this.

The problem is that it is self-perpetuating. It is impossible for a normal human to fully grasp the functionality of this query enough so that they can change it without introducing some other small (or large) bug. Every time it changes it just guarantees another cycle through testing and development.

But you can be the hero. You can fix it forever - and here's how!

You need to have the courage to tell your architect / boss / project manager that this SQL will always suck no matter what you do. What you need is a little more time - 3 or 4 days at most - to redevelop it in PL/SQL.

The SQL is currently trying to implement a logical set of rules - an algorithm - that has been defined from the business requirements. But nobody can tell what this is because of the strucure of the code. Use cursors, temporary tables, associative arrays, whatever you need to make a program that follows a logical algorithm. When you do this, people will be able to understand what it is doing because it will follow logical steps.

Now there's a certain amount of pride at stake here, because you've invested so much time in this thing already. You don't want to "fail" by not finishing what you started. Trust me - if you finish it as-is you will have failed; because the next guy who tries to work with it will suffer the same problems as you.

Do you want to inflict that on someone else?

If you have the guts to make a fresh start, read here on some tuning techniques to make sure your PL/SQL is efficient.

Ross Leishman
Re: Query taking long time to execute [message #288856 is a reply to message #288510] Tue, 18 December 2007 23:32 Go to previous message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
Hi Ross,

Thanks for your valuable Suggestions. Actually I was also involved in developing some part of this code. But I am the only person which involved in tuning the code to improve its performance.

As you said I will try the other way of developing it.

Thanks & Regards

Deekshit
Previous Topic: Fetching Cursor Performance
Next Topic: SQL Query Performance
Goto Forum:
  


Current Time: Sat Dec 10 22:42:56 CST 2016

Total time taken to generate the page: 0.23335 seconds