Oracle パフォーマンスレポートツール STATSPACKを利用する

仕事で資料を残さなきゃいけなくってまとめたので、せっかくだから自分のメモとして。

◎パフォーマンスレポートツール STATSPACKを利用する

Oracle10gには、標準で用意されています。が、設定はされていないので…

出力の見方は、そのうち…作るかなぁ…？

※本来は、運用開始前に導入します。

■表領域の準備

　領域名：TOOLS (自由です)

　領域サイズ：400MB (最低64MB以上)

　エクステントサイズ：1MB(オススメ値：100K、1MB、5MB)

■インストールスクリプトを実行する(SYSDBAユーザーにて)

SQL> @?/rdbms/admin/spcreate.sql

PERFSTATユーザーが作成されます。パスワードは自由です

DEFAULT表領域に準備した表領域を設定(TOOLS)

TEMPORALY表領域には、TEMPを設定(別個に作成しても良い)

■スナップショットを定期的(1時間毎)に取得するようにスケジュールする(PERFSTATユーザー)

SQL> @?/rdbms/admin/spauto.sql

■定期的な取得を30分毎に変更する(PERFSTATユーザー)

SQL> EXECUTE DBMS_JOB.INTERVAL (:jobno, 'SYSDATE+(1/48)');

■スナップショット・レベルを変更する(PERFSTATユーザー)

　　デフォルトはレベル5

　　+------------+-----------------------------------------------------------+

　　|i_snap_level| 取得データ |

　　+------------+-----------+---------+---------+------------+--------------+

　　+------------+-----------+---------+---------+------------+--------------+

　　| レベル 0 | ○ | ○ | | | |

　　| レベル 5 | ○ | ○ | ○ | | |

　　| レベル 6 | ○ | ○ | ○ | | |

　　| レベル 7 | ○ | ○ | ○ | ○ | |

　　| レベル 10 | ○ | ○ | ○ | ○ | ○ |

　　+------------+-----------+---------+---------+------------+--------------+

SQL> EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7);

(SQL> EXECUTE STATSPACK.SNAP(i_snap_level => 7))

■レポートを参照する(PERFSTATユーザー)

※日本語だとうまく出力されないので、英語で。

DOS> set NLS_LANG=American_America.UTF8

SQL> @?/rdbms/admin/spreport.sql

　　例）

　　*************************

Current Instance

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

DB Id DB Name Inst Num Instance

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

0000000000 XXXX 1 xxxx

Instances in this Statspack schema

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

DB Id Inst Num DB Name Instance Host

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

0000000000 1 XXXX xxxx DB-SERVER

Using 0000000000 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

xxxx XXXX 1 26 Aug 2009 21:20 7

34627 25 Jan 2010 00:19 7

34628 25 Jan 2010 00:49 7

34629 25 Jan 2010 01:19 7

34630 25 Jan 2010 01:50 7

34631 25 Jan 2010 02:20 7

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 34652

Begin Snapshot Id specified: 34652

Enter value for end_snap: 34741

End Snapshot Id specified: 34741

Specify the Report Name

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

The default report file name is sp_34652_34741. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sp_34652_34741

STATSPACK report for

Database DB Id

Instance Inst Num

Startup Time Release

RAC

~~~~~~~~ -----------

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

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

---

0000000000 xxxx 1 23-Jan-10 13:55

10.2.0.1.0 NO

Host Name: DB-SERVER Num CPUs: 4

Phys Memory (MB): 4,091

~~~~

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~ ---------- ------------------ -------- --------- -------------------

Begin Snap: 34652 25-Jan-10 08:51:08 36 7.4

End Snap: 34741 25-Jan-10 13:21:48 83 4.0

Elapsed: 270.67 (mins)

Cache Sizes Begin End

~~~~~~~~~~~ ---------- ----------

Buffer Cache: 848M 832M Std Block Size: 8K

Shared Pool Size: 160M 176M

Log Buffer: 6,860K

