绑定变量与PEEKING --CBO 的痛 ？

ylsired

SQL一般都是根据用户输入的条件而动态组合的，这个时候加HINT显得比较困难

ylsired

我们的应用查询，先COUNT(*)一个结果，得到总数，然后再SELECT * 前50条显示，这个COUNT(*)蚝资源很厉害，TOP 30都是很多查询的COUNT(*)

eagle_fan

你count(*)地执行计划不走索引吗？

为什么要count(*)?

ylsired

这是业务部门特别要求的，没有办法。
分页有什么好方法没有呢？

Yong Huang

最初由 ylsired 发布
[B]我们的应用查询，先COUNT(*)一个结果，得到总数，然后再SELECT * 前50条显示，这个COUNT(*)蚝资源很厉害，TOP 30都是很多查询的COUNT(*) [/B]

I remember Tom Kyte talked about this and said you can just give the user an estimate instead of a real, accurate, count. An estimate can be fetched from user_tables.num_rows after table analysis. Have you noticed you search on Google and it says it finds "about XXX" results? When the XXX number is small (as when you search for an obscure word), you can see the actual number and it differs from XXX. Users most likely are OK with this inaccurate count.

Yong Huang

玉面飞龙

Explain plan is always doing a hard parse.
[php]
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select /*+ dynamic_sampling(4) */ * from gtt where x = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX | 1 | 13 | 1 (0)| 00:00:01
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=1)
Note
-----
- dynamic sampling used for this statemen
[/php]

It says "you would do a index range scan IF YOU WERE TO HARD PARSE RIGHT NOW"  而且忽视peeking bind带来的可能的执行计划改变。

[php]
SQL> update big_table set object_type='One';
26708 rows updated.
SQL> commit;
Commit complete.
SQL> update big_table set object_type='Two' where rownum<10;
9 rows updated.
SQL> commit;
Commit complete.
SQL> create index bigIndx on big_table(object_type) nologging;
Index created.
SQL> analyze table big_table compute statistics for table for all indexes for all indexed columns size 75;
Table analyzed.
SQL> alter system flush shared_pool;
System altered.
SQL>  exec :var1 := 'Two';
PL/SQL procedure successfully completed.
SQL> explain plan for select * from big_table where object_type=:var1;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 13354 |   965K|    22 |
|*  1 |  TABLE ACCESS FULL   | BIG_TABLE   | 13354 |   965K|    22 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIG_TABLE"."OBJECT_TYPE"=:Z)
Note: cpu costing is off
14 rows selected.
SQL> exec :var1 := 'One';
PL/SQL procedure successfully completed.
SQL>  explain plan for select * from big_table where object_type=:var1;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 13354 |   965K|    22 |
|*  1 |  TABLE ACCESS FULL   | BIG_TABLE   | 13354 |   965K|    22 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIG_TABLE"."OBJECT_TYPE"=:Z)
Note: cpu costing is off
14 rows selected.
............
[/php]

如上发现，explain plan对不同的绑定变量根本就不考虑可能的执行计划改变；

[php]
SQL> alter session set sql_trace=true;
Session altered.
SQL>  exec :var1 := 'Two';
PL/SQL procedure successfully completed.
SQL>  select * from big_table where object_type=:var1;

********************************************************************************
select *
from big_table where object_type=:var1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          5          0           9

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  TABLE ACCESS BY INDEX ROWID OBJ#(7792)
      9   INDEX RANGE SCAN OBJ#(7796) (object id 7796)
.......
[/php]

相比sql_trace更准确

An important thing to understand about the EXPLAIN PLAN command is
that it shows you the execution plan Oracle might use if the statement
were to be hard parsed right now—which could be different from the true
execution plan currently in use for the very same statement.

http://asktom.oracle.com/pls/ask ... ERIA:53834589384045

http://www.dbspecialists.com/specialists/specialist2004-10.html

ylsired

最近就一个sql trace 问题与oracle 印度工程师讨论了很久，也终于知道是因为 bind variable peeking 的功能导致的: explain plan 的时候，cbo 是不作bind value peeking 的，而实际执行的时候是做peeking 的。 explain plan 就算是做hard pase ，也不会做peeking 。
因此，对于使用了bind 变量的sql ，explain plan 的计划只能是一个参考，实际计划必须使用v$sql_plan 了。

rollingpig

如果实在很烦bind peeking ,没问题，Oracle提供了关掉该功能的方法
_optim_peek_user_binds

show parameters peek

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optim_peek_user_binds               boolean     TRUE

lfree

最初由 biti_rainy 发布
[B]没办法，这就是一个痛  

peeking 只在hard  parse 的时候并且存在histograms时候生效,以后的soft  parse 选择的执行计划和前面hard parse 就是一样的。所以有运气成分在里面，就看第一次hard  parse时候 peeking 的值是什么了 [/B]

这个是不是讲第一次有效,后面就会按照第一次生成的计划执行.

lfree

最初由 ylsired 发布
[B]最近在9i 做SQL TUNNING，经常发现EXPLAIN PLAN的执行计划与语句实际执行的计划(v$sql_plan中查询）不同，原因就是语句中多处使用了绑定变量，特别是对于日期范围查询，单证范围查询等用了 > ,< 操作符的条件，cbo往往无法选择到真正最佳的执行计划；而不使用绑定变量，就没有此问题，真是苦恼啊。
大家对此类问题又没有什么好的避免措施？ [/B]

变态一点,,就是程序要写的复杂一下..
我们有一个程序就是这样,一般情况下仅仅查询几天的数据,,使用日期索引就很好.而如果查询几个月就慢的要命..

修改程序先判断查询的时间范围,然后选择合理的执行方式.

看来oracle要改变peeking的执行机制.