statspack 使用实例

--1.´´½¨ perfstat ±í¿Õ¼ä

create  tablespace  perfstat datafile  '/home/orapaid/oradata/prdyp/perfstat.dbf'  size 500m;

--2.ÒÑsysµÇ½ִÐÐÏÂÁнű¾
@/home/orapaid/product/92/rdbms/admin/catdbsyn.sql
@/home/orapaid/product/92/rdbms/admin/dbmspool.sql

--3.ÔËÐа²×°½Å±¾
--ÒÑsysÓû§µÇ¼

select instance_name,host_name,version,startup_time from v$instance;

select file_name from dba_data_files;

create tablespace perfstat  datafile '/home/orapaid/oradata/prdyp/perfstat.dbf'  size 500M;

--°²×°Ç°Òª×öµÄÊÂ
Ò». ϵͳ²ÎÊý   
ΪÁËÄܹ»Ë³Àû°²×°ºÍÔËÐÐStatspackÄã¿ÉÄÜÐèÒªÉèÖÃÒÔÏÂϵͳ²ÎÊý£º 
1. job_queue_processes 
ΪÁËÄܹ»½¨Á¢×Ô¶¯ÈÎÎñ£¬Ö´ÐÐÊý¾ÝÊÕ¼¯£¬¸Ã²ÎÊýÐèÒª´óÓÚ0¡£Äã¿ÉÒÔÔÚ³õÊÔ»¯²ÎÊýÎļþÖÐÐ޸ĸòÎÊý¡£
Ð޸ĴËĿ¼ÏÂ/home/orapaid/admin/prdyp/pfile µÄ.oraÎļþ  ÐèÒªÖØÆôÊý¾Ý¿â

2. timed_statistics 
ÊÕ¼¯²Ù×÷ϵͳµÄ¼ÆʱÐÅÏ¢£¬ÕâЩÐÅÏ¢¿É±»ÓÃÀ´ÏÔʾʱ¼äµÈͳ¼ÆÐÅÏ¢¡¢ÓÅ»¯Êý¾Ý¿âºÍ SQL Óï¾ä¡£Òª·ÀÖ¹Òò´Ó²Ù×÷ϵͳÇëÇóʱ¼ä¶øÒýÆðµÄ¿ªÏú£¬Ç뽫¸ÃÖµÉèÖÃΪFalse¡£ 
ʹÓÃstatspackÊÕ¼¯Í³¼ÆÐÅϢʱ½¨Ò齫¸ÃÖµÉèÖÃΪ TRUE£¬·ñÔòÊÕ¼¯µÄͳ¼ÆÐÅÏ¢´óÔ¼Ö»ÄÜÆðµ½10%µÄ×÷Ó㬽«timed_statisticsÉèÖÃΪTrueËù´øÀ´µÄÐÔÄÜÓ°ÏìÓëºÃ´¦Ïà±ÈÊÇ΢²»×ãµÀµÄ¡£ 
¸Ã²ÎÊýʹÊÕ¼¯µÄʱ¼äÐÅÏ¢´æ´¢ÔÚÔÚV$SESSTATS ºÍV$SYSSTATS ¶¯Ì¬ÐÔÄÜÊÓͼÖС£ 

Timed_statistics²ÎÊý¿ÉÒÔÔÚʵÀý¼¶½øÐиü¸Ä 

SQL> alter system set timed_statistics = true; 
System altered 

alter system set timed_statistics = false;

--°²×°Ç°²é¿´´ËĿ¼ÏµÄÎļþ
sql>host dir sp*

oracle 8i Ö´ÐÐ $ORACLE_HOME/rdbms/admin/statscre.sql
oracle 9i Ö´ÐÐ $ORACLE_HOME/rdbms/admin/spcreate.sql

@/home/newvers/product/92/rdbms/admin/spcreate

--°²×°ºó²é¿´´ËĿ¼ÏµÄÎļþ(Ó¦¸Ã¶àÁ˼¸¸ö.lisÎļþ)
sql>host dir sp* 

sql>host find ¡°ORA-¡° *.lis

sql>host find "err" *.lis

--ÔÚUNIXÉÏ£¬Äã¿ÉÒÔͨ¹ýÒÔÏÂÃüÁî²é¿´ÏàÓ¦µÄ´íÎóÐÅÏ¢ 

$ ls *.lis

$ grep ORA- *.lis 
$ grep err *.lis   

