Basic usage of Performance Statistics in oralcle statpack

In Oracle, using statpack for performance statistics is a good choice, but it is not a graphical interface. It is more troublesome and has many usage options. Generally, you can master the basic usage.
You can use it to query it.

 

1 Installation
First, create a separate tablespace as SYS. The requirement is relatively large, preferably 1, 2g
Create tablespace perfstat datafile 'f: "oracle" oradata "jssweb" perfstat. dbf'size 2000 m;

Run spcreate. SQL under the ORACLE_HOME \ RDBMS \ ADMIN directory.
Enter the password perfstat

Select the created tablespace, such as perfstat, and the installation is successful.

Verify the installation as follows:

Execute statspack. Snap

 

2 Basic use
You must use perfstat/perfstat to log on, and then
Run @ spreport. SQL,

You will be asked to enter the snapshotid, which is the number of days, and the number of the start and end days,

Define the report file (in the last step) to view the generated report.
Note that due to the sampling time, you can modify it in spauto. SQL and find:
Dbms_job.submit (: Job, 'statspack. Snap; ', trunc (sysdate + 1/24, 'hh24'), 'trunc (sysdate + 1/24, ''hh24 '')');

Modify.

If multiple instances exist, execute sprepins. SQL to generate a report.

View current job
Select job, schema_user, next_date, interval, what from user_jobs

View the snapshot list
Select snap_id, snap_time, startup_time from stats $ snapshot;

To view the execution plan of an SQL statement, use spreport to find the SQL statement and write down its hash value.
CPU elapsd old
Buffer gets executions gets per exec % Total time (s) Hash Value
--------------------------------------------------------------------------
250,175 259 965.9 63.5 36.93 386.17 2689373535

Run @ spresql. SQL and enter the hash value to view the corresponding execution plan.

 

Finally, it is a good tutorial for itpub statpack.