Oracle调优总结

/*==========================================================================
*Author: [e-mail]
*Date:   09/05/2007
*Description:ORACLE Summary
*==========================================================================*/
Problem Description:
1.每个表的结构及主键索引情况
2.每个表的count(*)记录是多少
3.对于创建索引的列，索引的类型是什么？count(distinct indexcol)的值是多少？
4.最后一次对表进行分析是在什么时间，分析后，是否又对相关表做过大的操作
5.索引最后一次rebuild，是在什么时间，此后对表的操作类型又是什么状况？索引中浪费的空间是多少？
6.这些表的存储情况，表的存储参数，表空间的类型，存储参数等
7.执行该SQL语句时，系统等候的资源是什么？ Trace SQL语句的执行过程
8.另一台执行相似SQL速度很快的机器上的相关表的如上信息是什么？

一：SQL tuning 类
1:列举几种表连接方式
  hash join/merge join/nest loop(cluster join)/index join

2:不借助第三方工具，怎样查看sql的执行计划
set autotrace on
set autotrace traceonly

explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);
http://download-west.oracle.com/ ... /b10752/ex_plan.htm

3:如何使用CBO,CBO与RULE的区别
  在optimizer_mode=choose时,如果表有统计信息（分区表外）,优化器将选择CBO,否则选RBO。

  RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目,
然后选择最佳级别（最少的数量）的执行路径来运行查询。
CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同
的执行计划的成本，并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。

4:如何定位重要(消耗资源多)的SQL
  select sql_text
  from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

5:如何跟踪某个session的SQL
  exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);

select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
  exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

6:SQL调整最关注的是什么
  查看该SQL的response time(db block gets/consistent gets/physical reads/sorts (disk))

7:说说你对索引的认识（索引的结构、对dml影响、为什么提高查询性能）
  b-tree index/bitmap index/function index/patitional index(local/global)
  索引通常能提高select/update/delete的性能,会降低insert的速度,

8:使用索引查询一定能提高查询的性能吗？为什么
  索引就是为了提高查询性能而存在的,
如果在查询中索引没有提高性能,
只能说是用错了索引,或者讲是场合不同

9:绑定变量是什么？绑定变量有什么优缺点？
  绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件，
这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，
查询bind value在运行时传递，然后绑定执行。

优点是减少硬解析,降低CPU的争用,节省shared_pool
缺点是不能使用histogram,sql优化比较困难

10:如何稳定(固定)执行计划
  query_rewrite_enabled = true
  star_transformation_enabled = true
  optimizer_features_enable = 9.2.0

创建并使用stored outline
  http://download-west.oracle.com/ ... /outlines.htm#26854

这个贴子:
http://www.cnoug.org/viewthread.php?tid=27598

11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么
   8i中sort_area_size/sort_area_retained_size决定了排序所需要的内存
  如果排序操作不能在sort_area_size中完成,就会用到temp表空间

  9i中如果workarea_size_policy=auto时,
  排序在pga内进行,通常pga_aggregate_target的1/20可以用来进行disk sort;
  如果workarea_size_policy=manual时,排序需要的内存由sort_area_size决定

   在执行order by/group by/distinct/union/create index/index rebuild/minus等操作时,
  如果在pga或sort_area_size中不能完成,排序将在临时表空间进行（disk sort）,
  临时表空间主要作用就是完成系统中的disk sort.