ÔÚÕâÒ»²½£¬Èç¹û³öÏÖ´íÎó£¬ÄÇôÄã¿ÉÒÔÔËÐÐspdrop.sql½Å±¾À´É¾³ýÕâЩ¶ÔÏó¡£È»ºóÖØÐÂÔËÐÐspcreate.sqlÀ´´´½¨ÕâЩ¶ÔÏó¡£ÔËÐÐ SQL*Plus, ÒÔ¾ßÓÐSYSDBA ȨÏÞµÄÓû§µÇ½£º 

/*

Èý. ²âÊÔ°²×°ºÃµÄStatspack 
ÔËÐÐstatspack.snap¿ÉÒÔ²úÉúϵͳ¿ìÕÕ£¬ÔËÐÐÁ½´Î£¬È»ºóÖ´ÐÐspreport.sql¾Í¿ÉÒÔÉú³ÉÒ»¸ö»ùÓÚÁ½¸öʱ¼äµãµÄ±¨¸æ¡£ 
Èç¹ûÒ»ÇÐÕý³££¬ËµÃ÷°²×°³É¹¦¡£

ÒÑ perfstat/perfstatµÇ½
*/

SQL>execute statspack.snap

SQL>execute statspack.snap

SQL>@/home/newvers/product/92/rdbms/admin/spreport

 

SQL>@/home/newvers/product/92/rdbms/admin/spauto

--ËÄ.ʹstatspack×Ô¶¯ÊÕ¼¯ÏµÍ³×´¿ö

alert system set job_queue_processes = 10;

alert system set job_queue_processes = 10  scope=both;

alter system set Timed_statistics=true;

[orapaid@bj37 admin]$ cat spauto.sql
Rem
Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
Rem
Rem spauto.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      spauto.sql
Rem
Rem    DESCRIPTION
Rem      SQL*PLUS command file to automate the collection of STATPACK
Rem      statistics.
Rem
Rem    NOTES
Rem      Should be run as the STATSPACK owner, PERFSTAT.
Rem      Requires job_queue_processes init.ora parameter to be
Rem      set to a number >0 before automatic statistics gathering
Rem      will run.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    12/06/99 - 1059172, 1103031
Rem    cdialeri    08/13/99 - Created
Rem

spool spauto.lis

--
--  Schedule a snapshot to be run on this instance every hour, on the hour

variable jobno number;
variable instno number;
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;
/

prompt
prompt  Job number for automated statistics collection for this instance
prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt  Note that this job number is needed when modifying or removing
prompt  the job:
print jobno

prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt  Below is the current setting of the job_queue_processes init.ora
prompt  parameter - the value for this parameter must be greater
prompt  than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt

prompt
prompt  Next scheduled run
prompt  ~~~~~~~~~~~~~~~~~~
prompt  The next scheduled run for this job is:
select job, next_date, next_sec
  from user_jobs
 where job = :jobno;

spool off;

--Îå.Éú³É·ÖÎö±¨¸æ
µ÷ÓÃ@/home/orapaid/product/92/rdbms/admin/spreportÉú³É

´Ë¹ý³ÌÖÐÒªÊäÈ뿪ʼ¿ìÕÕ  ºÍ ÖÕÖ¹¿ìÕÕ ±àºÅ

--Áù ÒƳý¶¨Ê±ÈÎÎñ
SQL>execute   dbms_job.remove('job_id');

--Æß É¾³ýÀúÊ·Êý¾Ý

ɾ³ýstats$snapshot±íÖÐÊý¾Ý  ,ÆäËû±íÖеÄÊý¾Ý»áÏàÓ¦µÄ¼¶Á¬É¾³ý

oracleÌṩÁËÓÃÓÚ truncate  ÕâЩͳ¼ÆÐÅÏ¢±íµÄ  ½Å±¾

[orapaid@bj37 admin]$ cat sptrunc.sql
Rem
Rem $Header: sptrunc.sql 19-feb-2002.11:36:28 vbarrier Exp $
Rem
Rem sptrunc.sql
Rem
Rem Copyright (c) 2000, 2002, Oracle Corporation.  All rights reserved. 
Rem
Rem    NAME
Rem      sptrunc.sql - STATSPACK - Truncate tables
Rem
Rem    DESCRIPTION
Rem      Truncates data in Statspack tables
Rem
Rem    NOTES
Rem      Should be run as STATSPACK user, PERFSTAT.
Rem
Rem      The following tables should NOT be truncated
Rem        STATS$LEVEL_DESCRIPTION
Rem        STATS$IDLE_EVENT
Rem        STATS$STATSPACK_PARAMETER
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    vbarrier    03/05/02 - Segment Statistics
Rem    cdialeri    04/13/01 - 9.0
Rem    cdialeri    09/12/00 - sp_1404195
Rem    cdialeri    04/11/00 - 1261813
Rem    cdialeri    03/15/00 - Created
Rem

