oracle数据库性能分析

1数据库的性能问题主要体现在：
1.1、 硬件资源的不足。如CPU、内存、I/O、网络
1.2、 资源设计的不合理。如SGA分配不合理、命中率低
1.3、 锁资源不足

2.硬件资源（以下以linux资源举例）
r 运行队列
Swpd 虚拟内存使用大小
free   空闲的物理内存的大小
si  每秒从磁盘读入虚拟内存的大小，如果这个值大于0，表示物理内存不够用或者内存泄露
us 用户CPU时间，我曾经在一个做加密解密很频繁的服务器上，可以看到us接近100,r运行队列达到80(机器在做压力测试，性能表现不佳)。
sy 系统CPU时间，如果太高，表示系统调用时间长，例如是IO操作频繁。
id  空闲 CPU时间，一般来说，id + us + sy = 100,一般我认为id是空闲CPU使用率，us是用户CPU使用率，sy是系统CPU使用率。
wt 等待IO CPU时间。
通过以上，我们可以分析数据库服务器的cpu、系统内存、IO的情况！

 

 

3.数据库分析
3.1SGAPGA
3.2临时表空间使用
3.3回滚段争用
3.4命中率
3.5综合分析：IO、表分析、

3.1查看SGA:
show parameter sga
SELECT* from v$parameter a WHERE a.name IN( 'shared_pool_size' , 'log_buffer',
'db_cache_size','java_pool_size','large_pool_size')
查看SGA使用情况：
select component,current_size/1048576 ,min_size/1048576 from
 v$sga_dynamic_components;
查看PGA:
SQL> show parameter pga
查看PGA使用情况：
select round(sum(pga_alloc_mem)/1048576,1) from v$process;

 

SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。
它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，
PGA 在创建进程时分配,在终止进程时回收

设置自动跟踪统计：
Set autot on stat
Set autot off
Show sga
Select * from v$sga
Show parameter sga;(查看sga max size，sga target size大小，运行时是否将SGAs锁定到物理内存（lock sga），启动时是否都使用物理内存（pre page sga）；
Show parametetr pre_page_sga;
Show parameter lock_sga;

Alter system set pre_page_sga=true scope=spfile
alter system set sga_max_size=512M scope=spfile (重启生效)
show parameter sga_max_size;
show parameter sga_target;
alter system set sga_target_size=400m scope=both (memory –a- s pfile)
Granule_size: sga内存的颗粒度大小，手工分配内存大小，会自动调整为颗粒度的整数倍。
若SGA小于1G，granule_size=4m
若SGA大于1G granule-size=8m (for windows) granule-size=16m(fro other)
SGA_TARGET   (Automatic shared memory management )

查看各缓存下限值：
Show parameter db_cache_size;
Show parameter shared_pool_size;
Show parameter java_pool_size;
Show parameter large_pool_size;
查看实际大小：
Select  name,current_size from v$buffer_pool;
Select pool,sum(bytes) from v$sgastat group by pool;(查看java，shared，large pool size)
 

3.2临时表空间大小：
SELECT round(sum(bytes)/1048576/1024) from dba_temp_files;
临时表空间使用情况：
select a.USERname,b.MACHINE,b.SID,b.SERIAL#,c.SQL_TEXT, ROUND(SUM(BLOCKS*8192*2)/1048576,2) usered from v$sort_usage a,v$session b,v$sqlarea c where a.SESSION_ADDR = b.SADDR AND b.SQL_ID=c.SQL_ID group by a.USERname,b.MACHINE,b.SID,b.SERIAL#,c.SQL_TEXT;
查看临时表空间大小：
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
System帐号 ：select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;
 

3.3检测回滚段的争用
 select sum(gets),sum(waits),sum(waits)/sum(gets)　from v$rollstat;
SUM(waits)值应小于SUM(gets)值的1%

回卷段的竟争会降低系统的性能。
GETS代表回卷段被访问的次数
WAITS代表进程等待回卷段中哪个进程的次数
如果GETS与WAITS的比大于2%表示存在竟争问题
select rn.name,rs.gets 被访问次数,rs.waits 等待回退段块的次数,(rs.waits/rs.gets)*100 命中率 from v$rollstat rs,v$rollname rn
 

3.4SGA命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
    round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
    from v$sysstat a, v$sysstat b, v$sysstat c
    where a.statistic# = 38 and b.statistic# = 39
    and c.statistic# = 40;
SGA中共享缓存的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
    sum(reloads)/sum(pins) *100 libcache
    from v$librarycache;
    select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
    from v$librarycache;
SGA中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
    Decode(gets,0,0,misses/gets*100) ratio1,
    Decode(immediate_gets+immediate_misses,0,0,
    immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
    FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
高速缓存与主存采用全相联地址映象方式，高速缓存的容量为4mb，分为4块，每块1mb，主存容量为256mb。若主存读写时间为30ns，高速缓存的读写时间为3ns，平均读写时间为3.27ns，则高速缓存的命中率是？
命中的话，读写时间为3ns，不命中，读写时间就是33ns，假设命中率是p

3.4I/0 分析
select a.sid,a.name,a.class,
case when a.name like '%byte%' then round((b.value-a.value)/1048576/1024,1) else
(b.value-a.value) end dif,
(b.time_num-a.time_num)/100 time,
row_number()over(partition by a.sid order by (b.value-a.value) desc) rn
from t_stats a,t_stats b
where a.name=b.name and a.sid=b.sid
and a.time_flag=1 and b.time_flag=2
and a.sid='sys'
and a. name LIKE '%time%' OR a.name ='CPU used by this session'-- 条件判断
and (b.value-a.value) >0