oracle 性能优化相关概念

收集资料级别
SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> select distinct sid from v$mystat;

       SID
----------
       503

SQL> select name,value from v$sessstat a m^H
  2  
SQL> select name,value from v$sesstat a , v$statname b where a.statistic#=b.statistic# and b.name like 'parse count%' and a.sid=503;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                      52
parse count (hard)                                                       43
parse count (failures)                                                    5
parse count (describe)                                                    0

等待事件
SQL> desc V$EVENT_NAME
v$session_event
v$system_event
v$session_wait

与I/O相关的重要视图：
 V$FILESTAT、V$TEMPSTAT
 
查看日志文件的信息：V$logfile、V$log和V$log_history
查看V$instance_recovery视图的Estimated_mttr列，看看估计的实例恢复时间是否过长

v$archive_processes视图中看到ARCn活动的进程的数量。
另外还有两个关于归档的视图是V$archive_dest，此视图中记录归档目的地信息。V$archive_log，此视图记录控制文件中所记载的所有归档日志信息

V$的信息来自于X$，而X$中的数据来自于可变区域中的一些结构
除Show Sga外，我们还可以用V$sgastat视图显示SGA各内存结构的大小，在此视图中，我们可以精确的看到共享池的大小，

V$SGA_DYNAMIC_COMPONENTS来了解SGA中各内存组件的大小

在另一会话中查询会话139的解析情况：
SQL> select name,value from v$sesstat a ,v$statname b
where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

通过V$librarycache视图来查看库缓存的一些情况
查看缓存在库缓存中对象的信息，它是V$db_object_cache。

游标：从DBA的角色说，游标是对存储在库缓存中可执行对象的统称
关于游标的视图和参数
- V$SQL、V$SQLAREA、V$sql_text
- V$open_cursor与Open_cursor参数
- CURSOR_SHARING参数
- session_cached_cursors

概念：子游标与父游标。
如果两个游标的文本一模一样，但由于环境不同，比如，游标所操作的表是不同用户下的同名表，这两个游标是不能共享执行计划的。
它们都有各自的执行计划存在库缓存中。
这两个游标就是子游标，Oracle还会建立一个父游标，父游标中没有执行计划，它只是文本相同但执行计划不同的所有游标的代表。

其实在库缓存中，即使没有文本相同的子游标，Oracle也会为每个游标都创建父游标。因为父游标是文本相同的子游标的代表吗，所有文本相同的游标共享同一个父游标。
也就是说，只要你执行SQL语句，Oracle都会在库缓存中保存一父一子两个游标。如果你执行了文本相同但环境不同因而不能共享执行计划的SQL语句，那么一个父游标可能就对应多个子游标。
父游标没有执行计划，它只有一些信息管理性数据，Oracle添加它的目的就是为了管理文本相同的游标。有一个视图是专门针对父游标的，就是V$sqlarea
VERSION_COUNT，它是对应同一父游标的子游标的数量。如果这个数字太高，可能代表由于某些原因使本可以共享执行计划的游标没有共享。

V$sql_text、
这个视图的目的是显示过长的SQL语句文本。对于这些文本过长的SQL语句，在V$sql_text中将分多行显示完整的SQL语句，每行显示64字节


V$open_cursor与Open_cursor参数
这个视图和参数涉及游标的打开。什么是游标的打开，就是在库缓存中，用户在软、硬解析游标时，会在游标对象的句柄上加一个锁，也就是Library cache lock。在解析并执行完游标后，这个锁并不会马上去掉，而是会一直保留着，直到用户发出了Close命令关闭游标时为止。我们在SQL*Plus命令窗口中发出的命令，在抓取完所有行后，SQL*Plus将自动为我们发出Close命令来关闭游标。
当游标打开时，Library cache lock将一直保持，这样，即使库缓存内存紧张，需要老化对象，也不会老化这些还正在加锁的对象。因此，如果用户不停的要求数据库服务器打开游标、执行SQL，但却忘了关闭游标，这很容易耗尽共享池的内存。为此，Oracle准备了
北京尚观科技有限公司 版权所有
Copyright ? 2005-2008 UPLOOKINGTechnology Co., Ltd. Page
Tel: 010-62113016/17 400-700-0056 LI-194 Oracle 性能调优
Mail: uplooking@uplooking.com Http://www.uplooking.com
72
一个参数，就是Open_cursor，它的默认值在9i下是50，在10g中是300，也就是说，在10g下，每个会话最多只能同时打开300个游标。有了这个限制，就不用害怕用户不停的打开游标但又不关闭它，而耗尽共享池内存了。
如果会话同时打开的游标数量超出了Open_cursor参数的限制，Oracle将禁止会话打开新的游标。同时报出错误：ORA-01000: 超出打开游标的最大数 。
在用户断开会话的连接后，会话打开的这些游标将自动关闭。
V$open_cursor视图专用来查看当前会话打开的游标信息。它只能查看当前会话打开的游标。

