Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can this bad query be improved?

jmft2012Feb 26 2013 — edited Mar 8 2013

Db:11.2.0.3
We have a 3rd party app and the web app runs very slow. We want to make the 3rd party to fix the issue. for the
app login process, I did an AWR , found the problem query it runs 10 mins. Then I did the sqltrace
here is it:

select clndr_id , count(*) 
from
 task where (clndr_id = :"SYS_B_0") group by clndr_id union select clndr_id , 
  count(*) from project where (clndr_id = :"SYS_B_1") group by clndr_id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2     53.32     612.03      81650      58920          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     53.34     612.04      81650      58920          0           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34  (PX)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  SORT UNIQUE (cr=58923 pr=81650 pw=22868 time=113329109 us cost=58277 size=24 card=2)
         2          2          2   UNION-ALL  (cr=58923 pr=81650 pw=22868 time=113329001 us)
         1          1          1    SORT GROUP BY NOSORT (cr=58330 pr=81070 pw=22868 time=104312437 us cost=58128 size=7 card=1)
   5589739    5589739    5589739     VIEW  index$_join$_003 (cr=58330 pr=81070 pw=22868 time=619784236 us cost=57240 size=38875249 card=5553607)
   5589739    5589739    5589739      HASH JOIN  (cr=58330 pr=81070 pw=22868 time=617373467 us)
   5590158    5590158    5590158       INDEX RANGE SCAN NDX_TASK_CALENDAR (cr=21676 pr=21676 pw=0 time=113637058 us cost=11057 size=38875249 card=5553607)(object id 24749)
   6673774    6673774    6673774       INDEX FAST FULL SCAN NDX_TASK_PROJ_RSRC (cr=36651 pr=36526 pw=0 time=213370625 us cost=21921 size=38875249 card=5553607)(object id 217274)
         1          1          1    SORT GROUP BY NOSORT (cr=593 pr=580 pw=0 time=9016527 us cost=149 size=17 card=1)
    136390     136390     136390     INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=580 pw=0 time=165434 us cost=132 size=2315876 card=136228)(object id 154409)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      2   SORT (UNIQUE)
      2    UNION-ALL
      1     SORT (GROUP BY NOSORT)
5589739      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'TASK' 
                 (TABLE)
5589739       INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                  'NDX_TASK_CALENDAR' (INDEX)
5590158     SORT (GROUP BY NOSORT)
6673774      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                 'NDX_PROJECT_CALENDAR' (INDEX)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                     22235        1.61        138.66
  direct path write                            1620        3.25        177.42
  db file scattered read                       2313        1.89        238.98
  direct path read                              385        1.72         19.52
  SQL*Net message from client                     2        0.11          0.21

Please make your comments.
Thanks in Advance.

This post has been answered by Jonathan Lewis on Mar 4 2013

Jump to Answer

722698

create or replace function add_working_days(
p_days in number,
p_dt in date default trunc(sysdate)
)
return date
as
v_weeks number;
v_adj number;
begin

v_weeks := trunc(p_days/5);

if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then
v_adj := 2;
else
v_adj := 0;
end if;

return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
end add_working_days;

This is not working correct if the date is SATURDAY, but seems working M-F and Sunday

select trunc(sysdate)+4,add_working_days(6,trunc(sysdate)+4) from dual;
Output : 19-sep2009 29-Sep-2009

722698

Frank Kulash

Hi, Niren,

