Home » RDBMS Server » Performance Tuning » want to reduce the COST (Oracle 11i , DB - 9.2.0)
want to reduce the COST [message #276551] Thu, 25 October 2007 08:30 Go to next message
AshaAtrey
Messages: 4
Registered: October 2007
Location: pune
Junior Member
Hi,

I am working in a B2B setup, and made programs to cancel an IR/PR line if an ISO/PO line get cancelled and vise versa.

the program is taking almost 10-12 mins to run from the front end.

Attaching both the programs, The cursor used in the programs seems too costly,
I tried to reduce the cost but could not reduce further.

Plz if any of senior member can help will be so helpful for me.
  • Attachment: Programs.zip
    (Size: 6.54KB, Downloaded 157 times)
Re: want to reduce the COST [message #276570 is a reply to message #276551] Thu, 25 October 2007 09:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
using DBMS_PROFILER check where your package take too much time to execute.
Re: want to reduce the COST [message #276623 is a reply to message #276551] Thu, 25 October 2007 11:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run it with SQL_TRACE = TRUE and post TKPROF report.

Tables/index defintions and some stats may be helpfull as well.
Re: want to reduce the COST [message #276762 is a reply to message #276623] Fri, 26 October 2007 05:19 Go to previous messageGo to next message
AshaAtrey
Messages: 4
Registered: October 2007
Location: pune
Junior Member
Attaching TKPROF file for one of the program,

This run is for only 2 records, and if you see the cpu and elapsed time is too high.


Regards
Asha
  • Attachment: tk.out.out
    (Size: 55.39KB, Downloaded 227 times)
Re: want to reduce the COST [message #276781 is a reply to message #276762] Fri, 26 October 2007 05:53 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Also you are fetching the rows one by one.
Try to use BULK COLLECT.

By
Vamsi
Re: want to reduce the COST [message #276796 is a reply to message #276781] Fri, 26 October 2007 06:05 Go to previous messageGo to next message
AshaAtrey
Messages: 4
Registered: October 2007
Location: pune
Junior Member
Hi Vasmi,

Could you please guide me how & where i can use BULK COLLECT
I never used this key word.

Thanks & Regards
Asha
Re: want to reduce the COST [message #276801 is a reply to message #276796] Fri, 26 October 2007 06:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
BULK COLLECT, using which you can stop pinging the database very frequently.

By
Vamsi
Re: want to reduce the COST [message #276956 is a reply to message #276551] Sat, 27 October 2007 05:07 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - you have a severe performance problems within your application:

1. Very high parsing times:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00      42.43          0          0          0           0


Elapse time for parse is not acceptable.
It may mean that your application shared pool is not sized correctly and the application is waiting a lot of time for each parse.

2. In most cases there is a huge difference between CPU and elapse times, meaning that application is waiting for some resource.
Instead of
Alter session set sql_trace = true;

try
Alter session set events '10046 trace name context forever, level 8';

Look at the raw trace file and try to find what is the resource.

Michael.

Previous Topic: find the query/plsql block which is causing performance hurt
Next Topic: Speed Diffrence
Goto Forum:
  


Current Time: Wed Dec 07 14:58:48 CST 2016

Total time taken to generate the page: 0.07228 seconds