REDOログファイルのサイズ変更をする

REDOログファイルのサイズ変更の基本的な考え方は次の通りです。

・REDOログファイルのサイズ変更は不可
・変更は、新規追加（別ファイル）し、不要のものを削除する
・削除は、STATUS=ACTIVEだとできない
・削除は、内容がアーカイブREDOファイルとして出力されていないとできない
・OMF（Oracle Managed Files）を未使用の場合、物理削除も必要
・RACの場合、インスタンス単位でサイズや個数を変えることはできる

まず、REDOログファイルを確認します。

   THREAD#     GROUP#     MBYTES STATUS     MEMBER
---------- ---------- ---------- ---------- ------------------------------
         1          1       1024 INACTIVE   +DATA/redo/redo111.dbf
         1          1       1024 INACTIVE   +LOG/redo/redo112.dbf
         1          2       1024 ACTIVE     +DATA/redo/redo121.dbf
         1          2       1024 ACTIVE     +LOG/redo/redo122.dbf
         1          3       1024 CURRENT    +DATA/redo/redo131.dbf
         1          3       1024 CURRENT    +LOG/redo/redo132.dbf
         2          4       1024 INACTIVE   +LOG/redo/redo212.dbf
         2          4       1024 INACTIVE   +DATA/redo/redo211.dbf
         2          5       1024 ACTIVE     +LOG/redo/redo222.dbf
         2          5       1024 ACTIVE     +DATA/redo/redo221.dbf
         2          6       1024 CURRENT    +LOG/redo/redo232.dbf
         2          6       1024 CURRENT    +DATA/redo/redo231.dbf

次に、新規REDOログファイルを追加します。
ここでは、2ノードRACを想定し、1GB→2GBのサイズ変更を行います。

