安装statspcak

SQL> create tablespace STATSPACK datafile '/oracle9idata/MGSSFCS/statspack01.dbf' size 500M;

Tablespace created.

sql>@$ORACLE_HOME/rdbms/admin/spcreate --安装

删除：

sql>@$ORACLE_HOME/rdbms/admin/spdrop

SQL> exec statspack.snap;
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1

这个问题只会出现在多位的字符集, 需要修改spcpkg.sql 脚本，$ORACLE_HOME/rdbms/admin/spcpkg.sql，将"substr" 修改为 "substrb"，然后重新运行该脚本。

sql>@$ORACLE_HOME/rdbms/admin/spcpkg.sql

收集信息

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

生成报告

sql>@$ORACLE_HOME/rdbms/admin/spreport

利用计划任务自动采集数据

sql>host vi $ORACLE_HOME/rdbms/admin/spauto.sql

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;

启动计划任务

sql>@$ORACLE_HOME/rdbms/admin/spauto.sql

sql>select * from user_jobs;

sql>select * from stats$snapshot; --查计划任务及执行结果

sql>@spreport

sql>exec dbms_job.remove('$JOB')

删除数据

SQL> delete from stats$snapshot where snap_id < = 166;

http://oracle.chinaitlab.com/optimize/721638_8.html

ITPub博客

ORA-06512: at "PERFSTAT.STATSPACK", line 2134

最新文章