关于oracle优化器的迷惑!!

Question

oracle的优化器毕竟不是智能的，所以每个sql都要做explain plan看看是否预期。如果不是预期而你又明确知道如何使用优化，直接用hint指定优化模式。

Answer 1

To drabit(square) ：
   何为预期的？如果不是预期的，又应该怎么改呢？能不能举个例子说明一下，不然我听得是云里雾里的。To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！

Answer 2

to diandianqiuqiu:我说的预期是指你写了一个sql，然后你期望它适用何种方式访问数据：
对于单表查询，最常用的是
1.index unique scan
2.index range scan
3.index fast full scan
4.table full scan看看下面的sql:
  select * from table_a where nvl(col1)='expect';
  如果col1上有单一索引，你本来希望用这个索引，但是oracle显然不会用。对于表链接，最常用的是：
1.nested-loop
2.sort-merge看看下面的sql:
  select * from table_a a,table_b b where a.col1=b.col1
  table_a和table_b的col1上都有索引。
  如果你知道这个select将返回几条或几十条数据，肯定nested-loop很快，但是优化器没这么聪明，很可能用 sort-merge，那就惨了。
  
  可以用hint指示oracle用nested-loop:
  select /*+ use_nl(a,b)*/ * from table_a a,table_b b where a.col1=b.col1  
  

Answer 3

To drabit(square) ：
   优化器用nested-loop还是sort-merge通过设置优化器模式不就可以了吗？RULE模式只用nested-loop，Cost-Based Optimizer (CBO) 模式只用sort-merge，而choose模式不就是自己会选择用nested-loop还是sort-merge么？那说到底对于优化器咱们只要选choose模式，然后再用hint来优化SQL文就是最佳的选择了呢？？To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！

Answer 4

to diandianqiuqiu:Cost-Based Optimizer (CBO) 模式只用sort-merge? -----不敢完全认同，我认为all_rows模式会优先使用sort-merge,first_rows模式会优先使用nested-loop.那说到底对于优化器咱们只要选choose模式，然后再用hint来优化SQL文就是最佳的选择了呢？？------我个人认为是这样，CBO要想用好，必须定期对表执行分析，对于数据量庞大而经常变化的表来说，开销很大。

Answer 5

To drabit(square) ：
   Cost-Based Optimizer (CBO) 模式只用sort-merge，这是肯定的。要不oracle分CBO和RULE干什么？
   多谢drabit(square)，我基本已经知道原则，再听听其他人的高见！

Answer 6

to diandianqiuqiu:Cost-Based Optimizer (CBO) 模式只用sort-merge，这是肯定的。要不oracle分CBO和RULE干什么？----你太武断了一点吧?CBO优化模式设置有all_rows和first_rows（first_rows_n)两种模式，all_rows会用sort_merge/hash_join，first_rows会用nested_loop

Answer 7

To ern(毕业生) :
   首先关于我在索引讨论的那个帖子中(http://expert.csdn.net/Expert/topic/2849/2849594.xml?temp=.9303705)的比喻：我自己终于在Oracle9i Database Performance Guide and Reference Release 1 (9.0.1)的"Understanding Access Paths for the CBO"这一章节找到了验证：
Access paths are ways in which data is retrieved from the database. For any row in any table accessed by a SQL statement, there are three common ways by which that row can be located and retrieved: １．A row can be retrieved through a full table scan, which reads all rows from a table and filters out those that do not meet the selection criteria. ２．A row can be retrieved also through the use of an index, by traversing the index using the indexed column values specified by the statement. ３．A row can be retrieved by specifying the ROWID. ROWID access is the fastest way to retrieve a row, because this specifies the exact location of the row in the database. In general, index access paths should be used for statements that retrieve a small subset of the table's rows, while full scans are more efficient when accessing a large portion of the table.To drabit(square) ：
   我又查了一下，的确是我武断了。是像你说的，CBO优化模式的all_rows会用sort_merge，full table Scans，而first_rows（first_rows_n)会用nested_loop，Index Scans 。现在我的问题：
1.我原来的结论还成立么？是不是将OPTIMIZER_MODE设成CBO更好呢？
2.既然CBO优化模式的all_rows和RULE模式一个作用（重视吞吐量），还要RULE模式干什么？
3.我现在就是想使我的查询sql文执行地更快，你有什么建议么？谢谢！！To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！

Answer 8

to diandianqiuqiu:1.你的结论依然成立，设置成CBO更好，oracle10已经声称不支持RULE了
2.你说的对，oracle将来会放弃RULE
3.没有非常好的建议，我觉得只有两个基本要点：
   a.你提到的access path，你是要throughput快还是response快？OLAP系统通常要求throughput快，而OLTP系统通常要求response快。throughput快的话，正如你提到的,access path不能选用索引。response快的话，access path必须选用索引。
   b.数据库参数的设置（非常关键），我知道的重要的参数有
     sort_area_size：对带有order by / group by的sql 和 merge-join path有决定影响
     hash_area_size: 对hash-join path有决定影响
     db_file_multiblock_read_count：对table full scan path有决定影响
     parallel_max_servers：对多CPU的系统，对table full scan path/index fast full scan path 有极大的速度提高个人意见供参考up your question:
To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！

Answer 9

To drabit(square) ：
   首先多谢你解答了我的疑惑。
   关于你的第一个优化建议我没怎么明白，不过我现在的理解是像我这样还不知道怎么用hint的人OPTIMIZER_MODE选CBO，会用hint的高人OPTIMIZER_MODE随便选，只要在每个SQL中指定是注重throughput还是注重response time就ok了！（因为事先知道该SQL取的数据量有多大），不知道我的理解是否正确？
   第二个建议由于我还没怎么接触，所以只好先，多谢！！！！To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！

Answer 10

to diandianqiuqiu:
  大家相互学习。
  你的理解我觉得基本没错，不过optimizer_mode设置成CBO的话，你要经常对变化了的表做analyse，否则优化器得不到足够的信息，access path还是有可能选错。一句话，我认为人总比机器灵活，写完sql后看看执行计划，能确保你没错。如果发现问题而你又对hint不熟，对表做一下analyse就得了。
  
  把问题搞透彻是我们共同的愿望，希望其它高人也能提出自己的想法。

Answer 11

To drabit(square) ：
   好，基本明白原则了。多谢！！To 大伙儿 ：
   我这里有个SQL Expert For Oracle3.7优化SQL文工具，SN是212335-165591-869435-625861，装完后把系统时间调到了2001.10.25，但是说我过期，不知道大家有没有好用的版本，听好多人说好用，偶也非常想用啊！！谢谢！！希望其他高人也能肯定或者质疑一下我和drabit(square)的观点！！！