登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

SQL语句性能调整之ORACLE的执行计划-性能调优-3  

2009-07-05 17:23:26|  分类: Oracle优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

SQL语句性能调整之ORACLE的执行计划

  在ORACLE文档上说:对于RBO来说,以from 子句中从右到左的顺序选择驱动表,即最右边的表为第一个驱动表,这是其英文原文:All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。不过,在我做的测试中,从来也没有验证过这种说法是正确的。我认为,即使在RBO中,也是有一套规则来决定使用哪种连接类型和哪个表作为驱动表,在选择时肯定会考虑当前索引的情况,还可能会考虑where 中的限制条件,但是肯定是与where中限制条件的位置无关。

  测试:

  如果我创建3个表:

  create table A(col1 number(4,0),col2 number(4,0), col4 char(30));

  create table B(col1 number(4,0),col3 number(4,0), name_b char(30));

  create table C(col2 number(4,0),col3 number(4,0), name_c char(30));

  create index inx_col12A on a(col1,col2);

  执行查询:

  select A.col4

  from B, A, C

  where B.col3 = 10

  and A.col1 = B.col1

  and A.col2 = C.col2

  and C.col3 = 5;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=RULE

  1 0 MERGE JOIN

  2 1 SORT (JOIN)

  3 2 NESTED LOOPS

  4 3 TABLE ACCESS (FULL) OF 'B'

  5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

  6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

  7 1 SORT (JOIN)

  8 7 TABLE ACCESS (FULL) OF 'C'

  select A.col4

  from B, A, C

  where A.col1 = B.col1

  and A.col2 = C.col2;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=RULE

  1 0 MERGE JOIN

  2 1 SORT (JOIN)

  3 2 NESTED LOOPS

  4 3 TABLE ACCESS (FULL) OF 'B'

  5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

  6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

  7 1 SORT (JOIN)

  8 7 TABLE ACCESS (FULL) OF 'C'

  将A表上的索引inx_col12A删除后:

  select A.col4

  from B, A, C

  where A.col1 = B.col1

  and A.col2 = C.col2;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=RULE

  1 0 MERGE JOIN

  2 1 SORT (JOIN)

  3 2 MERGE JOIN

  4 3 SORT (JOIN)

  5 4 TABLE ACCESS (FULL) OF 'C'

  6 3 SORT (JOIN)

  7 6 TABLE ACCESS (FULL) OF 'A'

  8 1 SORT (JOIN)

  9 8 TABLE ACCESS (FULL) OF 'B'

  通过上面的这些例子,使我对oracle文档上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”这句话持怀疑态度。此时,我也不能使用hints来强制优化器使用nested loop,如果使用了hints,这样就自动使用CBO优化器,而不是RBO优化器了。

  CBO根据统计信息选择驱动表,假如没有统计信息,则在from 子句中从左到右的顺序选择驱动表。这与RBO选择的顺序正好相反。这是英文原文(CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO) 。我还是没法证实这句话的正确性。不过经过验证:“如果用ordered 提示(此时肯定用CBO),则以from 子句中按从左到右的顺序选择驱动表”这句话是正确的。实际上在CBO中,如果有统计数据(即对表与索引进行了分析),则优化器会自动根据cost值决定采用哪种连接类型,并选择合适的驱动表,这与where子句中各个限制条件的位置没有任何关系。如果我们要改变优化器选择的连接类型或驱动表,则就需要使用hints了,具体hints的用法在后面会给予介绍。

  测试:

  如果我创建的3个表:

  create table A(col1 number(4,0),col2 number(4,0), col4 char(30));

  create table B(col1 number(4,0),col3 number(4,0), name_b char(30));

  create table C(col2 number(4,0),col3 number(4,0), name_c char(30));

  create index inx_col12A on a(col1,col2);

  执行查询:

  select A.col4

  from B, A, C

  where B.col3 = 10

  and A.col1 = B.col1

  and A.col2 = C.col2

  and C.col3 = 5;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)

  1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)

  2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)

  3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)

  5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  select A.col4

  from B, A, C

  where A.col1 = B.col1

  and A.col2 = C.col2;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)

  1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)

  2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)

  3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)

  将A表上的索引inx_col12A删除后:

  select A.col4

  from B, A, C

  where A.col1 = B.col1

  and A.col2 = C.col2;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)

  1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)

  2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)

  3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)

  select /*+ ORDERED */A.col4

  from C, A, B

  where B.col3 = 10

  and A.col1 = B.col1

  and A.col2 = C.col2

  and C.col3 = 5;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)

  1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)

  2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  这个查询验证了通过ORDERED提示可以正确的提示优化器选择哪个表作为优化器。

