ORACLE10g学习笔记(六)

1. 自动碎片整理

Coalesce Tablespace Automatically

Here is a handy script which can be scheduled to automatically run and coalesces the tablespaces.  
This script is designed to run in NT but can be run in any operating system by slight modifications in the path where the file spools from the SQLPLUS environment. It assumes that the user who runs the script has priviledges to view the data dictionary.  
Start of code
--------------------------------------
sqlplus /
prompt this script will coalesce the tablespace automatically
set verify off;
set termout off;
set head off;
spool c:\temp\coalesce.log
select 'alter tablespace '||TABLESPACE_NAME||' coalesce ;'
from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED <100
or PERCENT_BLOCKS_COALESCED<100 ;
spool off;
@ c:\temp\coalesce.log
set head on;
set termout on;
set verify on;
prompt Tablespaces are coalesced successfully

 

2. 序列过大与索引规则存在权衡？

 

3. 常用的V$ 视图脚本

1） 版本信息：
    SQL> select * from v$version;
2） 数据库中已安装的产品项
    SQL> select * from v$option;
3） 内存分配摘要(V$SGA)
      SQL> COLUMN value FORMAT 999,999,999,999
      SQL> select * from  v$sga;
4） 内存分配的细节(V$SGASTAT)
      SQL> select * from v$sgastat; 
5） 在V$PARAMETER显示init.ora信息
    SQL>select name, value, isdefault, isses_modifiable,issys_modifiable from v$parameter order by name;

6）  测定数据的命中率(V$SYSSTAT)
      查询V$SYSSTAT视图(如下程序清单所示)可以查看从内存中读取数据的频率。它提供了数据库中设置的数据块缓存区的命中率。这个信息可以帮助您判断系统何时需要更多的数据缓存(DB_CACHE_SIZE)，或者系统的状态何时调整得不佳(二者均将导致较低的命中率)。通常情况下，您应当确保读数据的命中率保持在95%以上。将系统的命中率从98%提高到99%，可能意味着性能提高了100%(取决于引起磁盘读操作的语句)。
SELECT   1
       - (  SUM (DECODE (NAME, 'physical reads', VALUE, 0))
          / (  SUM (DECODE (NAME, 'db block gets', VALUE, 0))
             + (SUM (DECODE (NAME, 'consistent gets', VALUE, 0)))
            )
         ) "Read Hit Ratio"
  FROM v$sysstat;

7） 测定数据字典的命中率(V$ROWCACHE)
      可以使用V$ROWCACHE视图(如程序清单所示)来发现对数据字典的调用是否有效地利用了通过init.ora参数SHARED_POOL_SIZE分配的内存缓存.如果字典的命中率不高，系统的综合性能将大受影响。推荐的命中率是95%或者更高。如果命中率低于这个百分比，说明可能需要增加init.ora参数SHARED_POOL_SIZE。但要记住，在V$SGASTAT视图中看到的共享池包括多个部分，而这里仅仅就是其中之一。注意：在大幅度使用公共同名的环境中，字典命中率可能难以超过75%，即使共享池的尺寸很大。这是因为Oracle必须经常检查不存在的对象是否依旧存在。

SQL>select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)+ sum(getmisses)))) * 100 HitRate from v$rowcache;

8）测定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)
      访问V$LIBRARYCACHE视图可以显示实际使用的语句(SQL和PL/SQL)访问内存的情况。如果init.ora的参数SHARED_POOL_SIZE设置得太小，内存中就没有足够的空间来存储所有的语句。固定命中率通常应该是95%或更高，而重载的次数不应该超过1%。查询V$SQL_BIND_CAPTURE视图，看看每个SQL绑定是否太高，是否需要CURSOR_SHARING。