$ sqlplus sys as sysdba
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 ('+DATA/redo/redo141.dbf', '+LOG/redo/redo142.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 8 ('+DATA/redo/redo151.dbf', '+LOG/redo/redo152.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9 ('+DATA/redo/redo161.dbf', '+LOG/redo/redo162.dbf') SIZE 2G;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATA/redo/redo241.dbf', '+LOG/redo/redo242.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 ('+DATA/redo/redo251.dbf', '+LOG/redo/redo252.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12 ('+DATA/redo/redo261.dbf', '+LOG/redo/redo262.dbf') SIZE 2G;

追加したら、REDOログファイルを強制的にローテートし、追加したREDOログがCURRENTになるまで繰り返します。
REDOログファイルはノードごとにあるので各ノードで実行します。

[oracle@sssdb1 ~]$ sqlplus sys as sysdba
SQL> alter system switch logfile;

[oracle@sssdb1 ~]$ sqlplus sys as sysdba
SQL> alter system switch logfile;

削除したいREDOログファイルをINACTIVEにし、アーカイブREDOログファイルを出力します。

SQL> ALTER SYSTEM CHECKPOINT;

不要になったREDOログファイルを削除します。

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
SQL> ALTER DATABASE DROP LOGFILE GROUP 6;

OMF（Oracle Managed Files）を利用していない場合、物理ファイルの削除をします。
この例では、ASMのファイルを削除します。

$ export ORACLE_SID=+sss011
$ asmcmd
ASMCMD> cd DATA/REDO
ASMCMD> rm REDO111.DBF
ASMCMD> rm REDO122.DBF
ASMCMD> rm REDO133.DBF
ASMCMD> rm REDO211.DBF
ASMCMD> rm REDO222.DBF
ASMCMD> rm REDO233.DBF

ASMCMD> cd ../../LOG/REDO
ASMCMD> rm REDO112.DBF
ASMCMD> rm REDO122.DBF
ASMCMD> rm REDO132.DBF
ASMCMD> rm REDO212.DBF
ASMCMD> rm REDO222.DBF
ASMCMD> rm REDO233.DBF

REDOログファイルを一覧表示する

REDOログファイルを一覧表示します。

set pages 10000
set lines 120
COL MEMBER FORMAT A30
COL STATUS FORMAT A10
SELECT LOG.THREAD#,LOG.GROUP#,
    LOG.BYTES/1024/1024 AS MBYTES,
    LOG.STATUS,LOG.ARCHIVED,LOGFILE.MEMBER 
 FROM   V$LOG LOG,V$LOGFILE LOGFILE
 WHERE LOG.GROUP# = LOGFILE.GROUP#
 ORDER BY THREAD#,GROUP#;

実行結果は次の通りです。
この例では2ノードRAC、3グループ、各1GBです。

STATUS=CURRECTが現在使用中のREDOログファイルです。
ACTIVEが使ったことがあるがアーカイブされていないもの、INACTIVEはアーカイブ済のものです。
UNUSEDはREDOログファイル追加後などまったく未使用のものです。

THREAD#     GROUP#     MBYTES STATUS     MEMBER
---------- ---------- ---------- ---------- ------------------------------
         1          1       1024 INACTIVE   +DATA/redo/redo111.dbf
         1          1       1024 INACTIVE   +LOG/redo/redo112.dbf
         1          2       1024 ACTIVE     +DATA/redo/redo121.dbf
         1          2       1024 ACTIVE     +LOG/redo/redo122.dbf
         1          3       1024 CURRENT    +DATA/redo/redo131.dbf
         1          3       1024 CURRENT    +LOG/redo/redo132.dbf
         2          4       1024 INACTIVE   +LOG/redo/redo212.dbf
         2          4       1024 INACTIVE   +DATA/redo/redo211.dbf
         2          5       1024 ACTIVE     +LOG/redo/redo222.dbf
         2          5       1024 ACTIVE     +DATA/redo/redo221.dbf
         2          6       1024 CURRENT    +LOG/redo/redo232.dbf
         2          6       1024 CURRENT    +DATA/redo/redo231.dbf

スワップしづらいようにRMANバックアップをする

担当案件では、RMAN→tar→圧縮→2GBファイル分割という手順でバックアップファイルを作成していました。
ですが、tarと圧縮にてOSのファイルキャッシュを大量に消費するため、スワップの発生が問題になっていました。

というのは、DBサーバのメモリのほとんどをSGAおよびOracleプロセスに割り当てていたためです。
ファイル操作用のOSファイルキャッシュが不足してスワップ→さらに負荷上昇という悪循環でした。

そこで、OSファイルキャッシュが効かないように、RAWデバイス側で圧縮およびファイル分割まですることにしました。
ファイル分割が終わるまでの時間は長くなりましたが、スワップは発生しなくなりました。

ここでは、バックアップセットを圧縮し、バックアップピースの最大サイズを2GBに制限する設定をします。
分割ファイル名が重複しないよう、formatでピース番号を指定するよう注意してください。

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

backup database format
  '${BKUPDIR}/%d_database_full_%T_%s_%p.bkp'
  tag = 'full_database_backup';

出力したファイルは次のようになります。

-rw-r--r-- 1 backup backup 2.0G  6月 27 07:27 201106/s01db3/S01_database_full_20110627_4730_1.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:33 201106/s01db3/S01_database_full_20110627_4730_2.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:40 201106/s01db3/S01_database_full_20110627_4730_3.bkp
-rw-r--r-- 1 backup backup 139M  6月 27 07:41 201106/s01db3/S01_database_full_20110627_4730_4.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:47 201106/s01db3/S01_database_full_20110627_4731_1.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:52 201106/s01db3/S01_database_full_20110627_4731_2.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:58 201106/s01db3/S01_database_full_20110627_4731_3.bkp
-rw-r--r-- 1 backup backup 470M  6月 27 08:01 201106/s01db3/S01_database_full_20110627_4731_4.bkp
-rw-r--r-- 1 backup backup 1.4M  6月 27 08:01 201106/s01db3/S01_database_full_20110627_4732_1.bkp

共有プールをクリアする

共有プールをインスタンス単位でクリアすることができます。
ただし、キャッシュヒット率が激減し、一時的にパフォーマンスダウンが予想されるのでタイミングに注意。
RACの場合は、一度に全ノードクリアするのではなく、クリア後ある程度キャッシュがたまってきてからの方がベターです。

共有プールのサイズが大きすぎるとエージアウトした際のロック（ラッチ）の時間が長くなり、DBサーバの応答が遅くなります。
この例のようにsql area（SQL数）が多いことが根本原因なのでアプリを直すべきですが、障害対応として紹介します。
（とはいえ、リスクの方が大きいので僕はめったにクリアしません）
共有プールのラッチは、CPU使用率もI/O負荷も上昇しないので検知しづらく潜在リスクは非常に高いと思っています。

まず、現状の共有プールの状況を確認します。

set pages 10000
set lines 120
set time on
col name for a40
select * from (
 select name, bytes from v$sgastat
 where pool = 'shared pool'
 order by bytes desc
) where rownum <= 20;

例えば、次のようにsql areaが3.5GBほどあるインスタンスをクリアすることにします。

NAME                                          BYTES
---------------------------------------- ----------
sql area                                 3690078544
free memory                               957169136
PCursor                                   637214224
CCursor                                   623044928
library cache                             499544096
sql area:PLSQL                            268971096
gcs resources                             233915744
gcs shadows                               140065792
kglsim object batch                       137342016
db_block_hash_buckets                      94371840
kglsim heap                                82446336
Cursor Stats                               64968688
ASH buffers                                30408704
transaction                                19464072
ges enqueues                               18813632
trace buffer                               16891904
ges big msg buffers                        15936168
KCL name table                             12582912
event statistics per sess                  12296000
FileOpenBlock                              11575096

共有プールのクリアはインスタンスごとにalter systemします。

$ sqlplus sys as sysdba
SQL> alter system flush shared_pool;

次のようにsql areaが消え、free memoryが増加します。

NAME                                          BYTES
---------------------------------------- ----------
free memory                              6376176928
gcs resources                             233915744
kglsim object batch                       191237088
library cache                             152614448
gcs shadows                               140065792
kglsim heap                               108622080
db_block_hash_buckets                      94371840
Cursor Stats                               85479368
sql area                                   44653328
ASH buffers                                30408704
transaction                                19490568
ges enqueues                               18813632
trace buffer                               16891904
ges big msg buffers                        15936168
KCL name table                             12582912
event statistics per sess                  12296000
FileOpenBlock                              11575096
CCursor                                    11276472
ges resource                               11221136

UNDO表領域の縮小

UNDO表領域は 縮小できない場合があります。
UNDO表領域を拡張するやデータファイルのサイズを縮小するで触れている通り、断片化しやすい表領域のためです。
自動拡張してしまった場合は、特に断片化が進んでいて縮小できないことが多いです。

また、UNDO表領域は削除することもできません。
そこで、縮小は、一時的に作ったUNDO表領域に切り替えてから古いものを再作成する必要があります。

まずは、今の表領域を確認します。
容量は、表領域の使用状況で確認します。
この例だと、32GBいっぱいまで自動拡張してますね・・・。

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTBS1

TABLESPACE_NAME             SIZEMB   USEDMB   FREEMB   RATIO AUTO
------------------------- -------- -------- -------- ------- ----
UNDOTBS1                    32,749       80   32,669     .25 YES

次に、一時的なUNDO表領域を作成し、切り替えます。

SQL> create undo tablespace UNDOTMP datafile '/DATA/DATABASE/UNDOTMP1.DBF' size 1G AUTOEXTEND ON;
SQL> alter system set undo_tablespace='UNDOTMP' sid='sss01dev';

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTMP

切り替え後の、サイズの大きいUNDO表領域は再作成します。

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
SQL> create undo tablespace UNDOTBS1 datafile '/DATA/DATABASE/UNDOTBS101.DBF' size 4G;

最後に、再作成したUNDO表領域に切り戻します。

SQL> alter system set undo_tablespace='UNDOTBS1' sid='sss01dev';

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTBS1

SQL> drop tablespace UNDOTMP including contents and datafiles;

SQLをトレースしTKPROFで整形する

EXPLAINで実行計画がわかりますが、バインド変数を使ったSQLの場合はデータ（レコード）を特定できないため、正確な実行計画が得られません。
ここでは実際に実行したSQLをトレースをすることにより実行計画を取得します。

次のように、トレースをONにして実行します。

var vcarrier number
var vleader number
exec :vcarrier := 2
exec :vleader := 2
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

SELECT
    *
FROM
    (SELECT
        rank() over (ORDER BY tt.regist_dt DESC) as rn,
        tt.team_nm AS team_nm ,
        tt.member_cnt AS member_cnt ,
        tt.team_status_id AS team_status_id ,
        tt.pr_comment AS pr_comment ,
        tt.team_id AS team_id ,
        tm.user_id AS user_id ,
        tt.regist_dt AS regist_dt ,
        wearing_avatar_kind,
        avatar_id,
        gender,
        avatar_session
    FROM
        T01_Test2 tt ,
        T01_TestMember2 tm ,
        T01_UserMyTest2 my
    WHERE
        tt.carrier_id = :vcarrier     AND
        tm.user_id = my.user_id     AND
        tt.team_id = tm.team_id     AND
        tm.leader_flg = :vleader
    ORDER BY
        tt.regist_dt DESC
    )
WHERE
    rn BETWEEN 1 AND
    30;

alter session set events '10046 trace name context off';
alter session set timed_statistics=false;

EVENT 10046のレベルは4種類あります。

level  1　　　alter session set sql_trace=trueと同じ
level  4　　　level 1にバインド変数情報
level  8　　　level 1に待機イベント情報
level 12　　　level 4 ＋ level 8

実行後、DBサーバにトレースファイルが出力されています。
そのトレースファイルそのままだと読みづらいので、TKPROFというツールで整形します。

$ ls -tlr /opt/app/oracle/admin/sss01dev/udump/ | tail -1
$ sudo scp /opt/app/oracle/admin/sss01dev/udump/sss01dev_ora_21243.trc snwdev1:/home/ito/tmp/
$ tkprof sss01dev_ora_21243.trc test.trc

ただし、TKPROFはバインド情報を無視するので必要であればトレースファイルを直接確認します。

$ grep -A4 "^ Bind" sss01dev_ora_21243.trc 
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=2b818341b4c0  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=2b818341b4d8  bln=22  avl=02  flg=01
  value=2

整形したファイル例は次の通りです。

TKPROF: Release 10.2.0.4.0 - Production on 火 4月 5 11:25:10 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: sss01dev_ora_29755.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT
    *
FROM
    (SELECT
        rank() over (ORDER BY tt.regist_dt DESC) as rn,
        tt.team_nm AS team_nm ,
        tt.member_cnt AS member_cnt ,
        tt.team_status_id AS team_status_id ,
        tt.pr_comment AS pr_comment ,
        tt.team_id AS team_id ,
        tm.user_id AS user_id ,
        tt.regist_dt AS regist_dt ,
        wearing_avatar_kind,
        avatar_id,
        gender,
        avatar_session
    FROM
        T01_Team2 tt ,
        T01_TeamMember2 tm ,
        T01_UserMyRoom2 my
    WHERE
        tt.carrier_id = :vcarrier     AND
        tm.user_id = my.user_id     AND
        tt.team_id = tm.team_id     AND
        tm.leader_flg = :vleader
    ORDER BY
        tt.regist_dt DESC
    )
WHERE
    rn BETWEEN 1 AND
    30

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.14       0.14          0       6097          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.14       0.14          0       6097          0          30

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 113  

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  VIEW  (cr=6097 pr=0 pw=0 time=140143 us)
   1153   WINDOW SORT PUSHED RANK (cr=6097 pr=0 pw=0 time=141286 us)
   1153    HASH JOIN  (cr=6097 pr=0 pw=0 time=129360 us)
   1164     TABLE ACCESS FULL T01_TEAM2 (cr=227 pr=0 pw=0 time=166 us)
   1153     HASH JOIN  (cr=5870 pr=0 pw=0 time=123033 us)
   1153      TABLE ACCESS FULL T01_TEAMMEMBER2 (cr=253 pr=0 pw=0 time=4643 us)
  86413      TABLE ACCESS FULL T01_USERMYROOM2 (cr=5617 pr=0 pw=0 time=86469 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      32        0.00          0.00
  SQL*Net message from client                    32        0.00          0.02



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.14       0.14          0       6097          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.14       0.14          0       6097          0          30

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      79        0.00          0.00
  SQL*Net message from client                    79       15.53         23.49


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: sss01dev_ora_29755.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
     263  lines in trace file.
       0  elapsed seconds in trace file.

シーケンスキャッシュのサイズを変更する

STATSPACKや待機しているセッションを調べるでenq: SQ - contentionが目立つ場合、シーケンスキャッシュが不足している場合があります。

担当案件にてユーザプロセスが急増したことがありますが、その際、ユーザプロセスのIDキャッシュサイズが小さいためにenq: SQ - contentionが多発しました。
（そもそもの原因はアプリケーションでしたが、急場しのぎのために設定変更しました）

今回の例では、ユーザプロセスのID発行に関わるキャッシュのサイズを大きくします。
ただし、これは諸刃の剣なので十分注意してください。
理由は、キャッシュサイズが大きくなるということは、キャッシュを使いきってリフレッシュする時のロック時間が長くなるということです。

まず、現在の設定値CACHE_SIZEを確認します。

set pages 10000
set lines 120
col SEQUENCE_OWNER for a15
col SEQUENCE_NAME for a15
select * from dba_sequences where sequence_name in ('AUDSES$','IDGEN1$');

SEQUENCE_OWNER  SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER
--------------- --------------- ---------- ---------- ------------ --- --- ---------- -----------
SYS             IDGEN1$                  1 1.0000E+27           50 N   N         2000  3.9574E+10
SYS             AUDSES$                  1 2000000000            1 Y   N         2000  1448207982

次のように値を10倍にします。

SQL> ALTER SEQUENCE SYS.AUDSES$ CACHE 20000;
SQL> ALTER SEQUENCE SYS.IDGEN1$ CACHE 20000;

自動アナライズの実行時刻を変更する

Oracleは定期的にアナライズをすることにより、実行計画の精度を上げるのが基本です。
10gではGATHER_STATS_JOBという自動アナライズ機能がデフォルトでONになっています。
とはいえ、たびたびアナライズをするとパフォーマンスダウンにつながるため、 GATHER_STATS_JOBは次のオブジェクトに対して実施します。

統計情報を取得していないオブジェクト
統計情報が失効（レコードが10%が変更された）オブジェクト

私の担当Webサイトでは、ジョブスケジューリングの設定変更をしています。
というのは、デフォルト設定のままだと平日22時および土曜日0時にしか実行されないためです。
担当サイトの高負荷時間帯は毎日22～25時なので、負荷の低い毎日14時に実行しています。

まず、GATHER_STATS_JOBがMAINTENANCE_WINDOW_GROUPというジョブグループで動いていることを確認します。

col JOB_NAME for a30
col SCHEDULE_NAME for a40
select JOB_NAME ,SCHEDULE_NAME from DBA_SCHEDULER_JOBS;

JOB_NAME                       SCHEDULE_NAME
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP
GATHER_STATS_JOB               MAINTENANCE_WINDOW_GROUP
FGR$AUTOPURGE_JOB
PURGE_LOG                      DAILY_PURGE_SCHEDULE
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB                MAINTENANCE_WINDOW_GROUP

次に、MAINTENANCE_WINDOW_GROUPのスケジュール内容を確認します。

set pages 10000
set lines 120
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a80
col DURATION for a30
select WINDOW_NAME ,REPEAT_INTERVAL ,DURATION from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          REPEAT_INTERVAL
-------------------- --------------------------------------------------------------------------------
DURATION
------------------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW       freq=daily;byday=SAT,SUN;byhour=0;byminute=0;bysecond=0
+000 48:00:00

設定は次のようにします。

exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=15;byminute=0; bysecond=0');
exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','duration','+000 05:00:00');

exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval','freq=daily;byday=SAT,SUN;byhour=15;byminute=0;bysecond=0');
exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','duration','+000 05:00:00');

実行されたかどうかは、スケジューラの履歴で確認できます。
なお、この例はRAC環境なのでインスタンス単位で表示されます。

set pages 10000
set lines 120
col job_name for a25
col status for a10
col START_DATE for a20
col END_DATE for a20
SELECT 
  TO_CHAR(ACTUAL_START_DATE, 'YYYY/MM/DD HH24:MI:SS') AS "START_DATE",
  TO_CHAR(LOG_DATE, 'YYYY/MM/DD HH24:MI:SS') AS "END_DATE",
  JOB_NAME,STATUS,INSTANCE_ID
 FROM DBA_SCHEDULER_JOB_RUN_DETAILS
 WHERE JOB_NAME in ('AUTO_SPACE_ADVISOR_JOB','GATHER_STATS_JOB')
 ORDER BY ACTUAL_START_DATE,JOB_NAME;

 START_DATE           END_DATE             JOB_NAME                  STATUS     INSTANCE_ID
-------------------- -------------------- ------------------------- ---------- -----------
2011/02/23 14:00:02  2011/02/23 14:05:19  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/23 14:00:02  2011/02/23 14:09:55  GATHER_STATS_JOB          SUCCEEDED            2
2011/02/24 14:00:02  2011/02/24 14:05:39  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/24 14:00:02  2011/02/24 14:10:30  GATHER_STATS_JOB          SUCCEEDED            2
2011/02/25 14:00:01  2011/02/25 14:11:04  GATHER_STATS_JOB          SUCCEEDED            1
2011/02/25 14:00:02  2011/02/25 14:05:38  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/26 14:00:00  2011/02/26 14:09:39  GATHER_STATS_JOB          SUCCEEDED            1
2011/02/26 14:00:00  2011/02/26 14:05:40  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            1

実行計画（EXPLAIN）を取得する

クエリの実行計画を取得するには2ステップ必要です。

(1)explainしてその結果を保存する
(2)保存結果を表示する

まずexplainすると、その結果はPLAN_TABLEに保存されます。

SQL> explain plan for select user_id from T01_AQUA1_APPITEM_LIMIT1;

Explained.

結果は、標準スクリプトでテーブル内容を表示できます。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4246935464

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |  7421K|    42M|  9167   (2)| 00:01:50 |
|   1 |  TABLE ACCESS FULL| T01_AQUA1_APPITEM_LIMIT1 |  7421K|    42M|  9167   (2)| 00:01:50 |
----------------------------------------------------------------------------------------------

表関数がサポートされている9i（9iR2？）以上は、DBMS_XPLANパッケージでさらに詳細な情報を表示することもできます。
3つ目の引数を'ALL'にするとパラレルクエリやカラム情報も表示します。

SQ> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4246935464

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |  7421K|    42M|  9167   (2)| 00:01:50 |
|   1 |  TABLE ACCESS FULL| T01_AQUA1_APPITEM_LIMIT1 |  7421K|    42M|  9167   (2)| 00:01:50 |
----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T01_AQUA1_APPITEM_LIMIT1@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "USER_ID"[NUMBER,22]

UNDO表領域を拡張する

表領域の自動拡張設定をしていれば自動的に拡張されますがパフォーマンスが落ちるので適度なサイズにした方がいいです。
注意として次の2つ。

・UNDO表領域のデータファイルは1つだけ
・UNDO表領域は縮小できない（ことが多い）
・UNDO表領域の最大サイズは32GB

まずデータファイル名を確認します。

set pages 10000
set lines 120
col NAME for a40
col bytes for 999,999,999,999
select file#, bytes, name from v$datafile where upper(name) like '%UNDO%';

     FILE#            BYTES NAME
---------- ---------------- ----------------------------------------
         2   11,188,305,920 /DATA/DATABASE/UNDOTBS101.DBF

サイズを拡張します。

ALTER DATABASE DATAFILE '/DATA/DATABASE/UNDOTBS101.DBF' RESIZE 16G;

共有プールのサイズ変更履歴を確認する

例えばWebリクエストと分析系バッチなど、特性が違いすぎる処理を実行しているとします。
自動共有メモリ管理（ASMM）を使っている場合、Oracleは共有プールとデータベースバッファの最適化に迷ってしまうことがあります。
特性の異なる最適化を繰り返してしまうのは、本当の意味で最適化とは言えません。

メモリ（SGA）のリサイズ結果を判断の1つにすることができます。

set time on 
set pages 10000
set lines 120
col component for a25
col oper_type for a12
col parameter for a25
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select component,oper_type,parameter,modsize,end_time from (
  select row_number() over (order by end_time desc) as rn,
    component,oper_type,parameter,(final_size-initial_size) as modsize,end_time
  from v$SGA_RESIZE_OPS
) where rn<=20;

実行結果は次の通り。 OPER_TYPEは増減（GROW/SHRINK）です。 頻度や量（MODSIZE）を確認するといいと思います。

COMPONENT                 OPER_TYPE    PARAMETER                    MODSIZE END_TIME
------------------------- ------------ ------------------------- ---------- -------------------
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 04:14:31
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 04:14:31
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 03:01:26
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 03:01:26
DEFAULT buffer cache      SHRINK       db_cache_size             -100663296 2011/03/04 18:57:11
shared pool               GROW         shared_pool_size           100663296 2011/03/04 18:57:11
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/04 04:32:11
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/04 04:32:11
DEFAULT buffer cache      SHRINK       db_cache_size             -201326592 2011/03/03 16:12:45
shared pool               GROW         shared_pool_size           201326592 2011/03/03 16:12:45

REDOログスイッチの完了時刻を確認する

更新処理によりREDOログファイルがいっぱいになると、REDOログファイルはローテートされます
これをログスイッチと呼びます。
1時間に1度を目処に考えていますが、頻繁に発生するとパフォーマンスが落ちるのでチューニングが必要です。

ログスイッチのタイミングはアラートログから調べることができます。

$ grep -B1 "LGWR switch" $ORACLE_BASE/admin/sss01/bdump/alert_sss011.log | tail -1000 | grep "Mar  6"

Sun Mar  6 00:29:08 2011
Sun Mar  6 02:07:04 2011
Sun Mar  6 02:07:43 2011
Sun Mar  6 07:31:04 2011
Sun Mar  6 07:32:33 2011
Sun Mar  6 13:57:04 2011
Sun Mar  6 13:58:38 2011
Sun Mar  6 18:57:06 2011
Sun Mar  6 18:58:25 2011

ログスイッチはロックによる待機を最小限にするため、次の順に処理します。

1)REDOログファイルのローテート
2)古いREDOログファイルをアーカイブREDOログファイルとして書き出す（アーカイブログモード）

アーカイブログモードでログスイッチが高頻度すぎると、ディスクI/Oが発生してパフォーマンスが低下します。
アーカイブREDOログファイルへの書き出しが完了しないと、そのREDOファイルは次回REDOログとして利用できません。
最悪の場合、REDOログファイルのローテートが1周してもアーカイブREDOログファイルへの書き出しが完了しません。
そうなると、利用出来るREDOログがなくなるため、ローテート待ちでデータベースがロックします。

注意として、アラートログの時刻はローテートが完了した時刻です。
アーカイブREDOログの書き出し完了時刻もチェックするといいと思います。

set pages 10000
set lines 100
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select SEQUENCE#,COMPLETION_TIME from (
  select row_number() over (order by COMPLETION_TIME desc) as rn,
    SEQUENCE#, COMPLETION_TIME from v$archived_log
) where rn<=10

 SEQUENCE# COMPLETION_TIME
