oracle_statspack
时间：2010-11-09 来源：forhope315
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(<job number>); To remove the auto collect job, exectute dbms_job.remove(<job number>); 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来做
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(<job number>); To remove the auto collect job, exectute dbms_job.remove(<job number>); 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来做
相关阅读 更多 +