select sum(pins) "Executions", sum(pinhits) "Hits",((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",sum(reloads) "Misses", ((sum(pins) / (sum(pins)+ sum(reloads))) * 100) "RelHitRatio" from v$librarycache; 

9）确定需要固定的PL/SQL对象
    碎片化现象造成共享池中的可用空间均成为许多零散的片段，而没有足够大的连续空间，这是共享池中的普遍现象。消除共享池错误(参阅第4章和第13章以了解更多信息)的关键是理解哪些对象会引起问题。一旦知道了会引起潜在问题的PL/SQL对象，就可以在数据库启动时固定这个代码(这时共享池是完全连续的)。

SQL>select name, sharable_mem from v$db_object_cache where sharable_mem > 100000 and type in ('PACKAGE', 'PACKAGE BODY','FUNCTION', 'PROCEDURE') and kept = 'NO'; 

10） 通过V$SQLAREA查找有问题的查询
    V$SQLAREA视图提供了一种识别有潜在问题或者需要优化的SQL语句的方法，从而可通过减少磁盘的访问来优化数据库的综合性能。
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.command_type, a.sql_text Statement from        v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;

11） 检查用户的当前操作及其使用的资源
    将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句，如下面的程序清单所示。这在有些时候是极为有用的，例如DBA希望查看某一个给定的时间点上系统究竟执行了哪些操作。
select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece;

select a.username, b.block_gets, b.consistent_gets,b.physical_reads, b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username; 

12） 查找用户正在访问的对象
    通过查询V$ACCESS视图可查看在给定的时间点上用户所访问的所有对象。这有助于查明有问题的对象，在想修改一个特定的对象时也很有用(查找谁在访问它)。然而，当系统有一个很大的共享池和数百个用户时，这个操作的开销将很大。
select a.sid, a.username, b.owner, b.object, b.type from v$session a, v$access b where a.sid = b.sid;

13） 使用索引
Oracle 9i提供了监控索引使用的功能。这个新的视图表示索引是否被引用，但不能反映索引使用的频率。要监控的索引需要单独打开和关闭。可以使用alter index命令来初始化监控工作，然后通过对视图V$OBJECT_USAGE的查询来实现索引的跟踪。

select * from v$object_usage;
开始监控索引： 
alter index HRDT_INDEX1 monitoring usage; 
select index_name, table_name, monitoring, used,start_monitoring, end_monitoring from v$object_usage;

14） 确定锁定问题
    确定锁定问题将有助于定位正在等待其他某些用户或者某些东西的用户。可以使用这个策略来确定当前被锁定在系统中的用户。这也使DBA们可以确认一个相关的Oracle进程是否真地被锁定了，还是仅仅运行得比较慢。您还能够识别当前的语句是否正在执行锁定用户的操作。
select  /*+ ordered */ b.username, b.serial#, d.id1, a.sql_text from v$lock d, v$session b, v$sqltext a where b.lockwait = d.kaddr and a.address = b.sql_address and      a.hash_value  = b.sql_hash_value;

查看系统中是哪个用户造成了前一个用户被锁定的问题
select  /*+ ordered */ a.serial#, a.sid, a.username, b.id1, c.sql_text from v$lock b, v$session a, v$sqltext c where b.id1 in(select /*+ ordered */ distinct e.id1 from v$lock e, v$session d where d.lockwait= e.kaddr) and a.sid = b.sid and c.hash_value  = a.sql_hash_value and b.request = 0; 

15） 查找磁盘I/O问题
    视图V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了数据库中所有数据文件和磁盘的文件I/O活动信息。理想情况下，物理的读和写应当平均分布。如果没有合理的配置系统，其综合性能就会受到影响。
select a.file#, a.name, a.status, a.bytes,b.phyrds, b.phywrts from  v$datafile a, v$filestat b where a.file# = b.file#; 

16） 查找回滚段的内容
    这个有帮助的查询显示了一个回滚段的实际等待数。可以显示回滚信息(包括自动撤消)。还可以从程序清单显示的视图中查询Shrink和 wrap信息。查询V$ROLLNAME、V$ROLLSTAT和V$TRANSACTION视图可以提供用户如何使用回滚段和撤消表空间的信息。通常情况下，在一个时间点上不应让多个用户访问同一个回滚段(尽管这是被允许的)。

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;

17） 检查空闲列表是否充足
    如果使用多进程完成大量的插入操作，空闲列表(空闲的数据库数据块的列表)的默认值1可能是不够的。如果没有使用自动空间段管理(Automatic Space Segment Management，简称ASSM)，您可能需要增加空闲列表，或者空闲列表组。在使用多进程完成大量的插入操作时，应确保有足够的空闲列表和空闲列表组。空闲列表的默认存储值是1。如果您使用了ASSM，Oracle将为您管理这些参数，但是一个有大量数据交换的事务环境中，在应用ASSM前应经过仔细的测试。虽然如此，但通常最好使用ASSM。

select ((A.Count/(B.Value + C.Value))*100)Pct from V$WaitStat A, V$SysStat B, V$SysStat C where  A.Class = 'free list' and B.Statistic# = ( select Statistic# from V$StatName where Name = 'db block gets') and C.Statistic# =  (select Statistic# from V$StatName where Name = 'consistent gets')；

 

4. 命中率查看

在数据库启动2小时后，可以通过以下SQL来测试数据库性能

1） 缓冲区命中率: 
缓冲区命中率表示在不需要进行磁盘访问的情况下在内存结构中找到常用数据块的频率

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 Ratio"
         from v$sysstat;

大于98%为最佳

2） 数据字典缓存命中率：
数据字典缓存命中率显示了对数据字典和其他对象的内存读操作所占的百分比。
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache; 
大于98%为最佳

3） 库缓存命中率：
库缓存命中率显示了对实际语句和PL/SQL对象的内存读操作所占的百分比。注意，很高的命中率并不总是一件好事。
select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;
大于98%为最佳

4） PGA内存排序命中率
自动PGA内存管理简化了分配PGA内存的方法。Oracle动态调整工作区PGA内存的大小(以SGA内存大小的20%为基础)。在自动PGA内存管理模式下运行时，所有会话的工作区大小都是自动的。实例中活动工作区可用的PGA内存总量自动由SORT_AREA_SIZE或PGA _ AGGREGATE_ TARGET(首选)初始化参数导出。PGA内存排序率的值应该大于98%。依据初始化参数PGA_AGGREGATE_TARGET(或者用于向后兼容的SORT _AREA _ SIZE)的值，用户排序可能在内存或者在指定的临时表空间中的磁盘上完成，如果这个初始化参数不是太高的话。

select a.value "Disk Sorts", b.value "Memory 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)';

5） 空闲的数据缓冲区的比例
从您首次启动Oracle数据库的那一天开始，用户们的查询就开始使用内存。空闲的记录数除以X$BH表中的记录总数(即所分配的数据块缓冲区的总数)就得到这个百分比。同时请注意，您必须以SYS的权限来运行该查询。此外，拥有众多的空闲缓冲区并不一定是就最佳环境。5%-10% 为最佳。当空闲比例高于25%时，数据缓冲区设置得太大了，可能会浪费资源。

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); 

6） 最浪费内存的前10个语句占所有语句的比例
在没有调整的情况下，大多数系统中10个最常使用的SQL语句的访问量占了整个系统中内存读操作的50%以上。本节测量了最影响性能的代码对整个系统所造成危害的严重性，以百分比表示。

select sum(pct_bufgets) "Percent" 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;

小于5%为最佳。

7） 调整滥用磁盘读操作的主要语句
我发现在没有作调整的情况下，在绝大多数的系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘和/或内存读操作的75%。
select disk_reads, substr(sql_text,1,4000) from v$sqlarea  order by disk_reads desc; 

8） 表和与它们相关联的索引应当放置在不同的物理磁盘上，以便减少文件I/O。
以上测试也可以通过AWR和STATSPACK来查看. 在分析结果中，我们首先要看的十项内容：
（1） 首要的5个等待时间（定时事件）
（2） 负载简档（Load profile）
（3） 实例效率点击率（Instance efficiency hit ratios）
（4） 等待时间（Wait events）
（5） 闩锁等待（Latch waits）
（6） 首要的SQL（Top SQL）
（7） 实例活动（Instance activity）
（8） 文件I/0和段统计数据（File I/0 and segement statistics）
（9） 内存分配（Memory allocation）
（10） 缓冲区等待（Buffer waits）