Insert 都做了什么？

zhanghome

后面呢？解决了吗？怎么就没有了^_^

caoliwei

这种东西 没有具体的环境
谁都不好说
除非把环境详细描述清楚
估计能详细描述环境
也能知道问题在那里了

alexzeng

从这个语句, 我想到一个问题:
========== 语句 ==========
insert into t_2(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12)
values
(:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12)

就是, 对于分区表不应该用绑定变量, 这样Oracle不知道该用哪个分区. 看看执行计划就知道, 本来不同分区插入的执行计划应该是不一样的,

参考下面这段, from http://www.ixora.com.au/tips/design/partitioning.htm
Partition Elimination
Another benefit of range partitioning, is automatic partition elimination during the formulation of query execution plans. The optimizer compares the WHERE clause predicates with the partitioning range for each partition and eliminates from the execution plan those partitions which can be guaranteed not to contain rows satisfying the query. Of course, partition elimination normally relies on not using bind variables, so that the optimizer can determine which partitions can be eliminated. However, in the case of equality predicates on the partitioning key, the optimizer is content in the knowledge that at most one partition will be required.

kenchendz

最初由 alexzeng 发布
[B]从这个语句, 我想到一个问题:
========== 语句 ==========
insert into t_2(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12)
values
(:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12)

就是, 对于分区表不应该用绑定变量, 这样Oracle不知道该用哪个分区. 看看执行计划就知道, 本来不同分区插入的执行计划应该是不一样的,

参考下面这段, from http://www.ixora.com.au/tips/design/partitioning.htm
Partition Elimination
Another benefit of range partitioning, is automatic partition elimination during the formulation of query execution plans. The optimizer compares the WHERE clause predicates with the partitioning range for each partition and eliminates from the execution plan those partitions which can be guaranteed not to contain rows satisfying the query. Of course, partition elimination normally relies on not using bind variables, so that the optimizer can determine which partitions can be eliminated. However, in the case of equality predicates on the partitioning key, the optimizer is content in the knowledge that at most one partition will be required. [/B]

这应该才是问题的所在。

sunchao

最初由 biti_rainy 发布
[B]除去SYSTEM，一共24个回滚段。
其中有19个100M的，4个2M的，1个20M的

干吗要弄成大小差异这么大的回滚段?
请全部设置成大小一致,要么把不是100M的删除


tkprof分析的结果中,能否看出是哪一步消耗的时间多?
尝试把log  buffer设置小一点?
还有跟检查点相关的参数,也设置小一点? [/B]

所谓的 破桶效应

cjf107

最初由 alexzeng 发布
[B]从这个语句, 我想到一个问题:
========== 语句 ==========
insert into t_2(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12)
values
(:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12)
就是, 对于分区表不应该用绑定变量, 这样Oracle不知道该用哪个分区. 看看执行计划就知道, 本来不同分区插入的执行计划应该是不一样的,

参考下面这段, from http://www.ixora.com.au/tips/design/partitioning.htm
Partition Elimination
Another benefit of range partitioning, is automatic partition elimination .............................. [/B]

“Elimination”本意是“排除、消除”的意思，那“ partition elimination”是“消除分区”还是什么意思啊？

cjf107

最初由 cjf107 发布
[B]

“Elimination”本意是“排除、消除”的意思，那“ partition elimination”是“消除分区”还是什么意思啊？ [/B]

如果是，那“Of course, partition elimination normally relies on not using bind variables, so that the optimizer can determine which partitions can be eliminated.”只能说明删的时候不要用绑定变量。虽然我觉得插入时基于分区的字段上最好也不要使用绑定变量。