[This thread|http://forums.oracle.com/forums/thread.jspa?messageID=3351822&#3351822] should give you some ideas. It does it without using PL/SQL, so it's actually a harder problem.

In PL/SQL, you could loop through the days, starting with the start_date parameter and decrementing a counter that is initialized to the other parameter, stopping when the counter reaches 0.
Or you could write a recursive function.

Arunkumar Ramamoorthy-Oracle

Like this?

SQL> ed
Wrote file afiedt.buf

create or replace function next_working_day(start_date in date,  add_days in number) return date is
return_date date;
begin
return_date:=start_date+add_days;
if (UPPER(to_char(return_date,'FMDAY'))='SATURDAY') then
return_date:=return_date+2;
elsif (UPPER(to_char(return_date,'FMDAY'))='SUNDAY') then
return_date:=return_date+1;
end if;
return return_date;
end;
SQL> /

Function created.

SQL> select next_working_day('10-AUG-2009',10) FROM DUAL;

NEXT_WORK
---------
20-AUG-09

SQL> select next_working_day('11-SEP-2009',1) from dual;

NEXT_WORK
---------
14-SEP-09

SQL>

-Arun

Tested

Posted after testing

722698

Hi Frant,

Please explain me with an example. If you have the exact function/Procedure than it can really help.

Thanks
Niren

Frank Kulash

Hi, Niren,

The first respondent gave a better function for testing for work days (Monday through Friday). If there's a bug with Saturdays, I'm sure that can fixed. Also,that function depends on your NLS settings. If that's an issue for you, that can be fixed, too. The idea that there are 5 work days in any 7 consecutive days will cut down the execution time considerable.

A function like I suggested would be useful if you had to consider holidays as well as weekends.
Here's an untested example:

CREATE OR REPLACE FUNCTION  work_days_away
(      in_start_date	    DATE
,      in_day_cnt	    NUMBER
)
RETURN	DATE
DETERMINISTIC
IS
(
	IF  in_start_date - TRUNC (in_start_date, 'IW') >= 5
	OR  is_holiday (in_start_date)
	THEN
		RETURN  work_days_away ( in_start_date + 1
				       , in_day_cnt
				       );
	ELSIF  in_day_cnt >= 1
	THEN
		RETURN  work_days_away ( in_start_date + 1
				       , in_day_cnt    - 1
				       );
	ELSE
		RETURN  in_start_date;
	END IF;
END	work_days_away;

This use the date format 'IW', which does not depend on NLS settings, rather than 'D', which does.

722698

Hi Arun,

The code example where 10-AUG-2009 + 10 days should return 22 as per the need but your code is not considering the saturday and sunday falling on 15,16 Aug 2009.

Thanks
Niren

Arunkumar Ramamoorthy-Oracle

Hi,

I was under the impression that, after adding the days, if the result falls on saturday / sunday, it should consider that and return the next working day i.e monday.

So, 10-AUG-2009 + 10 = 20-AUG-2009 which falls on Thursday.
11-SEP-2009 + 1 = 12-SEP-2009 which falls on Saturday. So, the next working day is Monday which is 14-Sep-2009.

Please let me know if my understanding about your requirement is not correct.

-Arun

722698

Hi Arun,

Basically the need is that any number of days added to a date should return date excluding all saturdays and sundays falling in between.
It would be great if you could help me on this .

Thanks
Niren
+919873314561

Arunkumar Ramamoorthy-Oracle

How about this?

create or replace function add_working_days(
p_days in number,
p_dt in date default trunc(sysdate)
)
return date
as
v_weeks number;
v_adj number;
begin

v_weeks := trunc(p_days/5);

if to_number(to_char(p_dt,'D')) + mod(p_days,5) <= 6 then
v_adj := 1;
elsif to_number(to_char(p_dt,'D')) + mod(p_days,5) = 7 then
v_adj := 2;
else
v_adj := 0;
end if;

return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
end add_working_days;

-Arun

Untested

Shahid Ali

Dear User,
Simple Select can be used to get the desired result.

Solution is :
select decode(to_char((<your_date> + <days_to_add>),'FMDAY'),'SATURDAY',(((<your_date> + <days_to_add>) +2 ,'SUNDAY' ,(((<your_date> + <days_to_add> ) +1, (((<your_date> + <days_to_add> )) from dual;

Examples :
For sysdate as 16 sep 09

Case 1 : Sunday
SQL> select decode(to_char((sysdate + 4),'FMDAY'),'SATURDAY',(sysdate + 4 ) +2 ,'SUNDAY' ,(sysdate + 4 ) +1, (sysdate + 4 )) from dual;

DECODE(TO
---------
21-SEP-09

Case 2 : Saturday
SQL> select decode(to_char((sysdate + 3),'FMDAY'),'SATURDAY',(sysdate + 3 ) +2 ,'SUNDAY' ,(sysdate + 3 ) +1, (sysdate + 3 )) from dual;

DECODE(TO
---------
21-SEP-09

Case 2 : Week day
SQL> select decode(to_char((sysdate + 2 ),'FMDAY'),'SATURDAY',(sysdate + 2 ) +2 ,'SUNDAY' ,(sysdate + 2 ) +1, (sysdate + 2 )) from dual;

DECODE(TO
---------
18-SEP-09

Revert me back in case of any confusion .

Sven W.

user3021403 wrote:
Hi Friends,

I need a funtion which can take in parameters as start_date and number , sum them as new date and return new_start_date as workind day.
Suppose '15-SEP-2009' + 6 should return 23-SEP-2009 as new date.
Basically the sum of date and number should return next working day (excluding saturday,sunday).
e.g '15-SEP-2009' + 6 =21-SEP-2009 , but it includes saturday and sunday , so it should return 23-SEP-2009.

Thanks
Niren

I've written such a logic some years ago. I think you have already given some usable results. Just a few comments from my own experience. There are special scenarios you should think about, how you want to handle them.

1) You add a weekend as date and add 0. Should the next monday be returned or should it stay at the weekend day?
2) Is the number parameter the number of workdays added or the number of days? Like: What should the result be if you add 20 days, will all weekends be skipped? You already gave a clear definition, but it should be specifically mentioned that the number to add is not a number of working days.
3) At a later point you might want to include holidays too (from a holiday table, for example)
4) What about negative numbers? Can your function also calculate back in time? Do you want the friday in this case or the next monday?

722698

Hi Friends,

Thanks for the reply, i have developed the solution.
Sending the code for the benefit of others.

CREATE OR REPLACE FUNCTION NIREN_ADD_DAYS (
p_date DATE,
p_days NUMBER
)
RETURN DATE
IS

v_date DATE;
v_count NUMBER;

BEGIN

SELECT
DECODE(
RTRIM(TO_CHAR(p_date, 'DAY')),
'SATURDAY',
NEXT_DAY(p_date, 'SUNDAY'),
NEXT_DAY((p_date - 7), 'SUNDAY')
)
INTO
v_date
FROM
dual;

v_count := p_date - v_date;

IF (v_count = -1)
THEN

v_count := 0;

END IF;

v_count := v_count + p_days;

IF (v_count > 5)
THEN

v_count := v_count + (FLOOR(v_count / 5.1) * 2);

END IF;

RETURN (v_date + v_count);

END NIREN_ADD_DAYS;
/

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 5 2013

Added on Feb 26 2013

#general-database-discussions

40 comments

901 views

Oracle Database Discussions

How can this bad query be improved?

Comments

Post Details