baseline依賴SQL文字還是SQL ID?

create table t1 (col1 number);

select /*+ find_me */ * from   t1 where        col1 = 3;
select /*+ find_me */ * from   t1 where        col1 =      3;
select /*+ find_me */ * from   t1 where        col1 =          3;
select /*+ find_me */ * from   T1 where        col1 =          3;

select /*+ find_me */ * from   t1 where        col1 =          3
select sql_id  from v$sql where sql_text like '%find_me%' and sql_text not like '%v$sql%';

ctjxy06k6d3mx
aqk191u7r1jcg
3tgvczhfa699z
g2paq9587m72x

set num 20
select sql_id, hash_value, old_hash_value, exact_matching_signature, force_matching_signature
from v$sql
where sql_id IN ('ctjxy06k6d3mx','aqk191u7r1jcg','3tgvczhfa699z','g2paq9587m72x');
SQL_ID                               HASH_VALUE       OLD_HASH_VALUE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
-------------------------- -------------------- -------------------- ------------------------ ------------------------
ctjxy06k6d3mx                        2758184573           2948577380      4456219368825610060     12886847737592201784
aqk191u7r1jcg                        2406532495           1523185690      4456219368825610060     12886847737592201784
3tgvczhfa699z                         480453951            921579551      4456219368825610060     12886847737592201784
g2paq9587m72x                        1350147165           2036140407      4456219368825610060     12886847737592201784

 

declare
 l_op pls_integer;
begin
 l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'ctjxy06k6d3mx');
 l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'aqk191u7r1jcg');
 l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'3tgvczhfa699z');
 l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'g2paq9587m72x');
end;
/

select to_char(signature), sql_text from dba_sql_plan_baselines where signature = 4456219368825610060 order by created desc;
TO_CHAR(SIGNATURE)   SQL_TEXT
-------------------- --------------------------------------------------------------------------------
4456219368825610060  select /*+ find_me */ * from   t1 where        col1 =          3

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where        col1 = 3;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  cr6chh7p7vvzt, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where        col1 = 3

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

Note
-----
   - SQL plan baseline SQL_PLAN_3vpxbwzn11rucdbd90e8e used for this statement

23 rows selected.

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where  col1 =        3;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  8j52h3wtgvu3n, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where  col1 =        3

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

Note
-----
   - SQL plan baseline SQL_PLAN_3vpxbwzn11rucdbd90e8e used for this statement

23 rows selected.

SQL> SELECT /*+ find_me */ *
  2  FROM   t1
  3  WHERE  col1 =        3;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  894k8t6nu8kbf, child number 0
-------------------------------------
SELECT /*+ find_me */ * FROM   t1 WHERE  col1 =        3

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

Note
-----
   - SQL plan baseline SQL_PLAN_3vpxbwzn11rucdbd90e8e used for this statement

23 rows selected.