Statspack installation and report Analysis

 

1. View tablespaces

Select tablespace_name, file_name from dba_data_files;

2. Create a statspack tablespace

Create tablespace perfstat datafile 'd: \ oracle \ oradata \ perfstat. dbf' size 500 m

The tablespace should be as big as possible.

3. Install statspack.

Run the following command in the oracle_home \ rdmbs \ admin \ directory:

@ Spcreate. SQL

If creation fails, run @ spdrop. SQL in the same directory.

4. test:

SQL> execute statspack. snap

PL/SQL procedure successfully completed.

SQL> execute statspack. snap

PL/SQL procedure successfully completed.

SQL> @ spreport. SQL

Use Statspack:

Sqlplus perfstat/perfstat

Exec statspack. snap; -- collects statistics and generates a snapshot number for each operation.

-- To obtain the snapshot number, you must have more than two snapshots to generate a report.

Select SNAP_ID, SNAP_TIME from STATS $ SNAPSHOT;

@ Spreport. SQL -- enter the start snapshot number and end snapshot number to be viewed

Other related scripts s:

Spauto. SQL-use dbms_job to submit a job and collect statistics on STATPACK information automatically.

Sppurge. SQL-to clear statistics within a period, you must provide the start snapshot and end snapshot number.

Sptrunc. SQL-clear all statistics

 

Statspack report analysis:

 

1) Adjustment order

1. Tune the design. -- Application designers

2. Tune the application. -- Application developers

3. Tune memory.

4. Tune I/O.

5. Tune contention.

6. Tune the operating system.

Statspack analysis report details:

10 items that must be viewed in the statspack output result

1. Load profile)

2. Instance efficiency CTR (Instance efficiency hit ratios)

3. Top 5 wait events)

4. Wait for the event (Wait events)

5. latch wait

6. Top SQL)

7. Instance activity)

8. File I/O (File I/O)

9. Memory allocation (Memory allocation)

