Use Statspack in Oracle to diagnose database performance instances

With Statspack, we can easily identify the bottleneck of the Oracle database, record the database performance status, and allow remote technical support staff to quickly understand the running status of your database. Therefore, it is important for DBA to understand and use Statspack.

Sort Analysis Results

You can create charts using various tools to make the data we collect more intuitive and persuasive.
The following is an example of an analysis report I provide to a customer.

1. Physical read/write IO operations:

Observe physical I/O access, and you can see the peak value and busy degree of daily database access.

Script: This script generates statistics by time (Note: The following example uses 8i as the basis, and statistic referenced in the SQL script # It may have different meanings in different versions. For 9i and other versions, you should modify the corresponding parameter values)

SQL code

select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),
(newreads.value-oldreads.value)reads,
(newwrites.value-oldwrites.value)writes
from
perfstat.stats$sysstatoldreads,
perfstat.stats$sysstatnewreads,
perfstat.stats$sysstatoldwrites,
perfstat.stats$sysstatnewwrites,
perfstat.stats$snapshotsn
where
newreads.snap_id=sn.snap_id
and
newwrites.snap_id=sn.snap_id
and
oldreads.snap_id=sn.snap_id-1
and
oldwrites.snap_id=sn.snap_id-1
and
oldreads.statistic#=40
and
newreads.statistic#=40
and
oldwrites.statistic#=41
and
newwrites.statistic#=41
and
(newreads.value-oldreads.value)>0
and
(newwrites.value-oldwrites.value)>0
select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI:SS'),12),
(newreads.value-oldreads.value)reads,
(newwrites.value-oldwrites.value)writes
from
perfstat.stats$sysstatoldreads,
perfstat.stats$sysstatnewreads,
perfstat.stats$sysstatoldwrites,
perfstat.stats$sysstatnewwrites,
perfstat.stats$snapshotsn
where
newreads.snap_id=sn.snap_id
and
newwrites.snap_id=sn.snap_id
and
oldreads.snap_id=sn.snap_id-1
and
oldwrites.snap_id=sn.snap_id-1
and
oldreads.statistic#=40
and
newreads.statistic#=40
and
oldwrites.statistic#=41
and
newwrites.statistic#=41
and
(newreads.value-oldreads.value)>0
and
(newwrites.value-oldwrites.value)>0
/

Chart:

Analysis:

From the trend chart, we can see that the daily read operations of the database are relatively stable, and the data volume is about 4000. Period from two o'clock P.M.
Busy. The peak value reaches about 150000.

Database write operations are also stable, with a data volume of around 80000. From half past one AM to half past eight AM, there are very few database accesses.
This is a write-based database. We need to pay more attention to the competition in writing.

2. Buffer hit rate

SQL code

select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),
round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/
((a.value-e.value)+(b.value-f.value)))
"BUFFERHITRATIO"
from
perfstat.stats$sysstata,
perfstat.stats$sysstatb,
perfstat.stats$sysstatc,
perfstat.stats$sysstatd,
perfstat.stats$sysstate,
perfstat.stats$sysstatf,
perfstat.stats$sysstatg,
perfstat.stats$snapshotsn
where
a.snap_id=sn.snap_id
and
b.snap_id=sn.snap_id
and
c.snap_id=sn.snap_id
and
d.snap_id=sn.snap_id
and
e.snap_id=sn.snap_id-1
and
f.snap_id=sn.snap_id-1
and
g.snap_id=sn.snap_id-1
and
a.statistic#=39
and
e.statistic#=39
and
b.statistic#=38
and
f.statistic#=38
and
c.statistic#=40
and
g.statistic#=40
and
d.statistic#=41
select
substr(to_char(snap_time,'yyyy-mm-ddHH24:MI'),12),
round(100*(((a.value-e.value)+(b.value-f.value))-(c.value-g.value))/
((a.value-e.value)+(b.value-f.value)))
"BUFFERHITRATIO"
from
perfstat.stats$sysstata,
perfstat.stats$sysstatb,
perfstat.stats$sysstatc,
perfstat.stats$sysstatd,
perfstat.stats$sysstate,
perfstat.stats$sysstatf,
perfstat.stats$sysstatg,
perfstat.stats$snapshotsn
where
a.snap_id=sn.snap_id
and
b.snap_id=sn.snap_id
and
c.snap_id=sn.snap_id
and
d.snap_id=sn.snap_id
and
e.snap_id=sn.snap_id-1
and
f.snap_id=sn.snap_id-1
and
g.snap_id=sn.snap_id-1
and
a.statistic#=39
and
e.statistic#=39
and
b.statistic#=38
and
f.statistic#=38
and
c.statistic#=40
and
g.statistic#=40
and
d.statistic#=41

Chart:

Analysis:

Bufferbufferhitratio) the hit rate is an important indicator to evaluate the performance of Oracle databases. It indicates finding the ratio of data in the memory.
Rate. Generally, if this value is less than 90%, it may indicate that the database has a large number of expensive IO operations and the database needs to be adjusted.
The buffer hit rate of our database is almost close to 100%, and the lowest value is around 95%. This ratio is relatively optimized.

Install statspack

SQL>connect/assysdba SQL>@?/rdbms/admin/spcreate

Uninstall

SQL>connect/assysdba SQL>@?/rdbms/admin/spdrop /* Timed_statistics = true before collecting information; Altersystemsettimed_statistics = true; */

Collect information

SQL>connectperfstat/perfstat SQL>executestatspack.snap;

Automatic Collection

SQL>connectperfstat/perfstat SQL>@?/rdbms/admin/spauto

Delete automatically collected jobs.

SQL> select * fromuser_jobs; SQL> execdbms_job.remove (JOB_ID) /* Executestatspack. snap (I _snap_level => 10, I _modify_parameter => 'true '); Levels = 5 Additionaldata: SQLStatements Levels = 6 Thislevelincludesallstatisticsgatheredinthelowerlevel (s ). Levels = 7 segmentslevelstatistics Levels = 10 Additionalstatistics: Childlatches I _modify_parameter => 'true'/'false' determines whether to save the level value. */

Generate Report

SQL>conectperfstat/perfstat SQL>@?/rdbms/admin/spreport

1. Explain several Oracle Performance Tuning Methods
2. Typical Oracle Application Development Issues
3. Use, monitoring, and maintenance of Oracle Data Space