Statspack

Perfstat Cron Jobs

Here is the update on changing the perfstat statspack job that I spoke of earlier. The question was how can I get statspacks to run every 15 minutes during OLTP and every 2 hours during batch. My thought is a Cron Job that will run the SQL for you. Just make a connection to the DB using SQLPLUS and call the SQL file with the appropriate sql to change the job. Since the user asking the question had the SQL done here is a sample of how I would write the script to run in Cron.

#!/bin/sh
sqlplus -S User/Password@$instance @/full/path/to/file/filename.sql

That is it. Pretty straightforward really. Hope that helps. If you have any questions about this or anything else just head over to http://sysadminsmith.com and click the ‘Submit a Question’ link and I will get back to you.

Changing Perfstat Job

I got a question about how I would change the Prefstat Statspack job in Oracle to run at 15 minute increments during the day when OLTP transactions are occurring and then change it to run only onve every hour during off hours while batch is running. Since the user already had the PL\SQL to change the job my answer is to build that SQL into a shell script that is scheduled in CRON. Have two jobs. One that changes the time to once an hour and one that changes back to every fifteen minutes. In the script just make a connection to the DB, run the SQL, Log the changes and perhaps build in some logic to alert you if the changes were not made. Sounds pretty basic and I will try to work out the shell scripts and have them posted tomorrow.

If you have a question please head over to http://sysadminsmith.com and click the ’submit a question’ link to the right. Thanks

Oracle Statspack Help

Recently I received an email from Ram. It said the following:

I am ram,I have a question regarding development DBA.I need to Optimize SQL and PL/SQL Queries.I need to analyze statspack and some knowledge transfer regarding Data Mining.Can you please help me regarding these concepts.

My company is using 9i Release 2 on an unix flavour.I can give you more information regarding this if i have any of of ur mail ID’s or chatting ID’s.

Thanks for ur time.i will be waiting for ur response.

Thanks & Regards,

Rakesh

To this I replied that I may not be able to help with every aspect of this question. I will try to do what I can. I pointed Ram to the http://itknowledgeexchange.techtarget.com/itanswers/ site in hopes that he would be able to ask the question there and get help with some of the things that I am not going to be much help with. Since I am not a developer, I am not very proficient in PL\SQL however I am learning more and more all the time. I also am not going to be able to help much with DataMining. I think, however, that I will be able to help with the statspack info. From the question I can only assume that Ram has the statspack job set up and running and collecting data. I can tell you that in my production system I have this job running every fifteen minutes and I am keeping 2 weeks worth of snaps. Here is a link to the Oracle site that will help you run the reports from the information gathered.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm

If this does not help then please let me know what you are having trouble with and I will be glad to help you out with that. Just goto http://sysadminsmith.com/ and click the Submit a Question link.

Thanks for the question Ram.