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.
Here's a long shot .. are you creating datafiles or autogrowing in 8.0.x? Those took FOREVER with UTF8 character set.
If you're spitting the results out to a log file, you might want to watch the file on your screen with a cool little utility for windows called tail. http://rhazes.com It works like "tail -f" in unix. As lines are added to the output file, they're also displayed on your console.
create table for job_log and
job_seq_no, - sequence
job_ts - sysdate
job_name - procedure name
Tot_rec_process - No of records processed
update store procedure
in starting add job start time and procedure name in job_log
and when job end update job_log table with end time and no of records process.
This will give you fair idea about how much time it takes for how many records and then you can improve performance.
Also you can create error log table and capture all errors in error_log tables and continue your jobs this is also done within store proc. Also when error comes it should email notification