Oracle 10gR2新SQL提示——opt_param
Oracle 10gR2新SQL提示——opt_param
[English]
作者： fuyuncat
来源： www.HelloDBA.com
分享到 
我们知道，在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 traceSQL> select t1.object_id, t2.tablespace_name2 from t_test1 t1, t_test2 t23 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_name2 from t_test1 t1, t_test2 t23 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 BWHERE A.SHMT_QUEUE_STATUS_CDE = :B3AND B.SHMT_QUEUE_ID = A.SHMT_QUEUE_IDAND B.ACTIVITY_STATUS_CDE = :B2AND A.CARRIER_ID = :B1AND A.REC_CREAT_DT > SYSDATE - 30AND 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_plan4 start with id = 05 and hash_value = 3807878376 connect by prior id = parent_id7 and hash_value = 380787837;Query Plan_Table------------------------------------------------------------------------------------------------------------SELECT STATEMENT Cost=3SORT AGGREGATECOUNT STOPKEYNESTED LOOPSTABLE ACCESS BY INDEX ROWID CSS_TP_SHMT_QUEUEINDEX UNIQUE SCAN CSS_TP_SHMT_QUEUE_PKINDEX RANGE SCAN CSS_TP_SHMT_QUEUE_ACTIVITY_IX4
通过分析，这个查询计划是存在问题的，它错误的选择了索引“CSS_TP_SHMT_QUEUE_PK”。我们将相关两张表的统计信息做了更新，但发现这条Top SQL并没有得到改观，还存在生产系统上。
当我们给语句带上参数数值在数据库上进行trace时，却发现它产生了正确的查询计划并在几秒钟之内返回了结果：
db01> SET TIMING ONdb01> SET AUTOT TRACEdb01>db01> SELECT count(a.carrier_id)2 FROM css_tp_shmt_queue a, css_tp_shmt_queue_activity b3 WHERE a.shmt_queue_status_cde = 'E'4 AND b.shmt_queue_id = a.shmt_queue_id5 AND b.activity_status_cde = 'EC'6 AND a.carrier_id = 17 AND a.rec_creat_dt > SYSDATE - 308 AND rownum <= 1;Elapsed: 00:00:04.01Execution 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 BWHERE A.SHMT_QUEUE_STATUS_CDE = :B3AND B.SHMT_QUEUE_ID = A.SHMT_QUEUE_IDAND B.ACTIVITY_STATUS_CDE = :B2AND A.CARRIER_ID = :B1AND A.REC_CREAT_DT > SYSDATE - 30AND ROWNUM <= 1;