---------- -------------------
      2750 2011/03/07 00:21:59
      2656 2011/03/06 18:58:26
      2749 2011/03/06 18:58:25
      2748 2011/03/06 18:58:24
      2655 2011/03/06 18:58:22
      2747 2011/03/06 13:58:39
      2654 2011/03/06 13:58:39
      2746 2011/03/06 13:58:36
      2653 2011/03/06 13:58:32
      2652 2011/03/06 07:32:34

SGAの大きさを変更する

SGAはインスタンスの再起動が必要です。
まず現在の設定を確認します。

SQL> show parameter sga_
NAME             TYPE         VALUE
---------------- ------------ ------
sga_max_size     big integer  1536M
sga_target       big integer  1536M

SGAサイズsga_max_sizeは動的変更（scope=both）できません。
初期化パラメータをサーバパラメータファイルで管理している、かつ、SGAの自動共有メモリ管理（ASMM）を使っている場合、sga_targetは動的変更できます。

SQL> alter system set sga_max_size = 4G scope=spfile
SQL> alter system set sga_target = 4G scope=spfile

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2089472 bytes
Variable Size            3707768320 bytes
Database Buffers          570425344 bytes
Redo Buffers               14684160 bytes
Database mounted.
Database opened.

作業後、確認します。

SQL> show parameter sga_
NAME             TYPE         VALUE
---------------- ------------ ------
sga_max_size     big integer  4G
sga_target       big integer  4G

