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>@/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 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 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 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 About to Truncate Statspack Tables

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt If you would like to continue, press <return>

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ÖÐ¼ì²âÇ±ÔÚµÄ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 perfstat/perfstat@prdyp<<EOF

3.±ØÐëÍ¨¹ýÉèÖÃSAMPLE_TIME ¸Ä±ä²ÉÑùÊ±¼ä:

SAMPLE_TIME=300

--get_vmstat.ksh

ORACLE_HOME=/home/newvers/product/92

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

SERVER_NAME= uname -a|awk '{print $2}'

typeset -u SERVER_NAME

export SERVER_NAME

SAMPLE_TIME=300

while true

vmstat ${SAMPLE_TIME} 2>/tmp/msg$$

# run vmstat and direct the output into the Oracle table

cat /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_CPU

$ORACLE_HOME/bin/sqlplus -a 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,

);

EXIT

EOF

done

rm /tmp/msg$$

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

--µ÷Õû·þÎñÆ÷»·¾³

1.µ÷Õû·þÎñÆ÷»·¾³ÊÇµ÷ÕûoracleÊý¾Ý¿âµÄÏÈ¾öÌõ¼þ

Èç¹û·þÎñÆ÷ÉÏµÄcpu»òÕßÄÚ´æ´æÔÚ¹ýÔØ»òÕßÆ¿¾±

¾Í²»¿ÉÄÜÓÃÈÎºÎoracleµ÷ÕûÀ´½â¾öÐÔÄÜÎÊÌâ

2.½øÐÐ×î´ó»¯ÀûÓÃÊÇÒ»¸öÉñÊ¥µÄÄ¿±ê ¶àÓàµÄ´¦ÀíÄÜÁ¦ºÍRAM½«ºÜÄÑÔÙÀûÓÃ

--ÔÚÏß·þÎñÆ÷¼àÊÓ¹¤¾ß

Ê¹ÓÃtopºÍsar¹¤¾ß²é¿´CPUºÍÄÚ´æ»î¶¯µÄÏ¸½Ú

--µ÷ÕûcpuÏûºÄ

--µ÷ÕûÄÚ´æÏûºÄ

ÑÐ¾¿»ù±¾µÄÄÚ´æ¹ÜÀí,·þÎñÆ÷ÄÚ´æ·Ö¸î¼¼ÇÉ

Ò³»»Èë±íÊ¾oracle·þÎñÆ÷µÄÐèÒª³¬¹ýÁËRAMÄÚ´æµÄÊýÁ¿

¶Ô½»»»À´½²×î³¤¼ûµÄ²¹¾È·½Ê½¾ÍÊÇ½«ÉÙSGAµÄ´óÐ¡»òÕßÎªÊý¾Ý¿â·þÎñÆ÷Ôö¼ÓÄÚ´æ

--±¨¸æ·þÎñÆ÷Í³¼Æ

Ê¹ÓÃtop¼àÊÓ·þÎñÆ÷

top

load averages ¸ºÔØÆ½¾ùÖµ

¹²ÓÐ3¸öÖµ

1.µÚÒ»¸öÊÇ¹ýÈ¥1·ÖÖÓÄÚµÄ¼´Ê±¸ºÔØ

2.µÚ¶þ¸öÊÇ¹ýÈ¥5·ÖÖÓµÄ¸ºÔØÆ½¾ùÖµ

3.µÚÈý¸öÊÇ¹ýÈ¥15·ÖÖÓµÄ¸ºÔØÆ½¾ùÖµ

ÎÞÂÛÈÎºÎÊ±ºò,¸ºÔØÆ½¾ùÖµ³¬¹ý1 ¶¼¿ÉÒÔÈÏÎª´¦ÀíÆ÷´¦ÓÚ¹ýÔØ×´Ì¬

Ó¦¸ÃÁ¢¿ÌÔËÐÐVMSTATA ÒÔ²é¿´ÔËÐÐ¶ÓÁÐÖµ

top ²é¿´cpuµÄÏêÏ¸Çé¿ö

½ø³ÌID ---PID

ÓÃ»§Ãû --USER

·ÖÅÉÓÅÏÈ¼¶ --PRI

ÓÅÏÈÖµ --NI

¸÷ÈÎÎñµÄÄÚ´æ´óÐ¡ --SIZE

×´Ì¬

Ö´ÐÐÊ±¼ä

topÌá¹©ÁËºÜ¶àÐÅÏ¢

ORACLE DBA Ö»ÐèÒªÁË½âÆäÖÐ¼¸ÁÐ