12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
   create table t(a number(,b number(,c number(,d number();
  /
  begin     
    for i in 1 .. 300 loop
      insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);
    end loop;
  end;
  /
   select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
   /
  select * from (select * from test order by c desc) x where rownum < 30
  minus
   select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc
   相比之 minus性能较差

二：数据库基本概念类

1:pctused and pctfree 表示什么含义有什么作用
  pctused与pctfree控制数据块是否出现在freelist中,
pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时,
该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将
被添加在freelist链表中。
--PCTFREE存储参数
　　PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是PCTFREE=10；
也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。
　--PCTUSED存储参数
　　PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数
据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往
其中插入数据。PCTUSED的默认值是PCTUSED=40。
--存储参数规则小结
　　（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致
I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以
减少I/O消耗，提高性能。
　　（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。PCTFREE
过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration)是指记录在是
UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到
新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。
　　（3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有助于提高
批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。

---------------------------------------------------------------------------------------------------------------------------------------------------
[e-mail] 17/05/2007--
--表的pctfree和pctused两个参数进行估算的方法

对于不同的应用系统，表的pctfree 和pctused两个参数有不同的设计原则，以下是根据特定的应用系统进行估算的例子，从中可以掌握基本的估算方法。

表的存储参数调整，一般情况，设置为pctfree 5 pctused 85即可（缺省为pctfree 10 pctused 40）
1.对于Pctfree参数
除了可以按字段及字段长度估算平均行长外，下面的方面可以根据已有数据分析出平均行长和每块行数
例：
analyze table 病人信息 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人信息'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN  
857291 14161 61 117

对于一般8192的块，实际可用空间为8100左右.
假设以前该表的pctfree为15，改为5后，pctfree减少10，就可以再存入约7行.

相同的1万4千块就可以多存放约10万行数据，
这10万行数据，如果按每块60行算，就可以少占用约1700块(约13M的空间)
如果全表扫描该表的话，少读1700块数据，少106次IO操作(按缺省db_file_multiblock_read_count=16计算)
少占13M的内存

另外，需要考虑的两个因素
1。更新操作时，数据增长量大不大，例如：主要是把状态字段由1改为3，还是把摘要由空改为一段文字
2。并发事务的多少，因为一个事务信息在块中要占用约24Byte，如果有10个并发事务的话，至少额外考虑240Byte的空闲空间。

2.对于Pctused参数
主要考虑删除后插入数据的情况多不多，以及平均行长大小
例如：
病人费用记录，医保如果存在校对操作的话，是先产生预交结算数据，正式结算时，删除这些数据再重新生成
所以，病人预交记录,Pctused不能设置太高，否则重用那些低于Pctused的块，只能插入少量数据行，增加了IO操作
analyze table 病人预交记录 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人预交记录'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN  
181758 2147 85 83

如果设置pctfree 5 pctused 85，那么当删除一些行使块的已用空间低于85%时，块会被重用，但是因为要预留5%的空闲空间，
所以,对于已用空间刚刚低于85%的块，重用空间就只有10%,对于8K的块，可用810Byte,平均行长83,可以再放入9行，所以这个参数也是可以的。

但是，如果是病人费用记录,平均行长229，这样设置，只能放下3行，这个参数就不太合适了

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN  
925133 38278 24 229

根据分析，病人费用记录的数据更新量不大，但是并发操作比较大，最好把Pctfree设置高一点
所以，可以设置为pctfree 10 pctused 75（重用的块至少可以放5行，约1-2张单据）,甚至pctused 70也是可以的。

如果一个块的数据行数太多，可能造成热块争用，但是相对于减少存储，减少IO，减少内存占用带来的好处来说，热块不是特别突出的情况下可以不考虑。

[e-mail] 17/05/2007--

PCTFREE=(Average Row Size-Initial Row Size)*100/Average Row Size
PCTUSED=(100-PCTFREE) -Average Row Size * 100/Availabe Data Space
　　
　　Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle的表和索引的
内部运作。不过，对于有经验的Oracle调优专家来说，他需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系
统来说，这是非常重要的。
　　
　　要精通对象的调整，你需要懂得freelists和freelist组的行为，它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划（ERP）的应用是
特别重要的，因为在这些应用中，不正确的表设置通常是DML语句执行慢的原因。　
　　对于初学者来说，最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的pctfree和pctused
参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists中。当这些设置不正确时，那些得到的freelists也是"dead"块，
因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
　　Freelists对于有效地重新使用Oracle表空间中的空间是很重要的，它和pctfree及pctused这两个存储参数的设置直接相关。如果将pctused设置为一个高的值，
这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空
间重用，并且据此来设置表的参数。以下我们来看一下这些freelists是如何影响Oracle的性能的。
　　
　　当有一个请求需要插入一行到表格中时，Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。你也许知道，freelist串是放在表格或者索引的第
一个块中，这个块也被称为段头（segment header）。pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelist link和 unlink
是简单的Oracle功能，不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle的性能确实有影响。
　　
　　由DBA的基本知识知道，pctfree参数是控制freelist un-links的（即将块由freelists中移除）。设置pctfree=10 意味着每个块都保留10%的空间用作行扩展。
pctused参数是控制freelist re-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。
　　
　　许多新手对于一个块重新回到freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到freelist中，它将会一直保留在freelist中
即使空间的使用超过了60%，只有在到达pctfree时才会将数据块由freelist中移走。
　　
　　表格和索引存储参数设置的要求总结
　　
　　以下的一些规则是用来设置freelists, freelist groups, pctfree和pctused存储参数的。你也知道，pctused和pctfree的值是可以很容易地通过alter table
命令修改的，一个好的DBA应该知道如何设置这些参数的最佳值。
　　
　　有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
　　
　　. 对于需要有效地重新使用空间，可以设置一个高的pctused值，不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因
此，这些块在再次满之前只可以接受几行记录，从而导致更多的I/O。
　　
　　. 追求高性能的话，可以将pctused设置为一个低的值，这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行，
因此可以减少插入操作的I/O。要记住Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说，扩展一个表比管理freelists消耗更少的资源。
　　
　　让我们来回顾一下设置对象存储参数的一些常见规则：
　　
　　．经常将pctused设置为可以接收一条新行。对于不能接受一行的free blocks对于我们来说是没有用的。如果这样做，将会令Oracle的性能变慢，因为Oracle将
在扩展表来得到一个空的块之前，企图读取5个"dead" 的free block。
　　
　　．表格中chained rows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下，RAW和LONG RAW列都很巨大，以至超过了Oracle的最大块的大小，
这时chained rows是不可以避免的。
　　
　　．如果一个表有同时插入的SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中，而没有其它包含有任何
空闲块的freelists出现。
　　
　　．freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有20个用户执行插入的操作，那么该表的参数应该设置为freelists=20。
　　
　　应记住的是freelist groups参数的值只是对于Oracle Parallel Server和Real Application Clusters才是有用的。对于这类Oracle，freelist groups应该设置
为访问该表格的Oracle Parallel Server实例的数目。

---------------------------------------------------------------------------------------------------------------------------------------------------

2:简单描述table / segment / extent / block之间的关系
  table创建时,默认创建了一个data segment,
每个data segment含有min extents指定的extents数,
每个extent据据表空间的存储参数分配一定数量的blocks

3:描述tablespace和datafile之间的关系
一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内,
table中的数据,通过hash算法分布在tablespace中的各个datafile中,
tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。

4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
  本地管理表空间（Locally Managed Tablespace简称LMT）
  8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。
  字典管理表空间（Dictionary-Managed Tablespace简称DMT）
  8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。
  动段空间管理（ASSM），
  它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，
  能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，
  ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。

5:回滚段的作用是什么
事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，
          当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
　
  事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，
           ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。
           当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）
      当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）
      来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，
      若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。
  http://www.itpub.net/showthread. ... E%B5%C4%D7%F7%D3%C3

6:日志的作用是什么
  记录数据库事务,最大限度地保证数据的一致性与安全性

  重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复,一个数据库至少需要两个重做日志文件
  归档日志文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

http://www.cnoug.org/viewthread. ... hlight=%C8%D5%D6%BE

7:SGA主要有那些部分，主要作用是什么
  SGA：db_cache/shared_pool/large_pool/java_pool
db_cache:
  数据库缓存（Block Buffer）对于Oracle数据库的运转和性能起着非常关键的作用，
   它占据Oracle数据库SGA（系统共享内存区）的主要部分。Oracle数据库通过使用LRU
   算法，将最近访问的数据块存放到缓存中，从而优化对磁盘数据的访问.
shared_pool:
  共享池的大小对于Oracle 性能来说都是很重要的。
  共享池中保存数据字典高速缓冲和完全解析或编译的的PL/SQL 块和SQL 语句及控制结构
large_pool:
  使用MTS配置时，因为要在SGA中分配UGA来保持用户的会话，就是用Large_pool来保持这个会话内存
   使用RMAN做备份的时候，要使用Large_pool这个内存结构来做磁盘I/O缓存器
java_pool:
  为java procedure预备的内存区域,如果没有使用java proc,java_pool不是必须的
 
8racle系统进程主要有哪些，作用是什么
  数据写进程(dbwr)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件
  日志写进程(lgwr)：将重做日志缓冲区中的更改写入在线重做日志文件
  系统监控(smon)  ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复
  进程监控(pmon)  ：负责在一个Oracle 进程失败时清理资源
  检查点进程(chpt)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。
  归档进程(arcn)  ：在每次日志切换时把已满的日志组进行备份或归档
  作业调度器(cjq)  :负责将调度与执行系统中已定义好的job,完成一些预定义的工作.
恢复进程(reco)  :保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

三：备份恢复类

1:备份如何分类
逻辑备份：exp/imp
物理备份：
    RMAN备份
     full backup/incremental backup(累积/差异)
     热备份:alter tablespace begin/end backup;
     冷备份:脱机备份(database shutdown)
   
2:归档是什么含义
关于归档日志：Oracle要将填满的在线日志文件组归档时,则要建立归档日志（archived redo log）。
其对数据库备份和恢复有下列用处：
    <1>数据库后备以及在线和归档日志文件，在操作系统和磁盘故障中可保证全部提交的事物可被恢复。
    <2>在数据库打开和正常系统使用下，如果归档日志是永久保存，在线后备可以进行和使用。
  数据库可运行在两种不同方式下：
   NOARCHIVELOG方式或ARCHIVELOG 方式
数据库在NOARCHIVELOG方式下使用时，不能进行在线日志的归档,
如果数据库在ARCHIVELOG方式下运行，可实施在线日志的归档。

3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复?
  手工拷贝回所有备份的数据文件
sql>startup mount;
sql>alter database recover automatic until time '2004-08-04:10:30:00';
sql>alter database open resetlogs;

4:rman是什么,有何特点?
RMAN(Recovery Manager)是DBA的一个重要工具，用于备份、还原和恢复oracle数据库,
RMAN 可以用来备份和恢复数据库文件、归档日志、控制文件、系统参数文件,也可以用来执行完全或不完全的数据库恢复。
RMAN有三种不同的用户接口：
  COMMAND LINE方式、GUI 方式（集成在OEM 中的备份管理器）、API 方式（用于集成到第三方的备份软件中）。
具有如下特点：
1）功能类似物理备份，但比物理备份强大N倍；
2）可以压缩空块；
3）可以在块水平上实现增量；
4）可以把备份的输出打包成备份集，也可以按固定大小分割备份集；
5）备份与恢复的过程可以自动管理；
6）可以使用脚本（存在Recovery catalog 中）
7）可以做坏块监测

5:standby的特点
备用数据库（standby database）：ORACLE推出的一种高可用性(HIGH AVAILABLE)数据库方案，
在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份
可以实现快速切换与灾难性恢复,从920开始，还开始支持物理与逻辑备用服务器。
  9i中的三种数据保护模式分别是：
  1)、MAXIMIZE PROTECTION ：最大数据保护与无数据分歧，LGWR将同时传送到备用节点，
    在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。
  2)、MAXIMIZE AVAILABILITY ：无数据丢失模式，允许数据分歧，允许异步传送。
    正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，
   主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。
  3)、MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，
    无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE

