个人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)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22934571/viewspace-1040919/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22934571/viewspace-1040919/