oracle statspack学习（一）(三)
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
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> !more spcpkg.lis
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> !
至此statspack创建完成
创建完成之后可以看到
数据库中多了一个PERFSTAT 用户。
2）创建自动收集系统性能信息的job
SQL> begin
2 select instance_number into :instno from v$instance;
3 dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
4 commit;
5 end;
6 /
使用spauto.sql脚本进行创建。
注意 此时为每一个小时运行一次，我们可以改成每半个小时运行一次为trunc（sysdate+1/48,'M')，每五分钟运行一次trunc（sysdate+/288,'MI')等等
SQL> conn perfstat/amy
Connected.
SQL> select job,priv_user,what from user_jobs;
SQL> set linesize 200
SQL> r
1* select job,priv_user,what from user_jobs
JOB PRIV_USER WHAT
---------- ------------------------------ --------------------------------------------------
23 PERFSTAT statspack.snap;
3）手动执行生成快照。
我们可以使用如下方式，手动执行快照收集，而不在等待job运行
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
4）删除该job
注意：删除oracle的job只能是在该用户下才能删除本用户的job，及时具有dba权限也不能删除其他用户的job。
SQL> conn sys/root as sysdba
Connected.
SQL> select job,priv_user from dba_jobs;
JOB PRIV_USER
---------- ------------------------------
4001 APEX_030200
4002 APEX_030200
3 SYSMAN
23 PERFSTAT
4 rows selected.
SQL> execute dbms_job.remove('23');
BEGIN dbms_job.remove('23'); END;
*
ERROR at line 1:
ORA-23421: job number 23 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1
SQL> conn perfstat/amy
Connected.
SQL> execute dbms_job.remove('23');
PL/SQL procedure successfully completed.
SQL> select job,priv_user from user_jobs;
no rows selected
5）生成快照信息；
我们可以使用spreport.sql来生成快照信息：
在这个过程中其实生成的信息都是在statspack表空间中的各个表的信息收集的到的。
SQL> @ /rdbms/admin/spreport
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ -----------