ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用Oracle Statpack的具体步骤

使用Oracle Statpack的具体步骤

原创 Linux操作系统 作者:mengzhaoliang 时间:2008-12-12 20:58:16 0 删除 编辑

环境:
操作系统: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-30

  • 博文量
    335
  • 访问量
    2938430