Home » RDBMS Server » Performance Tuning » Stored Procedure Performance Slows
|
Re: Stored Procedure Performance Slows [message #207102 is a reply to message #207046] |
Mon, 04 December 2006 02:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: Stored Procedure Performance Slows [message #207255 is a reply to message #207102] |
Mon, 04 December 2006 20:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 07:56:11 CDT 2024
|