¸ºÔØÆ½¾ù Õâ¸öÖµ³¬¹ý1±íÊ¾ ·þÎñÆ÷³öÏÖÁË¹ýÔØ

CPU

LOAD Õ¹Ê¾ÁË¸÷CPUµÄ¸ºÔØ

NI ÊÇÈÎÎñµÄ·ÖÅÉÓÅÏÈ¼¶

--·þÎñÆ÷ÈÎÎñ¸ºÔØÆ½ºâ

È·¶¨cpu·åÖµÊ±¼ä,±ÜÃâÔÚ·åÖµÊ±¼äÖ´ÐÐÅú´¦Àí³ÌÐòºÍ¶ÔcpuÊ¹ÓÃ¹ý¸ßµÄ³ÌÐò(¾¡Á¿½«Æä×ªÒÆµ½Ò¹ÍíÖ´ÐÐ)

--rpt_top_sql.sql

--²éÕÒ10µãºÍÏÂÎç3µã×îÏûºÄcpuµÄsql_text

select

to_char(snap_time,'yyyy-mm-dd hh24'),

substr(sql_text,1,50)

from

stats$sql_summary a,

stats$snapshot sn

where

a.snap_id=sn.snap_id

and

to_char(snap_time,'hh24')=10

to_char(snap_time,'hh24')=15

order 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_prdyp

newvers 1384 1 0 Feb16 ? 00:00:34 ora_dbw0_prdyp

newvers 1386 1 0 Feb16 ? 00:02:21 ora_lgwr_prdyp

newvers 1388 1 0 Feb16 ? 00:02:06 ora_ckpt_prdyp

newvers 1390 1 0 Feb16 ? 00:00:28 ora_smon_prdyp

newvers 1392 1 0 Feb16 ? 00:00:00 ora_reco_prdyp

newvers 1394 1 0 Feb16 ? 00:01:08 ora_cjq0_prdyp

newvers 1398 1 0 Feb16 ? 00:00:00 ora_s000_prdyp

newvers 1400 1 0 Feb16 ? 00:00:00 ora_d000_prdyp

newvers 1402 1 0 Feb16 ? 00:00:09 ora_arc0_prdyp

newvers 1404 1 0 Feb16 ? 00:00:05 ora_arc1_prdyp

newvers 1410 1 0 Feb16 ? 00:02:17 ora_qmn0_prdyp

newvers 11849 1 0 Feb26 ? 00:17:15 ora_p000_prdyp

newvers 11851 1 0 Feb26 ? 00:17:18 ora_p001_prdyp

newvers 11853 1 0 Feb26 ? 00:17:33 ora_p002_prdyp

newvers 11855 1 0 Feb26 ? 00:12:53 ora_p003_prdyp

newvers 11857 1 0 Feb26 ? 00:06:28 ora_p004_prdyp

newvers 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 ora

ora_pmon_prdyp --½ø³Ì¼àÊÓÆ÷½ø³Ì

ora_dbw0_prdyp --Êý¾Ý¿âÐ´ÈëÆ÷½ø³Ì

ora_lgwr_prdyp --ÈÕÖ¾Ð´ÈëÆ÷½ø³Ì

ora_ckpt_prdyp --¼ì²éµã½ø³Ì

ora_smon_prdyp --ÏµÍ³¼àÊÓÆ÷½ø³Ì

ora_reco_prdyp --·Ö²¼Ê½»Ö¸´

ora_cjq0_prdyp --

ora_s000_prdyp --·þÎñÆ÷ Õâ¸ö½ø³Ì»áÉú³ÉËùÓÐÐèÒªµÄÊý¾Ý¿âµ÷ÓÃ,ÎªÓÃ»§²éÑ¯·þÎñ.Ëü»á½«½á¹û·µ»Ø¸øµ÷ÓÃËüµÄ½ø³ÌDnnn

ora_d000_prdyp --·þÎñÆ÷ Dnnn µ÷¶È½ø³Ì

ora_arc0_prdyp --¹éµµ½ø³Ì1

ora_arc1_prdyp --¹éµµ½ø³Ì2

ora_qmn0_prdyp --

ora_p000_prdyp --²¢ÐÐ²éÑ¯½ø³Ì1(ÒòÎªÉèÖÃÁË±íµÄ²¢ÐÐ¶È,ËùÒÔºóÌ¨Æô¶¯ÁË)

ora_p001_prdyp --²¢ÐÐ²éÑ¯½ø³Ì2

ora_p002_prdyp --²¢ÐÐ²éÑ¯½ø³Ì3

ora_p003_prdyp --²¢ÐÐ²éÑ¯½ø³Ì4

