--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 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
do
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
do
$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,
0
);
EXIT
EOF
done
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
or
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¼ì²âʵÀý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_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³Ø·ÅÖöÔÏóµÄÄ¿±êÊǽ«È«±íËÑË÷ƵÂʵĴó±í½øÐзÖÀ룬ΪÁËÕÒµ½½øÐÐÈ«±íËÑË÷µÄ´ó±í£¬ÎÒÃDZØÐëÇóÖúÓÚ´Ó
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£¥µÄÊý¾Ý»º´æÖеĶÔÏó
£¬ÕâЩÊý¾Ý¶ÎÊÇDZÔÚµÄÔÚrecycle»º³å³ØÖзÅÖõĺòÑ¡¶ÔÏó£¬ÒòΪËûÃÇ¿ÉÄÜ»áÈò»»áÖØÓõÄÊý¾Ý¿éÕ¼ÓôóÁ¿µÄ»º´æ¿Õ¼ä
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
|
本类阅读TOP10