ORACLE STATSPACK
0: Introduction and Terminology
Baseline
statspack collects more data,eg :high resource SQL
statspack separates the data collection from the report generation;
permanent tables owned by PERFSTAT store performance statistics;
statspack pre-calculates many ratio useful when performance tuning;
data collection is easy to automate using either dbms_job or OS utility;
How does Statspack work?
user:PERFSTAT ,owns all objects needed by this package
1: EM,Automatic Workload Repository(AWR) and Statspack
2:Statspack Configuration
2.1: Database Space Requirements
Space Requirements
Using Locally Managed Tablespaces
2.2:Installing the Tool
SYSAUX is the default tablespace
SQL>conn / as sysdba
SQL>@?/rdbms/admin/spcreat
1,spcusr.sql
2,spctab.sql
3,spcpkg.sql
2.3Errors
SQL>@spdrop
SQL>@spcreate
3:Gathering data - taking a snapshot
conn prefstat/admin
execute statspack.snap
SQL> variable snap number;
SQL> begin :snap :=statspack.snap; end;
SQL> /
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
---------
12
3.1 Automating Statspack statistics gathering
dbms_job
cron or at
3.2 Using dbms_job
spauto.sql
job_queue_processes greater than 0 for the job to run automatically.
Changing the interval of statistics collection
execute dbms_job.interval(1,'SYSDATE+(1/48)');
To force the job to run immediately,
execute dbms_job.run();
To remove the auto collect job,
exectute dbms_job.remove();
4:Running the performance reports
Instance report
SQL report
4.1 Running the instance report
conn prefstat/admin
@?/rdbms/admin/spreport
define num_days=70
==================================================================
德哥statspack
install
专门的表空间
ORA-01659: 无法分配超出 7 的 MINEXTENTS (在表空间 PERFSTAT 中)
主要原因是表空间不够,将其设置为自动扩展即可。
alter database datafile 'D:\oracle\product\10.2.0\oradata\oracle\perfstat.dbf' autoextend on;
或者可以在创建perfstat表空间的时候进行设置：
create tablespace perfstat
datafile 'D:\oracle\product\10.2.0\oradata\oracle\perfstat.dbf' size 100m autoextend on
extend management local;
vi spcpkg.lis 查看错误信息
Capture snapshot:
Manual snap
automatic snap:
dbms_job
operation system command
conn perfstat/admin;
execute statspack.snap
select * from stats$statspack_parameter;
select * from stats$snapshot;
select job from user_jobs;
select * from user_jobs;
execute dbms_job.interval(61,'SYSDATE+1/(24*60)');
COMMIT;
select * from stats$snapshot;
report:
instance report
@?/rdbms/admin/spreport
sprepins.sql(multi-instance)
sql report
@?/rdbms/admin/sprepsql
多实例中查看单个SQL的情况：sprsqins.sql
Configure:
database parameter:
show parameter statistics
statistice_level(alter session,alter system)basic,typical,all
timed_statistics(和上面那一个参数是联动的) 一定要设置为TRUE
timed_os_statistics
snapshot parameter:
snapshot level :
0 ,general performance statistics
5 ,包括0级信息之外，SQL语句信息（也是有条件限制的）
6 ,SQL Plan ,SQL Plan usage
7 ,segment level statistics
logical reads
physical reads
buffer busy waits
ITL waits
row lock waits
global cache cr blocks served *
global cache current blocks served *
10 ,latch 要ORACLE 工程师支持的时候再做的。
怎么修改？
1，用包来修改：
desc stats$statspack_parameter
select snap_level from stat$statspack_parameter;
execute statspack.snap(i_snap_level=>7,i_modify_parameter=>'true');
select snap_level from stat$statspack_parameter;
2,execute statspack.modify_statspack_parameter(i_snap_level=>6)
Instance parameter:
sprepcon.sql
改变之前一定要备份
SQL report parameter:
sprepcon.sql
maintenance:
make_baseline && clear_baseline
purge snapshot except baseline
truncate all tables with perfstat schema
先做EXPORT
sptrunc.sql
drop perfstat schema
先做EXPORT
spdrop.sql 要用SYSDBA来做
