Statspack is an Oracle database tool that is both powerful and underused, it's the first reporting resource you have (it's free, comes in the box) to see what is happening in your DB at a glance.
1 Oracle sql*plus session
1 Tablespace with at least 200Mb (9i) or 400Mb (10g) of free space*
1 user with DBA power
*Recommend you create a brand new tablespace
You need to install Statspack previous to any use, then you have to run just one script... yes, it's that easy.
Login with your powerful user, then at the prompt type:
...you've started the Statspack setup, that will create the owner for the Statspack objects, which username is fixed to PERFSTAT. In order to acomplish that, asks for the tablespace where all objects will be stored, the temporary tablespace Statspack will use for sorting data and the user password.
You'll end with a message like this, and a result listing at the place you executed sqlplus.
It's recommended to establish a baseline for your reporting, then you must connect as PERFSTAT and take your first snapshot:
SQL> exec statspack.snap;
It will run with detail level 5, which is the default ... and enough for 90% of the cases. Taking samples 2 to 5 minutes apart is the period recommended, you may take samples with longer intervals but some results will get their formatting exceded and show # marks.
You may be interested on scheduling execution of statspack.snap, please read 2 minute guide for Statspack sampling
Further ... and detailed reading at the Oracle(R) Database Performance Tuning Guide.
Subscribe to Oracle Database Disected by Email