ora_p004_prdyp --²¢ÐÐ²éÑ¯½ø³Ì5

oracleprdyp (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_ipc

sqlnet.ora ¿Í»§ÎÄ¼þ

.break_poll_skip

tnsnames.ora

.SDU TDU

listener.ora

.SDU TDU

ptotocol.ora

.tcp.nodelay

1.--ptotocol.ora ÖÐµÄ .tcp.nodelay

oracle ½¨ÒéÖ»ÓÐµ±Óöµ½TCP³¬Ê±µÄÊ±ºò,²ÅÊ¹ÓÃtcp.nodelay

µ±Êý¾Ý¿â·þÎñÆ÷Ö®¼äÓÐ´óÁ¿Í¨ÐÅÇé¿öÏÂ,ÉèÖÃtcp.nodelayÄÜ¹»¼«´óµÄ¸ÄÉÆÐÔÄÜ

2.--sqlnet.oraµÄautomatic_ipc

automatic_ipc²ÎÊý»á¼ÓËÙµ½±¾µØµÄÁ¬½Ó,ÕâÊÇÒòÎªËü¿ÉÒÔÔ½¹ýÍøÂç²ã.

Èç¹ûautomatic_ipc=on ORACLE NET ¾Í»áÊ×ÏÈ¼ì²éÊÇ·ñ´æÔÚ¾ßÓÐÏàÍ¬±ðÃû¶¨ÒåµÄ±¾µØÊý¾Ý¿â

Èç¹û´æÔÚ¾Í»á½«Á¬½Ó½âÊÍÎªÒ»¸ö±¾µØÁ¬½Ó,ÕâÑù¾ÍÈÆ¹ýÁËÍøÂç²ã

3.ËùÓÐoracle net ¶¼Ó¦¸ÃÊ¹ÓÃÕâ¸öÉèÖÃÀ´¸ÄÉÆÐÔÄÜ

--oracle ½¨Òé¸ù¾Ý(mtu×î´ó´«Êäµ¥Ôª,Õâ¸öÖµÊÇ¹Ì¶¨µÄ)ÉèÖÃSDU

tnsnames.ora

.SDU TDU

listener.ora

.SDU TDU

--sqlnet.ora µÄ break_poll_skip ²ÎÊý

--sqlnet.ora µÄ disable_oob ²ÎÊý

epc_disabled »·¾³±äÁ¿

Ç¿ÁÒÍÆ¼öDBA½ûÓÃotrace

1.¹Ø±ÕÊý¾Ý¿âºÍÕìÌý³ÌÐò

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=true

6.ÖØÐÂÆô¶¯Êý¾Ý¿âºÍÕìÌý³ÌÐò

7.´Ó$ORACLE_HOME/binÖÐÔËÐÐotrccrefÃüÁî

--ÆäËûÓ°ÏìÍøÂçÐÐÎªµÄoracleÌØÐÔ

¿ÉÒÔÊ¹ÓÃÕâÐ©¼¼Êõ¹ÜÀíÍøÂç»î¶¯

Í¨³£ÓÐ¼¸¸öÑ¡Ïî

Ê¹ÓÃ×é»ñÈ¡(array fetch)

Ê¹ÓÃ¶àÏß³Ì·þÎñÆ÷(MTS)

Ê¹ÓÃÁ¬½Ó³Ø

Ê¹ÓÃODBC

Ê¹ÓÃOracle¸´ÖÆ

--Ê¹ÓÃÕóÁÐ»ñÈ¡À´Ìá¸ßÍøÂçÍÌÍÂÁ¿

--Ê¹ÓÃ¶àÏß³Ì·þÎñÆ÷

³ý·Ç·þÎñÆ÷ÉÏµÄÁ¬½ÓÆ½¾ù³¬¹ý300,·ñÔòOracle ²»ÍÆ¼öÊ¹ÓÃ MTS

select * from v$QUEUE

select * 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.sql

select 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 sn

where

e.snap_id=sn.snap_id

and

b.snap_id=e.snap_id-1

and

b.event=e.event

and

e.event like 'SQL*Net%'

and

e.total_waits-b.total_waits >100

and

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)ÉèÖÃÄÜ¹»Ó°Ïì´ÅÅÌio

2.oracle¶ÔÏó(±íºÍË÷Òý)ÉèÖÃÒ²»áÓ°Ïì´ÅÅÌIO

3.oracle sqlÖ´ÐÐ¼Æ»®»¹»áÖ±½ÓÓ°Ïì´ÅÅÌio

oracle ÊµÀý

´ó 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) c10

from sys.dba_segments ds ,

sys.dba_tables dt

where