CURSOR_SHARING参数
如果应用程序中有很多类似下面这样的SQL语句：
select * from 某表 where id=1;
select * from 某表 where id=2;
select * from 某表 where id=50;
等等，这些SQL语句严格来说是无法共享游标（也就是共享执行计划）的，但是这些语句所需要的执行计划其实都是一样的。无论你在表中查询ID为1的行还是查询ID为100的行，执行方式应该是一样的。如果你想让这样的语句共享游标，那么，你可以改变Cursor_sharing参数的值。
此参有三个值：
? EXACT：这个值是默认值。除非游标文本一模一样，否则不会共享游标。
? SIMILAR：这个最智能，如果游标只有条件中的数据值部分不同，并且库缓存中原有游标的执行计划对于新执行的SQL语句也是最优的，将不再为SQL语句创建新的游标，而是让它共享库缓存中原有的游标。
? FORCE ：不比较执行计划是否最优，只要游标中除了条件中的数据值部分不同外，其他部分都相同，就会共享游标。
此参数可以在会话级修改，也就是可以使用Alter session修改它的值，这将只影响某一个会话，而不会影响其他会话。

select sql_text, EXECUTIONS from v$sqlarea where sql_text like 'select * from ui2%';

session_cached_cursors参数
Oracle可以将一部分执行次数比较多的游标的信息缓存在会话的私人内存PGA中，这样当被缓存游标再被执行时，很多数据不必再到库缓存中寻找，会话直接可以在自己的PGA中取出。这可以大大提高软解析的速度，这样的解析被称为更软的软解析（或快速软解析）。一般来说，会话在执行游标时，第一步会到自己的PGA中搜索游标，如果找到了，这就是更软的软解析。如果游标没有被缓存到PGA中，再到库缓存中查找，如果找到了这就是普通的软解析。如果库缓存中也没有，就进行硬解析，重新生成游标相关数据和执行计划。
如果会话在执行游标时，发现游标的总的执行次数已经超过了三次，就会将游标信息缓存在自己的PGA中。此参数的作用是设定一个会话共可以缓存多少个游标。
此参数的值如果比较大，将会耗用更多的PGA和共享池内存，但是，这对提高软解析速度是很有帮助的。如果你的数据库软解析耗用了过多的时间，可以尝试加大此参数的值。在10g中，此参数默认值是30。在大型OLTP应用中，此参数的值一般都设置为几百甚至上千。

调节保留区：V$SHARED_POOL_RESERVED
V$LIBRARY_CACHE_MEMORY
V$Shared_pool_advice
根据这两个视图的信息，画出曲线图，这将帮助你决定共享池大小是否需要调整

v$rowcache与字典缓存命中率
递归调用这个概念，比如你想查询表TAB1，Oracle必须根据你提供的表名，查找出表TAB1具体在那个数据库文件、那些块中。
还有，你当前使用的用户是否有权限查询TAB1，等等，这些信息都保存在数据字典表中，查询这些信息的操作就被称为递归操作

用V$sgastat观察大池
大池是共享池的辅助，对于一些操作，它可以有效的缓解共享池的争用。

闩，Latch，它是一种在并发程序中常用的低级锁，通常用最低层的汇编指令实现，代码要力求简洁，闩的获取、释放也要尽量的快，尽可能在很少的指今周期内就可完成。
Oracle中的闩有两种目的，一是用来保护重要的内存结构，二是串行执行某些代码

在V$latch中看到父闩资料，在V$latch_children中看到子闩资料
Shared pool闩的作用是保护在共享池分配内存这个操作。
通常情况下，是不会有会话频繁的在共享中请求内存的。
每次硬解析，都需要在共享池的库缓存中分配空间，这将需要Shared pool闩。
如果有大量的游标没有共享，就很容易出现Shared pool闩的争用。
因此，如果在等待事件中发现Shared pool闩总的等待时间很高，就说明是游标没有共享。软解析时是不需要使用此闩的。