6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略
  rman/每月一号 level 0 每周末/周三 level 1 其它每天level 2

四：系统管理类

1:对于一个存在系统性能的系统，说出你的诊断处理思路
1 做statspack收集系统相关信息
  了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等
2 查v$system_event/v$session_event/v$session_wait
   从v$system_event开始,确定需要什么资源(db file sequential read)等
  深入研究v$session_event,确定等待事件涉及的会话
  从v$session_wait确定详细的资源争用情况(p1-p3的值:file_id/block_id/blocks等)
3 通过v$sql/v$sqltext/v$sqlarea表确定disk_reads、(buffer_gets/executions)值较大的SQL

2:列举几种诊断IO、CPU、性能状况的方法
    top/vmstat
  statspack
  sql_trace/tkprof
    查v$system_event/v$session_event/v$session_wait
  查v$sqlarea(disk_reads或buffer_gets/executions较大的SQL)

3:对statspack有何认识
StapSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包，该软件包从8i起，在9i、10g都有显著的增强
该软件包的辅助表（存储相关参数与收集的性能指标的表）由最初的25个增长到43个
收集级别参数由原来的3个（0、5、10）增加到5个（0、5、6、7、10）
通过分析收集的性能指标，数据库管理员可以详细地了解数据库目前的运行情况，对数据库实例、等待事件、SQL等进行优化调整
利用statspack收集的snapshot,可以统计制作数据库的各种性能指标的统计趋势图表。

