OraFAQ Forum: Performance Tuning » Stored Procedure Performance Slows

Home » RDBMS Server » Performance Tuning » Stored Procedure Performance Slows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stored Procedure Performance Slows [message #207046]

Mon, 04 December 2006 00:33

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

Attachment: GENERATE_CN1_REPORT.zip
(Size: 79.94KB, Downloaded 1199 times)

Report message to a moderator

Re: Stored Procedure Performance Slows [message #207102 is a reply to message #207046]

Mon, 04 December 2006 02:54

michael_bialik
Messages: 621
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.

Report message to a moderator

Re: Stored Procedure Performance Slows [message #207255 is a reply to message #207102]

Mon, 04 December 2006 20:14

rleishman
Messages: 3728
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

Report message to a moderator