--µ÷ÕûoracleÊý¾Ý¿âÊµÀý

½ÓÏÂÀ´ÎÒÃÇÒªµ÷ÕûoracleÊý¾Ý¿âÊµÀý,ÒÔ¼°²é¿´ËùÓÐÓ°ÏìÐÔÄÜµÄ ²ÎÊý,ÅäÖÃºÍÉè¶¨

ÓÃSTATAPACK¼ì²âÊµÀýÇ±ÔÚµÄÐÔÄÜÎÊÌâ

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_size

buffer_pool_keep

buffer_pool_recycle

oracle½¨Òé»º³åÇøµÄÃüÖÐÂÊÒª³¬¹ý90£¥ DBA¿ÉÒÔÍ¨¹ý¸ø³õÊ¼»¯²ÎÊýÔö¼ÓÊý¾Ý¿éÊýÁ¿À´¿ØÖÆÊý¾Ý»º³åÇøÃüÖÐÂÊ

Êý¾Ý¿â»º³å³ØµÄÄÚ²¿½á¹¹

--Ê¹ÓÃstatspack¼àÊÓ»º³å³ØµÄÊ¹ÓÃ

--»º³å³ØÃüÖÐÂÊºÍstatpack

select * from stats$buffer_pool_statistics

SGA_MAX_SIZE=6000M

DB_BLOCK_SIZE=16384

DB_CACHE_SIZE=5000M

BUFFER_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_touches

from

sys.x$bh a,

dba_objects b,

dba_segments s

where

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,obj

having avg(tch)>5

and count(1)>20;

Ê¶±ð³öÈÈµã¶ÔÏóºó£¬¿ÉÒÔ¾ö¶¨½«¶ÔÏó¸ôÀë·ÅÈëkeep³ØÖÐ

×÷ÎªÒ»°ãµÄ¹æÔò£¬Ó¦¸ÃÓÐ×ã¹»µÄRAM´æ´¢¿ÉÒÔÓÃÓÚÕû¸ö±í»òÕßË÷Òý

ÁÐÈç£¬Èç¹ûÏ£ÍûÎªkeep³ØÔö¼ÓÒ³±í£¬¾ÍÐèÒª¸øinit.oraµÄbuffer_pool_keep ²ÎÊýÔö¼Ó104¸öÊý¾Ý¿é

--µ÷Õû recycle ³Ø

ÔÚrecycle³Ø·ÅÖÃ¶ÔÏóµÄÄ¿±êÊÇ½«È«±íËÑË÷ÆµÂÊµÄ´ó±í½øÐÐ·ÖÀë£¬ÎªÁËÕÒµ½½øÐÐÈ«±íËÑË÷µÄ´ó±í£¬ÎÒÃÇ±ØÐëÇóÖúÓÚ´Ó

access.sqlÖÐ»ñµÃµÄÈ«±íËÑË÷±¨¸æ£º

access_recycle_syntax.sql

select

'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') p

where

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>1000

group by

p.owner,p.name,t.num_rows,s.blocks

order by

sum(s.executions) desc;

--¸ø±í·ÖÅärecycle³Ø

alter table user.table_name storage(buffer_pool recycle);

×¢Òâ£ºÔÚ½«ÈÎºÎ±í¼ÓÈëµ½RECYCLE³ØÖ®Ç°£¬DBA¶¼Ó¦¸Ã³éÈ¡sqlÔ´´úÂë£¬²¢ÇÒÑéÖ¤Õâ¸ö²éÑ¯ÊÇ·ñ»ñÈ¡³¬¹ýÁË±íÖÐÐÐµÄ40£¥

--¸ß¼¶recycle³Øµ÷Õû

ÏÂÁÐ²éÑ¯Ê¹ÓÃÁËx$bh.tchÀ´Ê¶±ð¾ßÓÐÒ»´Î»º³åÇø½Ó´¥¼ÆÊý£¬µ«ÊÇ×ÜÁ¿³¬¹ýÁËÕû¸ö»º´æµÄ5£¥µÄÊý¾Ý»º´æÖÐµÄ¶ÔÏó

select object_type mytype,

object_name myname,

blocks,

count(1) buffers,

100*(count(1)/totsize) pct_cache

from

sys.x$bh a,

dba_objects b,

dba_segments s,

()

--È¡Ïû¸ú×Ù¹¦ÄÜ

alter system set trace_enabled=false;

--STATISTICS_LEVEL

The 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_level

SHOW PARAMETER timed_statistics

Oracle 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_settable

FROM v$statistics_level

ORDER 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 Session

Statistics Name Status Status Level Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice DISABLED DISABLED TYPICAL NO

