Stored Procedure Tuning. [message #37618] |
Sun, 17 February 2002 18:43 |
Shankar Balakrishnan
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
Hi,
Need help ASAP regarding tuning of Stored Procedures on Oracle 8.1.7. The table has about 300,000 rows and three cursors are opened containing this table columns.
The box has about 4G RAM and 2 CPUs (model not sure!) and this procedure gets created properly (i.e. compiles) and also executes correctly for small amount of data.
However, for large volumes of data this just hangs/waits!
The table in picture is just One table with 13 columns and not indexed. From a total of 8 procedures that are running slow, INDEXing helped improve performance of one of them ... others are still slow.
Please reply ASAP with any tips or pointers to where I can get some help.
Thanks & Regards,
Shankar.
|
|
|
|
Re: Stored Procedure Tuning. [message #38865 is a reply to message #37618] |
Wed, 22 May 2002 22:24 |
Pradeep
Messages: 55 Registered: July 2000
|
Member |
|
|
try to figure our the cost associated with the cursors and other sql statements by using explain plan utility and also find out the cpu usage time by the stored procedure by using the tkprof and Sql Trace facility. This might give you some information.
Another thing is, as because u r dealing with huge data , database design will affect a lot.Particularly the database block size associated with each I/O operation will count also.
|
|
|