10. Buffer wait (Buffer waits

 

1. Report header information

Database instance information, including database name, ID, version number, host, and other information.

STATSPACK report

DB Name DB Id Instance Inst Num Release Cluster Host

BLISSDB 4196236801 blissdb 1 9.2.0.4.0 NO BLISS

Snap Id Snap Time Sessions Curs/Sess Comment

Begin Snap: 4-6-05 17:43:32 10 3.3

End Snap: 5-6 months-05 18:01:32 12 6.1

Elapsed: 18.00 (mins)

Cache Sizes (end)

Buffer Cache: 24 M Std Block Size: 8 K

Shared Pool Size: 48 M Log Buffer: 512 K

2. Load gap

This section provides statistics for every second and every thing. It is an important part of monitoring system throughput and load changes.

Load Profile

~~~~~~~~~~~~

Per Second Per Transaction

Redo size: 431,200.16 18,627,847.04 z

Logical read: 4,150.76 179,312.72

Block changes: 2,252.52 97,309.00

Physical read: 23.93 1,033.56

Physical writes: 68.08 2,941.04

User cballs: 0.96 41.36

Parses: 1.12 48.44

Hard parses: 0.04 1.92

Sorts: 0.77 33.28

Logons: 0.00 0.20

Executes: 2.36 102.12

Transactions: 0.02

Redo size: the size of Redo logs generated per second (in bytes), which indicates the data change frequency and whether the database task is heavy or not. In this example, a redo of around K is generated on average per second, and each transaction product generates a redo of 18 MB.

Logical reads: Logical reads generated per second. The unit is block.

Block changes: the number of block changes per second, and the number of blocks that database transactions change.

Physical reads: Average number of blocks read from the disk by the database per second.

Comparison between Logical reads and Physical reads: about 0.55% of Logical reads lead to Physical I/O. On average, each transaction executes about 0.18 million Logical reads. In this example, some large transactions are executed, so a high number of reads is acceptable.

Physical writes: Average number of blocks written to the disk by the database per second.

User CILS: the number of User calls per second.

Parses and Hard parses: About 1.12 resolutions per second, 4% of which are Hard-resolved. The system analyzes some SQL statements every 25 seconds, which is not bad. For the optimized system, after several days of running, this column should be 0, and all SQL statements should be in the shared pool after a period of time.

Sorts: the number of sorting times generated per second.

Executes: the number of executions per second.

Transactions: the number of Transactions generated per second, reflecting whether database tasks are heavy or not.

% Blocks changed per Read: 54.27 Recursive Call %: 86.94

Rollback per transaction %: 12.00 Rows per Sort: 32.59

% Blocks changed per Read: indicates that 46% of logical reads are used for Read-only but not modifiable Blocks. The system only updates 54% of Blocks.

Rollback per transaction %: Percentage of transaction Rollback. Calculation formula: Round (User rollbacks/(user commits + user rollbacks), 4) * 100%. In this example, every 8.33 transactions cause a rollback. If the rollback rate is too high, it may indicate that the database has undergone too many invalid operations. Excessive rollback may also lead to Undo Block competition.

3. instance hit rate

This part is important to identify potential performance problems in ORACLE in advance.

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %: 100.00

Buffer Hit %: 99.42 In-memory Sort %: 100.00

Library Hit %: 98.11 Soft Parse %: 96.04

Execute to Parse %: 52.57 Latch Hit %: 100.00

Parse CPU to Parse Elapsd %: 11.40% Non-Parse CPU: 99.55

Buffer Nowait %: unwaited Buffer ratio obtained in the Buffer, Buffer Nowait <99% description, there may be hot blocks (find the tch of x $ bh and the cache buffers chains of v $ latch_children ).

Redo NoWait %: the ratio of unwaited Buffer obtained in the Redo Buffer.

Buffer Hit %: the Hit rate of data blocks in the data Buffer, usually above 90%. Otherwise, if the Hit rate is less than 95%, you need to adjust the important parameter. If the Hit rate is less than 90%, db_cache_size may be added, however, a large number of unselected indexes also result in a high value (a large number of db file sequential read ). If the index of a frequently accessed column is deleted, it may cause a significant decrease in buffer hit. If an index is added, but it affects the driver sequence when ORACLE selects the correct table connection, the buffer hit may increase significantly. If the hit rate changes significantly, the SQL mode needs to be changed.

In-memory Sort %: Sorting rate In memory.

Library Hit %: It mainly indicates the SQL Hit rate in the shared area, usually more than 95%. Otherwise, you need to consider increasing the sharing pool, binding variables, and modifying cursor_sharing parameters.

Soft Parse %: similar to the hit rate of SQL in the shared area. If the hit rate is less than <95%, you must consider binding. If the hit rate is lower than 80%, the SQL statement may not be reused.

Execute to Parse %: the number of times a statement is executed and analyzed. In an analysis, the statement is then executed and is no longer in the system where it is executed in the same session. The ratio is 0. The formula is: Execute to Parse = 100 * (1-Parses/Executions ). Therefore, if the system Parses> Executions, this ratio may be less than 0. In this example, about 2.1 times are performed for each analysis. This value <0 usually indicates that there is a problem with the shared pool setting or efficiency, resulting in repeated parsing. The reparse may be serious, or is related to snapshot. If this value is negative or extremely low, it usually indicates that the database performance is faulty.

Latch Hit %: Make sure that the value is greater than 99%. Otherwise, serious performance problems may occur. For example, binding may affect this parameter.

Parse CPU to Parse Elapsd %: formula: Parse CPU to Parse Elapsd % = 100 * (parse time cpu/parse time elapsed ). That is, parse the actual running time/(parse the actual running time + wait for resource time in parsing ). This is 11.4%, which is very low. It takes about 8.77 seconds for every CPU second to parse the wall clock time, which means it takes a lot of time to wait for a resource. If this ratio is 100%, it means that the CPU time is equal to the elapsed time without any waiting.

% Non-Parse CPU: formula: % Non-Parse CPU = round (100 * 1-PARSE_CPU/TOT_CPU), 2 ). Too low indicates that resolution consumes too much time. Compared with PARSE_CPU, if TOT_CPU is very high, this ratio will be close to 100%. This is good, it means that most of the work executed by the computer is to execute the query, rather than to analyze the query.

4. Shared Pool statistics

Shared Pool Statistics Begin End

------------

Memory Usage %: 60.45 62.42

% SQL with executions> 1: 81.38 78.64

% Memory for SQL w/exec> 1: 70.36 68.02

Memory Usage %: Percentage of the Sharing pool in use. This number should be stable for a long time between 75% and ~ 90%. If the percentage is too low, the memory will be wasted. If this percentage is too high, components outside the Shared Pool will become aging. If the SQL statement is executed again, the SQL statement will be hard parsed. In a suitable system, the usage of the Shared Pool is between 75% and slightly lower than 90%.

% SQL with executions> 1: This is the measurement of the number of SQL statements executed more than once in the shared pool. This number must be carefully considered in a system that tends to run cyclically. In this cycle system, a set of different SQL statements are executed in part of the day relative to another part of time. In the shared pool, there will be a group of unexecuted SQL statements during observation, only because the statements to be executed are not running during observation. This number is close to 100% only when the system runs the same SQL statement group consecutively. It is shown that almost 80% of SQL statements in this shared pool run more than once in the observation window within 18 minutes. The remaining 20% of statements may already be there-the system just has no reason to execute it.