undefine anystring
set showmode off echo off;
whenever sqlerror exit;

spool sptrunc.lis

/* ------------------------------------------------------------------------- */

prompt
prompt Warning
prompt ~~~~~~~
prompt Running sptrunc.sql removes ALL data from Statspack tables.  You may
prompt wish to export the data before continuing.
prompt
prompt
prompt About to Truncate Statspack Tables
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt If you would like to continue, press <return>
prompt
prompt
prompt &return Entered - starting truncate operation

truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUE_STAT;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
truncate table STATS$RESOURCE_LIMIT;
truncate table STATS$DLM_MISC;
truncate table STATS$UNDOSTAT;
truncate table STATS$SQL_PLAN;
truncate table STATS$SQL_PLAN_USAGE;
truncate table STATS$SEG_STAT;
truncate table STATS$SEG_STAT_OBJ;
truncate table STATS$DB_CACHE_ADVICE;
truncate table STATS$PGASTAT;
truncate table STATS$INSTANCE_RECOVERY;

delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;

commit;

Rem This is required to allow further snapshots to work without
Rem recreating package or restarting the instance
alter package statspack compile;

prompt
prompt Truncate operation complete
prompt

/* ------------------------------------------------------------------------- */

spool off;

whenever sqlerror continue;
set echo on;
[orapaid@bj37 admin]$

--¾Åµ÷ÕûstatspackµÄÊÕ¼¯ÃÅÏÞ

SQL>execute statspack.snap(i_snap_level=>0 ,i_modify_parameter=>'true');

SQL>execute statspack.snap(i_snap_level=>10);

SQL>execute statspack.snap(i_snap_level=>5);

 

--ͨ¹ýÏÂÁÐÓï¾äÐÞ¸ÄÃÅÏÞµÄĬÈÏÖµ

SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);

--10 ÕûÀí·ÖÎö±¨¸æ

--11ÓÃshell  Éú³ÉÐÔÄÜ·ÖÎö±¨¸æ(Ò²¿ÉÒÔÊÖ¹¤Éú³É)

ORACLE_SID=$ORACLE_SID
EXPORT   ORACLE_SID
ORACLE_HOME='cat /etc/oratab|grep  ^$ORACLE_SID:|cut  -f2  -d':'
export  ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
EXPORT   PATH

echo  "please  enter   the  number  of seconds  between snapshots."
read   elapsed

$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!
execute statspack.snap;
exit
!

sleep   $elapsed
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!
execute statspack.snap;

select name,snap_id,to_char(start_time,'yyyymmdd:hh24:mi:ss')
from
stats\$snapshot  ,v\$database

where   snap_id>(select max(snap_id)-2  from  stats\$snapshot);

--stats$sql_statistics  ÊÓͼ
--Õâ¸öÊÓͼÓÃÓÚͳ¼Æ ϵͳSQLµÄ×Ü¿ªÏúÒÔ¼°SQL±»ÖØÓõİٷֱÈÌرðÓÐÓÃ

set   lines   80;
set   pages   999;
column  mydate  heading  'Yr.Mo Dy  Hr'  format  a16;
column  c1  heading  'Tot  SQL'  format  999,999,999;
column  c2  heading  'SINGLE USE  SQL'  format  999,999;
column  c3  heading  'Percent   re_used  SQL'  format  999,999;
column  c4  heading  'TOTAL  SQL  RAM'  format  999,999,999;

break   on   mydate   skip 2;

