Oracle 10gR2新SQL提示——opt_param

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-03-16 14:56:22

分享到 

我们知道，在Oracle中存在许多参数能够影响SQL的查询计划，如hash_join_enabled、optimizer_index_cost_adj、_optim_peek_user_binds。正确调整这些参数能够解决不少SQL所引起的性能问题。但是，在调整这些参数时需要注意一点，他们是对整个实例其作用的。他们的影响范围如此之大，以至于我们可能通过调整这些参数解决某一条SQL的问题，但是参数的调整却会给其他SQL带来负面影响。有没有办法使某个参数只针对某条语句有一个特殊的数值，而对系统没有影响呢？Oracle 10gR2就提供了一个这样的提示——opt_param。 看下这个提示的用法：/*+opt_param(<parameter_name>[,] <parameter_value>)*/。这个提示的作用就是使我们在某条语句中指定某个系统参数值。 下面看下这个参数是如何起作用的：   SQL> set autot trace SQL> select t1.object_id, t2.tablespace_name   2  from t_test1 t1, t_test2 t2   3  where t1.object_name = t2.table_name;   2536 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 3541962439   ------------------------------------------------------------------------------ | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |         |  3348 |   170K|   169   (2)| 00:00:03 | |*  1 |  HASH JOIN         |         |  3348 |   170K|   169   (2)| 00:00:03 | |   2 |   TABLE ACCESS FULL| T_TEST2 |  2072 | 51800 |    17   (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| T_TEST1 | 47582 |  1254K|   150   (1)| 00:00:02 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME")     ... ...   SQL> select /*+opt_param('hash_join_enabled', 'false')*/ t1.object_id, t2.tablespace_name   2  from t_test1 t1, t_test2 t2   3  where t1.object_name = t2.table_name;   2536 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2273132659   --------------------------------------------------------------------------------- | Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)|Time| --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |         |  3348 |   170K|       |   840   (1)| 00: 00:11 | |   1 |  MERGE JOIN         |         |  3348 |   170K|       |   840   (1)| 00: 00:11 | |   2 |   SORT JOIN         |         |  2072 | 51800 |   168K|    34   (3)| 00: 00:01 | |   3 |    TABLE ACCESS FULL| T_TEST2 |  2072 | 51800 |       |    17   (0)| 00: 00:01 | |*  4 |   SORT JOIN         |         | 47582 |  1254K|  3384K|   806   (1)| 00: 00:10 | |   5 |    TABLE ACCESS FULL| T_TEST1 | 47582 |  1254K|       |   150   (1)| 00: 00:02 | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      4 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME")        filter("T1"."OBJECT_NAME"="T2"."TABLE_NAME")   ... ...     在我们的系统中存在一个案例，就是通过这个提示来解决的。案例的背景描述如下：该系统是一个5*24系统，周末2天是系统维护时间。在系统中存在一个作业，会执行下面一条语句：    SELECT COUNT(A.CARRIER_ID) FROM CSS_TP_SHMT_QUEUE A,           CSS_TP_SHMT_QUEUE_ACTIVITY B WHERE A.SHMT_QUEUE_STATUS_CDE = :B3 AND B.SHMT_QUEUE_ID = A.SHMT_QUEUE_ID AND B.ACTIVITY_STATUS_CDE = :B2 AND A.CARRIER_ID = :B1 AND A.REC_CREAT_DT > SYSDATE - 30 AND ROWNUM <= 1;   我们发现有时这条语句在生产系统上跑的性能非常差，占用单颗CPU100%，运行时间达到几个小时。通过v$sql_plan我们获取到该语句在生产系统上的查询计划如下：   db01> select lpad(' ', 2*(level-1))||operation||' '||options||' '||  2  object_name||' '||decode(id, 0, 'Cost='||cost) "Query Plan_Table"   3  from v$sql_plan   4  start with id = 0   5  and hash_value = 380787837   6  connect by prior id = parent_id   7  and hash_value = 380787837;   Query Plan_Table ------------------------------------------------------------------------------------------------------------ SELECT STATEMENT   Cost=3   SORT AGGREGATE     COUNT STOPKEY       NESTED LOOPS         TABLE ACCESS BY INDEX ROWID CSS_TP_SHMT_QUEUE           INDEX UNIQUE SCAN CSS_TP_SHMT_QUEUE_PK         INDEX RANGE SCAN CSS_TP_SHMT_QUEUE_ACTIVITY_IX4   通过分析，这个查询计划是存在问题的，它错误的选择了索引“CSS_TP_SHMT_QUEUE_PK”。我们将相关两张表的统计信息做了更新，但发现这条Top SQL并没有得到改观，还存在生产系统上。 当我们给语句带上参数数值在数据库上进行trace时，却发现它产生了正确的查询计划并在几秒钟之内返回了结果：   db01> SET TIMING ON db01> SET AUTOT TRACE db01> db01> SELECT count(a.carrier_id)   2  FROM   css_tp_shmt_queue a, css_tp_shmt_queue_activity b   3  WHERE  a.shmt_queue_status_cde = 'E'   4  AND    b.shmt_queue_id            = a.shmt_queue_id   5  AND    b.activity_status_cde   = 'EC'   6  AND    a.carrier_id      = 1   7  AND    a.rec_creat_dt             > SYSDATE - 30   8  AND    rownum <= 1;   Elapsed: 00:00:04.01   Execution Plan ---------------------------------------------------------- Plan hash value: 3859675621   ------------------------------------------------------------------------------ | Id  | Operation            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT     |                                |     1 |    19 |   169   (2)| 00:00:03 | |   1 |  SORT  AGGREGATE     |                                |     1 |    19 |   150   (1)| 00:00:02 | |   2 |   COUNT  STOPKEY     |                                |     1 |  8652 |   169   (2)| 00:00:03 | |   2 |    NESTED LOOPS      |                                |  3348 |    10K|   169   (2)| 00:00:03 | |   3 |     INDEX RANGE SCAN | CSS_TP_SHMT_QUEUE_IDX6         |   234 |  8719 |    17   (0)| 00:00:01 | |   4 |     INDEX RANGE SCAN | CSS_TP_SHMT_QUEUE_ACTIVITY_IX4 |    21 |   134 |   150   (1)| 00:00:02 | ------------------------------------------------------------------------------        0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=28)    1    0   SORT (AGGREGATE)    2    1     COUNT (STOPKEY)    3    2       NESTED LOOPS (Cost=9 Card=18465 Bytes=517020)    4    3         INDEX (RANGE SCAN) OF 'CSS_TP_SHMT_QUEUE_IDX6' (NON-           UNIQUE) (Cost=15 Card=18294 Bytes=347586)    5    3         INDEX (RANGE SCAN) OF 'CSS_TP_SHMT_QUEUE_ACTIVITY_IX4' (NON-UNIQUE)   因此，我们初步判断这是由于bind peeking所导致的，因为每周系统维护后，周一会传来一新的数据需要处理，这些数据作为参数传入函数后，根据bind peeking机制，SQL在第一次执行时会根据传入的数据来计算出查询计划。但是由于数据是无法控制的，第一次传入的数据并非典型数据，从而导致优化器采用了不理想的查询计划（关于bind peeking对查询计划的影响，可以参见文章《什么时候使用绑定变量性能反而更差》）。 当然，我们可以通过修改参数“_optim_peek_user_binds”来关闭bind peeking。但是，由于这个参数是实例级，我们无法预知这个参数的修改会对系统其他SQL造成什么影响。轻易改变生产数据库上的任何一个系统参数的DBA是鲁莽的，且后果是不可预知的。我们在这就可以通过使用Oracle提供的这个提示来强制使这条SQL来关闭bind peeking并不会对其他语句产生任何影响：   SELECT /*+opt_param('_optim_peek_user_binds', 'false')*/ COUNT(A.CARRIER_ID) FROM CSS_TP_SHMT_QUEUE A,           CSS_TP_SHMT_QUEUE_ACTIVITY B WHERE A.SHMT_QUEUE_STATUS_CDE = :B3 AND B.SHMT_QUEUE_ID = A.SHMT_QUEUE_ID AND B.ACTIVITY_STATUS_CDE = :B2 AND A.CARRIER_ID = :B1 AND A.REC_CREAT_DT > SYSDATE - 30 AND ROWNUM <= 1;