4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
在系统比较空闲时
nologging选项（如果有dataguard则不可以使用nologging）
大的sort_ared_size或pga_aggregate_target较大

5:对raid1+0 和raid5有何认识
RAID 10(或称RAID 1＋0)与RAID 0＋1不同，它是用硬盘驱动器先组成RAID 1阵列，然后在RAID 1阵列之间再组成RAID 0阵列。
RAID 10模式同RAID 0+1模式一样具有良好的数据传输性能，但却比RAID 0+1具有更高的可靠性。RAID 10阵列的实际容量为M×n/2，
磁盘利用率为50％。RAID 10也需要至少4个硬盘驱动器构成，因而价格昂贵。
  RAID 10的可靠性同RAID 1一样，但由于RAID 10硬盘驱动器之间有数据分割，因而数据传输性能优良。

RAID 5与RAID 3很相似，不同之处在于RAID 5的奇偶校验信息也同数据一样被分割保存到所有的硬盘驱动器，
而不是写入一个指定的硬盘驱动器，从而消除了单个奇偶校验硬盘驱动器的瓶颈问题。RAID 5磁盘阵列的性能比RAID 3有所提高，
但仍然需要至少3块硬盘驱动器。其实际容量为M×(n-1)，磁盘利用率为(n-1)/n 。

五：综合随意类

1:你最擅长的是oracle哪部分?
pl/sql及sql优化

2:喜欢oracle吗？喜欢上论坛吗？或者偏好oracle的哪一部分？
喜欢,sql的优化

3:随意说说你觉得oracle最有意思的部分或者最困难的部分
latch free的处理

4:为何要选择做DBA呢?
兴趣所在

[e-mail] 17/05/2007--
消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。
但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。
准备执行SQL语句
当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：
1) 语法检查：检查SQL语句拼写是否正确和词序。

2) 语义分析：核实所有的与数据字典不一致的表和列的名字。

3) 轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。

4) 生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。

5) 建立二进制代码：基于执行计划，Oracle生成二进制执行代码。

一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成
特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并且前一个执行计划通常不能够被重用。
对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。

评估表的连接顺序

在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须
考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）种可能的连接线路。
当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿
（准确的数字是1,307,674,368,000）种。

使用optimizer_search_limit参数来设定限制

通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器
消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的
连接组合。

例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5
（默认值），则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的
数目比optimizer_search_limit小时，带星号的提示将被优先考虑。

另一个工具：参数optimizer_max_permutations

