个人QQ:149196034 欢迎联系

[oracle@DELL admin]$ pwd
/oracle/product/database/rdbms/admin

SQL> select instance_name,host_name,version,startup_time from v$instance;

INSTANCE_NAME HOST_NAME VERSION

STARTUP_TIME
---------------- ---------------------------------------------------------------- ------------

----- ------------
mis DELL 9.2.0.6.0

2010-9-2 12:

1:修改2个参数

SQL> show parameter job_queue_processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0

alter system set job_queue_processes=6 scope=memory;
System altered

SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 6

SQL> show parameter timed_statistics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE

3：

创建表空间perfstat
首先查看数据文件存放地点

select file_name from dba_data_files;

SQL> select file_name from dba_data_files;
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle/oradata/mis/mis12.dbf
/oracle/oradata/mis/misidx01.dbf
/oracle/oradata/mis/temp02.dbf
/oracle/oradata/mis/mis13.dbf
/oracle/oradata/mis/misidx02.dbf
/oracle/oradata/mis/discover02.dbf

SQL> create tablespace perfstat
2 datafile
3 '/oracle/oradata/mis/perfstat01.dbf' size 1024m
4 extent management local;

Tablespace created

5：安装statspack
必须以sysdba角色创建。

a:查看脚本
[oracle@DELL admin]$ pwd
/oracle/product/database/rdbms/admin

[oracle@DELL admin]$ ls |grep sp
[oracle@DELL admin]$ ls|grep sp
spauto.sql spctab.sql spdrop.sql sppurge.sql sprepsql.sql spup816.sql
spcpkg.sql spcusr.sql spdtab.sql sprepins.sql sptrunc.sql spup817.sql
spcreate.sql spdoc.txt spdusr.sql spreport.sql spuexp.par spup90.sql

b:安装脚本：spcreate.sql

需要输入用户password,default tablespace,temporary_tablespace,
cd $ORACLE_HOME/rdbms/admin/
@spcreate.sql

Specify PERFSTAT password
Enter value for perfstat_password: pengpeng
pengpeng

Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: perfstat

Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: temp101
.......
Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
出现上面几行说明创建成功，而且没有报错！

6:
测试
SQL> execute statspack.SNAP;

PL/SQL procedure successfully completed

SQL> execute statspack.SNAP;

PL/SQL procedure successfully completed

7：
规划自动采样，1个小时一次
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc

(SYSDATE+1/24,''HH'')', TRUE, :instno);

SQL> @spauto

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 TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 6

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- --------- ----------------
21 04-NOV-10 16:00:00

7:
确定是否自动采样，以sysdba角色登陆

SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

JOB LOG_USER PRIV_USER LAST_DATE NEXT_DATE

INTERVAL
---------- ------------------------------ ------------------------------ ----------- ---------

-- --------------------------------------------------------------------------------
21 SYS SYS 2010-11-4

1 trunc(SYSDATE+1/24,'HH')

移除定时任务
execute dbms_job.remove('21')

SQL> execute dbms_job.remove('21');

PL/SQL procedure successfully completed.

完成一个采样报告，应该及时移除这个JOB任务！如果statspack运行一个星期，采样数据量是非常惊人的。

8：
删除历史数据
select snap_id from stats$snapshot;
SQL> select snap_id from stats$snapshot;

SNAP_ID
-------
1
2

delete from stats$snapshot where snap_id<=100; (输入要删除的小于snap_id)

或者执行脚本：
sptrunc.sql

9:
statspack收集门限
level 0,5,10
level 0 包括等待事件、系统时间、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等。
level 5 增加SQL语句。收集结果在stat$sql_summary中。
level 10 增加了子锁存统计。子锁存在stat$lathc_children

sql>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
本次修改有效：
则
sql>execute statspack.snap(i_snap_level=>10)

[@more@]