ROWID有行所在的文件号、块号、行编号构成，通过ROWID访问行，是Oracle中最直接、速度最快的方式。
通过DBMS_ROWID.ROWID_BLOCK_NUMBER（ROWID）包，可以从ROWID中解析出块编号，如下：
Select DBMS_ROWID.ROWID_BLOCK_NUMBER（ROWID）,rowid from t4_1 ;
在一个会话中，通过ROWID访问某表中任意一行，然后在另一会话中，通过如下声明，观察Touch Count的变化：
Select file#,dbablk,tch from x$bh where obj=(select object_id from dba_objects where object_name=’’ and owner=’’);
在Oracle中，对全表扫描作另外的处理。Oracle经过统计，认为全表扫描的块很少被再次使用，因此Oracle倾向优先重用因全表扫描而占用的Buffer。
因此，全扫描块的TCH一直为0，无论全扫描几次，TCH不会增加。
Oracle在有关Buffer cache算法设计时的宗旨，就是将单一的操作，尽可能的积攒，等待时机一起操作。这样也有利于提高单一操作的完成速度。
LRU的宗旨，“将最后访问时间距现在最远的块作为牺牲者”

块的读
第一次读块时，块会被挂在LRU链中部冷端开始处。这一过程就是物理读
用ROWID每读一行，都是一次逻辑读
全表扫描每读arraysize行算一次逻辑读
索引扫描和ROWID扫描类似

递归调用主要的作用是将用户要访问表的数据字典信息读进共享池，如果再次访问表，无论声明是否可以共享执行计划，一定是可以共享这些数据字典信息的。
现在，我们已经执行过一次声明了，只要把刚才访问过的块从Buffer cache中清除，再次执行声明时，就不会有递归调用的影响了。
物理读是按块计算的，而逻辑读则是按读取的行数计算的。
select rowid,dbms_rowid.rowid_block_number(rowid),rownum,id from t4_1 where rownum<=75;
从块中读满足条件的行产会产生额外的逻辑IO。而只要在Arraysize范围内，跨块读并不会额外增加逻辑IO

观察写脏块的信息。
select * from v$sysstat where lower(name) like '%physical writes%';

利用 alter session set events 'immediate trace name buffers level 1'; 我们可以将Buffer cache中内存块的实际内容转存到磁盘上去，我们就利用它来观察Oracle完成修改时Buffer的状态。

当前读
为修改而读块，就是当前读
一致读
因查询而读块，是一致读
当前读、一致读和起来是逻辑读
在更新和删除时，当前读的数量，不会小于你所修改的行数。而插入则又不一同，它所产生的当前读，按你插入的行所占的块数计算，一般会略多于你实际插入的块数。

Oracle中，对表的所有DML操作，不会阻塞SELECT。
当SELECT的行在另外会话被修改过，但还没有提交时，Oracle会到回滚段中取出块的前映像信息，构造一个和块没被修改前一模一样的块，供Select声明读取。
这个一模一样的块，就是CR块
CR块是Oracle实现“写不阻塞读”这一重要创新的基石，因此，避免CR块的产生是不可能的。
CR块可以很快被重用，只要在LRU列冷端遇到CR块，就可以重用。另外，同一块的CR块数量超过5个，也会重用以前的CR块。

查看等待事件
select * from v$session_event where sid>=8 and event not like 'SQL*Net%' order by sid;
select * from v$session_wait where event not like 'SQL%' and event<>'null event' and sid>=8 order by sid;
在X$BH视图中，NXT_HASH和PRV_HASH列分别代表Cache buffers chains链上一BH（Buffer header）、下一BH的地址。当此两值相等时，Cache buffers chains上只有一个BH。
select count(*) from x$bh where nxt_hash=prv_hash;

Cache buffers chains latch的个数：
select count(*) from v$latch_children where name='cache buffers chains'
如果你发现某一个CBC latch上有等待，通过用V$LATCH_CHILDREN.ADDR列和X$BH.hladdr列关联，可以得知造成CBC latch等待的块的文件号和编号。
select * from v$waitstat;   v$session_wait

SELECT component, parameter, initial_size, final_size, status,
to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed
FROM v$sga_resize_ops ORDER BY component;

SELECT component, min(final_size) low, (min(final_size/1024/1024)) lowMB,
max(final_size) high, (max(final_size/1024/1024)) highMB
FROM v$sga_resize_ops GROUP BY component ORDER BY component;

工作区使用：
SQL> select a.statistic#,sid,name,value from v$sesstat a,v$statname b
where a.statistic#=b.statistic# and a.sid=&sid and b.name like 'workarea%';