初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限，且依赖于初始参数optimizer_search_limit。
optimizer_max_permutations的默认值是80,000。

参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限：除非（表或组合数目）
超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值，否则优化器将生成所有可能的连接组合。一旦优
化器停止评估表的连接组合，它将选择成本最低的组合。

使用ordered提示指定连接顺序

你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估，我们仍然拥有使优化器可以尽早地放弃复杂的查询
的重要机会。回想一下含有15个连接查询的例子，它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止，那么它才
仅仅评估了0.000006%的可能组合，而且或许还没有为这个巨大的查询找到最佳的连接顺序。

在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起
来，通常优先使用限制最严格的WHERE子句来连接表。

下面的代码是一个查询执行计划的例子，该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意，我已经使用了ordered提示来
直接最优化表的评估顺序，最终它们表现在WHERE子句上。
select /*+ ordered use_nl(bonus) parallel(e, 4) */
　　  e.ename,
　　 hiredate,
　　 b.comm.
from
　　  emp e,
　　  bonus b
where
　　 e.ename = b.ename
这个例子要求优化器按顺序连接在SQL语句的FROM子句中指定的表，在FROM子句中的第一个表指定了驱动表。ordered提示通常被用来与
其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。
另外一个例子，下面的查询使用ordered提示按照指定的顺序来连接表：emp、dept、sal，最后是bonus。我通过指定emp到dept使用哈
希连接和sal到bonus使用嵌套循环连接，来进一步精炼执行计划。 　
select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
　　
from
　　 emp,
　　  dept,
　　  sal,
　　  bonus
where . . .
　　
实践建议
实际上，更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小，并且总是使用稳定的优化计划或存储轮廓来
防止出现耗时的含有大量连接的查询。一旦找到最佳的连接顺序，您就可以通过增加ordered提示到当前的查询中，并保存它的存储轮廓，
来为这些表手工指定连接顺序，从而使其持久化。
当你打算使用优化器来稳定计划，则可以照下面的方法使执行计划持久化，临时将optimizer_search_limit设置为查询中的表的数目，
从而允许优化器考虑所有可能的连接顺序。然后，通过重新编排WHERE子句中表的名字，并使用ordered提示，与存储轮廓一起使变更
持久化，来调整查询。在查询中包含四个以上的表时，ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务，从而提高
查询的速度。
一旦检测到最佳的连接顺序，我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数。
ordered提示要求表按照它们出现在FROM子句中的顺序进行连接，所以优化器没有加入描述。
作为一个Oracle专业人员，你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟。但是聪明的Oracle DBA和开发人
员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序，从而显著地减少优化和执行新查询所需的时间

[e-mail]  14/05/2007--

Oracle专家调优秘密

在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用 Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一
是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。 Oracle 调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善
Oracle 数据库的性能，有一些基本的概念是每个 Oracle DBA 都应该遵从的。
　　 在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
　　-- 外部调整：我们应该记住 Oracle 并不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能。
　　--Row re-sequencing 以减少磁盘 I/O ：我们应该懂得 Oracle 调优最重要的目标是减少 I/O 。
　　--Oracle SQL 调整。 Oracle SQL 调整是 Oracle 调整中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都
不奇怪的。
　　-- 调整 Oracle 排序：排序对于 Oracle 性能也是有很大影响的。
　　-- 调整 Oracle 的竞争：表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响。

　　 我们首先从调整 Oracle 外部的环境开始。如果内存和 CPU 的资源不足的话，任何的 Oracle 调整都是没有帮助的。

　　外部的性能问题
　　
　　Oracle 并不是单独运行的。 Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：
　　 ．CPU--CPU 资源的不足令查询变慢。当查询超过了 Oracle 服务器的 CPU 性能时，你的数据库性能就受到 CPU 的限制。
　　 ．内存 -- 可用于 Oralce 的内存数量也会影响 SQL 的性能，特别是在数据缓冲和内存排序方面。
　　 ．网络 -- 大量的 Net8 通信令 SQL 的性能变慢。
　　 许多新手都错误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle 调整都是没有帮助的。
　　 在检查 Oracle 的外部环境时，有两个方面是需要注意的：
　　1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的限制。补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件，
例如 Oracle Parallel Query 。
　　2 、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA 的大小，或者关闭
Oracle 的多线程服务器。
　　 可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top 和 sar 。 DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理
Oracle 的请求。
　　 以下让我们来看一下 Oracle 的 row-resequencing 是如何能够极大地减少磁盘 I/O 的。

