Home » RDBMS Server » Performance Tuning » Please help me to tune this procedure (Oracle 11g)
Please help me to tune this procedure [message #598193] Fri, 11 October 2013 06:42 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

Please help me to tune this procedure it's taking 30 minute to complete.


CREATE OR REPLACE PROCEDURE del_merge_proc
IS
ownername VARCHAR2(30);
mymainquery CLOB; 
v_tat_value VARCHAR2(100);
prior_process_exist NUMBER;
prior_process_dt VARCHAR2(100);

BEGIN
--  DELETES THE RECORDS WHICH ARE OLDER THAN TWO WEEKS.

DELETE  FROM maint_data 
WHERE datatime_dt <=(WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,'DAY')), 
    'SATURDAY',1, 
    'SUNDAY',2, 
    'MONDAY',3, 
    'TUESDAY',4, 
    'WEDNESDAY',5, 
    'THURSDAY',6, 
    'FRIDAY',7) daynumber  
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7);
COMMIT;

/* KEEPING THE DATE IN THE STAMPING TABLE WHEN THE PROCEDURE RAN 
AND THE NEXT RUN IT SHOULD TAKE THE RECORDS BETWEEN PREVIOUS RUN AND SYSDATE */

SELECT COUNT(*) INTO prior_process_exist from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;

FOR schemain IN (SELECT schema_name FROM schema_list  ORDER BY schema_name) 
LOOP
SELECT tat_value INTO v_tat_value FROM tat_info_tbl WHERE tat_name='MASTERTAT';
BEGIN
ownername := schemain.schema_name||'.';

mymainquery := 'MERGE INTO maint_data target
USING (
WITH unitvalues AS (
SELECT MNG_NO, BIDVAL_NO, 
    SUM(unitval) unitval,
    SUM(unitrate) unitrate,
    SUM(unitmargin)  unitmargin
FROM '||ownername||'rate_values R
GROUP BY MNG_NO,BIDVAL_NO)
SELECT DISTINCT 
    PHD.MNG_NO MNG_NO,
    RL.BIDVAL_NO BIDVAL_NO,
    PHD.ccd_code ccd_code,
    CASE WHEN UPPER(PHD.ccd_code)= UPPER('''||v_tat_value||''') 
    THEN 1
    ELSE 2
    END ccd_rate,
    RL.making_name making_name,
    PMR.making_unit making_unit
FROM '||ownername||'ps_hpold_desg PHD
    LEFT OUTER JOIN '||ownername||'ps_manage_rts PMR
    ON PHD.MNG_NO = PMR.MNG_NO
    LEFT OUTER JOIN '||ownername||'rate_values RL 
    ON (RL.BIDVAL_NO = PMR.BIDVAL_NO OR PMR.BIDVAL_NO IS NULL) AND PHD.MNG_NO = RL.MNG_NO
    INNER JOIN unitvalues ON unitvalues.MNG_NO=PHD.MNG_NO AND unitvalues.BIDVAL_NO = RL.BIDVAL_NO
    LEFT OUTER JOIN '||ownername||'GEDIS_SALESPERSON GLS ON PHD.LEAD_SALESPERSON = GLS.SALESPERSON_ID
    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,''DAY'')), 
    ''SATURDAY'',1, 
    ''SUNDAY'',2, 
    ''MONDAY'',3, 
    ''TUESDAY'',4, 
    ''WEDNESDAY'',5, 
    ''THURSDAY'',6, 
    ''FRIDAY'',7) daynumber  
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7) 
    AND ('||prior_process_exist||' = 0)
    OR ('||prior_process_exist||' <> 0 AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE))source
ON(source.MNG_NO = target.tg_MNG_NO
AND source.BIDVAL_NO=target.tg_BIDVAL_NO)
WHEN MATCHED THEN
UPDATE SET 
target.tg_ccd_code.ccd_code,
target.tg_ccd_rate =ccd_rate,  
target.tg_making_name = source.making_name, 
target.tg_making_unit = source.making_unit
WHEN NOT MATCHED THEN
INSERT 
(
target.tg_MNG_NO,
target.tg_BIDVAL_NO,
target.tg_ccd_code,
target.tg_ccd_rate,
target.making_name,
target.making_unit,
)
VALUES
(
source.MNG_NO,
source.tg_BIDVAL_NO,
source.ccd_code,
source.ccd_rate,
source.making_name,
source.making_unit
)';