PGA资料
V$SQL_WORKAREA
V$SQL_WORKAREA_HISTOGRAM
V$SQL_WORKAREA_ACTIVE
V$PROCESS
在驱动行源表(就是您正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。
嵌套循环连接比其他连接方法有优势地方是,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。
这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录

当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。
在哈希连接中,Oracle访问一张表(通常是较大的表),并在内存中建立一张基于连接键的哈希表。
然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。
当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。
哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。
哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速

和排序合并连接、群集连接一样，哈希连接只能用于等价连接。
和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。
最后，只有基于代价的优化器才可以使用哈希连接。

在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
但是,排列合并连接只能用于等价连接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。
排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。

大纲，又叫存储大纲。它提供一种手段，可以跨越Oracle版本、数据库改变或者其他可能导致执行计划改变的因素，使用某条语句的执行计划保持不变

用于记录SQL信息的Stats表是stats$sql_summary和stats$sqltext。

收集的表资料：
SQL> exec dbms_stats.delete_table_stats('u2','t1');

当用户发出语句访问一个表时，如果此表没有收集过资料，Oracle可以先估计表的资料，再根据估计的资料生成执行计划，这被叫做动态采样。
开启系统资料收集
SQL> exec dbms_stats.gather_system_stats('START');

创建柱状图命令非常简单：
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('scott','aa', METHOD_OPT => 'FOR COLUMNS SIZE 10 OBJECT_ID');
SIZE 10：创建11个桶。也可以不指定桶个数，让Oracle自行统计：SIZE AUTO。
当使用绑定变量时，优化器将无法正确使用柱状图。
Oracle 9iR1的一个新特性是优化程序能够在第一次硬分析一个查询前窥视绑定变量值。
这表示优化程序将看到绑定变量值，然后就像这些值是查询中的字面值一样对查询进行优化。
当执行计划生成后，再有同样的声明发布时，也就是软解析时，优化器就不再去窥视绑定量了。

段层资料是Oracle自动收集的资料
它并不用来生成执行计划，而用于查找I/O问题
通过以下三个视图查看：
V$Segstat_name
V$Segstat
V$Segment_statistics

另有一个命令也可以用来收集表、列、索引资料。那就是Analyze，不过这个命令在9i之后就不再推荐使用。
如果是为了优化器而收集资料，最后使用DBMS_STATS包中的函数。不过，Analyze可以收集一些优化器不使用的资料。

例如，在使用DBMS_STATS.GATHER_TABLE_STAT后，显示DBA_TABLES的结果：
SQL> select num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len from user_tables where table_name='T1';
仍有好几列为0。再使用Analyze后，这些为0的列将被填入具体的数据：
SQL> analyze table t1 compute statistics;
收集行迁移、链接的资料
SQL> analyze table t1 list chained rows;

使用analyze index还能收集一些Dbms_stats.gather_index_stats不能收集的资料：
SQL> analyze index t1_id validate structure;
资料被存入INDEX_STATS表。INDEX_STATS是个基于会话的临时表。在此表中，我们主要可以看到索引中已经删除行的数量，通过除以索引中总的行数，我们可以得到一个比值。
如果已经删除行的比例超过了20%，我们就应该重建索引，以提高索引的效率。
删除行过多的情况不仅因为你删除了表中太多行，相应的索引也跟着被删除。
在你Update表中索引列时，索引并没有Update操作，对于索引来说是先删除原有行，再在新的位置插入行。
这也会引起索引中行被删除。因此，不但删除了表中过多行后，有可能需要重建索引，当Update过多行后，也可能需要重建索引

大块的优点和缺点
1．优点
（1）、顺序读取、索引读取的性能更好。
这一点的原因上面已经说过了。
（2）、适于比较大行
2．缺点
（1）、增加了块争用的可能性
这一点的原因上面也已经说过了。
（2）、在Buffer cache中占用了更多的空间
ORACLE每次都是至少将一个块读进Buffer cache。Buffer cache中空间的使用，也是按块大小来划分的，Buffer cache中空间的最小单位就是一个块的大小。
即使只为了从磁盘上读一个字节，ORACLE也必须把那个字节所在块全部的读进缓存。因此，如果块越大，势必占用的缓存空间就越大。


索引聚簇适合DML操作较少且需经常进行连接的表
索引聚簇可以减少I/O但会增加CPU的负担

SQL> alter system flush buffer_cache;
通常很少更新的表，又需要经常连接起来查询，同时数据库的CPU负载并不过重，这时就特别适合使用聚簇。
如果只是前两条满足，更新很少且经常连接起来查询，但此时主机一向CPU负载都比较很高，这时使用聚簇表就需要考虑考虑了，
因为聚簇表的作用就是通过让CPU作更多的工作，来减少物理I/O。在CPU负载过高的情况下，不太适合再去额外的增加CPU的负担。

HASH聚簇适用于DML较少且需经常进行等值查询的单表
HASH聚簇可以减少I/O，但会增加CPU的负担

创建一个索引组织表：
SQL> create table t3_iot(id number(5), name varchar2(30), primary key(id)) organization index tablespace tp1 ;

映像表名称的格式是SYS_IOT_MAP_对象ID。
SQL> select object_id from dba_objects where object_name='T3_IOT';
SQL> select * from sys_iot_map_51936 where rownum<=2;

删除映像表：
SQL> alter table t3_iot move NOMAPPING;
当映像表被删除后，相应的位图索引将随之被删除。

创建一个带有溢出段的索引组织表：
SQL> create table iot2(id number(5),rq date,bz varchar2(2000),primary key(id)) organization index pctthreshold 5 overflow;
SQL> create table iot3(id number(5),rq date,bz varchar2(2000),primary key(id)) organization index tablespace users including rq overflow tablespace tp1;
与索引组织表相关的段类型有三种，可以在DBA_TABLES中的IOT_TYPE列中看到。
IOT_TYPE列为IOT代表段是索引组织表段，
IOT_TYPE列为IOT_MAPPING代表是映像表段
IOT_TYPE列为IOT_OVERFLOW代表段是溢出段

反向键索引的作用，减少顺序增加列插入时的争用。创建反向键索引
SQL> create unique index test1_id3 on test1(id3) reverse;
create index test1_id on test1(id1,id2,id3) compress 2 reverse;

整个重定义期间，只有最后的对换表名、修改所有属性、所有对象这一步是要封锁表的。不过，这一步只是修改数据字典，速度是很快的。其他各步骤，对表并不加锁。对原表可以正常修改。

就是优化器根据语句中的条件，可以确定只访问部分分区就可以满足用户的需求，而不必访问表的所有分区。这就是分区消除
分区的消除不但可以提高速度，还可以提高可用性。在一部分数据已损坏的情况下，只要不访问这部分已经损坏的数据，整个表还可以正常的访问，可用性比普通表要好一些。
行移动完成的操作将和删除再重新插入行类似。这比正常的在区内的更新要消耗更多的资源，我们在设计分区表时，应该尽量避免这种情况的出现。
SQL> alter table range1 enable row movement;

Oracle提供了两套命令收集资料，一套是Analyze另一套是DBMS_STATS包，有关分区的资料，我们一定要使用DBMS_STATS包。
因为Analyze有时会收集到错误的资料。而且DBMS_STATS包可以并行收集资料，而Analyze只能串行的收集。
DBMS_STATS.GATHER_TABLE_STATS在收集分区资料时，有个粒度选项：granularity。使用它我们可以选择在全局层、分区层、子分区层收集资料。
在全局层收集资料是只收集表层资料，并不涉及各个分区的资料。
SQL> exec dbms_stats.delete_table_stats('U3','PT3');
SQL> exec dbms_stats.gather_table_stats('U3','PT3',granularity=>'GLOBAL');
对于HASH分区，除非条件是等值条件，否则分区消除不可用：


局部索引就是对表的每个分区，独立的创建索引
局部索引又分两种：
- 局部前缀索引
- 局部非前缀索引
注意局部索引不适合需要索引的约束
在局部前缀索引中，分区列就是索引列。如果索引是组合索引，分区列是索引关键中的第一个列。这就是局部前缀索引。如果索引关键字中不包含分区列，就是局部非前缀索引。
这两种局部索引，在性能上并没有好坏之分，应该根据应用、以最大化分区消除为目的进行选择。分区消除是我们使用分区的一个很重要的因素。
局部非前缀索引不能是唯一索引：

创建局部索引
SQL> create index pt2_inx on pt2(load_date) local;
交换：
SQL> alter table pt3 exchange partition fy_2006 with table fy_2006 including indexes without validation;
对于不可用的全局索引，现在只能通过重建来解决问题了。
SQL> alter index pt3_idx_g rebuild partition p1;
如果系统目前是CPU密集型的应用，而且CPU的使用不是因为竞争和Latch的自旋（Spin），那么，引入分区可能并不能提高效率，而只会让性能更糟。