statspack 使用实例

statspack 使用实例

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 [url=mailto:perfstat/perfstat@prdyp]perfstat/perfstat@prdyp<<EOF3.±ØÐëͨ¹ýÉèÖÃSAMPLE_TIME ¸Ä±ä²ÉÑùʱ¼ä:SAMPLE_TIME=300--get_vmstat.kshORACLE_HOME=/home/newvers/product/92export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATHSERVER_NAME= uname -a|awk '{print $2}' typeset -u SERVER_NAMEexport SERVER_NAMESAMPLE_TIME=300while true do vmstat ${SAMPLE_TIME} 2>/tmp/msg$$# run vmstat and direct the output into the Oracle tablecat /tmp/msg$$|sed 1,3d| awk '{printf("%s %s %s %s %s %s\n",$1,$8,$9,$14,$15,$16)}' |while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPUdo $ORACLE_HOME/bin/sqlplus -a [url=mailto:perfstat/perfstat@prdyp]perfstat/perfstat@prdyp<<EOF insert into perfstat.stats\$vmstat values( sysdate, $SAMPLE_TIME, '$SERVER_NAME', $RUNQUE, $PAGE_IN, $PAGE_OUT, $USER_CPU, $SYSTEM_CPU, $IDLE_CPU, 0 ); EXIT EOF done done rm /tmp/msg$$ ******************************************************************************************************************--µ÷Õû·þÎñÆ÷»·¾³1.µ÷Õû·þÎñÆ÷»·¾³Êǵ÷ÕûoracleÊý¾Ý¿âµÄÏȾöÌõ¼þÈç¹û·þÎñÆ÷ÉϵÄcpu»òÕßÄÚ´æ´æÔÚ¹ýÔØ»òÕßÆ¿¾±¾Í²»¿ÉÄÜÓÃÈκÎoracleµ÷ÕûÀ´½â¾öÐÔÄÜÎÊÌâ2.½øÐÐ×î´ó»¯ÀûÓÃÊÇÒ»¸öÉñÊ¥µÄÄ¿±ê ¶àÓàµÄ´¦ÀíÄÜÁ¦ºÍRAM½«ºÜÄÑÔÙÀûÓÃ--ÔÚÏß·þÎñÆ÷¼àÊÓ¹¤¾ßʹÓÃtopºÍsar¹¤¾ß²é¿´CPUºÍÄÚ´æ»î¶¯µÄϸ½Ú--µ÷ÕûcpuÏûºÄ--µ÷ÕûÄÚ´æÏûºÄÑо¿»ù±¾µÄÄÚ´æ¹ÜÀí,·þÎñÆ÷ÄÚ´æ·Ö¸î¼¼ÇÉÒ³»»Èë±íʾoracle·þÎñÆ÷µÄÐèÒª³¬¹ýÁËRAMÄÚ´æµÄÊýÁ¿¶Ô½»»»À´½²×¼ûµÄ²¹¾È·½Ê½¾ÍÊǽ«ÉÙSGAµÄ´óС»òÕßΪÊý¾Ý¿â·þÎñÆ÷Ôö¼ÓÄÚ´æ--±¨¸æ·þÎñÆ÷ͳ¼ÆʹÓÃtop¼àÊÓ·þÎñÆ÷topload averages ¸ºÔØƽ¾ùÖµ¹²ÓÐ3¸öÖµ 1.µÚÒ»¸öÊǹýÈ¥1·ÖÖÓÄڵļ´Ê±¸ºÔØ2.µÚ¶þ¸öÊǹýÈ¥5·ÖÖӵĸºÔØƽ¾ùÖµ3.µÚÈý¸öÊǹýÈ¥15·ÖÖӵĸºÔØƽ¾ùÖµÎÞÂÛÈκÎʱºò,¸ºÔØƽ¾ùÖµ³¬¹ý1 ¶¼¿ÉÒÔÈÏΪ´¦ÀíÆ÷´¦ÓÚ¹ýÔØ״̬Ӧ¸ÃÁ¢¿ÌÔËÐÐVMSTATA ÒԲ鿴ÔËÐжÓÁÐÖµtop ²é¿´cpuµÄÏêϸÇé¿ö½ø³ÌID ---PIDÓû§Ãû --USER·ÖÅÉÓÅÏȼ¶ --PRIÓÅÏÈÖµ --NI¸÷ÈÎÎñµÄÄÚ´æ´óС --SIZE״ִ̬ÐÐʱ¼ätopÌṩÁ˺ܶàÐÅÏ¢ORACLE DBA Ö»ÐèÒªÁ˽âÆäÖм¸ÁиºÔØƽ¾ù Õâ¸öÖµ³¬¹ý1±íʾ ·þÎñÆ÷³öÏÖÁ˹ýÔØCPULOAD չʾÁ˸÷CPUµÄ¸ºÔØNI ÊÇÈÎÎñµÄ·ÖÅÉÓÅÏȼ¶ --·þÎñÆ÷ÈÎÎñ¸ºÔØƽºâÈ·¶¨cpu·åֵʱ¼ä,±ÜÃâÔÚ·åֵʱ¼äÖ´ÐÐÅú´¦Àí³ÌÐòºÍ¶ÔcpuʹÓùý¸ßµÄ³ÌÐò(¾¡Á¿½«ÆäתÒƵ½Ò¹ÍíÖ´ÐÐ)--rpt_top_sql.sql--²éÕÒ10µãºÍÏÂÎç3µã×îÏûºÄcpuµÄsql_textselect to_char(snap_time,'yyyy-mm-dd hh24'), substr(sql_text,1,50)from stats$sql_summary a, stats$snapshot snwhere a.snap_id=sn.snap_idand to_char(snap_time,'hh24')=10or to_char(snap_time,'hh24')=15order by rows_processed desc ; --°´Ðд¦ÀíÅÅÐò --²é¿´ÓÅÏȼ¶ps -elf|more¸Ä±äniceÖµ--Èç¹ûÄÚ´æ·¢ÉúÒ³»»Èë,¿ÉÒÔʹÓüõÉÙsort_area_size ʵÏÖ¶àÏ̷߳þÎñÆ÷ÒÔ¼°¼õÉÙ shared_pool»òÕßdb_block_buffersµÄÖµÀ´¼õÉÙÄÚ´æµÄÐèÒª--·þÎñÆ÷ÄÚ´æÉèÖÃÓйØÄÚ´æʹÓõÄÄÚºËÉèÖÃ(SHMMAX,SHMMNI,db_max_pct)ÊÇ»ñµÃÓÐЧoracleÐÔÄܵĹؼü,Ó¦¸Ã·´¸´¼ì²éËùÓÐÄں˲ÎÊý,ÒÔÈ·±£·þÎñÆ÷ÄÚ´æÒѾ­ÕýÈ·ÅäÖÃÒ²±ØÐëÑéÖ¤½»»»´ÅÅ̵ÄÅäÖÃ,½»»»´ÅÅÌÓÃÓÚ½ÓÊÜ´ÓÎïÀíRAMÒ³»»³ö²âÄÚ´æÖ¡´ó¶àÊý·þÎñÆ÷½¨Ò齫½»»»´ÅÅ̵ĴóСÉèÖÃΪÎïÀíRAMµÄÒ»±¶´óС***********************************************************************************************************************newvers 1382 1 0 Feb16 ? 00:01:38 ora_pmon_prdypnewvers 1384 1 0 Feb16 ? 00:00:34 ora_dbw0_prdypnewvers 1386 1 0 Feb16 ? 00:02:21 ora_lgwr_prdypnewvers 1388 1 0 Feb16 ? 00:02:06 ora_ckpt_prdypnewvers 1390 1 0 Feb16 ? 00:00:28 ora_smon_prdypnewvers 1392 1 0 Feb16 ? 00:00:00 ora_reco_prdypnewvers 1394 1 0 Feb16 ? 00:01:08 ora_cjq0_prdypnewvers 1398 1 0 Feb16 ? 00:00:00 ora_s000_prdypnewvers 1400 1 0 Feb16 ? 00:00:00 ora_d000_prdypnewvers 1402 1 0 Feb16 ? 00:00:09 ora_arc0_prdypnewvers 1404 1 0 Feb16 ? 00:00:05 ora_arc1_prdypnewvers 1410 1 0 Feb16 ? 00:02:17 ora_qmn0_prdypnewvers 11849 1 0 Feb26 ? 00:17:15 ora_p000_prdypnewvers 11851 1 0 Feb26 ? 00:17:18 ora_p001_prdypnewvers 11853 1 0 Feb26 ? 00:17:33 ora_p002_prdypnewvers 11855 1 0 Feb26 ? 00:12:53 ora_p003_prdypnewvers 11857 1 0 Feb26 ? 00:06:28 ora_p004_prdypnewvers 7180 1 0 14:20 ? 00:00:00 oracleprdyp (LOCAL=NO)newvers 10195 1 0 17:22 ? 00:00:00 oracleprdyp (LOCAL=NO)newvers 10197 1 0 17:22 ? 00:00:00 oracleprdyp (LOCAL=NO)newvers 10199 8672 0 17:22 pts/0 00:00:00 grep oraora_pmon_prdyp --½ø³Ì¼àÊÓÆ÷½ø³Ìora_dbw0_prdyp --Êý¾Ý¿âдÈëÆ÷½ø³Ìora_lgwr_prdyp --ÈÕ־дÈëÆ÷½ø³Ì ora_ckpt_prdyp --¼ì²éµã½ø³Ìora_smon_prdyp --ϵͳ¼àÊÓÆ÷½ø³Ìora_reco_prdyp --·Ö²¼Ê½»Ö¸´ora_cjq0_prdyp --ora_s000_prdyp --·þÎñÆ÷ Õâ¸ö½ø³Ì»áÉú³ÉËùÓÐÐèÒªµÄÊý¾Ý¿âµ÷ÓÃ,ΪÓû§²éѯ·þÎñ.Ëü»á½«½á¹û·µ»Ø¸øµ÷ÓÃËüµÄ½ø³ÌDnnnora_d000_prdyp --·þÎñÆ÷ Dnnn µ÷¶È½ø³Ìora_arc0_prdyp --¹éµµ½ø³Ì1 ora_arc1_prdyp --¹éµµ½ø³Ì2ora_qmn0_prdyp --ora_p000_prdyp --²¢Ðвéѯ½ø³Ì1(ÒòΪÉèÖÃÁ˱íµÄ²¢ÐжÈ,ËùÒÔºǫ́Æô¶¯ÁË)ora_p001_prdyp --²¢Ðвéѯ½ø³Ì2ora_p002_prdyp --²¢Ðвéѯ½ø³Ì3ora_p003_prdyp --²¢Ðвéѯ½ø³Ì4ora_p004_prdyp --²¢Ðвéѯ½ø³Ì5oracleprdyp (LOCAL=NO) --±¾µØÁ¬½Ó--²é¿´ºǫ́½ø³Ìselect * from v$bgprocess where paddr <> '00';--³£¼û¹éµµ¹ÒÆðÎÊÌâµÄ´¦ÀíÓÉARCHÒýÆðµÄÊý¾Ý¿â¹ÒÆð? Êý¾Ý¿âȱʡ°²×°Ê±£¬Ò»°ã´¦Óڷǹ鵵ģʽ£¬Óû§¿É¸ù¾ÝÐèÒª¸ÄΪ¹éµµÄ£Ê½¡£ÔÚÐí¶àÇé¿öÏÂÓû§Ö»ÐÞ¸ÄÁËÊý ¾Ý¿âµÄģʽ£¬Ã»ÓÐÆô¶¯ARCH½ø³Ì£¬ÕâÖÖÇé¿öÏ£¬¾­¹ýÒ»¶ÎÔËÐÐºó £¬Õû¸öÊý¾Ý¿â¹ÒÆ𣬲éѯv$session_waitÊÓͼ£¬»á·¢ÏÖ archive required µÄʼþ¡£ÊÖ¹¤¹éµµ»ò×Ô¶¯Æô¶¯ARCH ¼´¿É½â¾ö¡£»¹ÓÐÒ»ÖÖÇé¿öÊÇÊý¾Ý¿âÔËÐÐÔڹ鵵״̬£¬µ«Êǹ鵵ÈÕÖ¾ËùÐè¿Õ¼ä²»×㣬ÕâʱÊý¾Ý¿â¹ÒÆ𣬽â¾ö·½·¨Êǽâ¾ö¹éµµ¿Õ¼ä²»×ãÎÊÌ⣬ȻºóÖØÐÂÆô¶¯¹éµµ½ø³Ì¡£***************************************************************************************************************--Æß.µ÷ÕûÍøÂç»·¾³¶Ôoracle net ×î³£¼ûµÄÎó½âÊÇ:ͨ¹ýµ÷Õûoracle ÍøÂç²ÎÊýʵÏÖÍøÂçÐÔÄܵÄÌá¸ß!³ýÁËÉÙÊýµÄÀýÍâÇé¿öÖ®Íâ,ËùÓеÄÍøÂçͨÐŶ¼ÊÇÔÚoracleµÄ·¶Î§Ö®Íâ,²»Äܹ»ÔÚoracle»·¾³ÄÚ²¿½øÐе÷Õûoracle netÖ»ÊÇOSIģʽÖÖµÄÒ»²ã ËüλÓÚÌض¨µÄÍøÂçЭÒéÕ»Ö®ÉÏ Êµ¼ÊÉÏËùÓеÄÍøÂçµ÷Õû¶¼ÊÇÔÚoracle »·¾³Ö®Íâʵ¼ÊÉ϶ÔÓÚ¸ÄÉÆÐÔÄÜÀ´½²oracle net ¿ÉÒÔ×÷µÄ¹¤×÷ºÜÉÙDBA ¿ÉÒÔ¿ØÖÆÍøÂç°üµÄ´óСºÍƵÂÊ¿ÉÒԸıä¿ìÕյĸüмä¸ô ,ÒÔ¸üСµÄƵÂʼä¸ôÔÚÍøÂçÉÏ´«µÝ¸ü¶àµÄÊý¾Ý±¾Õ°üÀ¨ÏÂÁÐÉæ¼°ÍøÂçµ÷ÕûÎÊÌâµÄ²¿·Ö:ÓÅ»¯ORACLE NETÅäÖÃÓ°ÏìÍøÂçÐÔÄܵÄÆäËûoracle ÌØÐÔʹÓÃSTATPACK ¼àÊÓÍøÂçÐÔÄܵ÷Õû·Ö²¼ÍøÂç--ÓÅ»¯ORACLE Net ÅäÖÃÓм¸¸öµ÷Õû²ÎÊý¿ÉÒÔÓ°Ïì·þÎñÆ÷¼äORACLE NETÁ¬½ÓÐÔÄÜ Ó¦¸ÃÔںϸñµÄÍøÂç¹ÜÀíÔ±µÄ°ïÖúϵ÷ÕûÍøÂçÏÂÁвÎÊýÎļþ°üº¬µÄÉèÖÿÉÒÔÓ°ÏìÍøÂçÉÏ°ü´«µÝµÄ´óСºÍƵÂÊsqlnet.ora ·þÎñÆ÷Îļþ .automatic_ipcsqlnet.ora ¿Í»§Îļþ .break_poll_skiptnsnames.ora .SDU TDUlistener.ora .SDU TDUptotocol.ora .tcp.nodelay1.--ptotocol.ora ÖÐµÄ .tcp.nodelayoracle ½¨ÒéÖ»Óе±Óöµ½TCP³¬Ê±µÄʱºò,²ÅʹÓÃtcp.nodelayµ±Êý¾Ý¿â·þÎñÆ÷Ö®¼äÓдóÁ¿Í¨ÐÅÇé¿öÏÂ,ÉèÖÃtcp.nodelayÄܹ»¼«´óµÄ¸ÄÉÆÐÔÄÜ2.--sqlnet.oraµÄautomatic_ipcautomatic_ipc²ÎÊý»á¼ÓËÙµ½±¾µØµÄÁ¬½Ó,ÕâÊÇÒòΪËü¿ÉÒÔÔ½¹ýÍøÂç²ã.Èç¹ûautomatic_ipc=on ORACLE NET ¾Í»áÊ×Ïȼì²éÊÇ·ñ´æÔÚ¾ßÓÐÏàͬ±ðÃû¶¨ÒåµÄ±¾µØÊý¾Ý¿âÈç¹û´æÔھͻὫÁ¬½Ó½âÊÍΪһ¸ö±¾µØÁ¬½Ó,ÕâÑù¾ÍÈƹýÁËÍøÂç²ã3.ËùÓÐoracle net ¶¼Ó¦¸ÃʹÓÃÕâ¸öÉèÖÃÀ´¸ÄÉÆÐÔÄÜ--oracle ½¨Òé¸ù¾Ý(mtu×î´ó´«Êäµ¥Ôª,Õâ¸öÖµÊǹ̶¨µÄ)ÉèÖÃSDU tnsnames.ora .SDU TDUlistener.ora .SDU TDU--sqlnet.ora µÄ break_poll_skip ²ÎÊý--sqlnet.ora µÄ disable_oob ²ÎÊýepc_disabled »·¾³±äÁ¿Ç¿ÁÒÍƼöDBA½ûÓÃotrace1.¹Ø±ÕÊý¾Ý¿âºÍÕìÌý³ÌÐò2.´Ó$ORACLE_HOME/otrace/adminĿ¼ÖÐÒÆ×ß*.datÎļþ3.ʹÓÃUNIXµÄtouchÃüÁîÖØн¨Á¢datÎļþ4.ÔÚUNIX OracleµÄ .profile .login »òÕß .cshrc ×¢²áÎļþµÄÔËÐл·¾³Öй涨 "epc_disabled=true".Õ⽫»á½ûÓÃotrace¹¦ÄÜ5.ÐÞ¸Älistener.oraÎļþ,ÒÔ±ãΪËùÓеÄÊý¾Ý¿âÔÚsid_descÖй涨epc_disabled=true6.ÖØÐÂÆô¶¯Êý¾Ý¿âºÍÕìÌý³ÌÐò7.´Ó$ORACLE_HOME/binÖÐÔËÐÐotrccrefÃüÁî--ÆäËûÓ°ÏìÍøÂçÐÐΪµÄoracleÌØÐÔ¿ÉÒÔʹÓÃÕâЩ¼¼Êõ¹ÜÀíÍøÂç»î¶¯Í¨³£Óм¸¸öÑ¡ÏîʹÓÃ×é»ñÈ¡(array fetch)ʹÓöàÏ̷߳þÎñÆ÷(MTS)ʹÓÃÁ¬½Ó³ØʹÓÃODBCʹÓÃOracle¸´ÖÆ--ʹÓÃÕóÁлñÈ¡À´Ìá¸ßÍøÂçÍÌÍÂÁ¿--ʹÓöàÏ̷߳þÎñÆ÷³ý·Ç·þÎñÆ÷ÉϵÄÁ¬½Óƽ¾ù³¬¹ý300,·ñÔòOracle ²»ÍƼöʹÓà MTSselect * from v$QUEUEselect * from v$dispatcherÕâ2¸öÊÓͼ½«»áÖ¸³öMTS·ÖÅÉÆ÷µÄÊýÁ¿ÊÇ·ñÌ«µÍËäÈ»·ÖÅÉÆ÷µÄÊýÁ¿ÊÇÔÚinit.oraÎļþÖй涨µÄ,µ«ÊÇÒ²¿ÉÒÔÔÚSQL*DBAÖÐʹÓÃALTER SYSTEM SET MTS_DISPATCHERS='TCPIP,4'ÔÚÏ߸ıäÈç¹ûÄãÓöµ½ÓëMTSÓйصÄÎÊÌâ,¿ÉÒÔͨ¹ýµ÷ÓÃSVRMGRL>ALTER SYSTEM SET MTS_DISPATCHERS=0;ÃüÁî¿ìËÙÍ˻ص½×¨Ó÷þÎñÆ÷--Á¬½Ó¹²ÏíºÍÍøÂçÐÔÄÜʹÓÃodbcµÄÊý¾Ý¿âÁ¬½ÓÔÚÐí¶àoracleÓ¦ÓÃÖж¼»á²úÉú´óÁ¿¸ºÔØ--µ÷Õûoracle ¸´ÖÆ--´ÓOracle statpack ÖмàÊÓÍøÂçÐÔÄÜselect * from stats$system_event where event like 'SQL%';ÕâÊÇÒ»¸öÊä³ö±¨¸æʾÀý,ËüչʾÁËʼþÒÔ¼°¸÷¸öʱ¼äµÄµÈ´ýÊ®¼Ñµ±ÍøÂç´æÔÚÊý¾Ý°ü´«ÊäÁ¿¹ýÔصÄʱºò,Õâ¸ö±¨¸æ·Ç³£ÊÊÓÃÓÚչʾÌض¨Ê±¼ä--rpt_event.sqlselect to_char(snap_time,'yyyy-mm-dd HH24') mydate, e.event, e.total_waits-nvl(b.total_waits,0) waits, ((e.time_waited_micro-nvl(b.time_waited_micro,0))/100)/nvl((e.total_waits-nvl(b.total_waits,0)),.01) avg_wait_secs from stats$system_event b , stats$system_event e, stats$snapshot snwhere e.snap_id=sn.snap_idand b.snap_id=e.snap_id-1and b.event=e.eventand e.event like 'SQL*Net%' and e.total_waits-b.total_waits >100and e.time_waited_micro-b.time_waited_micro >100; --µ÷Õû·Ö²¼Ê½ÍøÂçʹÓÃnetstat¼àÊÓÍøÂç»î¶¯netstat -sp tcp--ʹÓÃstatspack µ÷Õû´ÅÅÌI/O×ÓϵͳӰÏì´ÅÅÌioµÄoracleµ÷ÕûÒòËØÀí½âʵÀý²ÎÊýÔõÑùÓ°Ïì´ÅÅÌIO.oracleÉèÖÃÖеÄÈý¸öÁìÓò¿ÉÒÔÖ±½ÓÓ°Ïì´ÅÅÌioÊýÁ¿1.oracleʵÀý (init.ora)ÉèÖÃÄܹ»Ó°Ïì´ÅÅÌio2.oracle¶ÔÏó(±íºÍË÷Òý)ÉèÖÃÒ²»áÓ°Ïì´ÅÅÌIO3.oracle sqlÖ´Ðмƻ®»¹»áÖ±½ÓÓ°Ïì´ÅÅÌiooracle ʵÀý´ó db_block_size ´ó db_cache_sizeʹÓöà¸ö¿é´óС¶à¸öÊý¾Ý¿âдÈë(DBWR)½ø³Ì´ósort_area_size´óµÄÔÚÏßÖØ×÷ÈÕÖ¾oracle ¶ÔÏóÔÚÊý¾Ý¿âÄÚ²¿(±íºÍË÷ÒýµÄÉèÖÿÉÒÔ¼õÉÙÎïÀí´ÅÅÌIO)µÍpctused pctuseedµÄֵԽС,ËæºóµÄsql²åÈëÖгöÏÖµÄio¾ÍÔ½ÉÙµÍpctfree Èç¹ûÉèÖÃÁËpctfree,ÒÔÔÊÐíÔÚûÓзָîµÄÇé¿öÏÂÀ©Õ¹ËùÓÐÐÐ,ÄÇôÔÚËæºóµÄsqlÑ¡ÔñÖоͻá²úÉú¸üÉٵĴÅÅÌioʹÓÃË÷Òý½«±íÖØÐÂ×éÖ¯³É´ØÐÐ Èç¹ûÒÔ×ʹÓÃË÷ÒýµÄÏàͬÎïÀí´ÎÐò·ÅÖñí3 oracle sql ÔÚsqlÓï¾äÄÚ,ÓÐÐí¶à¼¼Êõ¿ÉÒÔ¼õÉÙÎïÀí´ÅÅÌioʹÓÃË÷Òý»òÌáʾ(hint)·ÀÖ¹²»±ØÒªµÄÈ«±íËÑË÷ʹÓÃλӳÉä(bitmapped)Ë÷ÒýÓ¦ÓÃsqlÌáʾoracle ÄÚ²¿½á¹¹ºÍ´ÅÅÌio --²éÕÒÏ¡Êè±í(×ÔÓɱíʧȥƽºâ!)select substr(dt.table_name,1,10) c3, ds.extents c5, ds.bytes/1048576 c4, dt.next_extent/1048576 c8, (dt.empty_blocks*4096)/1048576 c7, (ds.bytes*4096)/1048576 c6, (avg_row_len*num_rows)/(db.blocks*4096) c10from sys.dba_segments ds , sys.dba_tables dtwhere --µ÷ÕûoracleÊý¾Ý¿âʵÀý½ÓÏÂÀ´ÎÒÃÇÒªµ÷ÕûoracleÊý¾Ý¿âʵÀý,ÒÔ¼°²é¿´ËùÓÐÓ°ÏìÐÔÄÜµÄ ²ÎÊý,ÅäÖúÍÉ趨ÓÃSTATAPACK¼ì²âʵÀýDZÔÚµÄÐÔÄÜÎÊÌâ1.oracleʵÀý¸ÅÊö2.µ÷Õûoracle Êý¾Ý»º³åÇø3.µ÷Õû¹²Ïí³Ø¸ÅÊö4.µ÷Õû¿â¸ßËÙ»º´æ5.µ÷ÕûoracleÅÅÐò6.µ÷Õû»Ø¹ö¶Î7.oracle 9i RAM ÄÚ´æµ÷Õûͨ³£µÄ½Ç¶È¿´ oracleʵÀý°üÀ¨ÁËÁ½¸ö×é¼þ: ϵͳȫ¾ÖÇø(SGA) ÒÔ¼° oracleºǫ́½ø³ÌÎÒÃÇͨ³£Í¨¹ýµ÷Õûoracle²ÎÊýÀ´¿ØÖÆSGAºÍºǫ́½ø³Ìµ±oracleÆô¶¯Ê± oracle¾Í»áʹÓÃmalloc()ÃüÁîÈ¥½¨Á¢Ò»¸öRAMÄÚ´æÇøÓò,Õâ¸öSGAͨ³£Ò²³ÆΪoracleÇøÓòoracle DBA ¿ÉÒÔ¿ØÖÆSGAµÄ¹æÄ£ ÕýÈ·µÄSGA¹ÜÀí¿ÉÒÔ¼«´óµÄÓ°ÏìÐÔÄܾ¡¹Ü³õʼ»¯²ÎÊý³É°ÙÉÏǧµ«ÊÇÖ»ÓкÜÉÙµÄoracle9i²ÎÊý¶Ôµ÷Õû·Ç³£ÖØÒª:buffer_pool_keep Õâ¸öÊý¾Ý»º³å³ØÓÃÓÚ´æ´¢Ö´ÐÐÈ«±íɨÃèµÄС±íbuffer_pool_recycle Õâ¸ö³ØÓÃÀ´±£´æ½øÐÐÈ«±íɨÃèµÄ·Ç³£´óµÄ±íµÄ±í¿édb_cache_size Õâ¸ö²ÎÊý»á¾ö¶¨ORACLE SGA ÖÐÊý¾Ý¿â¿é»º³åÇøµÄÊýÁ¿,ËüÊÇoracleÄÚ´æµÄ×îÖØÒªµÄ²ÎÊýdb_block_size Êý¾Ý¿â¿é´óСÄܹ»¶ÔÐÔÄܲúÉú(×÷Ϊһ¸öÒ»°ãµÄ¹æÔò,¿é³ß´çÔ½´ó,ÎïÀíIO¾ÍÔ½ÉÙ,ÕûÌåÐÔÄܾÍÔ½¿ì)db_file_multiblock_read_count Õâ¸ö²ÎÊýÓÃÓÚÈ«±íËÑË÷»òÕß´ó±í·¶Î§É¨ÃèµÄʱºò,½øÐжà¿é¶ÁÈëlarge_pool_szie ÕâÊÇÒ»¸öʹÓöàÏ̷߳þÎñÆ÷µÄʱºò,±£ÁôÓÃÓÚSGAʹÓõĹ²Ïí³ØÖеÄÌØÊâÇøÓò.×î´ó³ØÒ²ÓÃÓÚ²¢ÐвéѯRAM½ø³Ìlog_buffer Õâ¸ö²ÎÊý»á¾ö¶¨ÎªoracleÖØ×÷ÈÕÖ¾»º³åÇø·ÖÅäµÄÄÚ´æÊýÁ¿.Èç¹û¾ßÓдóÁ¿µÄ¸üл£¬¾ÍÓ¦¸Ã¸ølog_buffer·ÖÅä¸ü¶àµÄ¿Õ¼äshared_pool_size Õâ¸ö²ÎÊý»á¶¨ÒåϵͳÖÐËùÓÐÓû§µÄ¹²Ïí³Ø,°üÀ¨SQLÇøÓòºÍÊý¾Ý×Öµä¸ßËÙ»º´æ.--ÓÐÈý¸öoracle²ÎÊý¿ÉÒÔÓ°ÏìÊý¾Ý»º³åÇøµÄ´óСdb_cache_sizebuffer_pool_keepbuffer_pool_recycleoracle½¨Ò黺³åÇøµÄÃüÖÐÂÊÒª³¬¹ý90£¥ DBA¿ÉÒÔͨ¹ý¸ø³õʼ»¯²ÎÊýÔö¼ÓÊý¾Ý¿éÊýÁ¿À´¿ØÖÆÊý¾Ý»º³åÇøÃüÖÐÂÊÊý¾Ý¿â»º³å³ØµÄÄÚ²¿½á¹¹--ʹÓÃstatspack¼àÊÓ»º³å³ØµÄʹÓÃ--»º³å³ØÃüÖÐÂʺÍstatpackselect * from stats$buffer_pool_statisticsSGA_MAX_SIZE=6000MDB_BLOCK_SIZE=16384DB_CACHE_SIZE=5000MBUFFER_POOL_KEEP=(1400,3)BUFFER_POOL_RECYCLE=(900,3)--ÔÚoracle8 ¿ÉÒÔʹÓÃALTER TABLE CUSTOMER STORAGE(buffer_pool KEEP);ALTER TABLE USER.TABLE_NAME STORAGE(buffer_pool keep);--¸ß¼¶KEEP³Øºòѡʶ±ð³ýÁ˽øÐÐÈ«±íɨÃèµÄС±íÖ®Í⣬keep»º³å³Ø»¹·Ç³£ÊʺϷÅÖÃƵ·±Ê¹ÓõÄÊý¾Ý¶ÎµÄÊý¾Ý¿é--ʹÓÃx$bhÊÓͼÀ´Ê¶±ðƽ¾ù¿é½Ó´¥´ÎÊý³¬¹ý5´Î£¬²¢ÇÒÔÚ»º´æÖÐÕ¼Óó¬¹ý20¸öÊý¾Ý¿éµÄ¶ÔÏó--hot_buffer.sql--ʶ±ðÈȵã¶ÔÏó select object_type mytype,object_name myname ,blocks,count(1) buffers,avg(tch) avg_touchesfrom sys.x$bh a, dba_objects b, dba_segments swhere a.obj=b.object_id and b.object_name=s.segment_name and b.owner not in('SYS','SYSTEM')GROUP BY object_name,object_type,blocks,objhaving avg(tch)>5and count(1)>20;ʶ±ð³öÈȵã¶ÔÏóºó£¬¿ÉÒÔ¾ö¶¨½«¶ÔÏó¸ôÀë·ÅÈëkeep³ØÖÐ×÷Ϊһ°ãµÄ¹æÔò£¬Ó¦¸ÃÓÐ×ã¹»µÄRAM´æ´¢¿ÉÒÔÓÃÓÚÕû¸ö±í»òÕßË÷ÒýÁÐÈ磬Èç¹ûÏ£ÍûΪkeep³ØÔö¼ÓÒ³±í£¬¾ÍÐèÒª¸øinit.oraµÄbuffer_pool_keep ²ÎÊýÔö¼Ó104¸öÊý¾Ý¿é--µ÷Õû recycle ³ØÔÚrecycle³Ø·ÅÖöÔÏóµÄÄ¿±êÊǽ«È«±íËÑË÷ƵÂʵĴó±í½øÐзÖÀ룬ΪÁËÕÒµ½½øÐÐÈ«±íËÑË÷µÄ´ó±í£¬ÎÒÃDZØÐëÇóÖúÓÚ´Óaccess.sqlÖлñµÃµÄÈ«±íËÑË÷±¨¸æ£ºaccess_recycle_syntax.sqlselect 'alter table '||p.owner||'.'||p.name||' storage (buffer_pool recyle);'from dba_tables t,dba_segments s,sqltemp s,(select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation='TABLE ACCESS' and options='FULL') pwhere s.addr||':'||TO_CHAR(s.hashval)=p.stid and t.table_name=p.name and t.owner=p.owner and t.buffer_pool<>'RECYCLE'having s.blocks>1000group by p.owner,p.name,t.num_rows,s.blocksorder by sum(s.executions) desc;--¸ø±í·ÖÅärecycle³Øalter table user.table_name storage(buffer_pool recycle);×¢Ò⣺ÔÚ½«Èκαí¼ÓÈëµ½RECYCLE³Ø֮ǰ£¬DBA¶¼Ó¦¸Ã³éÈ¡sqlÔ´´úÂ룬²¢ÇÒÑéÖ¤Õâ¸ö²éѯÊÇ·ñ»ñÈ¡³¬¹ýÁ˱íÖÐÐеÄ40£¥--¸ß¼¶recycle³Øµ÷ÕûÏÂÁвéѯʹÓÃÁËx$bh.tchÀ´Ê¶±ð¾ßÓÐÒ»´Î»º³åÇø½Ó´¥¼ÆÊý£¬µ«ÊÇ×ÜÁ¿³¬¹ýÁËÕû¸ö»º´æµÄ5£¥µÄÊý¾Ý»º´æÖеĶÔÏó£¬ÕâЩÊý¾Ý¶ÎÊÇDZÔÚµÄÔÚrecycle»º³å³ØÖзÅÖõĺòÑ¡¶ÔÏó£¬ÒòΪËûÃÇ¿ÉÄÜ»áÈò»»áÖØÓõÄÊý¾Ý¿éÕ¼ÓôóÁ¿µÄ»º´æ¿Õ¼äselect object_type mytype,object_name myname,blocks,count(1) buffers,100*(count(1)/totsize) pct_cachefrom sys.x$bh a, dba_objects b, dba_segments s,()--È¡Ïû¸ú×Ù¹¦ÄÜalter system set trace_enabled=false;--STATISTICS_LEVELThe STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:BASIC: No advisories or statistics are collected.TYPICAL: The following advisories or statistics are collected:Buffer cache advisory MTTR advisory Shared Pool sizing advisory Segment level statistics PGA target advisory Timed statistics ALL: All of TYPICAL, plus the following: Timed operating system statistics Row source execution statistics The parameter is dynamic and can be altered using:ALTER SYSTEM SET statistics_level=basic;ALTER SYSTEM SET statistics_level=typical;ALTER SYSTEM SET statistics_level=all;Current settings for parameters can be shown using:SHOW PARAMETER statistics_levelSHOW PARAMETER timed_statisticsOracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile. By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level, along with any other conflicting parameters:ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';This setting will not take effect until the database is restarted.At this point the affect of the statistics level can be shown using the following query:COLUMN statistics_name FORMAT A30 HEADING "Statistics Name"COLUMN session_status FORMAT A10 HEADING "Session|Status"COLUMN system_status FORMAT A10 HEADING "System|Status"COLUMN activation_level FORMAT A10 HEADING "Activation|Level"COLUMN session_settable FORMAT A10 HEADING "Session|Settable"SELECT statistics_name, session_status, system_status, activation_level, session_settableFROM v$statistics_levelORDER BY statistics_name;A comparison between the levels can be shown as follows:SQL> ALTER SYSTEM SET statistics_level=basic;System altered.SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable------------------------------ ---------- ---------- ---------- ----------Buffer Cache Advice DISABLED DISABLED TYPICAL NOMTTR Advice DISABLED DISABLED TYPICAL NOPGA Advice DISABLED DISABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED ALL YESSegment Level Statistics DISABLED DISABLED TYPICAL NOShared Pool Advice DISABLED DISABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics DISABLED DISABLED TYPICAL YES8 rows selected.SQL> ALTER SYSTEM SET statistics_level=typical;System altered.SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable------------------------------ ---------- ---------- ---------- ----------Buffer Cache Advice ENABLED ENABLED TYPICAL NOMTTR Advice ENABLED ENABLED TYPICAL NOPGA Advice ENABLED ENABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED ALL YESSegment Level Statistics ENABLED ENABLED TYPICAL NOShared Pool Advice ENABLED ENABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.SQL> ALTER SYSTEM SET statistics_level=all;System altered.SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable------------------------------ ---------- ---------- ---------- ----------Buffer Cache Advice ENABLED ENABLED TYPICAL NOMTTR Advice ENABLED ENABLED TYPICAL NOPGA Advice ENABLED ENABLED TYPICAL NOPlan Execution Statistics ENABLED ENABLED ALL YESSegment Level Statistics ENABLED ENABLED TYPICAL NOShared Pool Advice ENABLED ENABLED TYPICAL NOTimed OS Statistics ENABLED ENABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.SQL>Hope this helps. Regards Tim...--ÄÚ´æµ÷Õûselect * from v$sga;--µ÷ÕûÇ°SGANAME VALUE-------------------- ----------Fixed Size 452184Variable Size 402653184Database Buffers 251658240Redo Buffers 667648select * from v$sgastat;POOL NAME BYTES----------- -------------------------- ---------- fixed_sga 452184 buffer_cache 251658240 log_buffer 656384shared pool errors 8940shared pool enqueue 171860shared pool KGK heap 3756shared pool KQR M PO 1393788shared pool KQR S PO 177272shared pool KQR S SO 5120shared pool sessions 410040shared pool sql area 61446860POOL NAME BYTES----------- -------------------------- ----------shared pool 1M buffer 2098176shared pool KGLS heap 2613480shared pool PX subheap 19684shared pool parameters 39012shared pool free memory 125812664shared pool PL/SQL DIANA 3445584shared pool FileOpenBlock 695504shared pool PL/SQL MPCODE 637644shared pool PL/SQL PPCODE 48400shared pool PL/SQL SOURCE 14344shared pool library cache 19376952POOL NAME BYTES----------- -------------------------- ----------shared pool miscellaneous 8639216shared pool PLS non-lib hp 2068shared pool joxs heap init 4220shared pool table definiti 2632shared pool trigger defini 1128shared pool trigger inform 528shared pool trigger source 624shared pool Checkpoint queue 564608shared pool VIRTUAL CIRCUITS 265160shared pool dictionary cache 1614976shared pool KSXR receive buffers 1032500POOL NAME BYTES----------- -------------------------- ----------shared pool character set object 432136shared pool FileIdentificatonBlock 319452shared pool message pool freequeue 833032shared pool KSXR pending messages que 840636shared pool event statistics per sess 1908760shared pool fixed allocation callback 268large pool free memory 83886080java pool free memory 8388608041 rows selected.--UGAµÄ´óС,UGAÖ÷Òª°üº¬Ò»Ï²¿·ÖµÄÄÚ´æÉèÖÃshow parameters area_size;NAME TYPE VALUE------------------------------------ ----------- ------------------------------bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 1048576sort_area_size integer 524288workarea_size_policy string AUTO--¼ÆËãÊý¾Ý»º³åÇøÃüÖÐÂÊselect value from v$sysstat where name='physical reads' 4383475select * from v$sysstat where name='physical reads direct' 3834798select * from v$sysstat where name='physical reads direct (lob)' 374616select * from v$sysstat where name like 'consistent gets' 1198738167select * from v$sysstat where name like 'db block gets' 53472785x=physical reads direct+physical reads direct (lob)100-(physical reads-x)/(consistent gets+db block gets-x)*100100-(4383475-3834798-374616)/(1198738167+53472785-3834798-374616)*100--¹²Ïí³ØµÄÃüÖÐÂÊselect sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;--¹ØÓÚÅÅÐò²¿·Öselect name,value from v$sysstat where name like '%sort%';select sorts(disk)/(sorts (memory)+sorts(disk)) from dualselect 0/(17038425+0) from dual--¹ØÓÚlog_bufferselect name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');redo buffer allocation retries/redo entries >1% ¿¼ÂÇÔö¼Ólog_buffer--v$db_cache_advice¡¢v$pga_target_advice¡¢v$java_pool_advice ºÍ v$db_shared_pool_advice