环境:
操作系统:AIX5.3(64位)
Oracle版本:Oracle10.2.1.0.1(64位)
使用Statpack的具体步骤
Oracle Statspack是用来诊断Oracle数据库性能的强有力的工具。通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在,记录数据库性能状态,也可以使远程技术支持人员迅速了解数据库运行状况。
1、telnet到远程的数据库服务器
CMD>telnet 远程数据库的IP
2、切换到数据库的用户
DQXXDBS01:/> su - oraoms
3、用超级用户进入数据库
$ sqlplus "/as sysdba"
4、查看参数,是否可以用job(可以用job进行自动的收集Statpack Report的数据)
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ----------
job_queue_processes integer 10
5、该参数可以收集操作系统的信息
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- --------
timed_statistics boolean TRUE
6、为了创建表空间,表空间的数据文件放在哪比较好
SQL> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------
/oracle/oms/oradata/sysdata/system01.dbf
/oracle/oms/oradata/undo/undotbs01.dbf
/oracle/oms/oradata/sysdata/sysaux01.dbf
/oracle/oms/oradata/sysdata/users01.dbf
/oracle/oms/oradata/sysdata/RMAN_data000.dbf
/oracle/oms/oradata/pub/Pub_Norm_data000.dbf
......
21 rows selected.
7、查看哪个目录的空间比较大,确定perfstat表空间的数据文件的位置
$ df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/gpelv 64.00 33.99 47% 6 1% /oracle/oms/oradata/gpe
/dev/jbpmlv 5.00 2.46 51% 5 1% /oracle/oms/oradata/jbpm
/dev/loglv 175.00 144.97 18% 6 1% /oracle/oms/oradata/log
/dev/undolv 30.00 29.53 2% 7 1% /oracle/oms/oradata/undo
/dev/wpslv 10.00 2.48 76% 5 1% /oracle/oms/oradata/wps
/dev/redolv 1.00 0.41 59% 8 1% /oracle/oms/redolog
......
8、创建表空间:
SQL> create tablespace perfstat
2 datafile '/oracle/oms/oradata/temp/perfstat.dbf'
3 size 500M;
Tablespace created.
9、创建Statspack需要的脚本
SQL> @/oracle/oms/102_64/rdbms/admin/spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: (可以输入perfstat,便于记忆)
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: perfstat
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
Pressing will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: temp
10、测试是否成功,生成数据库的一个快照
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
11、再生成一个快照
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
12、取两个快照之间时间段的Statspack报告
SQL> @/oracle/oms/102_64/rdbms/admin/spreport.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- ------------------
DQOMS DQOMS 1 12 Dec 2008 11:53 5
2 12 Dec 2008 11:54 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 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.
Enter value for report_name: (输入要生产文件的名称:20081212Statspack)
13、用job进行定时产生Statspack的快照,便于收集数据(默认为1小时产生一个快照)
SQL> @/oracle/oms/102_64/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
21
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
job_queue_processes
integer
10
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- ------------ ----------------
21 12-DEC-08 12:00:00
11、当最好生成需要的Statspack之后,记住移除任务,查看任务:
SQL> select job, log_user, priv_user, last_date,next_date, interval from user_jobs;
12、移除生成Statspack快照的任务:
SQL> execute dbms_job.remove('21')
PL/SQL procedure successfully completed
完毕!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-510231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-510231/