　　Row-resequencing （行的重新排序）
　　
　　就象我们上面提到的，有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分。其中磁盘 I/O 特别厉害，因为当 Oracle 由磁盘上的一个数据文件得到一个
数据块时，读的进程就必须等待物理 I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以令 I/O 最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈
，就可以大大地改善 Oracle 数据库的性能。如果系统响应很慢，通过减少磁盘 I/O 就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key
索引来访问表，那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略。通过在物理上将行排序为和 primary-key 索引一样的顺序，就可以加快获得数据的速度。
就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系统中大大地减少响应的时间。 在高容量
的在线事务处理环境中（ online transaction processing ， OLTP ），数据是由一个 primary 索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary
索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O 并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询
来得到连续的 key 时有效。对于随机的唯一 primary-key （主键）的访问将不会由行重新排序中得到好处。
　　 让我们看一下它是如何工作的。考虑以下的一个 SQL 的查询，它使用一个索引来得到 100 行：
select salary from employee where last_name like 'B%';
这个查询将会使用 last_name_index ，搜索其中的每一行来得到目标行。这个查询将会至少使用 100 次物理磁盘的读取，因为 employee 的行存放在不同的数据块中。
　　 不过，如果表中的行已经重新排序为和 last_name_index 的一样，同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 100 个
员工的资料（一次用作索引的读取，两次用作数据块的读取），减少了 97 次的块读取。
　  重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据
字典中的 dba_indexes 和 dba_tables 视图得到。
　　 在 dba_indexes 的视图中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果
clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
　　 行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
　　 一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。
　　. 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
　　. Oracle9i 自带的表格重新组织工具
　　
SQL 语句的调优
　　SQL 调优
　　Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的细微差别。不过有一些基本的规则是每个
Oracle DBA 都需要跟从的，这些规则可以改善他们系统的性能。 SQL 调优的目标是简单的：
　　消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的 I/O ，从而拖慢整个数据库的性能。调优专家首先会根据查询
返回的行数目来评价 SQL 。在一个有序的表中，如果查询返回少于 40% 的行，或者在一个无序的表中，返回少于 7% 的行，那么这个
查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说，最常见的调优方法是增加索引。可以在表中加入标准的
B 树索引，也可以加入 bitmap 和基于函数的索引。要决定是否消除一个全表搜索，你可以仔细检查索引搜索的 I/O 开销和全表搜索
的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对比。在一些情况下，一些不必要的全表搜索的消除可以通过
强制使用一个index 来达到，只需要在 SQL 语句中加入一个索引的提示就可以了。
　　在全表搜索是一个最快的访问方法时，将小表的全表搜索放到缓存中，调优专家应该确保有一个专门的数据缓冲用作行缓冲。在
Oracle7 中，你可以使用 alter table xxx cache 语句，在 Oracle8 或以上，小表可以被强制为放到 KEEP 池中缓冲。
　  确保最优的索引使用 ：对于改善查询的速度，这是特别重要的。有时 Oracle 可以选择多个索引来进行查询，调优专家必须检查
每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。
　　. 确保最优的 JOIN 操作：有些查询使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是
sort-merge join 更快。
这些规则看来简单，不过它们占 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL
的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。
　　 调整 Oracle 的排序操作
　　 排序是 SQL 语法中一个小的方面，但很重要，在 Oracle 的调整中，它常常被忽略。当使用 create index 、 ORDER BY 或者
GROUP BY 的语句时， Oracle 数据库
将会自动执行排序的操作。通常，在以下的情况下 Oracle 会进行排序的操作：
　　 使用 Order by 的 SQL 语句
　　 使用 Group by 的 SQL 语句
　　 在创建索引的时候
　　 进行 table join 时，由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT
　　 当与 Oracle 建立起一个 session 时，在内存中就会为该 session 分配一个私有的排序区域。如果该连接是一个专用的连接
(dedicated connection) ，那么就会根据 init.ora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area (PGA)
如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool 中分配。不幸的是，对于所有的 session ，用做排序的内存
量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以
避免发生大的排序任务时出现磁盘排序（ disk sorts ）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，
当排序的空间需求超出了 sort_area_size 的大小时，这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢
14,000 倍。
　　 上面我们已经提到，私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora
中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实例中的临
时表空间中进行。 磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时
表空间中的资源。 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会
令任务变慢，并且会影响 Oracle 实例的当前任务的执行。还有，过多的磁盘排序将会令free buffer waits 的值变高，从而令其它
任务的数据块由缓冲中移走。
　　 接着，让我们看一下 Oracle 的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。

调整 Oracle 的竞争
　　Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理，这样就可以让我们无需
懂得 Oracle 的表和索引的内部运作。不过，对于有经验的 Oracle 调优专家来说，他需要懂得 Oracle 是如何管理表的 extent 和空
闲的数据块。对于调整拥有高的 insert 或者 update 的系统来说，这是非常重要的。
　　 要精通对象的调整，你需要懂得 freelists 和 freelist 组的行为，它们和 pctfree 及 pctused 参数的值有关。这些知识对于
企业资源计划（ ERP ）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML 语句执行慢的原因。
　　 对于初学者来说，最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设
置表的 pctfree 和 pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists 中。当这些
设置不正确时，那些得到的 freelists 也是 "dead" 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的，它和 pctfree 及 pctused 这两个存储参数的设置直接相关。
通过将 pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整
Oracle 的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些
freelists 是如何影响 Oracle 的性能的。
　　当有一个请求需要插入一行到表格中时， Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道，
freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头（ segment header ）。 pctfree 和 pctused 参数的唯一目的就
是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能，不过设置 freelist link (pctused)
和 unlink (pctfree) 对 Oracle 的性能确实有影响。
　　 由 DBA 的基本知识知道， pctfree 参数是控制 freelist un-links 的（即将块由 freelists 中移除）。设置 pctfree=10
意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的
使用低于 40% 时才会回到表格的 freelists 中。
　　 许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到 freelist
中，它将会一直保留在 freelist 中即使空间的使用超过了 60% ，只有在到达 pctfree 时才会将数据块由 freelist 中移走。