基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从而使语句高效的运行。例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们就可以指示优化器使用全表扫描。在ORACLE中,是通过为语句添加hints(提示)来实现干预优化器优化的目的。

  hints是oracle提供的一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现:

  1) 使用的优化器的类型

  2) 基于代价的优化器的优化目标,是all_rows还是first_rows。

  3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。

  4) 表之间的连接类型

  5) 表之间的连接顺序

  6) 语句的并行程度

  除了”RULE”提示外,一旦使用的别的提示,语句就会自动的改为使用CBO优化器,此时如果你的数据字典中没有统计数据,就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示,则最好对表和索引进行定期的分析。

  Hints只应用在它们所在sql语句块(statement block,由select、update、delete关键字标识)上,对其它SQL语句或语句的其它部分没有影响。如:对于使用union操作的2个sql语句,如果只在一个sql语句上有hints,则该hints不会影响另一个sql语句。

  我们可以使用注释(comment)来为一个语句添加hints,一个语句块只能有一个注释,而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面

  使用hints的语法:

  {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

  or

  {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

  注解:

  1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字,包含提示的注释只能出现在这些关键字的后面,否则提示无效。

  2) “+”号表示该注释是一个hints,该加号必须立即跟在”/*”的后面,中间不能有空格。

  3) hint是下面介绍的具体提示之一,如果包含多个提示,则每个提示之间需要用一个或多个空格隔开。

  4) text 是其它说明hint的注释性文本

  如果你没有正确的指定hints,Oracle将忽略该hints,并且不会给出任何错误。

当使用hints时,在某些情况下,为了确保让优化器产生最优的执行计划,我们可能指定全套的hints。例如,如果有一个复杂的查询,包含多个表连接,如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引),优化器需要来决定其它应该使用的访问路径和相应的连接方法。因此,即使你给出了一个INDEX提示,优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和存取路径,而基于这些连接方法和存取路径,优化器认为用户给出的INDEX提示无用。为了防止这种情况,我们要使用全套的hints,如:不但指定要使用的索引,而且也指定连接的方法与连接的顺序等。

  下面是一个使用全套hints的例子,ORDERED提示指出了连接的顺序,而且为不同的表指定了连接方法:

  SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)

  USE_NL (glcc glf) USE_MERGE (gp gsb) */

  b.application_id, b.set_of_books_id ,

  b.personnel_id, p.vendor_id Personnel,

  p.segment1 PersonnelNumber, p.vendor_name Name

  FROM jl_br_journals j, jl_br_balances b,

  gl_code_combinations glcc, fnd_flex_values_vl glf,

  gl_periods gp, gl_sets_of_books gsb, po_vendors p

  WHERE ...

  指示优化器的方法与目标的hints:

  ALL_ROWS -- 基于代价的优化器,以吞吐量为目标

  FIRST_ROWS(n) -- 基于代价的优化器,以响应时间为目标

  CHOOSE -- 根据是否有统计信息,选择不同的优化器

  RULE -- 使用基于规则的优化器

  例子:

  SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id

  FROM employees

  WHERE department_id = 20;

  SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id

  FROM employees

  WHERE employee_id = 7566;

  SELECT /*+ RULE */ employee_id, last_name, salary, job_id

  FROM employees

  WHERE employee_id = 7566;

  FULL /*+ FULL ( table ) */

  指定该表使用全表扫描

  ROWID /*+ ROWID ( table ) */

  指定对该表使用rowid存取方法,该提示用的较少

  INDEX /*+ INDEX ( table [index]) */

  使用该表上指定的索引对表进行索引扫描

  INDEX_FFS /*+ INDEX_FFS ( table [index]) */

  使用快速全表扫描

  NO_INDEX /*+ NO_INDEX ( table [index]) */

  不使用该表上指定的索引进行存取,仍然可以使用其它的索引进行索引扫描

  SELECT /*+ FULL(e) */ employee_id, last_name

  FROM employees e

  WHERE last_name LIKE :b1;

  SELECT /*+ROWID(employees)*/ *

  FROM employees

  WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

  SELECT /*+ INDEX(A sex_index) use sex_index because there are few

  male patients */ A.name, A.height, A.weight

  FROM patients A

  WHERE A.sex = ’m’;

  SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id

  FROM employees

  WHERE employee_id > 200;

  指示连接顺序的hints:

  ORDERED /*+ ORDERED */

  按from 字句中表的顺序从左到右的连接

  STAR /*+ STAR */

  指示优化器使用星型查询

  SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity

  FROM customers c, order_items l, orders o

  WHERE c.cust_last_name = :b1

  AND o.customer_id = c.customer_id

  AND o.order_id = l.order_id;

  /*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

  指示连接类型的hints:

  USE_NL /*+ USE_NL ( table [,table, ...] ) */

  使用嵌套连接

  USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */

  使用排序- -合并连接

  USE_HASH /*+ USE_HASH ( table [,table, ...]) */

  使用HASH连接

  注意:如果表有alias(别名),则上面的table指的是表的别名,而不是真实的表名

  具体的测试实例:

  create table A(col1 number(4,0),col2 number(4,0), col4 char(30));

  create table B(col1 number(4,0),col3 number(4,0), name_b char(30));

  create table C(col2 number(4,0),col3 number(4,0), name_c char(30));

  select A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE

  1 0 MERGE JOIN

  2 1 SORT (JOIN)

  3 2 MERGE JOIN

  4 3 SORT (JOIN)

  5 4 TABLE ACCESS (FULL) OF 'B'

  6 3 SORT (JOIN)

  7 6 TABLE ACCESS (FULL) OF 'A'

  8 1 SORT (JOIN)

  9 8 TABLE ACCESS (FULL) OF 'C'

  select /*+ ORDERED */ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=110)

  1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)

  2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  select /*+ ORDERED USE_NL (A C)*/ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=110)

  1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)

  2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  创建索引:

  create index inx_col12A on a(col1,col2);

  select A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE

  1 0 MERGE JOIN

  2 1 SORT (JOIN)

  3 2 NESTED LOOPS

  4 3 TABLE ACCESS (FULL) OF 'B'

  5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

  6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

  7 1 SORT (JOIN)

  8 7 TABLE ACCESS (FULL) OF 'C'

  select /*+ ORDERED */ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=110)

  1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)

  2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  select /*+ ORDERED USE_NL (A C)*/ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=110)

  1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)

  2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  select /*+ USE_NL (A C)*/ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  我们这个查询的意思是让A、C表做NL连接,并且让A表作为内表,但是从执行计划来看,没有达到我们的目的。

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=110)

  1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)

  2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)

  4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)

  5 4 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

  6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

  对对象进行分析后:

  analyze table a compute statistics;

  analyze table b compute statistics;

  analyze table c compute statistics;

  analyze index inx_col12A compute statistics;

  select A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=336)

  1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)

  2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)

  3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)

  5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  select /*+ ORDERED */ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9 Bytes=378)

  1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)

  2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  select /*+ ORDERED USE_NL (A C)*/ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)

  1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)

  2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  select /*+ USE_NL (A C)*/ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)

  1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)

  2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  select /*+ ORDERED USE_NL (A B C) */ A.col4

  from C , A , B

  where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

  and B.col3 = 10;

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)

  1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)

  2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)

  3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  对于这个查询我无论如何也没有得到类似下面这样的执行计划:

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)

  1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)

  2 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

  3 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)

  4 3 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)

  5 3 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

  从上面的这些例子我们可以看出:通过给语句添加HINTS,让其按照我们的意愿执行,有时是一件很困难的事情,需要不断的尝试各种不同的hints。对于USE_NL与USE_HASH提示,建议同ORDERED提示一起使用,否则不容易指定那个表为驱动表。

