2.oracle性能日常查看

1.判断回滚段竞争的SQL

SELECT
    rn.name,
    rs.gets,
    rs.waits,
    ( rs.waits / rs.gets ) * 100 ratio
FROM
    v$rollstat rs,
    v$rollname rn
WHERE
    rs.usn = rn.usn;

--当Ratio大于2是存在回滚段竞争，需要增加更多的回滚段。

 2.判断表空间碎片

SELECT
    t.tablespace_name,
    SUM(t.bytes),
    MAX(t.bytes),
    COUNT(*),
    MAX(t.bytes) / SUM(t.bytes) radio
FROM
    dba_free_space t
GROUP BY
    t.tablespace_name
ORDER BY
    t.tablespace_name;

--如果最大空闲空间占总空间很大比例则可能不存在碎片，如果比例很小，且有很多空闲空间，则可能碎片很多

3.确定命令排序域的次数

select t.NAME, t.VALUE from v$sysstat t where t.NAME like 'sort%';  

4.确定当前sga的值

select name,value/1024/1024 from v$sga;

5.查看高速缓冲区命中率

SELECT
    1 - SUM(decode(name, 'physical reads', value, 0)) / ( SUM(decode(name, 'db block gets', value, 0)) * SUM(decode(name, 'consistent gets'
    , value, 0)) ) hit_ratio
FROM
    v$sysstat t
WHERE
    name IN ( 'physical reads', 'db block gets', 'consistent gets' );

--如果命中率低于70%，则应该加大db_block_buffer的值

6.查看共享池命中率

SELECT
    SUM(pins)                          pins,
    SUM(reloads)                       reloads,
    ( SUM(reloads) / SUM(pins) ) * 100 ratio1
FROM
    v$librarycache;

SELECT
    SUM(gets)                            gets,
    SUM(getmisses)                       getmisses,
    ( SUM(getmisses) / SUM(gets) ) * 100 ratio2
FROM
    v$rowcache;

-- 如果ratio1 大于1时，需要加大共享池，如果ratio2大于10%时，需要加大共享池shared_pool_size)

7.根据sid查询os的进程id

SELECT
    p.spid "OS Thread",
    b.name "Name-User",
    s.program,
    s.sid,
    s.serial#,
    s.osuser,
    s.machine
FROM
    v$process   p,
    v$session   s,
    v$bgprocess b
WHERE
        p.addr = s.paddr
    AND p.addr = b.paddr
    AND ( s.sid = 210
          OR p.spid = 3 )
UNION ALL
SELECT
    p.spid     "OS Thread",
    s.username "Name-User",
    s.program,
    s.sid,
    s.serial#,
    s.osuser,
    s.machine
FROM
    v$process p,
    v$session s
WHERE
        p.addr = s.paddr
    AND ( s.sid = 210
          OR p.spid = 3 )
    AND s.username IS NOT NULL;

8.内存调整

SELECT
    name,
    value
FROM
    v$sysstat
WHERE
    name IN ( 'physical reads', 'physical reads direct', 'physical reads direct (lob)', 'consistent gets', 'db block gets' );

数据缓存命中率：
( physical reads - (physical reads direct + physical reads direct (lob) )) / (consistent gets db block gets - (physical reads direct + physical reads direct (lob) ))，通过如果发现命中率低于90%，则应该调整应用可考虑是否增大数据缓冲区。

select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;
共享池命中率低于95%,就要考虑调整应用(通常没有使用绑定变量)或者增加内存。

select name,value from v$sysstat where name like '%sort%';
如果sorts(disk) /(sorts(memory) +sort(disk)) 的比例过高，则通常意味着sort_area_size部分内存较小，可以考虑调整相应的参数。


SELECT
    name,
    value
FROM
    v$sysstat
WHERE
    name IN ( 'redo entries', 'redo buffer allocation retries' );

这里是关于log_buffer，假如redo buffer allocation retries /redo entries 的比例超过1%，我们就可以考虑增大log_buffer