数据库调优，前期准备

--重要参数
select name, value from v$parameter where name in ('sga_max_size','pga_aggregate_target','db_cache_size','shared_pool_size','sga_target');

--数据高速缓存区命中率
--计算公式：1-(physical reads / (db block gets + consistent gets))
--命中率应大于0.90最好

--缓存命中率，最差缓存命中率也应该>0.9;从0.9 提高到 0.95，可以使性能翻倍；【保证数据缓存命中率超过0.95】从0.9 提高到 0.98 可以使性能提高 500%【oracle 10g性--能调整与优化】从0.95 到 0.98的提高 将显著提升系统性能（一般低于0.95的命中率都应增加缓存大小DB_CACHE_SIZE）
--但是缺少索引或限制了索引的使用也可能降低命中率

column phys    format 999,999,999 heading 'Pyhsical Reads'
column gets     format 999,999,999 heading 'DB Block Gets'
column con_gets format 999,999,999 heading 'Consistent Gets'
column hitratio format 99.99       heading 'Hit Ratio'
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0)) / (sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0)))))*100 hitratio
from v$sysstat;

/
--共享区库缓存区命中率
--计算公式：SUM(pins - reloads) / SUM(pins)
--命中率应大于0.99

select sum(Pins) "Hits",sum(Reloads) "Misses",sum(Pins) / (sum(Pins) + sum(Reloads)) "Hits Ratio" from V$librarycache;

select sum(pins-reloads)/sum(pins)
from v$librarycache;
/

--共享区字典缓存区命中率
--计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
--命中率应大于0.85

select sum(gets-getmisses-usage-fixed)/sum(gets)
from v$rowcache;
/

--检测回滚段的争用
--SUM(waits)值应小于SUM(gets)值的1%

select sum(gets),sum(waits),sum(waits)/sum(gets)
from v$rollstat;
/

--检测回滚段收缩次数

select name,shrinks
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn;
/

--查询外键无索引，外键无索引会导致全表扫描
SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
       NVL2(CNAME3, ',' || CNAME3, NULL) ||
       NVL2(CNAME4, ',' || CNAME4, NULL) ||
       NVL2(CNAME5, ',' || CNAME5, NULL) ||
       NVL2(CNAME6, ',' || CNAME6, NULL) ||
       NVL2(CNAME7, ',' || CNAME7, NULL) ||
       NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT B.TABLE_NAME,
               B.CONSTRAINT_NAME,
               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
               COUNT(*) COL_CNT
          FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
WHERE COL_CNT > ALL
(SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION <= CONS.COL_CNT
         GROUP BY I.INDEX_NAME);

--找出滥用磁盘读操作的25个主要语句
set serverout on size 1000000
declare
  top25 number;
  text1 varchar2(4000);
  x     number;
  len1  number;
cursor c1 is
       select disk_reads, substr(sql_text,1,4000)
       from v$sqlarea order by disk_reads desc;
begin
  dbms_output.put_line('Reads'||' '||'Text');
  dbms_output.put_line('----------'||' '||'----------------------');
  open c1;
  for i in 1..25 loop
    fetch c1 into top25, text1;
    dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,66));
    len1:=length(text1);
    x:=66;
    while len1 > x-1 loop
      dbms_output.put_line('" '||substr(text1,x,66));
      x:=x+66;
      end loop;
  end loop;
end;
/

--最占用资源的查询
select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
 and a.disk_reads > 100000   --a.disk_reads 换成a.buffer_gets提供占用最多内存的sql
order by a.disk_reads desc;

--发现禁用的触发器
col       'Owner/Table' format a30
col       'Trigger Name' format a25
col       'Event' format a15
col       'Owner' format a10
select    substr(owner,12) "Owner", trigger_name "Trigger Name",
trigger_type "Type", triggering_event "Event",
table_owner||'.'||table_name "Owner/Table"
from       dba_triggers
where      status <> 'ENABLED'
order by   owner, trigger_name;