sys用户和 普通用户执行同一sql，执行计划为什么不一样？
本帖最后由 bayannur 于 2014-8-26 17:20 编辑数据库版本10.2.5，linux主机，64G内存
以下两个执行计划，第一个为普通用户(有dba权限)，第二个为sys用户。同样一条sql，为什么会产生两个执行计划？
第一个执行计划对一个历史表采取了全表扫，用时很长，估算几个小时以上。
第二个执行计划对该表走了索引范围扫描，用于不到1秒。
事实证明第二个执行计划是正确的，为什么普通用户不走索引呢？我该怎么优化一下，大家给支支招。
对应的10046已上传
sql如下：
select b.* from cpt_hforderinfo_his a, cpt_orderinfo_his b, dual where 1 = 1 and a.cpt_serialid = b.cpt_serialid and b.partner_orderid in (select * from table(mvadmin.myconvert('AB011408072139208118,AB011408072139208156,AB011407472139208118',',')));cpt_orderinfo_his 为分区表，按时间每月分区，大约1亿条数据
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1559K| 2193M| 751K (5)| 02:30:16 | | |
| 1 | NESTED LOOPS | | 1559K| 2193M| 751K (5)| 02:30:16 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 4828K| 6695M| 751K (5)| 02:30:14 | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH| MYCONVERT | | | | | | |
| 4 | PARTITION RANGE ALL | | 482M| 652G| 745K (5)| 02:29:06 | 1 | 99 |
| 5 | TABLE ACCESS FULL | CPT_ORDERINFO_HIS | 482M| 652G| 745K (5)| 02:29:06 | 1 | 99 |
|* 6 | INDEX RANGE SCAN | INDEX_HFORDERHIS_CPT_SERIALID | 1 | 21 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1559K| 2268M| 231K (1)| 00:46:20 | | |
| 1 | NESTED LOOPS | | 1559K| 2268M| 231K (1)| 00:46:20 | | |
| 2 | NESTED LOOPS | | 4828K| 6925M| 231K (1)| 00:46:19 | | |
| 3 | VIEW | VW_NSO_1 | 8168 | 414K| 24 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 16336 | | | | |
| 5 | COLLECTION ITERATOR PICKLER FETCH| MYCONVERT | | | | | | |
| 6 | PARTITION RANGE ALL | | 4828K| 6685M| 231K (1)| 00:46:19 | 1 | 99 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | CPT_ORDERINFO_HIS | 4828K| 6685M| 231K (1)| 00:46:19 | 1 | 99 |
|* 8 | INDEX RANGE SCAN | INDEX_ORDERINFOHIS_PARTNER_OD | 1931K| | 65 (11)| 00:00:01 | 1 | 99 |
|* 9 | INDEX RANGE SCAN | INDEX_HFORDERHIS_CPT_SERIALID | 1 | 21 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------- 为什么执行计划应当一样？ 简单看了一下 运行环境细节 差了不是一点 2点，很难理解为什么你认为应当一样。 本帖最后由 bayannur 于 2014-8-25 23:07 编辑
Maclean Liu(刘相兵 发表于 2014-8-25 22:02 static/image/common/back.gif
为什么执行计划应当一样？ 简单看了一下 运行环境细节 差了不是一点 2点，很难理解为什么你认为应当一样。 ...
运行环境具体指的是？
因为sql中的object是同一个数据库的表，既然sql中的表一样，即使是不同的两个用户运行这个sql，那执行计划应该是一样的吧。
如果我想把第一个的执行计划转换成与第二个执行。该怎么优化呢？ table(pay_report.myconvert('HF011408072139208118,HF011408072139208156,HF011407472139208118', ',')))
SQL 里用了table + 自己的函数 这会导致基数评估很不准确 ==》开发的是省力了，但ORACLE CBO就吃瘪了
具体为什么走不同的执行计划要看10053 trace ,我不认为 你这2个SQL的 runtime环境会真的无差别
Maclean Liu(刘相兵 发表于 2014-8-26 12:15 static/image/common/back.gif
table(pay_report.myconvert('HF011408072139208118,HF011408072139208156,HF011407472139208118', ',')))
...
好的，我做10053看看。 Maclean Liu(刘相兵 发表于 2014-8-26 12:15 static/image/common/back.gif
table(pay_report.myconvert('HF011408072139208118,HF011408072139208156,HF011407472139208118', ',')))
...
10053我看了，运行sql的环境应该一致的，但是还没弄明白两个计划不一致。
两个10053文件已经上传了，刘大给点指导意见吧 加上 where rownum>0 之后，普通用户可以走的正确的执行计划，错误的执行计划耗时1.2h，正确的执行计划耗时1s。这个问题可能与试图拆分，嵌套子查询的问题相关。
select b.* from cpt_hforderinfo_his a, cpt_orderinfo_his b, dual where 1 = 1 and a.cpt_serialid = b.cpt_serialid and b.partner_orderid in (select * from table(mvadmin.myconvert('AB011408072139208118,AB011408072139208156,AB011407472139208118',',')) where rownum>0 ); 对比一下2个session 下面的具体 参数配置， 如优化器版本， 优化器的 goal ... 等等。 对比下 一般 如果这些一样。 2个执行计划一般是一样的。 robin 发表于 2014-8-29 16:55 static/image/common/back.gif
对比一下2个session 下面的具体 参数配置， 如优化器版本， 优化器的 goal ... 等等。 对比下 一般 如果这 ...
我看10053里面的参数是一样的，v$sql里面的参数也是一样的。 ***************
SYS 10053：
Now joining: CPT_ORDERINFO_HIS#1
***************
NL Join
Outer table: Card: 255.25 Cost: 20.78 Resp: 20.78 Degree: 1 Bytes: 2
Inner table: CPT_ORDERINFO_HIS Alias: B
Access Path: TableScan
NL Join: Cost: 152167093.64 Resp: 152167093.64 Degree: 1
Cost_io: 144145483.00 Cost_cpu: 68720052519389
Resp_io: 144145483.00 Resp_cpu: 68720052519389
普通用户10053：
***************
Now joining: CPT_ORDERINFO_HIS#1
***************
NL Join
Outer table: Card: 155958051.00 Cost: 180608.39 Resp: 180608.39 Degree: 1 Bytes: 73
Inner table: CPT_ORDERINFO_HIS Alias: B
Access Path: TableScan
NL Join: Cost: 93065411361881.14 Resp: 93065411361881.14 Degree: 1
Cost_io: 88159393776128.00 Cost_cpu: 42029188541311721472
Resp_io: 88159393776128.00 Resp_cpu: 42029188541311721472 SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
No statistics type defined for function MYCONVERT
Table: KOKBF$ Alias: KOKBF$
Card: Original: 8168 Rounded: 8168 Computed: 8168.00 Non Adjusted: 8168.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 19.23 Resp: 19.23 Degree: 0
Cost_io: 19.00 Cost_cpu: 1937344
Resp_io: 19.00 Resp_cpu: 1937344
Best:: AccessPath: TableScan
Cost: 19.23 Degree: 1 Resp: 19.23 Card: 8168.00 Bytes: 0
虽然 MYCONVERT都计算为 基数8168.00，但在Join阶段 2者基数计算差异很大。
取消这里的动态采样，再再次测试。 Maclean Liu(刘相兵 发表于 2014-8-29 23:24 static/image/common/back.gif
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinalit ...
是用以下的sql测试吗？我在普通用户下，查看执行计划跟之前是一致的，还是全表扫。
select b.*
from cpt_hforderinfo_his a, cpt_orderinfo_his b, dual
where 1 = 1
and a.cpt_serialid = b.cpt_serialid
and b.partner_orderid in
(select /*+dynamic_sampling(t 0) */ t.column_value
from table(mvadmin.myconvert('Ab011408072139208118,AB011408072139208156,AB011407472139208118',
',')) t)
页:
[1]