oracle statspack report,oracle_statspack

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

operationsystem 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来做