　　 表格和索引存储参数设置的要求总结
　　 以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道， pctused 和 pctfree
的值是可以很容易地通过 alter table 命令修改的，一个好的 DBA 应该知道如何设置这些参数的最佳值。
　　 有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
. 对于需要有效地重新使用空间，可以设置一个高的 pctused 值，不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满
的块都会放到 freelist 中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的 I/O 。
. 追求高性能的话，可以将 pctused 设置为一个低的值，这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的。
那么块将可以在满之前接收更多的行，
因此可以减少插入操作的 I/O 。要记住 Oracle 扩展新块的性能要比重新使用现有的块高。对于 Oracle 来说，扩展一个表比管理
freelists 消耗更少的资源。
　　 让我们来回顾一下设置对象存储参数的一些常见规则：
　　 ．经常将 pctused 设置为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做，
将会令 Oracle 的性能变慢，因为
Oracle 将在扩展表来得到一个空的块之前，企图读取 5 个 "dead" 的 free block 。
　　 ．表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下， RAW 和 LONG RAW 列都很巨
大，以至超过了 Oracle 的最大块的大小，这时 chained rows 是不可以避免的。
　　 ．如果一个表有同时插入的 SQL 语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个
freelist 中，而没有其它包含有任何空闲块的 freelists 出现。
　　 ． freelist 参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20 个用户执行插入的操作，那么
该表的参数应该设置为 freelists=20 。
　　 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这
类 Oracle ， freelist groups
应该设置为访问该表格的 Oracle Parallel Server 实例的数目。

---------------------------------------------------------------------------------------------------------------------------------------------------
[e-mail] 13/04/2007--　　
  --在rbo中，连接表的顺序，和predicate的顺序都是有要求的，一般会按照从右往左来访问FROM clause的表，从下往上来访问where clause的predicates，
选择小表作驱动表是关键的。
  --cbo中，则主要是根据表的统计信息来觉得访问路径，所以保持表的统计信息准确性就很有必要了。
  --在optimizer_mode＝choose时候，系统首先会判断FROM clause中的表是否有统计信息，如果有，则根据CBO来执行sql，否则根据rbo来执行。也可以通过
hints来改变sql的访问路径。
  --ORACLE檢索順序有關--自底向上，所以小表放在最後面可以縮小檢索范圍,數據量大的放在最左邊（以FROM作參考）

优化器模式
　　rule模式
　　
　　。总忽略CBO和统计信息而基于规则
　　choose模式
　　
　　。Oracle根据情况选择rule or first_rows or all_rows
　　first_rows 模式
　　
　　。基于成本，以最快的速度返回记录，会造成总体查询速度的下降或消耗更多的资源，倾向索引扫描，适合OLTP系统

