数据库

img llmmysun

安装和使用 Statspack

发表于2004/10/14 15:47:00  1504人阅读

--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中检测潜在的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检测实例潜在的性能问题

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%的数据缓存中的对象
,这些数据段是潜在的在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

 

 

 

阅读全文
0 0

相关文章推荐

img
取 消
img