Home » RDBMS Server » Performance Tuning » Stored Procedure Performance Slows
icon9.gif  Stored Procedure Performance Slows [message #207046] Mon, 04 December 2006 00:33 Go to next message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi there,
i'm having a problem of executing the stored procedure. It takes very long time 1/2 hour to complete.
Attached, i tag along the stored procedure, trace files and the database parameter list.

Please help me to get something out from this.


Appreciate your helps

Faizal
Re: Stored Procedure Performance Slows [message #207102 is a reply to message #207046] Mon, 04 December 2006 02:54 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. Run TKPROF on your trace file.
2. It looks like all these RTRIM functions prevent index usage, so your statements perform a lot of full table scans.
3. Try using
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || <table_name>;
instead of DELETE.

HTH.
Re: Stored Procedure Performance Slows [message #207255 is a reply to message #207102] Mon, 04 December 2006 20:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Had you actually bothered to run your own trace file through TK*Prof, you would have found that most of the time is spent on the following SQL (notwithstanding Michaels good advice above)

INSERT INTO pfods.tmp_cn_report
           (oucuno
            , oucono
            , ouitno
            , ouitds
            , qty05
            , amt05
            , noofco05
            , qty06
            , amt06
            , inqty06
            , inamt06
            , noofco06
            , ouitcl)
SELECT DISTINCT (b.oucuno) oucuno
                , 1
                , b.ouitno ouitno
                , Rtrim(a.mditds) mditds
                , c.qty05
                , c.amt05
                , c.cnt05
                , d.qty06
                , d.amt06
                , e.qty06i
                , e.amt06i
                , e.cnt06
                , f.mmitcl
FROM   pfods.mitlad a
       , pfods.osastd_122 b
       , (SELECT   uccuno
                 , ucitno
                 , Nvl(SUM(ucivqt),0) qty05
                 , Nvl(SUM(ucsaam),0) amt05
                 , COUNT(DISTINCT (ucorno)) cnt05
        FROM     pfods.osbstd2005_122
        GROUP BY uccuno
                 , ucitno) c
       , (SELECT   uccuno
                 , ucitno
                 , Nvl(SUM(ucivqt),0) qty06
                 , Nvl(SUM(ucsaam),0) amt06
        FROM     pfods.osbstd_122
        WHERE    To_char(ucivdt,'YYYY') = '2006'
        GROUP BY uccuno
                 , ucitno) d
       , (SELECT   oucuno
                 , ouitno
                 , Nvl(SUM(ouorqt),0) qty06i
                 , Nvl(SUM(ousaam),0) amt06i
                 , COUNT(DISTINCT (ouorno)) cnt06
        FROM     pfods.osastd2006_122
        GROUP BY oucuno
                 , ouitno) e
       , pfods.mitmas f
WHERE  Rtrim(b.oucuno) = Rtrim(c.uccuno (+) )
AND Rtrim(b.ouitno) = Rtrim(c.ucitno (+) )
AND Rtrim(b.oucuno) = Rtrim(d.uccuno (+) )
AND Rtrim(b.ouitno) = Rtrim(d.ucitno (+) )
AND Rtrim(b.oucuno) = Rtrim(e.oucuno (+) )
AND Rtrim(b.ouitno) = Rtrim(e.ouitno (+) )
AND b.oucono = a.mdcono (+) 
AND Rtrim(b.ouitno) = Rtrim(a.mditno (+) )
AND a.mdcono = f.mmcono
AND Rtrim(a.mditno) = Rtrim(f.mmitno)
AND To_char(b.ouosdt,'YYYY') IN ('2005'
                                 , '2006')




call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      2   1880.89    1921.37     133316   45502363       6736       15913
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   1880.93    1921.41     133316   45502363       6736       15913


Run your trace file through TK*Prof on your own database, and make sure it generates Explain Plans for the SQLs. This will show the number of rows processed on each tep of the plan. Post that plan here if you want more help.

Ross Leishman
icon14.gif  Re: Stored Procedure Performance Slows [message #207256 is a reply to message #207102] Mon, 04 December 2006 20:15 Go to previous messageGo to next message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi HTH,
tried with removing the rtrim, and it works!.
Appreciate your assistance.


Regards, Faizal
icon14.gif  Re: Stored Procedure Performance Slows [message #207257 is a reply to message #207255] Mon, 04 December 2006 20:51 Go to previous message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi Ross,
thanks for your advice. I will to TKprof for this as well.

Regards, Faizal
Previous Topic: Analyze tables using OEM
Next Topic: Access to the database is very slow.
Goto Forum:
  


Current Time: Fri Dec 09 08:15:37 CST 2016

Total time taken to generate the page: 0.08344 seconds