Home » SQL & PL/SQL » SQL & PL/SQL » Tunning of pl/sql package (merged 6)
Tunning of pl/sql package (merged 6) [message #413063] Tue, 14 July 2009 04:35 Go to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi friends,

I have a requirement to Tune the pl/sql package which is taking huge time alomost 8 Hrs in Production.

I have gone through the Tuning guide available on the site,i am not able find the solution.I am Confused

I would like to have your help on Tuning the Package.

I am Uploading the trace File for your reference.

Thanks In advance!

Thanks & Regards
Ramya Nomula
Re: Tunning of pl/sql package (merged 4) [message #413070 is a reply to message #413063] Tue, 14 July 2009 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
A good start would be to use the correct version of tkprof for your database version.
I can tell you're using the wrong one because all the CPU times are multiples of 100.
Re: Tunning of pl/sql package (merged 4) [message #413073 is a reply to message #413070] Tue, 14 July 2009 05:09 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi cookiemonster,

we are using Oracle 9i - DataBase Version.

I am bit confused,can you brief the point mentioned.

Thanks In Advance!

Ramya Nomula

Re: Tunning of pl/sql package (merged 6) [message #413075 is a reply to message #413063] Tue, 14 July 2009 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well it would appear you're not using the 9i version of tkprof. Do you have multiple oracle homes?
Re: Tunning of pl/sql package (merged 6) [message #413116 is a reply to message #413075] Tue, 14 July 2009 07:38 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi Cookiemonster,

DataBase Version -

"Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production"


Tkprof Version -

"TKPROF: Release 9.2.0.8.0 - Production on Tue Jul 14 12:09:15 2009

Copyright (c) 1982,2002, Oracle Corporation. All rights reserved."

And, currently there is only one oracle home shared between COST (Test Instance) and PATCH (Development Instance) Instances.

Thanks In Advance!

Thanks & Regards
Ramya Nomula
Re: Tunning of pl/sql package (merged 6) [message #413122 is a reply to message #413063] Tue, 14 July 2009 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok - looks like the right version of tkprof.
Is this a database that you've upgraded from a previous oracle version (8i maybe)?
You're tkprof output is definitely screwed up.
Re: Tunning of pl/sql package (merged 6) [message #413129 is a reply to message #413122] Tue, 14 July 2009 09:15 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi Cookiemonster,

How should i proceed further on this.

Kindly provide me tips to proceed further.

Looking forward for your reply on the same.

Thanks & Regards
Ramya Nomula
Re: Tunning of pl/sql package (merged 6) [message #413141 is a reply to message #413063] Tue, 14 July 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
one NASTY SQL!



SELECT ROUND (CONVERSION_RATE, 5) 

FROM

 GL_DAILY_RATES WHERE FROM_CURRENCY = :B1 AND TO_CURRENCY = 'USD' AND 

  CONVERSION_TYPE = 'Corporate' AND TRUNC (CONVERSION_DATE) = TRUNC (SYSDATE)





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2    100.00      29.99          0          0          0           0

Execute  59104  67800.00   70184.35          0          0          0           0

Fetch    591049258600.00 9145254.73        654    1642187          0       59104

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total   1182109326500.00 9215469.07        654    1642187          0       59104


Something majorly bad, wrong, & otherwise messed up with SQL execution above.

Find & fix this performance.
Re: Tunning of pl/sql package (merged 6) [message #413288 is a reply to message #413063] Wed, 15 July 2009 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is that with the TkProf figures being messed up, it's not possible to see what's really going on with your code.

If you can't get the Tkprof problem fixed, try running statspack while you run the procedure.
Re: Tunning of pl/sql package (merged 6) [message #413411 is a reply to message #413288] Wed, 15 July 2009 10:36 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

JRowbottom,

Thanks for the reply!

Can you guide me how to run the statspack while running the Package.

Thanks & Regards
Ramya Nomula
Re: Tunning of pl/sql package (merged 6) [message #413412 is a reply to message #413411] Wed, 15 July 2009 10:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Step 1: Instal Statspack

Step 2: Take a snapshot

Step 3: Run your application

Step 4: Take a snapshot

Step 5: Run the statspack report
Re: Tunning of pl/sql package (merged 6) [message #415018 is a reply to message #413412] Fri, 24 July 2009 07:29 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi All,

The same package is running less than 3 Hrs in 11.5.8 instance,but taking 14.5 Hrs in 11.5.10.2.

Kindly Advice!

Thanks & Regards
Ramya Nomula
Re: Tunning of pl/sql package (merged 6) [message #415025 is a reply to message #415018] Fri, 24 July 2009 07:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Run it on the 11.5.8 instance then.
Re: Tunning of pl/sql package (merged 6) [message #415602 is a reply to message #413063] Tue, 28 July 2009 06:04 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
For a starter:

1.

You are using TKPROF 8.1.7 (look at your trace file headings):

TKPROF: Release 8.1.7.4.0 - Production on Fri Jun 26 07:04:30 2009

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Trace file: cost_ora_8499_REDDYS_CR54518907.trc
Sort options: default


So all time values (CPU and Elapsed) must be divided by 10000.

2.

SELECT ROUND (CONVERSION_RATE, 5) 
FROM
 GL_DAILY_RATES WHERE FROM_CURRENCY = :B1 AND TO_CURRENCY = 'USD' AND 
  CONVERSION_TYPE = 'Corporate' AND TRUNC (CONVERSION_DATE) = TRUNC (SYSDATE)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2    100.00      29.99          0          0          0           0
Execute  59104  67800.00   70184.35          0          0          0           0
Fetch    591049258600.00 9145254.73        654    1642187          0       59104
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1182109326500.00 9215469.07        654    1642187          0       59104


Try:

CREATE INDEX ... ON
 GL_DAILY_RATES ( FROM_CURRENCY, TO_CURRENCY,
  CONVERSION_TYPE, TRUNC (CONVERSION_DATE) )
  NOLOGGING COMPUTE STATISTICS...


3.

UPDATE CUSTOM.PWR_WB_INV_TURNS SET BUYER = :B2 
WHERE
 PROCESS_TYPE = 'BACKGROUND' AND ATTRIBUTE_CONTEXT = 'VALUE OF CODED SAFETY 
  STOCK' AND BUYER = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      21.47          0          0          0           0
Execute    22115726600.0015915847.79    8518082    8824496     877419      772856
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      22215726600.0015915869.26    8518082    8824496     877419      772856


1591 sec - execution time and no explain for a statement.

It's difficult to advice without kknowing your indexes and data.

4.

Use sort=prsela,fchela,exeela as TKPROF parameter to sort the statements.

HTH.
Previous Topic: need Query Help
Next Topic: Grouping via analytical function
Goto Forum:
  


Current Time: Sun Dec 11 06:10:16 CST 2016

Total time taken to generate the page: 0.07214 seconds