EXECUTE IMMEDIATE mymainquery;
COMMIT;

MERGE INTO maint_data t
USING
(
SELECT * FROM(
WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,'DAY')), 
    'SATURDAY',1, 
    'SUNDAY',2, 
    'MONDAY',3, 
    'TUESDAY',4, 
    'WEDNESDAY',5, 
    'THURSDAY',6, 
    'FRIDAY',7) daynumber  
FROM dual )
SELECT (sysdate-(ROWNUM-1)) weekday_name,
CASE WHEN (daynumber)-(ROWNUM-1)<=0 THEN 8
ELSE (daynumber)-(ROWNUM-1)
END weekday_number
FROM dates CONNECT BY ROWNUM <=dates.daynumber+7)
)s
ON(TRUNC(s.weekday_name)=TRUNC(t.datatime_dt))
WHEN MATCHED THEN
UPDATE SET t.QO_SNAPSHOT_ID=s.weekday_number;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;

Thanks.
Re: Please help me to tune this procedure [message #598195 is a reply to message #598193] Fri, 11 October 2013 06:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Fri, 11 October 2013 17:12
Please help me to tune this procedure it's taking 30 minute to complete.

.....
PRIOR_PROCESS_DT    VARCHAR2(100);
.....
PRIOR_PROCESS_DT := TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
      INSERT INTO STAMPING_TBL
         (STAMP_NAME, STAMP_DATE)
      VALUES
         ('PROCESS_DONE', PRIOR_PROCESS_DT);



1. First and foremost thing, why the STAMP_DATE is VARCHAR data type? Date should always be stored as DATA in the table, and only while retrieving you can use TO_CHAR in the desired format.

And due to this you are doing this -

Quote:

UPDATE STAMPING_TBL
         SET STAMP_DATE = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')


STAMP_DATE should be a DATE data type and just update as -

UPDATE STAMPING_TBL
         SET STAMP_DATE = SYSDATE


2. Quote:

.....
COMMIT;
.....
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;


You are using COMMIT inside the procedure and then a ROLLBACK if there is WHEN OTHERS EXCEPTION. Do you think it is a robust code?

3. The subject does not sound professional, other people searching the forum will never be helped. Instead of just saying "Help me to tune this procedure", you could give some meaningful subject.

4. And if it is a performance related question, it should be posted in Performance tuning forum.

[update : Added more comments]

[Updated on: Fri, 11 October 2013 07:08]

Report message to a moderator

Re: Please help me to tune this procedure [message #598200 is a reply to message #598195] Fri, 11 October 2013 07:22 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
HI,

I want date along with time if I use PRIOR_PROCESS_DT as DATE


I am not able to get the data and time in this format 'DD-MON-YYYY HH24:MI:SS'.
So that I have used VARCHAR2.

This also I need in this format 'DD-MON-YYYY HH24:MI:SS'.

UPDATE STAMPING_TBL
SET STAMP_DATE = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')

Can you please help me how to put data and time in the desired format in the DATE datetype variable.

Thnaks.
Re: Please help me to tune this procedure [message #598201 is a reply to message #598200] Fri, 11 October 2013 07:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Fri, 11 October 2013 17:52

I want date along with time if I use PRIOR_PROCESS_DT as DATE


I am not able to get the data and time in this format 'DD-MON-YYYY HH24:MI:SS'.
So that I have used VARCHAR2.

This also I need in this format 'DD-MON-YYYY HH24:MI:SS'.

UPDATE STAMPING_TBL
SET STAMP_DATE = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')

Can you please help me how to put data and time in the desired format in the DATE datetype variable.



SQL> SELECT VALUE FROM v$parameter WHERE NAME='nls_date_format';
 
VALUE
--------------------------------------------------------------------------------
mm/dd/yyyy hh24:mi:ss
SQL> SELECT SYSDATE from dual;
 
SYSDATE
-----------
10/11/2013
SQL> DROP TABLE t;
 
Table dropped
SQL> CREATE TABLE t(dt DATE);
 
Table created
SQL> INSERT INTO t VALUES(SYSDATE);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT to_char(dt, 'DD-MON-YYYY HH24:MI:SS') dt from t;
 
DT
--------------------
11-OCT-2013 05:39:36
SQL> UPDATE t SET dt = SYSDATE+1;
 
1 row updated
SQL> COMMIT;
 
Commit complete
SQL> SELECT to_char(dt, 'DD-MON-YYYY HH24:MI:SS') dt from t;
 
DT
--------------------
12-OCT-2013 05:39:37


Internally Oracle stores date in it's own way, while inserting the date just store it as date. While retrieving use to_char. You are getting confused with the display and storage of date.

Here is a link from Edstevens, there you will also find a link from PSOUG by D.Morgan, beautiful demonstration on date storage and retreival. http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

[Updated on: Fri, 11 October 2013 07:49]

Report message to a moderator

Re: Please help me to tune this procedure [message #598202 is a reply to message #598201] Fri, 11 October 2013 07:59 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Thanks for your explanation.

Can you please help me on this.If I changed this prior_process_dt to DATE datatype.

DECLARE
prior_process_dt DATE;
BEGIN
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;


How to change my below condition

AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'')

PHD.UPDATED_TIME is in 'DD-MON-YYYY HH24:MI:SS' this format.

Thanks.
Re: Please help me to tune this procedure [message #598204 is a reply to message #598202] Fri, 11 October 2013 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
ajaykumarkona wrote on Fri, 11 October 2013 13:59


How to change my below condition

AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'')


Since you're using dynamic sql and concatenating strings it'll have to be:
 AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||to_char(prior_process_dt, 'DD-MON-YYYY HH24:MI:SS')||''',''DD-MON-YYYY HH24:MI:SS'')
 


ajaykumarkona wrote on Fri, 11 October 2013 13:59

PHD.UPDATED_TIME is in 'DD-MON-YYYY HH24:MI:SS' this format.

Assuming updated_time is a date, no it's not. Dates are not stored in any human readable format. read the link Lalit posted above.

All this probably isn't going to do much for the speed though.
You really need to tell us where the time is being spent currently. tracing the session while running the procedure would help, however I suspect your main issue is multiple dynamic merges in a loop. How many schemas do you pull data from?
icon4.gif  Re: Please help me to tune this procedure [message #598209 is a reply to message #598193] Fri, 11 October 2013 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;


I have a very good new for you, you can improve your procedure to a microsecond.
Just replace it by:
CREATE OR REPLACE PROCEDURE del_merge_proc
IS
BEGIN
  NULL;
END;

It will do exactly the thing you accept.

Re: Please help me to tune this procedure [message #598258 is a reply to message #598202] Fri, 11 October 2013 15:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Fri, 11 October 2013 18:29
Thanks for your explanation.

Can you please help me on this.If I changed this prior_process_dt to DATE datatype.

DECLARE
prior_process_dt DATE;
BEGIN
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;




You have now declared prior_process_dt as DATE data type, so your code becomes -

DECLARE
  prior_process_dt DATE;
BEGIN
  IF prior_process_exist = 0 THEN
    prior_process_dt    := SYSDATE;
    INSERT
    INTO stamping_tbl
      (
        stamp_name,
        stamp_date
      )
      VALUES
      (
        'PROCESS_DONE',
        prior_process_dt
      );
  ELSE
    SELECT stamp_date
    INTO prior_process_dt
    FROM stamping_tbl
    WHERE stamp_name = 'PROCESS_DONE';
    
    UPDATE stamping_tbl
    SET stamp_date  =SYSDATE
    WHERE stamp_name='PROCESS_DONE';
  END IF;


Your actual question was that your code takes 30 minutes to execute, so you want it to tune. For that you need to provide more information. From your previous topics you know what they are.
Re: Please help me to tune this procedure [message #598307 is a reply to message #598258] Sun, 13 October 2013 03:58 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi ,

Thanks for your response.

I have changed the code as you said.

I have one question can you please clarify.

If I see the output of the variable it is showing only date not time part.

DBMS_OUTPUT.PUT_NE(prior_process_dt);

My question is in my below condition if I not change the format will it consider date only or date and time also.

PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE

Please confirm .

Thanks.

Re: Please help me to tune this procedure [message #598308 is a reply to message #598307] Sun, 13 October 2013 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ajaykumarkona wrote on Sun, 13 October 2013 10:58

If I see the output of the variable it is showing only date not time part.

DBMS_OUTPUT.PUT_NE(prior_process_dt);

Who knows, maybe you didn't let time part display. See how and why:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> declare
  2    l_date date := sysdate;
  3  begin
  4    dbms_output.put_line(l_date);
  5  end;
  6  /
13.10.2013

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> declare
  2    l_date date := sysdate;
  3  begin
  4    dbms_output.put_line(l_date);
  5  end;
  6  /
13.10.2013 11:35:03

PL/SQL procedure successfully completed.

SQL>


Quote:

My question is in my below condition if I not change the format will it consider date only or date and time also.

PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE

It depends on what PRIOR_PROCESS_DT contains. If it contains date only, there's no format mask which will force it have time part (how could it?).
Re: Please help me to tune this procedure [message #598309 is a reply to message #598308] Sun, 13 October 2013 04:59 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,
DECLARE
prior_process_dt DATE;
BEGIN
IF prior_process_exist = 0 THEN
prior_process_dt := SYSDATE;
INSERT
INTO stamping_tbl
(
stamp_name,
stamp_date
)
VALUES
(
'PROCESS_DONE',
prior_process_dt
);
ELSE
SELECT stamp_date
INTO prior_process_dt
FROM stamping_tbl
WHERE stamp_name = 'PROCESS_DONE';

UPDATE stamping_tbl
SET stamp_date =SYSDATE
WHERE stamp_name='PROCESS_DONE';
END IF;

The above code is storing sysdate(date and time) into the variable prior_process_dt
If I use DBMS_OUTPUT.PUT_LINE(prior_process_dt); showing data only.
Why it's not showing time.
But in the table I am able to date and time.

SELECT * FROM stamping_tbl;

Now in the below condition format is required?

PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE;

Thanks.
Re: Please help me to tune this procedure [message #598310 is a reply to message #598309] Sun, 13 October 2013 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Why it's not showing time.

Did you read what I wrote in my previous message?

Quote:
Now in the below condition format is required?

No, nor is TO_DATE because you said that PRIOR_PROCESS_DT's datatype is DATE. All you need is
PHD.UPDATED_TIME BETWEEN prior_process_dt AND SYSDATE;
Re: Please help me to tune this procedure [message #598311 is a reply to message #598309] Sun, 13 October 2013 05:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Sun, 13 October 2013 15:29

The above code is storing sysdate(date and time) into the variable prior_process_dt
If I use DBMS_OUTPUT.PUT_LINE(prior_process_dt); showing data only.

Why it's not showing time.
But in the table I am able to date and time.


It is explained to you multiple times. Did you understand what LF explained? Did you go through my previos post and the link?

Quote:

PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE;


Why TO_DATE on a date type?

Lalit Kumar B wrote on Sat, 12 October 2013 02:14
ajaykumarkona wrote on Fri, 11 October 2013 18:29
Thanks for your explanation.

Can you please help me on this.If I changed this prior_process_dt to DATE datatype.

DECLARE
prior_process_dt DATE;
BEGIN
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;




You have now declared prior_process_dt as DATE data type, so your code becomes -

DECLARE
  prior_process_dt DATE;
BEGIN
  IF prior_process_exist = 0 THEN
    prior_process_dt    := SYSDATE;
    INSERT
    INTO stamping_tbl
      (
        stamp_name,
        stamp_date
      )
      VALUES
      (
        'PROCESS_DONE',
        prior_process_dt
      );
  ELSE
    SELECT stamp_date
    INTO prior_process_dt
    FROM stamping_tbl
    WHERE stamp_name = 'PROCESS_DONE';
    
    UPDATE stamping_tbl
    SET stamp_date  =SYSDATE
    WHERE stamp_name='PROCESS_DONE';
  END IF;


Your actual question was that your code takes 30 minutes to execute, so you want it to tune. For that you need to provide more information. From your previous topics you know what they are.

[Updated on: Sun, 13 October 2013 05:13]

Report message to a moderator

Re: Please help me to tune this procedure [message #598312 is a reply to message #598310] Sun, 13 October 2013 05:15 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your explanation.

In merge statement I am using the below filter.

WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(
    TRIM(TO_CHAR(SYSDATE,''DAY'')),
    ''SATURDAY'',1,
    ''SUNDAY'',2,
    ''MONDAY'',3,
    ''TUESDAY'',4,
    ''WEDNESDAY'',5,
    ''THURSDAY'',6,
    ''FRIDAY'',7) daynumber
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)


Is it better to use directly the query (or)
storing the value of MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) into variable
and using that variable in filter condition?.
please confirm.

Thanks.
Re: Please help me to tune this procedure [message #598313 is a reply to message #598312] Sun, 13 October 2013 05:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
Why do you think a variable is used in any code in general?

Does the query needs to be executed multiple times in the code? If yes, then store the value thus returned by the query in a variable and use it multiple places.

Once again, your question was to tune the code to make it execute faster. What about that?

[Updated on: Sun, 13 October 2013 05:46]

Report message to a moderator

Re: Please help me to tune this procedure [message #598315 is a reply to message #598312] Sun, 13 October 2013 05:47 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

For testing I have done the following.

I am getting OCT invalid identifier.

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from DATE_STAMP 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '||LAST_PROCESS_DATE||' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;


Please help me.

Thanks.
Re: Please help me to tune this procedure [message #598318 is a reply to message #598315] Sun, 13 October 2013 05:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Sun, 13 October 2013 16:17
I am getting OCT invalid identifier.


OCT? Let me guess, the date used in the dynamic sql has month part as "OCT", without proper number of single quotes it is not identifying it as date.

If you execute in SQL*Plus, it will tell you the error message with the exact line number.

The error has to be from the dynamic sql, use dbms_output to check whether it is formed properly or not. Use proper quotes around LAST_PROCESS_DATE.

Regards,
Lalit

[Updated on: Sun, 13 October 2013 06:12]

Report message to a moderator

Re: Please help me to tune this procedure [message #598322 is a reply to message #598318] Sun, 13 October 2013 06:34 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have done the change now it's working fine.

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from DATE_STAMP 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;


But it's got considering time ,it's considering only date.

Please help me.

Thanks.
Re: Please help me to tune this procedure [message #598323 is a reply to message #598322] Sun, 13 October 2013 06:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Sun, 13 October 2013 17:04

But it's got considering time ,it's considering only date.


Prove it.

Everything that needs to be answered has been explained wonderfully to you.
Re: Please help me to tune this procedure [message #598324 is a reply to message #598323] Sun, 13 October 2013 06:54 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have verified as below.

INSERT INTO stamping_tbl VALUES('PROCESS_TIME',SYSDATE);

After 30 minutes

SELECT a.dt_value,sysdate FROM stamping_tbl a;

10/13/2013 12:18:43 PM  10/13/2013 12:48:43 PM

INSERT INTO dates_tbl VALUES(sysdate-interval '10' minute);
INSERT INTO dates_tbl VALUES(sysdate-3/24);
INSERT INTO dates_tbl VALUES(sysdate-4/24);

The below pl/sql block is returning count as 3 .Instead of 1

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from stamping_tbl 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;


Please help me.

Thanks.

Re: Please help me to tune this procedure [message #598325 is a reply to message #598324] Sun, 13 October 2013 07:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ajaykumarkona wrote on Sun, 13 October 2013 17:24

I have verified as below.


It works perfectly for me and returns a count 1. It is becoming a silly discussion, so this is one last demo -

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> create table stamping_tbl(name varchar2(20), dt_value date);

Table created.

SQL> INSERT INTO stamping_tbl VALUES('PROCESS_TIME',SYSDATE-1/24);

1 row created.

SQL>
SQL> SELECT dt_value, sysdate FROM stamping_tbl;

DT_VALUE             SYSDATE
-------------------- --------------------
13-oct-2013 17:09:52 13-oct-2013 18:09:52

SQL>
SQL> create table dates_tbl(r_date date);

Table created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-interval '10' minute);

1 row created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-3/24);

1 row created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-4/24);

1 row created.

SQL>
SQL> select * from dates_tbl;

R_DATE
--------------------
13-oct-2013 17:59:52
13-oct-2013 15:09:52
13-oct-2013 14:09:52

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  v_sql varchar2(2000);
  3  LAST_PROCESS_DONE   NUMBER;
  4  LAST_PROCESS_DATE   date;
  5  tcount NUMBER :=0;
  6  begin
  7  SELECT COUNT(*) INTO LAST_PROCESS_DONE from stamping_tbl
  8  WHERE name = 'PROCESS_TIME';
  9  IF LAST_PROCESS_DONE = 0 THEN
 10      DBMS_OUTPUT.PUT_LINE('INSIDE IF BLOCK');
 11      LAST_PROCESS_DATE := SYSDATE;
 12      INSERT INTO stamping_tbl (name,dt_value)
 13      VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
 14  ELSE
 15      DBMS_OUTPUT.PUT_LINE('INSIDE ELSE BLOCK');
 16      SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl
 17        WHERE name = 'PROCESS_TIME';
 18      UPDATE stamping_tbl
 19      SET dt_value=SYSDATE
 20      WHERE name='PROCESS_TIME';
 21  END IF;
 22  v_sql := 'SELECT COUNT(*)  FROM dates_tbl
 23  WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
 24  execute immediate v_sql INTO tcount;
 25      DBMS_OUTPUT.PUT_LINE(LAST_PROCESS_DATE ||' , '||sysdate);
 26      DBMS_OUTPUT.PUT_LINE(tcount);
 27  end;
 28  /
INSIDE ELSE BLOCK
13-oct-2013 17:09:52 , 13-oct-2013 18:09:53
1

PL/SQL procedure successfully completed.


Regards,
Lalit

[Updated on: Sun, 13 October 2013 07:40]

Report message to a moderator

icon13.gif  Re: Please help me to tune this procedure [message #598326 is a reply to message #598324] Sun, 13 October 2013 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
v_sql := 'SELECT COUNT(*) FROM dates_tbl
WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
execute immediate v_sql INTO tcount;


There is no need of dynamic SQL for this.

Re: Please help me to tune this procedure [message #598331 is a reply to message #598193] Sun, 13 October 2013 17:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
There you go, got it : Please help me to tune this procedure. But this time with a different username in OTN forum.

The last post was also exactly similar in both the forums. First you posted here please convert this procedure to oracle and then it was immediately posted by Ramya_162 in OTN please convert this procedure to oracle. By the way, Ramya_162 is also in Orafaq.

How many login credentials do you have in different forums?


[Updated on: Sun, 13 October 2013 17:43]

Report message to a moderator

Re: Please help me to tune this procedure [message #598368 is a reply to message #598331] Mon, 14 October 2013 06:25 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Lalit, people are perfectly entitled to post the same question on multiple forums. Don't chide members for casting their net wide when fishing for a solution.
Re: Please help me to tune this procedure [message #598370 is a reply to message #598368] Mon, 14 October 2013 06:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
That's perfectly OK to post in multiple forums. I am just puzzled by the multiple usernames in multiple forums, doesn't sound professional. Anyway, different people different behavior. I just posted the other forums' links to bring to attention that the same thing is being told to the OP commonly across all the forums.

[Updated on: Mon, 14 October 2013 06:37]

Report message to a moderator

Re: Please help me to tune this procedure [message #598371 is a reply to message #598370] Mon, 14 October 2013 06:48 Go to previous message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Lalit Kumar B wrote on Mon, 14 October 2013 12:36
That's perfectly OK to post in multiple forums.
The tone of your post implied otherwise, but at least you've clarified now.
Quote:
I am just puzzled by the multiple usernames in multiple forums, doesn't sound professional.

1. What does that matter?
2. I don't think it sounds unprofessional. Neither do I think it sounds professional. I don't think that it has any bearing on the poster's level of professionalism. The content of the posts and the ability to take and heed advice however ...
Quote:
Anyway, different people different behavior.
Very true.
Quote:
I just posted the other forums' links to bring to attention that the same thing is being told to the OP commonly across all the forums.
And that is a pretty valid point I'd say, I just don't think it was necessary or appropriate to quiz the OP on the different names that he uses or to accuse him of unprofessionalism on that basis (I'm not saying that there isn't/aren't basis for that, just that having different names on different forums ain't one of them).

Cheers
Previous Topic: performance - index creation on text column
Next Topic: Force the optimizer to consider all join permutations
Goto Forum:
  


Current Time: Sun Apr 20 11:37:34 CDT 2014

Total time taken to generate the page: 0.08756 seconds