oracle用hints调优,oracle hints的那点事

3.通过append hint来插入数据，演示它和普通插入数据的性能比较。

LEO1@LEO1> set timing on                                    显示执行时间

LEO1@LEO1> insert into leo1 select * from leo1;            普通加载数据，会扫描空闲空间加以利用

71958 rows created.

Elapsed: 00:00:00.89                                   执行了00.89秒

Execution Plan

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

Plan hash value: 2716644435

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | INSERT STATEMENT         |      | 71958 |  6816K|   287   (1)| 00:00:04 |

|   1 |  LOAD TABLE CONVENTIONAL | LEO1 |       |       |            |          |

|   2 |   TABLE ACCESS FULL      | LEO1 | 71958 |  6816K|   287   (1)| 00:00:04 |

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

Statistics

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

518  recursive calls

15560  db block gets

3693  consistent gets                         产生了3693次一致性读

4  physical reads

13892928  redo size                              产生了13892928大小redo日志

843  bytes sent via SQL*Net to client

792  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

71958  rows processed

LEO1@LEO1> rollback;                              回滚

Rollback complete.

Elapsed: 00:00:00.10

LEO1@LEO1> insert /*+ append */ into leo1 select * from leo1;  直接加载数据，不扫描空闲空间，直接定位HWM加载数据，效率高

71958 rows created.

Elapsed: 00:00:00.36                                    执行了00.36秒

Execution Plan

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

ERROR:

ORA-12838: cannot read/modify an object after modifying it in parallel(无法在并行模式下修改之后读写对象)

SP2-0612: Error generating AUTOTRACE EXPLAIN report   生成执行计划报告时出错

Statistics

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

340  recursive calls

2441  db block gets

2253  consistent gets                          产生了2253次一致性读

0  physical reads

2268672  redo size                               产生了2268672大小redo日志

829  bytes sent via SQL*Net to client

806  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

71958  rows processed

LEO1@LEO1> rollback;                           回滚

Rollback complete.

Elapsed: 00:00:00.09

小结：从比较结果一眼看出，直接加载的效率要比普通加载高很多，时间上差不多快了一倍。原因有以下2点：

第一点：普通加载会扫描空闲空间，利用这些空闲空间插入数据，直接加载不扫描空闲空间直接定位到HWM直接加载数据，从而效率较高

第二点：可以看出普通加载的一致性读和redo量都要大于直接加载，产生这些数据量也是要消耗资源的，所以普通加载没有直接加载性能好。

4.用cardinality hint来模拟表中的数据，写一条SQL语句并给出它的执行计划。

名词解释：cardinality这个关键字在10g执行计划里被rows代替，实际上两个词指的是一个东西。

Cardinality(基数)在执行计划中表示每一步操作返回的记录数，这个数是oracle估算出来的并不是真实返回的记录数，CBO根据这个值计算权重，来选择使用哪种方式来访问数据。

作用：1.我们一般使用“cardinality”hints来比较不同数量返回值在执行计划中效率。

2.当有特殊场景不容易模拟出来的时候，我们可以使用“cardinality”hints方式来轻松解决

LEO1@LEO1> select count(*) from leo1;                    leo1表有71958条记录

COUNT(*)

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

71958

LEO1@LEO1> create table leo2 as select * from dba_objects;    创建leo2表

Table created.

LEO1@LEO1> insert into leo2 select * from leo2;            在插入一次，为了比leo1表记录数多一倍，好做比较

71960 rows created.

LEO1@LEO1> create index idx_leo2 on leo2(object_id);       在object_id字段上创建索引

Index created.

LEO1@LEO1> select count(*) from leo2;                   现在有143920条记录

COUNT(*)

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

143920

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true); leo2和索引都做分析

PL/SQL procedure successfully completed.

LEO1@LEO1> set autotrace traceonly;

LEO1@LEO1> select * from leo1,leo2 where leo1.object_id=leo2.object_id;

143916 rows selected.                             返回143916行

Execution Plan

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

Plan hash value: 2436308224

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

| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |   141K|    26M|       |  2291   (1) | 00:00:28 |

|*  1 |  HASH JOIN        |      |   141K|    26M|  7664K|  2291   (1) | 00:00:28 |

|   2 |   TABLE ACCESS FULL| LEO1  | 71958 |  6816K|       |   587   (1) | 00:00:08 |

|   3 |   TABLE ACCESS FULL| LEO2  |   143K|    13M|       |   588   (1) | 00:00:08 |

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

Predicate Information (identified by operation id):

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

1 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID")       谓词条件2个索引字段相等

Statistics

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

1  recursive calls

0  db block gets

13672  consistent gets                      全表扫描产生了13672个一致性读

2134  physical reads

0  redo size

12630296  bytes sent via SQL*Net to client

106058  bytes received via SQL*Net from client

9596  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

143916  rows processed

我们强制指定leo1表返回100行，来看执行计划如何选择访问数据的方式

LEO1@LEO1> select /*+ cardinality(leo1 100) */ * from leo1,leo2 where leo1.object_id=leo2.object_id;

143916 rows selected.                           也返回143916行，返回值没有按执行计划走

Execution Plan

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

Plan hash value: 2751515442

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |          |   197 | 38218 |   887   (1)| 00:00:11 |

|   1 |  NESTED LOOPS              |          |       |       |            |          |

|   2 |   NESTED LOOPS             |          |   197 | 38218 |   887   (1)| 00:00:11 |

|   3 |    TABLE ACCESS FULL         | LEO1     |   100 |  9700 |   587   (1)| 00:00:08 |

|*  4 |    INDEX RANGE SCAN         | IDX_LEO2 |     2 |       |    1   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| LEO2     |     2 |   194 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID")

Statistics

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

1  recursive calls

0  db block gets

177213  consistent gets                   因为有索引扫描所以有177213个一致性读

2134  physical reads                    物理读都是一样的，说明只有内存IO增加了

0  redo size

7727088  bytes sent via SQL*Net to client

106058  bytes received via SQL*Net from client

9596  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

143916  rows processed

数据访问：全表扫描小表leo1(因为强制指定返回100行就认为是小表)拿出一条记录，去大表leo2中匹配(索引扫描leo2表，因为当检索范围较大时扫描索引的速度较快)，嵌套循环遍历leo2，如果找到匹配记录，就去leo2表rowid所在的数据块上取出，最后需要的就是leo2表里面整个数据。

使用场景：1.外部表是一张小表  例 leo1表  因为记录少会执行全表扫描

2.内部表是一张大表，并在关联字段上创建索引，当检索范围较大时扫描索引的速度较快

3. 当有特殊场景不容易模拟出来的时候，我们可以使用“cardinality”hints方式来轻松解决

hash join  merge join  nested loops  lead  cardinality  append  full  index

Leonarding

2012.12.23

天津&winter

分享技术~成就梦想

Blog：www.leonarding.com