opt_param

This hint is valid only for the following parameters: 
OPTIMIZER_DYNAMIC_SAMPLING,
OPTIMIZER_INDEX_CACHING,
OPTIMIZER_INDEX_COST_ADJ,
OPTIMIZER_SECURE_VIEW_MERGING,
STAR_TRANSFORMATION_ENABLED


举例如下

SQL> set autotrace trace
SQL> select empno from emp e, dept d where e.ename=d.dname ;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3434568436

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     4 |    80 |     5	(20)| 00:00:01 |
|*  1 |  HASH JOIN	   |	       |     4 |    80 |     5	(20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | IDX_DEPT1 |     4 |    40 |     1	 (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP       |    14 |   140 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."ENAME"="D"."DNAME")


统计信息
----------------------------------------------------------
	 24  recursive calls
	  0  db block gets
	  7  consistent gets
	  0  physical reads
	  0  redo size
	286  bytes sent via SQL*Net to client
	405  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

加hint后

SQL>  select /*+ opt_param('hash_join_enabled','false') */ empno from emp e, dept d where e.ename=d.dname; 

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 2082400136

---------------------------------------------------------------------------------
| Id  | Operation	    | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |		|     4 |    80 |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN	    |		|     4 |    80 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN	    |		|     4 |    40 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | IDX_DEPT1 |     4 |    40 |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN	    |		|    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP	|    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."ENAME"="D"."DNAME")
       filter("E"."ENAME"="D"."DNAME")


统计信息
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
	286  bytes sent via SQL*Net to client
	405  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

备之后查
static const struct { int valid; enum virtio_ib_qp_attr_mask req_param[VIRTIO_IB_QPT_UD + 1]; enum virtio_ib_qp_attr_mask opt_param[VIRTIO_IB_QPT_UD + 1]; } qp_state_table[VIRTIO_IB_QPS_ERR + 1][VIRTIO_IB_QPS_ERR + 1] = { [VIRTIO_IB_QPS_RESET] = { [VIRTIO_IB_QPS_RESET] = { .valid = 1 }, [VIRTIO_IB_QPS_INIT] = { .valid = 1, .req_param = { [VIRTIO_IB_QPT_UD] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_UC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_RC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_SMI] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_GSI] = VIRTIO_IB_QP_QKEY, } }, }, [VIRTIO_IB_QPS_INIT] = { [VIRTIO_IB_QPS_RESET] = { .valid = 1 }, [VIRTIO_IB_QPS_ERR] = { .valid = 1 }, [VIRTIO_IB_QPS_INIT] = { .valid = 1, .opt_param = { [VIRTIO_IB_QPT_UD] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_UC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_RC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_SMI] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_GSI] = VIRTIO_IB_QP_QKEY, } }, [VIRTIO_IB_QPS_RTR] = { .valid = 1, .req_param = { [VIRTIO_IB_QPT_UC] = (VIRTIO_IB_QP_AV | VIRTIO_IB_QP_PATH_MTU | VIRTIO_IB_QP_DEST_QPN | VIRTIO_IB_QP_RQ_PSN), [VIRTIO_IB_QPT_RC] = (VIRTIO_IB_QP_AV | VIRTIO_IB_QP_PATH_MTU | VIRTIO_IB_QP_DEST_QPN | VIRTIO_IB_QP_RQ_PSN | VIRTIO_IB_QP_MAX_DEST_RD_ATOMIC | VIRTIO_IB_QP_MIN_RNR_TIMER), }, .opt_param = { [VIRTIO_IB_QPT_UD] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_UC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_RC] = VIRTIO_IB_QP_ACCESS_FLAGS, [VIRTIO_IB_QPT_SMI] = VIRTIO_IB_QP_QKEY, [VIRTIO_IB_QPT_GSI] = VIRTIO_IB_QP_QKEY, }, }, }, [VIRTIO_IB_QPS_RTR] = { [VIRTIO_IB_QPS_RESET] = { .valid = 1 }, [VIRTIO_IB_QPS_ERR] = { .valid = 1 }, [VIRTIO_IB_QPS_RTS] = { .valid = 1, .req_param = { [VIRTIO_IB_QPT_UD] = VIRTIO_IB_QP_SQ_PSN, [VIRTIO_IB_QPT_UC] = VIRTIO_IB_QP_SQ_PSN, [VIRTIO_IB_QPT_RC] = (VIRTIO_IB_QP_TIMEOUT | VIRTIO_IB_QP_RETRY_CNT | VIRTIO_IB_QP_RNR_RETRY | VIRTIO_IB_QP_SQ_PSN | VIRTIO_IB_QP_MAX_QP_RD_ATOMIC), [VIRTIO_IB_QPT_SMI] = VIRTIO_IB_QP_SQ_PSN, [VIRTIO_IB_QPT_GSI] = VIRTIO_IB_QP_SQ_PSN, }, .opt_param = { [VIRTIO_IB_QPT_UD] = (VIRTIO_IB_QP_CUR_STATE | VIRTIO_IB_QP_QKEY), [VIRTIO_IB_QPT_UC] = (VIRTIO_IB_QP_CUR_STATE | VIRTIO_IB_QP_ACCESS_FLAGS), [VIRTIO_IB_QPT_RC] = (VIRTIO_IB_QP_CUR_STATE | VIRTIO_IB_QP_ACCESS_FLAGS | VIRTIO_IB_QP_MIN_RNR_TIMER), [VIRTIO_IB_QPT_SMI] = (VIRTIO_IB_QP_CUR_STATE | VIRTIO_IB_QP_QKEY), [VIRTIO_IB_QPT_GSI] = (VIRTIO_IB_QP_CUR_STATE | VIRTIO_IB_QP_QKEY), } } },
04-09
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值