　　all_rows模式
　　
　　。基于成本，确保总体查询时间最短，倾向并行全表扫描
　　
　　例如：
　　Select last_name from customer order by last_name;用first_rows时，迅速返回记录，但I/O量大，用all_rows时，返回记录慢，但使用资源少。
　　
　　调整SQL表访问
　　
　　全表扫描
　　
　　。返回记录：未排序表>40%，排序表>7%，建议采用并行机制来提高访问速度，DDS；
　　
　　索引访问
　　
　　。最常用的方法，包括索引唯一扫描和索引范围扫描，OLTP；
　　
　　快速完全索引扫描
　　
　　。访问索引中所有数据块，结果相当于全表扫描，可以用索引扫描代替全表扫描，例如：
　　
　　Select serv_id,count(* ) from tg_cdr01 group by serv_id;
　　
　　评估全表扫描的合法性
　　
　　如何实现并行扫描
　　
　　。永久并行化（不推荐）
　　alter table customer parallel degree 8;
　　
　　。单个查询并行化
　　select /*+ full(emp) parallel(emp,8)*/ * from emp;
　　
　　分区表效果明显
　　
　　优化SQL语句排序
　　
　　排序的操作：
　　
　　。order by 子句
　　。group by 子句
　　。select distinct子句
　　。创建索引时
　　。union或minus
　　。排序合并连接
　　
　　如何避免排序
　　
　　。添加索引
　　。在索引中使用distinct子句
　　。避免排序合并连接
　　
　　使用提示进行调整
　　
　　使用提示的原则
　　
　　。语法：/*+ hint */
　　。使用表别名:select /*+ index(e dept_idx)*/ * from emp e
　　。检验提示
　　
　　常用的提示
　　
　　。rule (基于规则)
　　。all_rows
　　。first_rows
　　。use_nl
　　。use_hash
　　。use_merge
　　。index
　　。index_asc
　　。no_index
　　。index_desc（常用于使用max内置函数）
　　。index_combine(强制使用位图索引)
　　。index_ffs（索引快速完全扫描）
　　。use_concat(将查询中所有or条件使用union all)
　　。parallel
　　。noparallel
　　。full
　　。ordered（基于成本）
　　
5.调整表连接
　　
　　表连接的类型
　　
　　。等连接
　　where 条件中用等式连接；
　　。外部连接（左、右连接）
　　
　　在where条件子句的等式谓词放置一个(+)来实现，例如：
　　select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+);
　　
　　该语句返回所有emp表的记录；
　　。自连接
　　　Select a.value total, B.value hard, (A.value - b.value) soft ,
　　Round((b.value/a.value)*100,1) perc
　　From v$sysstat a,v$sysstat b
　　Where a.statistic# = 179
　　and B.statistic# = 180;
　　
　　反连接
　　
　　反连接常用于not in or not exists中，是指在查询中找到的任何记录都不包含在结果集中的子查询；不建议使用not in or not exists;
　　
　　。半连接
　　
　　查询中使用exists，含义：即使在子查询中返回多条重复的记录，外部查询也只返回一条记录。
　　
　　嵌套循环连接
　　
　　。被连接表中存在索引的情况下使用；
　　。使用use_nl。
　　
　　hash连接
　　
　　。Hash连接将驱动表加载在内存中，并使用hash技术连接第二个表，提高等连接速度。
　　。适合于大表和小表连接；
　　。使用use_hash。
　　
　　排序合并连接
　　
　　。排序合并连接不使用索引
　　。使用原则：
　　
　　连接表子段中不存在可用索引；
　　
　　查询返回两个表中大部分的数据快；
　　
　　CBO认为全表扫描比索引扫描执行的更快。
　　
　　。使用use_merge
　　
　　使用临时/中间表
　　
　　多个大表关联时，可以分别把满足条件的结果集存放到中间表，然后用中间表关联；
　　
　　SQL子查询的调整
　　
　　关联与非关联子查询
　　
　　。关联：子查询的内部引用的是外部表，每行执行一次；
　　。非关联：子查询只执行一次，存放在内存中。
　　
　　调整not in 和not exists语句
　　
　　。可以使用外部连接优化not in子句，例如：
　　select ename from emp where dept_no not in
　　(select dept_no from dept where dept_name =‘Math’);
　　
　　改为：
　　select ename from emp,dept
　　where emp.dept_no=dept.dept_no
　　and dept.dept_name is null;
　　
6.使用索引调整SQL
　　
　　Oracle 为什么不使用索引
　　
　　。检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。
　　
　　。看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。
在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多
只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。
　　
　　。看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有
emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，
emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。
　　
　　。是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据
字典表做分析，否则可能导致死锁，或系统性能下降。
　　
　　。索引列是否函数的参数。如是，索引在查询时用不上。
　　
　　。是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，
从而导致上一种现象的发生。
　　
　　。是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句
“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。
　　
　　。索引列的选择性不高。 　　我们假设典型情况，有表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，
如10、20、30、40。虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万
数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高。
　　
　　。索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引，如COUNT（*）
，而是用全表扫描。这是因为索引中存储值不能为全空。
　　
　　。看是否有用到并行查询（PQO）。并行查询将不会用到索引。
　　
　　。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划”。 　hint采用注
释的方式，有行注释和段注释两种方式。 　如我们想要用到A表的IND_COL1索引的话，可采用以下方式：
　SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX;
　　
　　如何屏蔽索引
　　
　　语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：
　　
　　。数值型：在索引字段上加0，例如
　　select * from emp where emp_no+0 = v_emp_no;
　　
　　。字符型：在索引字段上加‘’，例如
　　select * from tg_cdr01 where msisdn||’’=v_msisdn;
---------------------------------------------------------------------------------------------------------------------------------------------------
[e-mail] 17/04/2007--

（1）RBO&CBO。

Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；
另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引
没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的
方向，自8i版本来已经逐渐取代RBO.

（2）AUTOTRACE。

要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行
sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：

① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请
运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。

② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行
plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。

③ AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。
Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

    SQL> set autotrace on
SQL> select * from test;
         A
----------
         1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
rows processed


SQL> set autotrace traceonly
SQL> select * from test.test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
rows processed

Hints是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起得作用也是提示优化器按它所提供的关键字来
选择执行路径，特别适用于sql调整的时候。使

评论

1 楼 bo521dai 2012-10-23  

Bravo. contains everything.