Statspack 생성/삭제/Sanpshot생성

출처 : http://www.urbantree.wo.tc/entry/5-Using-

Statspack 생성

---

SQL> shutdown abort
SQL> startup

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
...

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

..
Enter value for perfstat_password : perfstat
Enter value for default_tablespace : enter
Enter value for temporary_tablespace : enter
..

Statspack 생성 확인

---

SQL> show user

USER is "PERFSTAT"

SQL> col object_name for a40
SQL> select object_name, object_type from user_objects order by 2;

....
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
V_$DATAGUARD_STATUS                      VIEW
V_$THREAD                                VIEW
V_$PROCESS                               VIEW
V_$BGPROCESS                             VIEW
V_$SESSION                               VIEW
V_$LICENSE                               VIEW
V_$TRANSACTION                           VIEW
V_$BSP                                   VIEW
V_$FAST_START_SERVERS                    VIEW
WEEKNIGHT_WINDOW                         WINDOW
WEEKEND_WINDOW                           WINDOW
MAINTENANCE_WINDOW_GROUP                 WINDOW GROUP
...

수동 Snapshot 생성

---

SQL> select * from stats$sga;
SQL> exec statspack.snap
SQL> col name for a40
SQL> select * from stats$sga;

   SNAP_ID       DBID INSTANCE_NUMBER NAME                                          VALUE STARTUP_T PAR VERSION
---------- ---------- --------------- ---------------------------------------- ---------- --------- --- -----------------
         1 1235361507               1 Fixed Size                                  1218992
         1 1235361507               1 Variable Size                             100664912
         1 1235361507               1 Database Buffers                          180355072
         1 1235361507               1 Redo Buffers                                2973696

4 rows selected.

SQL> variable no number
SQL> exec :no :=statspack.snap
SQL> print no


자동 Snapshot 생성

---

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
....

SQL> !more $ORACLE_HOME/rdbms/admin/spauto.sql

...
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')'
, TRUE, :instno);
  commit;
end;
/
...

Report 생성

---

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepsql.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprsqins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sptrunc.sql
...

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

...

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 15 Jan 2010 14:16     5
                                 11 15 Jan 2010 14:29     5

※ snap level 이 커질수록 내용이 더욱 자세하다

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 11
End   Snapshot Id specified: 11



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

Enter value for report_name: enter

...

End of Report ( sp_1_11.lst )

SQL> !ls sp*
SQL> !more sp_1_11.lst


Statspack 삭제

---

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdoc.txt
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdrop.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdtab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdusr.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sppurge.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
...

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