In our production Database we have a massive job ( Which cosist of couple of stored procedures with lots of cursors and DML apply to millions rows range tables ) which must be run every month.
Tomorrow is the day we run it !! I was asking to monitor the process as a DBA and gives some recommendations to improve the process time and report bottlenecks. The problem is I donít have very good idea what I have to do!!
Can you explain for me in these type of task assignment what would you usually monitor and thinking of ?
Here is the list of some of the things I think itís important to look at during this process :
- Review stored procedures to see how frequent commit happens, explain plan the cursors and evaluate indexe utilizations and FTS
- look at v$sql to see whatís SQL Statements run most of time during process and focus on them
- Look at all hit ratios ( may be more on sort and db buffer ratios )
- look at machine ( We have Oracle 8.0.5 running on NT 4.0 SP5 ) and see how hard drive and CPU doing
Please verify these are important things or not and what else I am missing and bottom line whatís actions return most gain.
An ounce of prevention is worth a pound of cure