% Memory for SQL w/exec> 1: This is a measure of the Memory consumption of frequently used SQL statements compared with infrequently used SQL statements. This number will be very similar to % SQL with executions> 1 in general, unless some query tasks consume irregular memory.

In a stable state, we can see about 75% ~ 85% of the Shared Pool is used. If the time window of the Statspack report is large enough to cover all cycles, the percentage of SQL statements executed more than once should be close to 100%. This is a statistical number affected by the observed duration. It is expected to increase with the length of time between observations.

5. Primary wait events

Common wait events:

Oracle wait events are important bases and instructions for measuring the running status of oracle, including idle wait events and non-idle wait events.

TIMED_STATISTICS: = TRUE, waiting for events to be sorted by the waiting time, = FALSE, waiting for events to be sorted by the waiting amount.

TIMED_STATISTICS = TRUE must be set on the session during statspack operation.

The idle wait event is a task that oracle is waiting for. When diagnosing and optimizing the database, you don't need to pay too much attention to this event. The non-idle wait event is dedicated to oracle activities, it refers to the waiting events that occur during database tasks or application program running. We should pay attention to these waiting events when adjusting the database.

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

---------------------------------------------------------------------------

Db file sequential read 22,154 259 62.14

CPU time: 49 11.67

Log file parallel write 2,439 26 6.30

Db file parallel write 400 22 5.32

SQL * Net message from dblink 4,575 15 3.71

-------------------------------------------------------------

This is an event that slows down the speed than any other event. Common wait events that affect performance:

Db file scattered read: this event is usually related to full table scan. Because full table scan is carried out in the memory, it is generally not possible to be placed in a continuous buffer, so it is distributed in the buffer cache. If the number of indexes is too large, the index is missing or the index usage is limited (optimizer_index_cost_adj can also be adjusted ). This situation may also be normal, because the full table scan may be more efficient than the index scan. When the system has these waits, you need to check whether the full table scan is necessary. If you need to scan the entire table frequently and the table is small, save the table to the keep pool. If a large table often performs full table scanning, it should be an OLAP system rather than an OLTP system.

Db file sequential read: This event indicates a large amount of waiting on a single data block. This value is too high, usually because of the bad connection sequence between tables or the use of non-selective indexes. By associating this wait with other known issues in the statspack report (for example, low-efficiency SQL statements), check to ensure that index scanning is required, make sure that the connection sequence of the Multi-Table connection is adjusted. DB_CACHE_SIZE can determine the frequency of the event.

Db file sequential read: This event indicates a large amount of waiting on a single data block. This value is too high, usually because of the bad connection sequence between tables or the use of non-selective indexes. By associating this wait with other known issues in the statspack report (for example, low-efficiency SQL statements), check to ensure that index scanning is required, make sure that the connection sequence of the Multi-Table connection is adjusted. DB_CACHE_SIZE can determine the frequency of the event.

Buffer busy wait: This wait occurs when the buffer is in a non-shared mode or is being read into the buffer. The value should not be greater than 1%. check whether it is caused by hotspot blocks (if you can use reverse indexes or smaller blocks ).

