statspack 使用实例-数据库专栏,SQL Server

–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