select to_char(snap_time,'yyyy-mm-dd  hh24)  mydate,
total_sql     c1,
single_use_sql  c2,
(single_use_sql/total_sql)*100  c3,
total_sql_mem  c4
from    stats$sql_statistics  sq,
stats$snapshot    sn
where   sn.snap_id=sq.snap_id;

--oracle 8i     stats$sqltextÓÃÀ´É¾³ýstats_sql_summary±íÏà¹ØµÄ¾Þ´ó´æ´¢¿ªÏú.
--oracle 9i     stats$sqltext(Ö»´æ´¢sqlÔ´´úÂë)

select * from  stats$sql_text

--stats$latch_misses_summary  ¼Ç¼ÁËoracleÊý¾Ý¿âµÄËø´æʧ°Ü

select * from stats$latch_misses_summary
--Éú³ÉËø´æ±¨¸æ

select sn.snap_time ,sl.parent_name,sl.where_in_code,sum(nwfail_count) sum_nwfail, sum(sleep_count)  sum_sleep 
 from stats$latch_misses_summary sl  ,stats$snapshot    sn
where sn.snap_id=sl.snap_id  group by sn.snap_time,sl.parent_name,sl.where_in_code ;

--stats$sql_summary ±í

--sqlͳ¼Æ»ã×ÜÊÇstatspack ¹¤¾ßÖÐ×îÖØÒªµÄ±íÖ®Ò».
--sqlµ÷Õû¾­³£¿ÉÒÔ¼«´óµÄÓ°ÏìoracleϵͳµÄÐÔÄÜ

 

--stats$parameter

select * from stats$parameter  where name like '%optimizer_mode%'

****************************************************************
STATSPACKϵͳ±í

stats$rollstat

stats$latch

select * from stats$latch_children

select * from stats$librarycache(¿â¸ßËÙ»º´æ±íÏîµÄÃüÖÐÂʶ¼Òª±£³ÖÔÚ90%ÒÔÉÏ  ·ñÔòÒª¶ÔSGA   oracle ¹²Ïí³Ø ½øÐе÷Õû)

select * from stats$waitstat  where wait_count>0
Åж϶ÔÏóÊÇ·ñ²»ÕýÈ·µÄ´æ´¢²ÎÊýÉèÖà ,×îºÃµÄ;¾¶Ö®Ò»¾ÍÊǹ۲ì×ÔÓɱíµÈ´ý
Èç¹û×ÔÓɱíµÈ´ý·Ç³£¸ß,¾Í˵Ã÷ËùÓµÓеıí´æÔÚ¾ºÕùÐÔµÄinert»òÕßupdateÈÎÎñ
ÕâЩ±íûÓж¨Òå×ã¹»µÄ×ÔÓɱí

select * from stats$enqueue_stat
select * from stats$enqueue_stat where failed_req#>0
·ÖÎöstats$enqueue_stat ±íµÄʱºò  ºÜÖØÒªµÄÒ»µã¾ÍÊÇÒª¼Çס¶ÓÁеȴýÊÇoracle´¦ÀíµÄÕý³£²¿·Ö

select * from stats$sysstat

select * from stats$sesstat

select * from v$statname

select * from stats$sgastat
***************************************************************************
statspackÊÂÎñ±í
stats$buffer_pool_statistics 

select * from stats$buffer_pool_statistics
Õâ¸ö±íÊÇ»º³å³ØЧÂʵÄÒ»°ã¶ÈÁ¿

select * from stats$filestatxs
stats$filestatxs ÊǹØÓÚoracle µ÷ÕûµÄ×îÖØÒªµÄ±íÖ®Ò»
°üÀ¨oracleÊý¾ÝÎļþµÄÏêϸÐÅÏ¢,°üÀ¨¶ÁÈëIOÊýÁ¿  ,дÈëIOÊýÁ¿ ÒÔ¼°´¦Àí¹ý³Ì¾­ÀúµÄµÈ´ýÕùÓÃ

I/O×Óϵͳ¸ºÔØƽºâ
ÕÒµ½"Èȵã"ÎļþÒÔ¼°Èȵã±í
ÕÒµ½Êý¾Ý¿â¶ÁÈëºÍдÈë»î¶¯µÄ·åֵʼþ

****************************************************
statspackʼþ±í

select * from stats$system_event

select * from stats$session_event

select * from stats$idle_event

select * from stats$bg_event_summary 
stats$bg_event_summary  »ã×ÜÁËËùÓÐÊý¾Ý¿âʵÀýµÄºǫ́ʼþ
ºÍstats$system_event

 

******************************************************************************************************************

--À©Õ¹statspack ÊÕ¼¯·þÎñÆ÷ͳ¼Æ
--ÿ¸ô2Ãë ¹²ÊÕ¼¯5´Î

[newvers@bj37 newvers]$ vmstat 2 5
   procs                      memory      swap          io     system      cpu
 r  b  w   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id
 0  0  0 118136  49908  98268 1668004    0    0     1     0    0     0  0  0  1
 0  0  0 118136  51472  98276 1668012    0    0     4    88  177   790  2  0 97
 0  0  0 118136  51468  98276 1668012    0    0     0    24  116    39  0  0 100
 0  0  0 118136  49608  98276 1668092    0    0    30   138  286  1144  4  1 95
 0  0  0 118136  51340  98276 1668116    0    0    12    92  183   263  0  0 100

r ÔËÐжÓÁР µ±Õâ¸öÖµ³¬¹ý·þÎñÆ÷ÉÏcpuµÄÊýÁ¿  ¾Í»á´æÔÚcpuÆ¿¾±
pi ÊÇÒ³»»Èë  Ò³»»Èë²Ù×÷±íʾ·þÎñÆ÷³öÏÖÁËÄÚ´æ¶Ìȱ
us ÊÇÓû§cpu
sy ÊÇϵͳcpu
id ÊÇ¿ÕÏÐ
wa Êǵȴý
ËùÓеÄcpuÖµ(us+sy+id+wa)×ÜÊǵÈÓÚ100%

--ʹÓÃvmstat ʶ±ðcpuÆ¿¾±

linux ²é¿´cpuÊýÁ¿
/proc/Ŀ¼ÏÂÓг£¼ûµÄϵͳÐÅÏ¢
cat /proc/cpuinfo|grep  processor|wc -l

ibm aix ºÍhp_ux
lsdev -C|grep  Processor|wc  -l

SolarisÖÐÏÔʾcpuµÄÊýÁ¿
psrinfo  -v|grep   "Status of processor"|wc  -l

--vmstat ʶ±ðƵ·±Ê¹ÓõÄcpu

ÒªÊÇusÓësyµÄºÍ±Æ½ü100 ¾Í±íʾcpuÔÚÂú¸ººÉÔËÐÐ
ΨһÄÜÖ¸³öcpuÆ¿¾±µÄ¶ÈÁ¿ÊÇÔËÐжÓÁÐ"r"µÄÖµ
³¬¹ýÁËcpuµÄÊýÁ¿

ʶ±ðRAM ÄÚ´æÆ¿¾±

hp/ux ÏÔʾÄÚ´æ´óС

dmesg

ÏÔʾ dec-unix ÄÚ´æ´óС

uerf -r 300|grep -i  mem

aix ÉÏÏÔʾÄÚ´æ´óС

µÚÒ»²½ : lsdev  -C|grep  mem
µÚ¶þ²½ : lsattr  -El  mem0

ÏÔʾsqlarisÉÏÄÚ´æ´óС

prtconf|grep  -i  mem

--ʹÓÃtop¹¤¾ßÏÔʾRAM

top -d  2
ÿ¸ô2ÃëË¢ÐÂÒ»´Î

--hpºÍsolaris¿ÉÊÇÓÃglance²é¿´ÄÚ´æ

RAMÄÚ´æºÍ½»»»´ÅÅÌ
ÒòΪ½»»»(pi)»á»¨·Ñ´óÁ¿µÄʱ¼ä´Ó½»»»´ÅÅÌÉϽ«ÄÚ´æ¶Î¸´ÖÆ»ØRAM,ËùÒÔËû»á¼õÂý·þÎñÆ÷µÄËÙ¶È,ÔÚ
oracleÊý¾Ý¿â·þÎñÆ÷ÉÏ,½â¾öÒ³»»ÈëÎÊÌâµÄ·½°¸:
¸üСµÄSGA
¸ü¶àµÄRAM
¼õÉÙRAMÐèÇó(¼õÉÙ¶Ô³ÌÐòÈ«¾ÖÇøµÄ(PGA)ÄÚ´æµÄÐèÇóÀ´¼õÉÙÊý¾Ý¿â·þÎñÆ÷µÄRAMÏûºÄ.)

--ÔÚaixÖмì²âDZÔÚµÄI/OÆ¿¾±

waÁбíʾÏÖÔڵȴýÍⲿos·þÎñµÄcpu°Ù·Ö±È ¸ß²»Ò»¶¨¾ÍÊÇÓÐI/OÆ¿¾±

»ñÈ¡vmstatÐÅÏ¢µÄ½Å±¾

connect perfstat/perfstat

drop table stats$vmstat

create table stats$vmstat
(
start_date   date,
duration     number,
server_name  varchar2(20) ,
runque_waits number,
page_in      number,
page_out     number,
user_cpu     number,
system_cpu   number,
idle_cpu     number,
wait_cpu     number

)
tablespace  perfstat
storage(initial  10m
        next 1m
        pctincrease 0);

--linux²Ù×÷ϵͳµÄVMSTAT »ñÈ¡¹¤¾ß½Å±¾

1.±ØÐ뽫ORACLE_HOMEÉèÖõ½ÄãµÄĿ¼:

ORACLE_HOME=/home/newvers/product/92
2.±ØÐëÔÚsqlplusÃüÁîÖÐÉèÖÃORACLE_SID
$ORACLE_HOME/bin/sqlplus

本文地址：http://com.8s8s.com/it/it20199.htm