Load Profile Per Second Per Transaction

~~~~~~~~~~~~ --------------- ---------------

Redo size: 6,017.10 1,855.21

(中略)

Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 34652-34741

->ordered by IOs (Reads + Writes) desc

Tablespace

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

Av Av Av Av Buffer Av Buf

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

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

SYSAUX

514 0 2.9 1.1 3,667 0 0 0.0

TOOLS

1,030 0 2.8 1.0 2,498 0 0 0.0

SYSTEM

1,547 0 3.9 1.0 889 0 0 0.0

(中略)

File IO Stats DB/Inst: ORCL/orcl Snaps: 34652-34741

->Mx Rd Bkt: Max bucket time for single block read

->ordered by Tablespace, File

Tablespace Filename

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

Av Mx Av

Av Rd Rd Av Av Buffer BufWt

Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)

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

SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

514 0 2.9 64 1.1 3,667 0 0

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

1,547 0 3.9 32 1.0 889 0 0

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TOOLS_REORG0.D

1,030 0 2.8 16 1.0 2,498 0 0

(中略)

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

File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 34652-34741

->Number of single block reads in each time range

->ordered by Tablespace, File

Tablespace Filename

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

0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms

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

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TOOLS_REORG0.D

342 430 258 3 0 0

(中略)

Segments by Logical Reads DB/Inst: XXXX/xxxx Snaps: 34652-34741

-> End Segment Logical Reads Threshold: 10000

-> Pct Total shows % of logical reads for each top segment compared with total

logical reads for all segments captured by the Snapshot

Subobject Obj. Logical Pct

Owner Tablespace Object Name Name Type Reads Total

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

SYS SYSTEM I_HH_OBJ#_INTCOL# INDEX 318,064 11.1

SYS SYSAUX SYS_IOT_TOP_8802 INDEX 234,512 8.2

(中略)

undo_management AUTO

undo_tablespace UNDOTBS1

user_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR

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

End of Report ( sp_34652_34741.lst )

　　*************************

　　SQLPLUSを実行したフォルダに「sp_34652_34741.lst」が出来ています。

■SQLレポートを参照する(PERFSTATユーザー)

　上記のレポート中から「SQL ordered by」のキーワードを探す.

　　*************************

SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 34652-34741

-> Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

-> Total DB CPU (s): 562

-> Captured SQL accounts for 9.1% of Total DB CPU

-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old

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

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

26.84 535 0.05 4.8 30.58 19,340 1206412919

Module: OEM.SystemPool

BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

　　*************************

SQL> @?/rdbms/admin/sprepsql.sql

　　例）

　　*************************

Current Instance

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

DB Id DB Name Inst Num Instance

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

0000000000 XXXX 1 xxxx

Instances in this Statspack schema

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

DB Id Inst Num DB Name Instance Host

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

0000000000 1 XXXX xxxx DB-SERVER

Using 0000000000 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

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

xxxx XXXX 1 26 Aug 2009 21:20 7

34652 25 Jan 2010 08:51 7

34661 25 Jan 2010 09:21 7

34671 25 Jan 2010 09:51 7

34681 25 Jan 2010 10:21 7

34691 25 Jan 2010 10:51 7

34701 25 Jan 2010 11:21 7

34711 25 Jan 2010 11:51 7

34721 25 Jan 2010 12:21 7

34731 25 Jan 2010 12:51 7

34741 25 Jan 2010 13:21 7

34751 25 Jan 2010 13:51 7

34761 25 Jan 2010 14:21 7

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 34652

Begin Snapshot Id specified: 34652

Enter value for end_snap: 34741

End Snapshot Id specified: 34741

Specify the old (i.e. pre-10g) Hash Value

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

Enter value for hash_value: 1206412919

Hash Value specified is: 1206412919

Specify the Report Name

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

The default report file name is sp_34652_34741_1206412919. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sp_34652_34741_1206412919