环境:oracle 817 + linux + 阵列柜

  swd_billdetail 表5000万条数据

  SUPER_USER 表2800条数据

  连接列上都有索引,而且super_user中的一条对应于swd_billdetail表中的很多条记录

  表与索引都做了分析。

  实际应用的查询为:

  select a.CHANNEL, B.user_class

  from swd_billdetail B, SUPER_USER A

  where A.cn = B.cn;

  这样在分析时导致查询出的数据过多,不方便,所以用count(a.CHANNEL||B.user_class)来代替,而且count(a.CHANNEL||B.user_class)操作本身并不占用过多的时间,所以可以接受此种替代。

  利用索引查询出SWD_BILLDETAIL表中所有记录的方法

  SQL> select count(id) from SWD_BILLDETAIL;

  COUNT(ID)

  ----------

  53923574

  Elapsed: 00:02:166.00

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18051 Card=1)

  1 0 SORT (AGGREGATE)

  2 1 INDEX (FAST FULL SCAN) OF 'SYS_C001851' (UNIQUE) (Cost=18051 Card=54863946)

  Statistics

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

  0 recursive calls

  1952 db block gets

  158776 consistent gets

  158779 physical reads

  1004 redo size

  295 bytes sent via SQL*Net to client

  421 bytes received via SQL*Net from client

  2 SQL*Net roundtrips to/from client

  1 sorts (memory)

  0 sorts (disk)

  1 rows processed

  利用全表扫描从SWD_BILLDETAIL表中取出全部数据的方法。

  SQL> select count(user_class) from swd_billdetail;

  COUNT(USER_CLASS)

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

  53923574

  Elapsed: 00:11:703.07

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165412 Card=1 Bytes=2)

  1 0 SORT (AGGREGATE)

  2 1 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=109727892)

  Statistics

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

  0 recursive calls

  8823 db block gets

  1431070 consistent gets

  1419520 physical reads

  0 redo size

  303 bytes sent via SQL*Net to client

  421 bytes received via SQL*Net from client

  2 SQL*Net roundtrips to/from client

  1 sorts (memory)

  0 sorts (disk)

  1 rows processed

  select count(a.CHANNEL||B.user_class)

  from swd_billdetail B, SUPER_USER A

  where A.cn = B.cn;

  EXEC_ORDER PLANLINE

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

  6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=108968,CARD=1,BYTES=21)

  5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)

  4 NESTED LOOPS (COST=108968,CARD=1213745,BYTES=25488645)

  1 TABLE ACCESS (FULL) OF 'SWORD.SUPER_USER' (COST=2,CARD=2794,BYTES=27940)

  3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SWD_BILLDETAIL' (COST=39,CARD=54863946,BYTES=603503406)

  2 INDEX (RANGE SCAN) OF 'SWORD.IDX_DETAIL_CN' (NON-UNIQUE) (COST=3,CARD=54863946,BYTES=)

  这个查询耗费的时间很长,需要1个多小时。

  运行后的信息如下:

  COUNT(A.CHANNEL||B.USER_CLASS)

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

  1186387

  Elapsed: 01:107:6429.87

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=108968 Card=1 Bytes=21)

  1 0 SORT (AGGREGATE)

  2 1 NESTED LOOPS (Cost=108968 Card=1213745 Bytes=25488645)

  3 2 TABLE ACCESS (FULL) OF 'SUPER_USER' (Cost=2 Card=2794Bytes=27940)

  4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SWD_BILLDETAIL' (Cost=39 Card=54863946 Bytes=603503406)

  5 4 INDEX (RANGE SCAN) OF 'IDX_DETAIL_CN' (NON-UNIQUE) (Cost=3 Card=54863946)

  Statistics

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

  0 recursive calls

  4 db block gets

  1196954 consistent gets

  1165726 physical reads

  0 redo size

  316 bytes sent via SQL*Net to client

  421 bytes received via SQL*Net from client

  2 SQL*Net roundtrips to/from client

  2 sorts (memory)

  0 sorts (disk)

  1 rows processed

  将语句中加入hints,让oracle的优化器使用嵌套循环,并且大表作为驱动表,生成新的执行计划:

  select /*+ ORDERED USE_NL(A) */ count(a.CHANNEL||B.user_class)

  from swd_billdetail B, SUPER_USER A

  where A.cn = B.cn;

  EXEC_ORDER PLANLINE

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

  6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=109893304,CARD=1,BYTES=21)

  5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)

  4 NESTED LOOPS (COST=109893304,CARD=1213745,BYTES=25488645)

  1 TABLE ACCESS (FULL) OF 'SWORD.SWD_BILLDETAIL' (COST=165412,CARD=54863946,BYTES=603503406)

  3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SUPER_USER' (COST=2,CARD=2794,BYTES=27940)

  2 INDEX (RANGE SCAN) OF 'SWORD.IDX_SUPER_USER_CN' (NON-UNIQUE) (COST=1,CARD=2794,BYTES=)

  这个查询耗费的时间较短,才20分钟,性能比较好。

  运行后的信息如下:

  COUNT(A.CHANNEL||B.USER_CLASS)

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

  1186387

  Elapsed: 00:20:1208.87

  Execution Plan

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

  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=109893304 Card=1 Bytes=21)

  1 0 SORT (AGGREGATE)

  2 1 NESTED LOOPS (Cost=109893304 Card=1213745 Bytes=25488645)

  3 2 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=603503406)

  4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUPER_USER' (Cost=2Card=2794 Bytes=27940)

  5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost=1 Card=2794)

  Statistics

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

  0 recursive calls

  8823 db block gets

  56650250 consistent gets

  1413250 physical reads

  0 redo size

  316 bytes sent via SQL*Net to client

  421 bytes received via SQL*Net from client

  2 SQL*Net roundtrips to/from client

  2 sorts (memory)

  0 sorts (disk)

  1 rows processed

  因为上两个查询都是采用nested loop循环,这时采用哪个表作为driving table就很重要。在第一个sql中,小表(SUPER_USER)作为driving table,符合oracle优化的建议,但是由于SWD_BILLDETAIL表中cn列的值有很多重复的,这样对于SUPER_USER中的每一行,都会在SWD_BILLDETAIL中有很多行,利用索引查询出这些行的rowid很快,但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值,就比较慢了。原因是这些rowid是随机的,而且该表比较大,不可能缓存到内存,所以几乎每次按照rowid查询都需要读物理磁盘,这就是该执行计划比较慢的真正原因。从结果可以得到验证:查询出1186387行,需要利用rowid从SWD_BILLDETAIL表中读取1186387次,而且大部分为从硬盘上读取。

  反其道而行之,利用大表(SWD_BILLDETAIL)作为driving表,这样大表只需要做一次全表扫描(而且会使用多块读功能,每次物理I/O都会读取几个oracle数据块,从而一次读取很多行,加快了执行效率),对于读出的每一行,都与SUPER_USER中的行进行匹配,因为SUPER_USER表很小,所以可以全部放到内存中,这样匹配操作就极快,所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多(SWD_BILLDETAIL全表用11分钟,而此查询用20分钟)。

  另外:如果SWD_BILLDETAIL表中cn列的值唯一,则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大,如500万行,则第2个sql执行计划执行的结果反而又可能会差。其实,如果SUPER_USER表很小,则第2个sql语句的执行计划如果不利用SUPER_USER表的索引,查询或许会更快一些,我没有对此进行测试。

  所以在进行性能调整时,具体问题要具体分析,没有一个统一的标准。

 

引文来源  SQL语句性能调整之ORACLE的执行计划-性能调优-Oracle频道-中国IT实验室

  评论这张
 
阅读(924)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018