cursor_sharing and like

SQL>  create table test_like as select * from dba_objects;
表已创建。
SQL> exec dbms_stats.gather_table_stats(user,'TEST_LIKE');
PL/SQL 过程已成功完成。
SQL> select /*+ aa1 */ * from test_like where object_name like 'aaa%';
未选定行
SQL> select /*+ aa2 */ * from test_like where object_name like 'aaa'||'%';
未选定行
SQL> show parameter cursor
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR
cursor_space_for_time                boolean                          FALSE
open_cursors                         integer                          300
session_cached_cursors               integer                          20
SQL> select sql_text,sql_id from v$sql where sql_text like '%/*+ aa%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID
-------------
select /*+ aa1 */ * from test_like where object_name like :"SYS_B_0"
0s1kqm36b5zhz
select /*+ aa2 */ * from test_like where object_name like :"SYS_B_0"||:"SYS_B_1"
4w18s9jrw692g

SQL> select * from table(dbms_xplan.display_cursor('0s1kqm36b5zhz'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0s1kqm36b5zhz, child number 0
-------------------------------------
select /*+ aa1 */ * from test_like where object_name like :"SYS_B_0"
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   155 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |     2 |   186 |   155   (2)| 00:00:02 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE :SYS_B_0)

已选择18行。
SQL> select * from table(dbms_xplan.display_cursor('4w18s9jrw692g'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4w18s9jrw692g, child number 0
-------------------------------------
select /*+ aa2 */ * from test_like where object_name like
:"SYS_B_0"||:"SYS_B_1"
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   156 (100)|          |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |  2498 |   226K|   156   (3)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE :SYS_B_0||:SYS_B_1)

已选择19行。

可以看到 like 'aaa'||'%'在cursor_sharing设置成similar的情况下,不做bind peeking,
把这个谓词的选择度按照5%计算，因此cardinality为5%,对于这个单表查询可能cardinality
的影响不算太大，但是多表查询的情况下，cardinality影响了后续连接方式的选择，因此
cardinality的评估特别重要


SQL> select /*+ aa3 */ * from test_like where object_name like 'aaa%';
未选定行
SQL> select /*+ aa4 */ * from test_like where object_name like 'aaa'||'%';
未选定行
SQL> select sql_text,sql_id from v$sql where sql_text like '%/*+ aa%';
SQL_TEXT
-------------------------------------------------------------------------------------------------
SQL_ID
-------------
select /*+ aa4 */ * from test_like where object_name like 'aaa'||'%'
gnc7bptvgqcfg
select sql_text,sql_id from v$sql where sql_text like '%/*+ aa%'
47u9yx2vqfdcv
select /*+ aa3 */ * from test_like where object_name like 'aaa%'
9bz9pvm32fkjg

SQL> select * from table(dbms_xplan.display_cursor('gnc7bptvgqcfg'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  gnc7bptvgqcfg, child number 0
-------------------------------------
select /*+ aa4 */ * from test_like where object_name like 'aaa'||'%'
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   155 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |     2 |   186 |   155   (2)| 00:00:02 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE 'aaa%')

已选择18行。
SQL> select * from table(dbms_xplan.display_cursor('9bz9pvm32fkjg'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  9bz9pvm32fkjg, child number 0
-------------------------------------
select /*+ aa3 */ * from test_like where object_name like 'aaa%'
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   155 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |     2 |   186 |   155   (2)| 00:00:02 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE 'aaa%')

已选择18行。

当cursor_sharing=exact的情况下,这2个语句没区别oracle会自己把'aa'||'%'转成'aa%'

SQL> alter session set cursor_sharing=force;
会话已更改。
SQL> select /*+ aa5 */ * from test_like where object_name like 'aaa%'
  2  /
未选定行
SQL> select /*+ aa6 */ * from test_like where object_name like 'aaa'||'%'
  2  /
未选定行
SQL> select sql_text,sql_id from v$sql where sql_text like '%/*+ aa%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID
-------------
select /*+ aa5 */ * from test_like where object_name like :"SYS_B_0"
b8tyv92rx8bbg
select sql_text,sql_id from v$sql where sql_text like '%/*+ aa%'
47u9yx2vqfdcv
select /*+ aa6 */ * from test_like where object_name like :"SYS_B_0"||:"SYS_B_1"
2mjux99x96du4

SQL> select * from table(dbms_xplan.display_cursor('b8tyv92rx8bbg'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  b8tyv92rx8bbg, child number 0
-------------------------------------
select /*+ aa5 */ * from test_like where object_name like :"SYS_B_0"
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   155 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |     2 |   186 |   155   (2)| 00:00:02 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE :SYS_B_0)

已选择18行。
SQL> select * from table(dbms_xplan.display_cursor('2mjux99x96du4'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  2mjux99x96du4, child number 0
-------------------------------------
select /*+ aa6 */ * from test_like where object_name like
:"SYS_B_0"||:"SYS_B_1"
Plan hash value: 4011982204
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   156 (100)|          |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST_LIKE |  2498 |   226K|   156   (3)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE :SYS_B_0||:SYS_B_1)

已选择19行。

当cursor_sharing=force的时候,原因同cursor_sharing=similar

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8984272/viewspace-619908/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8984272/viewspace-619908/