查看数据库性能 常用SQL

---内存评估


--缓冲区命中率
select (1 - (sum(decode(name, 'physical reads', value, 0)) /
       (sum(decode(name, 'db block gets', value, 0)) +
       sum(decode(name, 'consistent gets', value, 0))))) * 100 "Hit Ration"
  from v$sysstat;
--数据字典命中率
select (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ration"
  from v$rowcache;


--库缓存命中率
select sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio"
  from v$librarycache;


--内存中的排序
select a.value "Dish Sorts",
       b.value "Memor Sorts",
       round((100 * b.value) /
             decode((a.value + b.value), 0, 1, (a.value + b.value)),
             2) "Pct Memory Sorts"
  from v$sysstat a, v$sysstat b
 where a.name = 'sorts (disk)'
   and b.name = 'sorts (memory)';


--空闲的数据缓冲区比例


select decode(state,
              0,
              'FREE',
              1,
              decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
              3,
              'BEING USED',
              state) "Block Status",
       count(*)
  from x$bh
 group by decode(state,
                 0,
                 'FREE',
                 1,
                 decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
                 3,
                 'BEING USED',
                 state);
 --最浪费内存的前10个语句占全部内催读取量的比例                
 select sum(pct_bufgets)
   from (select rank() over(order by buffer_gets desc) as rank_bufgets,
                to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets
           from v$sqlarea)
  where rank_bufgets < 11;
--最应该优化的25个SQL
select * from (
SELECT buffer_gets,sql_text,rownum rn from v$sqlarea order by buffer_gets desc )
where rn<26;  


--固定缓存对象


select *
  from v$db_object_cache
 where sharable_mem > 100000
   and type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION');






--磁盘评估


---调整表和索引  表和索引不要在同一个表空间中


select i.index_name, t.table_name, t.tablespace_name
  from user_tables t, user_indexes i
 where t.table_name = i.table_name
   and t.tablespace_name = i.tablespace_name;


--表的行链接问题
select count(*) chained_rows, table_name
  from chained_rows
 group by table_name;


--关键文件路径
select file_name, tablespace_name, bytes
  from dba_data_files
union all
select file_name, tablespace_name, bytes
  from dba_temp_files
union all
select name file_name, null, null
  from v$controlfile
union all
select member file_name, to_char(a.group#) tablespace_name, b.bytes bytes
  from v$logfile a, v$log b
 where a.group# = b.group#
union all (select value file_name, null, null
             from v$parameter
            where name like 'log_archive_dest_%'
              and value is not null
           minus
           select value file_name, null, null
             from v$parameter
            where name like 'log_archive_dest_state%');


--回滚段平衡
select name,value from v$parameter where name like '%undo_%';  


select a.name,
       b.extents,
       b.rssize,
       b.xacts,
       b.waits,
       b.gets optsize,
       status
  from v$rollname a, v$rollstat b
 where a.usn = b.usn;
 --磁盘排序
 select name,value from v$sysstat where name like '%sorts%';
 
 --最浪费磁盘读操作的前10个语句占用所有语句的比例
 select sum(pct_bufgets)
   from (select rank() over(order by disk_reads desc) as rank_bufgets,
                to_char(100 * ratio_to_report(disk_reads) over() , '999.99') pct_bufgets from v$sqlarea)
  where rank_bufgets < 11;
  
  --前25个最浪费磁盘读操作的SQL
  select *
    from (select executions,
                 disk_reads,
                 substr(sql_text, 1, 4000),
                 rownum rn
            from v$sqlarea
           order by disk_reads desc)
   where rn < 26;
--字典管理表控件中的EXTENT总数不超过4096
select a.tablespace_name ,sum(a.extents)
from dba_segments a ,dba_tablespaces b
where a.tablespace_name=b.tablespace_name
and b.extent_management ='DICTIONARY'
group by a.tablespace_name
order by sum(a.extents)