似て非なるSQLを探す

Oracleは意識的にプレースホルダを使わないと、共有プールのSQL領域を食いつぶしてSQLのキャッシュ率が悪くなります。
SGAを増やす手段もありますが、数ギガ規模にまで増やすとキャッシュの吐き出し（エージアウト）をしている間、SQLキャッシュ全体をロック（ラッチ）されて処理を何も受け付けなくなります。
そこで、リテラル値が含まれているSQLを定期的に探すといいでしょう。
この例では、SQL文を前方一致100文字だけで比較していますが、アプリケーションによっては50～150文字など調整するといいかと思います。
CNTが多いものを最優先に対応するといいでしょう。

select "Stmt",cnt,"Mem/count","Exec" from (
  SELECT row_number() over (order by count(*) desc) as rn,
      substr(sql_text,1,100) "Stmt", count(*) cnt,
      sum(sharable_mem)/count(*)    "Mem/count",
      sum(executions)      "Exec"
  FROM v$sqlstats
  GROUP BY substr(sql_text,1,100)
  HAVING count(*) > 10
) where rn<=100;

実行例は次の通り。

Stmt                                                                                    CNT  Mem/count       Exec
-------------------------------------------------------------------------------- ---------- ---------- ----------
    SELECT     msfr.free_rankavg_id,     msfr.free_rankavg_nm,     msfr.event_id       4308 36555.0975       4312