Latch free: It is often related to applications that do not have good application binding. The latch is the underlying queue mechanism (the more accurate name should be the mutex mechanism), which is used to protect the shared memory structure of the system global zone (SGA). the latch is used to prevent parallel access to the memory structure. If the locks are unavailable, a loss of the locks will be recorded. Most of the latches have failed to bind variables (database cache latches), generate duplicate locks (re-execute the allocation locks), and cache contention issues (Cache LRU chains) and cache hot data blocks (Cache chains. When the latch loss rate is higher than 0.5%, you need to adjust this problem.

Log buffer space: log buffer writes faster than LGWR writes REDOFILE. You can increase the log file size, increase the log buffer size, or use a faster disk to write data.

Logfile switch: Usually because the archiving speed is not fast enough, you need to increase the redo log.

Log file sync: when a user submits or rolls back data, LGWR fills session redo operations from the log buffer into the log file, and the user's process must wait until the filling is completed. Each submission occurs. If this wait event affects the database performance, you need to modify the submission frequency of the application. To reduce this wait event, you must submit more records at a time, alternatively, you can access the redo log file on different physical disks.

Wait time: the Wait time includes the write and send operations of the log buffer.

6. All wait events of the database user program

Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> S-second

-> Cs-centisecond-100th of a second

-> MS-millisecond-1000th of a second

-> Us-microsecond-000000th of a second

-> Ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (MS)/txn

--------------------------------------------------------------------------

Db file sequential read 22,154 0 259 12 886.2

Log file parallel write 2,439 2,012 26 11 97.6

Db file parallel write 400 0 22 55 16.0

SQL * Net message from dblink 4,575 0 15 3 183.0

SQL * Net more data from dblin 64,490 0 13 0 2,579.6

Control file parallel write 416 0 5 13 16.6

Db file scattered read 456 0 5 11 18.2

Write complete waits 9 0 5 568 0.4

Control file sequential read 370 0 5 13 14.8

Log buffer space 126 0 4 34 5.0

Free buffer waits 11 1 3 313 0.4

Log file switch completion 13 0 2 188 0.5

Log file sync 90 0 1 8 3.6

Log file sequential read 10 0 0 16 0.4

Latch free 17 6 0 8 0.7

Direct path read 56 0 0 1 2.2

Direct path write 56 0 0 1 2.2

SQL * Net more data to client 173 0 0 0 6.9

SQL * Net message to dblink 4,575 0 0 0 183.0

LGWR wait for redo copy 8 0 0 1 0.3

Log file single write 10 0 0 1 0.4

Db file single write 5 0 0 0.2

SQL * Net break/reset to clien 5 0 0 0.2

Async disk IO 15 0 0 0 0.6

SQL * Net message from client 789 0 3,290 4170 31.6

Virtual circuit status 36 36 1,082 30069 1.4

Wakeup time manager 34 34 1,034 30403 1.4

SQL * Net message to client 791 0 0 0 31.6

SQL * Net more data from clien 30 0 0 1.2

-------------------------------------------------------------

7. Wait events for the background process of the database

Background Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> Ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (MS)/txn

--------------------------------------------------------------------------

Log file parallel write 2,439 2,012 26 11 97.6

Db file parallel write 400 0 22 55 16.0

Control file parallel write 406 0 5 13 16.2

Control file sequential read 258 0 4 16 10.3

Db file sequential read 19 0 1 1 51 0.8

Log buffer space 24 0 0 9 1.0

Log file sequential read 10 0 0 16 0.4

Latch free 14 6 0 9 0.6

Db file scattered read 6 0 0 14 0.2

Direct path read 56 0 0 1 2.2

Direct path write 56 0 0 1 2.2

LGWR wait for redo copy 8 0 0 1 0.3

Log file single write 10 0 0 1 0.4

Rdbms ipc message 7,339 3,337 3,172 432 293.6

Pmon timer 373 373 1,083 2903 14.9

Smon timer 3 3 924 ##### 0.1

-------------------------------------------------------------

8. TOP SQL

By adjusting the first 25 buffer read operations and the first 25 disk read operations, the system performance can be increased by 5% to 5000%.

SQL ordered by Gets for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> Endbuffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL nodes des the resources used

All SQL statements called within the PL/SQL code. As individual SQL

Statements are also reported, it is possible and valid for the summed

Total % to exceed 100.

CPU Elapsd

Buffer Gets Executions Gets per Exec % Total Time (s) Hash Value

--------------------------------------------------------------------------

1,230,745 1 1,230,745.0 27.5 16.39 60.69

Module: PL/SQL Developer

Insert into city_day_cal select * from rptuser. city_day_cal @ db15

1

143,702 1 143,702.0 3.2 1.75 18.66

Module: PL/SQL Developer

Insert into city_day_cal select * from rptuser. city_day_cal @ db15

1 where curtime between to_date ('20140901', 'yyymmm') and to_date ('

200502 ', 'yyyymmm')-1

In this part of the report, the SQL statement is sorted by Buffer Gets, that is, the number of logical I/O executed by the SQL statement. The comment at the top shows that Buffer Gets is obtained by cache of a PL/SQL Unit, including the Buffer Gets of all SQL statements executed by this code block. Therefore, PL/SQL processes are often seen at the top of this list, because the number of separate statements executed by stored procedures is total.

SQL ordered by Reads for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> End Disk Reads Threshold: 1000

CPU Elapsd

Physical Reads Executions Reads per Exec % Total Time (s) Hash Value

--------------------------------------------------------------------------

3,587 1 3,587.0 13.9 0.17 5.13

Module: PL/SQL Developer

Select min (curtime), max (curtime) from city_day_cal

1,575 1 1,575.0 6.1 1.75 18.66

Module: PL/SQL Developer

Insert into city_day_cal select * from rptuser. city_day_cal @ db15

1 where curtime between to_date ('20140901', 'yyymmm') and to_date ('

200502 ', 'yyyymmm')-1

This part sorts SQL statements through physical reading. This shows that most of the SQL statements that read the system, that is, physical I/O.

SQL ordered by Executions for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> End Executions Threshold: 100

CPU per Elap per

Executions Rows Processed Rows per Exec (s) Hash Value

--------------------------------------------------------------------------

748 748 1.0 0.00 0.00 3371479671

Select t. name, (select owner_instance from sys. aq $ _ queue_table _

Affinities where table_objno = t. objno) from system. aq $ _ queue

_ Tables t where t. name =: 1 and t. schema =: 2 for update skip lo

Cked

442 1,142 2.6 0.00 0.00 1749333492

Select position #, sequence #, level #, argument, type #, charsetid, chars

Etform, properties, nvl (length, 0), nvl (precision #, 0), nvl (scale,

0), nvl (radix, 0), type_owner, type_name, type_subname, type_linknam

E, pls_type from argument $ where obj # =: 1 and procedure # =: 2 order

By sequence # desc

This part tells us the most executed SQL statements during this period. It may be useful to isolate frequently executed queries to see if there are some methods to change the logic to avoid the need to execute these queries so frequently. Maybe a query is being executed inside a loop, and it may be executed once outside the loop. You can design simple algorithm changes to reduce the number of times that the query must be executed. Even if it runs fast, any millions of operations will start to exhaust a lot of time.

9. instance Activity

Instance Activity Stats for DB: BLISSDB Instance: blissdb Snaps: 4-5

Statistic Total per Second per Trans

CPU used by this session 4,870 4.5 194.8

CPU used when call started 4,870 4.5 194.8

CR blocks created 45 0.0 1.8

DBWR buffers scanned 24,589 22.8 983.6

DBWR checkpoint buffers written 14,013 13.0 560.5

DBWR checkpointts 5 0.0 0.2

......

Dirty buffers inspected 38,834 36.0 1,553.4 -- number of dirty buffers

Free buffer inspected 40,463 37.5 1,618.5 -- if the number is large, the buffer is too small

......

10. I/O

The following two reports are for I/O. Generally, read and write operations on various devices are expected to be evenly distributed. Find out which files may be very "hot ". Once the DBA understands how to read and write the data, they may be able to get some performance improvements by allocating I/O evenly between disks.

Tablespace IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> Ordered by IOs (Reads + Writes) desc

Tablespace

Av Buffer Av Buf

Reads/s Rd (MS) Blks/Rd Writes/s Waits Wt (MS)

BLISS_DATA

17,649 16 12.3 1.2 44,134 41 0 0.0

UNDOTBS1

4,484 4 9.6 1.0 29,228 27 0 0.0

SYSTEM

340 0 31.0 1.1 36 0 0.0

File IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> Ordered by Tablespace, File

Tablespace Filename

----------------------------------------------------------------------------

Av Buffer Av Buf

Reads/s Rd (MS) Blks/Rd Writes/s Waits Wt (MS)

----------------------------------------------------------------------

BLISS_DATA D: ORACLEORADATABLISSDBBLISS01.DBF

5,779 5 12.0 1.2 14,454 13 0

D: ORACLEORADATABLISSDBBLISS02.DBF

5,889 5 12.1 1.2 14,772 14 0

D: ORACLEORADATABLISSDBBLISS03.DBF

5,981 6 12.6 1.2 14,908 14 0

11. Buffer Pool

Buffer Pool Statistics for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> Standard block size Pools D: default, K: keep, R: recycle

-> Default Pools for other block sizes: 2 k, 4 k, 8 k, 16 k, 32 k

Free Write Buffer

Number of Cache Buffer Physical Buffer Complete Busy

P Buffers Hit % Gets Reads Writes Waits

-----------------------------------------------------------------------

D 3,000 99.4 4,482,816 25,756 73,470 11 9 0

-------------------------------------------------------------

If we use the multi-buffer pool function, the above report will tell us the usage Fault Caused by the buffer pool. In fact, this is just a duplicate of the information we see at the beginning of the report.

12. rollback segment Activity

Instance Recovery Stats for DB: BLISSDB Instance: blissdb Snaps: 4-5

-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt

MTTR Recovery Actual Target Size Timeout Interval

(S) Estd IOs Redo Blks

B 37 17 169 4012 3453 184320 3453

E 37 32 1385 57132 184320 184320 436361

Generally, it is expected that the activities will be evenly distributed among each rollback segment (except the SYSTEM rollback segment. When you check this part of a report, the report title also has the most useful information to remember. In particular, it is recommended that Optmal be larger than Avg Active when the best settings are fully used. This is the most relevant DBA activity (I/O and rollback segment information ).