STATSPACK SQL report for Old Hash Value: 1206412919 Module: OEM.SystemPool

DB Name DB Id Instance Inst Num Release RAC Host

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

XXXX 0000000000 xxxx 1 10.2.0.1.0 NO DB-SERVER

Start Id Start Time End Id End Time Duration(mins)

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

34652 25-Jan-10 08:51:08 34741 25-Jan-10 13:21:48 270.67

SQL Statistics

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

-> CPU and Elapsed Time are in seconds (s) for Statement Total and in

milliseconds (ms) for Per Execute

% Snap

Statement Total Per Execute Total

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

Buffer Gets: 19,340

36.1 .25

Disk Reads: 2

0.0 .01

Rows processed: 0

0.0

CPU Time(s/ms): 27

50.2

Elapsed Time(s/ms): 31

57.2

Sorts: 0

Parse Calls: 535

1.0

Invalidations: 0

Version count: 1

Sharable Mem(K): 16

Executions: 535

SQL Text

~~~~~~~~

BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

Plans in shared pool between Begin and End Snap Ids

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

Shows the Execution Plans found in the shared pool between the begin and end

snapshots specified. The values for Rows, Bytes and Cost shown below are those

which existed at the time the first-ever snapshot captured this plan - these

values often change over time, and so may not be indicative of current values

-> Rows indicates Cardinality, PHV is Plan Hash Value

-> ordered by Plan Hash Value

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

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

End of Report

　　*************************

■レポートを削除する(PERFSTATユーザー)

　スナップショットが溜まると表領域の使用率も増えるので、不要なものは削除します。

SQL> @?/rdbms/admin/sppurge.sql

　　例）

　　*************************

Database Instance currently connected to

========================================

Instance

DB Id DB Name Inst Num Name

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

0000000000 XXXX 1 xxxx

Snapshots for this database instance

====================================

Base- Snap

Snap Id Snapshot Started line? Level Host Comment

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

32997 18 1月 2010 00:24:2 7 DB-SERVER

32998 18 1月 2010 00:54:3 7 DB-SERVER

(中略)

34721 25 1月 2010 12:21:3 7 DB-SERVER

34731 25 1月 2010 12:51:4 7 DB-SERVER

34741 25 1月 2010 13:21:4 7 DB-SERVER

Warning

~~~~~~~

sppurge.sql deletes all snapshots ranging between the lower and

upper bound Snapshot Id's specified, for the database instance

you are connected to. Snapshots identified as Baseline snapshots

which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge

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

losnapidに値を入力してください: 32997

Using 32997 for lower bound.

hisnapidに値を入力してください: 34626

Using 34626 for upper bound.

Deleting snapshots 32997 - 34626.

行は切り捨てられます。

Number of Snapshots purged: 335

Purge of specified Snapshot range complete.

　　*************************

■レポートを"全て"削除する(PERFSTATユーザー)

SQL> @?/rdbms/admin/sptrunc.sql

■スケジュールを削除する(PERFSTATユーザー)

job_numberを調べる

SQL> select job,to_char(next_date,'YYYYMMDD HH24:MI:SS'),what from user_jobs;

この結果でWHAT が「statspack.snap;」のものが対象

SQL> EXECUTE DBMS_JOB.REMOVE(job_number);

■アンインストールする(SYSDBAユーザーにて)

SQL> @?/rdbms/admin/spdrop.sql

■参考URI

　Oracleデータベースの性能対策機能～StatspackとDiagnostics Packを使いこなす～

　http://otn.oracle.co.jp/skillup/stats_diag/1/index.html

　パフォーマンスセラピー

　http://perfopy.ashisuto.co.jp/statspack01.html

　パフォーマンステストでの STATSPACK の取得について

　http://d.hatena.ne.jp/yohei-a/20090729/1248835068

Day2day Opportunism

まぁ、平たく言うと「雑記」がだらだらと･･･

Oracle パフォーマンスレポートツール STATSPACKを利用する