,     msfr.team_flg,

select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)         308 12900.4416       3365
no_parallel_index(t)

select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_T        170 44840.8471        380
RANS,INITIAL_EXTENT/

select column_name,data_type,data_length,nullable,data_default,data_precision,da        127 48466.2047        310
ta_scale from user_t

 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact         104 11319.4615       1182
use_weak_name_resl d

SELECT /*+ cursor_sharing_exact */ count(*) FROM  "SYS"."KUPC$DATAPUMP_QUETAB" T        100      12426          0
AB, SYS.DUAL WHERE t

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u         93 51273.5484       1069
se_weak_name_resl dy

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB         91 17089.3187       1338
) opt_param('paralle

    SELECT     tau.user_id,     tau.aplus_level_no,     taf.appr_stat_cd    FROM         63 19573.8413        252
     aplus001iuser.t

select * from ( select rank() over ( order by last_access_dt desc , rownum) AS r         49 32231.8367        161
n, user_id,uid_2,car

insert into TBL_ObjectiveEventLog (regist_dt,event_id,objective_id,carrier_id,us         34 10804.2353        397
er_id,event_clear_co

SELECT COUNT(DISTINCT BAG_USER_ID), COUNT(DISTINCT CAP_USER_ID) FROM (SELECT CAS         32    22367.5       4622
E MBAG.BAG_KIND WHEN

SELECT COUNT(DISTINCT APL_USER_ID), COUNT(DISTINCT SMS_USER_ID) FROM (SELECT CAS         32   23160.75       4458
E MAPP.SMST_FLG WHEN

select item_id,category_id,item_nm,carrier_1_publish_flg,carrier_2_publish_flg,c         25   13244.16         88
arrier_3_publish_flg

SELECT * FROM (SELECT DISTINCT user_id, 1 as carrier_id, row_number() over( orde         22 17519.6364         65
r by last_adm_dt  )

SELECT alias000$."STYLESHEET" AS STYLESHEET FROM "SYS"."METASTYLESHEET" alias000         20      14730        992
$ WHERE 1 = 1 AND ((

select user_regist_kind1, TO_CHAR(last_adm_dt_kind1,'YYYY/MM/DD HH24:MI:SS') as          20    24929.2         93
last_adm_dt_kind1 fr

SELECT     user_id,     sum(prev_user_point_amt),      sum(user_point_result_amt         18 15337.3333         19
),     trunc(update_

    SELECT     mfri.free_rankavg_incentive_id,     mfri.free_rankavg_id,     mfr         18      15336         18
i.team_flg,     mfri

     sss01dbiuser.         17 22733.6471        291
tbl_usermyroom1

select * from (       select temp_.*, rownum as rownumber_ from (            SEL         16    35747.5        389
ECT         mwp.aplu

select * from (       select temp_.*, rownum as rownumber_ from (            sel         15    34043.2      13478
ect     user_id,

SELECT count(DISTINCT user_id) as datacount FROM MST_User1 WHERE user_id in (SEL         15    14788.8         42
ECT user_id FROM MST

SELECT AP001_ID,USER_ID,AP001_LEVEL_NO,MISSION_COMPLETE_PCT,TOTAL_GAME_CNT,TOTAL         13 27131.0769         87
_COIN_AMT,TOTAL_DECO

select user_id,nick_name,mail,mail_sec,mail_domain,user_name,user_zip,user_pref,         12 29984.6667       5073
user_address,to_char

SELECT AP001_ITEM_ID,AP001_ID,AP001_ITEM_CATEGORY_CD,CARRIER_1_PUBLISH_FLG,CARRI         12       9270         31
ER_1_PUBLISH_DT,CARR

LOBセグメントからテーブル名を探す

LOBはテーブルセグメントとは別管理なので、セグメントのサイズを確認するとSYS_LOB
から始まるセグメント名で表示されます。
shrinkするのにテーブル名を知りたい場合、dba_lobsで探すことができます。

set time on
set pages 10000
set lines 120
col owner for a20
col tablespace_name for a20
col table_name for a30
col column_name for a20
col segment_name for a30
select owner,segment_name,tablespace_name,table_name,column_name from dba_lobs
where segment_name in ('SYS_LOB0000032214C00005$$','SYS_LOB0000032217C00014$$');

実行例は次の通り。

OWNER           SEGMENT_NAME                   TABLESPACE_NAME TABLE_NAME           COLUMN_NAME
--------------- ------------------------------ --------------- -------------------- --------------------
SSS01DBIUSER    SYS_LOB0000032214C00005$$      SSS01_I_DATA    TBL_AAARESTEXT1      AAA_ANS_TEXT
SSS01DBIUSER    SYS_LOB0000032217C00014$$      SSS01_I_DATA    TBL_GENERALENTRY1    GENERALENTRY_FREE

セグメントのサイズを確認する

テーブルやインデックスなどのセグメントは、使ううちに断片化して肥大化します。
定期的にサイズをチェックし、shrinkなどの対策をするといいでしょう。

set time on
 set pages 10000
 set lines 120
 col segment_name for a30
 col tablespace_name for a20
 col bytes for 9,999,999,999
 select tablespace_name,segment_name,bytes from (
   select rank() over (order by bytes desc) rnk,
     tablespace_name,segment_name,bytes from dba_segments
 ) where rnk<=20;

実行例は次の通り。

TABLESPACE_NAME      SEGMENT_NAME                            BYTES
-------------------- ------------------------------ --------------
SSS01_INDPA1_DATA    TBL_CHARGECONSENTLOG1           1,412,431,872
SSS01_MASTERD_DATA   TBL_OBJECTIVESTREAMING01LOG1    1,046,478,848
SSS01_MASTERD_DATA   IXT2_OBJECTIVESTREAMING01LOG1     745,537,536
SSS01_MASTERD_DATA   TBL_MEDIAAPPITEMLOG1              732,954,624
SSS01_MASTERD_DATA   TBL_OBJECTIVESTREAMING01LOG3      495,976,448
SSS01_MASTERD_DATA   TBL_OBJSTREAMING01LOG1_1201       490,733,568
SSS01_MASTERD_DATA   TBL_OBJSTREAMING01LOG1_0101       475,004,928
SSS01_MASTERD_DATA   TBL_OBJSTREAMING01LOG1_0201       452,984,832
SSS01_MASTERD_DATA   TBL_CCACCESS1                     418,381,824
SSS01_MASTERD_DATA   TBL_OBJSTREAMING01LOG1_1101       387,973,120
SSS01_MASTERD_DATA   IXT2_OBJECTIVESTREAMING01LOG3     353,370,112
FUKA_I_DATA          TBL_OBJECTIVESTREAMING011         344,981,504
SSS01_MASTERD_DATA   TBL_MEDIABAGLOG1                  342,884,352
FUKA_I_DATA          TBL_DAILYTEXTMACHINEUPLOAD1       319,815,680
FUKA_I_DATA          TBL_USERSESSION1                  318,767,104
FUKA_I_DATA          TBL_DAILYTEXTTOTALUPLOAD1         279,969,792
SSS01_I_DATA         TBL_USERSESSION1                  264,241,152
FUKA_I_DATA          TBL_CCQUIZ_USER_RESULT1           244,318,208
FUKA_I_IDX           IXT1_DAILYTEXTTOTALUPLOAD1        236,978,176
SSS01_MASTERD_DATA   TBL_OBJSTREAMING01LOG3_0201       227,540,992

共有プールの利用状況を確認する

負荷特性の異なる処理を混在させている場合、共有プールの変化をウォッチしておくとボトルネックを見つけられることがあります。

sql area   ・・・   SQL文そのもののキャッシュ
CCursor/PCursor   ・・・   カーソルキャッシュ
library cache   ・・・   コンパイル済SQLのキャッシュ
free memory   ・・・   空きメモリ

set pages 10000
set lines 100
set time on
col name for a30
col bytes for 999,999,999,999
select name,bytes from (
 select row_number() over (order by bytes desc) as rn,name,bytes from v$sgastat
 where pool = 'shared pool'
) where rn <= 20;

NAME                                      BYTES
------------------------------ ----------------
sql area                          2,484,421,928
free memory                       1,734,338,672
CCursor                             678,436,864
PCursor                             472,727,184
library cache                       352,787,688
sql area:PLSQL                      346,800,984
kglsim object batch                 182,272,608
gcs resources                       128,586,016
kglsim heap                         104,469,120
Cursor Stats                         98,273,576
gcs shadows                          76,999,136
db_block_hash_buckets                47,185,920
ASH buffers                          30,408,704
ges enqueues                         19,842,976
ges big msg buffers                  15,936,168
trace buffer                         14,057,472
KGLS heap                            13,290,704
event statistics per sess            12,296,000
FileOpenBlock                        11,575,096
ges resource                         11,342,888

似たSQLが多いWebサイト処理と、SQL数が多くなりがちな大量更新バッチを混在させていると次のグラフのようになることがあります。 バッチを実行していない時はfreeがかなり多いためボトルネックを見つけづらいと思います。

ライブラリキャッシュの利用状況を確認する

SQLチューニングをほとんどしていない環境では、SQLの本数が多すぎることがあります。
その場合、データバッファキャッシュよりライブラリキャッシュがボトルネックになるかもしれません。

v$librarycacheのライブラリキャッシュ情報をウォッチするといいでしょう。

ヒット率は95%以上が理想です。
SQLの本数が多い場合、ラッチ（キャッシュのロック）がボトルネックになるのでgethitratioを注視すべきです。

gethitratio・・・ラッチの取得率
pinhitratio・・・キャッシュ（のデータ）の取得率。

set time on
set pages 10000
set lines 120
col namespace for a20
select namespace,gethitratio,pinhitratio from v$librarycache;

実行例は次の通り。

NAMESPACE            GETHITRATIO PINHITRATIO
-------------------- ----------- -----------
SQL AREA              .872027894  .981766737
TABLE/PROCEDURE       .994262842   .97704901
BODY                  .999516938  .998802497
TRIGGER               .915954808  .921656241
INDEX                 .692952564  .828487881
CLUSTER               .990343307  .990707854
OBJECT                         1           1
PIPE                           1           1
JAVA SOURCE           .194444444  .236842105
JAVA RESOURCE         .194444444  .236842105
JAVA DATA                      1           1

なお、10gR2(?)では、たまにhitratioが1000%を超えたりほぼ0%になる不具合(?)があります。

パフォーマンスツールSTATSPACKの利用

STATSPACKは、定期的に取得したスナップショットからDBのパフォーマンス値を取得するツールです。
スナップショットの差分により、v$sysstatなどのように累積値で記録している値の動きをとらえることができます。

1.STATSPACK用表領域の作成

CREATE TABLESPACE PERFSTAT_DATA
  DATAFILE '/DATA/DATABASE/PERFSTAT_DATA.DBF' SIZE 1024M REUSE AUTOEXTEND ON
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  SEGMENT SPACE MANAGEMENT AUTO;

2.STATSPACKのインストール
インストール用のコマンドで作成します。

$ sqlplus sys as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
default_tablespaceに値を入力してください: PERFSTAT_DATA　★作成した表領域名
Using tablespace PERFSTAT_DATA as PERFSTAT default tablespace.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME
--------------------------------------------------------------------------------
CONTENTS                    DB DEFAULT TEMP TABLESPACE
--------------------------- --------------------------
TEMP
TEMPORARY                   *

temporary_tablespaceに値を入力してください: TEMP

3.スナップショットの作成
スナップショット間隔が短いほど精度は上がります。
ただし、スナップショットの作成には負荷がかかるので1時間ごとが目安です。

$ sqlplus perfstat@SSS01DB
SQL> execute statspack.snap

セグメント（テーブル等）の情報も欲しい時は、スナップショットレベルを7以上にします。
SQL> execute statspack.snap(i_snap_level => 7) 

4.スナップショットの確認
STATSPACKはスナップショットの「差分」で生成するので、スナップショットが2つ以上必要です。

SQL> select snap_id, snap_time from stats$snapshot;

 SNAP_ID   SNAP_TIM
 --------- -----------------
         1 09-10-27 11:50:02
         2 09-10-27 11:50:18

5.STATSPACKレポートの作成
レポート出力用のコマンドを使ってレポートを作成します。

$ sqlplus perfstat@SSS01DB
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql;
Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  190003545 SSS01DEV            1 sss01dev
                      ：
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
sss01dev     SSS01DEV             1 27 10月 2009 15:2     5
                                    7
                                  2 27 10月 2009 15:3     5
                                    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 1　★開始スナップショットID
Begin Snapshot Id specified: 1

end_snapに値を入力してください: 2　★終了スナップショットID
End   Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press  to continue, otherwise enter an alternative.

report_nameに値を入力してください: 　★改行あるいは任意のファイル名

6.スナップショットの削除
スナップショットは削除しないと貯まり続けるので、適度に削除します。
スナップショット単位で削除すると負荷上昇やUNDO消費の原因になるので注意が必要です。

6-1.スナップショット単位で削除

$ sqlplus perfstat@SSS01DB
SQL> @?/rdbms/admin/sppurge
（削除開始スナップID）：1
（削除終了スナップID）：2

6-2.まるごと削除

$ sqlplus perfstat@SSS01DB
SQL> @?/rdbms/admin/sptrunc

SQL> select count(*) from stats$snapshot;
→ゼロ件であることを確認できます。