MTTR Advice DISABLED DISABLED TYPICAL NO

PGA Advice DISABLED DISABLED TYPICAL NO

Plan Execution Statistics DISABLED DISABLED ALL YES

Segment Level Statistics DISABLED DISABLED TYPICAL NO

Shared Pool Advice DISABLED DISABLED TYPICAL NO

Timed OS Statistics DISABLED DISABLED ALL YES

Timed Statistics DISABLED DISABLED TYPICAL YES

8 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 Session

Statistics Name Status Status Level Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice ENABLED ENABLED TYPICAL NO

MTTR Advice ENABLED ENABLED TYPICAL NO

PGA Advice ENABLED ENABLED TYPICAL NO

Plan Execution Statistics DISABLED DISABLED ALL YES

Segment Level Statistics ENABLED ENABLED TYPICAL NO

Shared Pool Advice ENABLED ENABLED TYPICAL NO

Timed OS Statistics DISABLED DISABLED ALL YES

Timed Statistics ENABLED ENABLED TYPICAL YES

8 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 Session

Statistics Name Status Status Level Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice ENABLED ENABLED TYPICAL NO

MTTR Advice ENABLED ENABLED TYPICAL NO

PGA Advice ENABLED ENABLED TYPICAL NO

Plan Execution Statistics ENABLED ENABLED ALL YES

Segment Level Statistics ENABLED ENABLED TYPICAL NO

Shared Pool Advice ENABLED ENABLED TYPICAL NO

Timed OS Statistics ENABLED ENABLED ALL YES

Timed Statistics ENABLED ENABLED TYPICAL YES

8 rows selected.

SQL>

Hope this helps. Regards Tim...

--ÄÚ´æµ÷Õû

select * from v$sga;

--µ÷ÕûÇ°SGA

NAME VALUE

-------------------- ----------

Fixed Size 452184

Variable Size 402653184

Database Buffers 251658240

Redo Buffers 667648

select * from v$sgastat;

POOL NAME BYTES

----------- -------------------------- ----------

fixed_sga 452184

buffer_cache 251658240

log_buffer 656384

shared pool errors 8940

shared pool enqueue 171860

shared pool KGK heap 3756

shared pool KQR M PO 1393788

shared pool KQR S PO 177272

shared pool KQR S SO 5120

shared pool sessions 410040

shared pool sql area 61446860

POOL NAME BYTES

----------- -------------------------- ----------

shared pool 1M buffer 2098176

shared pool KGLS heap 2613480

shared pool PX subheap 19684

shared pool parameters 39012

shared pool free memory 125812664

shared pool PL/SQL DIANA 3445584

shared pool FileOpenBlock 695504

shared pool PL/SQL MPCODE 637644

shared pool PL/SQL PPCODE 48400

shared pool PL/SQL SOURCE 14344

shared pool library cache 19376952

POOL NAME BYTES

----------- -------------------------- ----------

shared pool miscellaneous 8639216

shared pool PLS non-lib hp 2068

shared pool joxs heap init 4220

shared pool table definiti 2632

shared pool trigger defini 1128

shared pool trigger inform 528

shared pool trigger source 624

shared pool Checkpoint queue 564608

shared pool VIRTUAL CIRCUITS 265160

shared pool dictionary cache 1614976

shared pool KSXR receive buffers 1032500

POOL NAME BYTES

----------- -------------------------- ----------

shared pool character set object 432136

shared pool FileIdentificatonBlock 319452

shared pool message pool freequeue 833032

shared pool KSXR pending messages que 840636

shared pool event statistics per sess 1908760

shared pool fixed allocation callback 268

large pool free memory 83886080

java pool free memory 83886080

41 rows selected.

--UGAµÄ´óÐ¡,UGAÖ÷Òª°üº¬Ò»ÏÂ²¿·ÖµÄÄÚ´æÉèÖÃ

show parameters area_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

hash_area_size integer 1048576

sort_area_size integer 524288

workarea_size_policy string AUTO

--¼ÆËãÊý¾Ý»º³åÇøÃüÖÐÂÊ

select value from v$sysstat where name='physical reads' 4383475

select * from v$sysstat where name='physical reads direct' 3834798

select * from v$sysstat where name='physical reads direct (lob)' 374616

select * from v$sysstat where name like 'consistent gets' 1198738167

select * from v$sysstat where name like 'db block gets' 53472785

x=physical reads direct+physical reads direct (lob)

100-(physical reads-x)/(consistent gets+db block gets-x)*100

100-(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 dual

select 0/(17038425+0) from dual

--¹ØÓÚlog_buffer

select 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