Show parameter的結果，是可以相信的嗎?

Oracle DBA常常會使用SQLPLUS指令:show parameter，來顯示目前的參數值
例如:
SQL> show parameter fast_start_mttr_target
NAME TYPE VALUE
------------------ ------- ------
fast_start_mttr_target integer 0

但是這個參數值的來源到底是Session Specific還是Instance Level的參數值
其實show parameter顯示的是目前這個Session的參數值，也就是v$parameter的內容

但是有些參數可以在Session Level與Instance Level有不同的參數值
所有想要知道Instance Level的參數，必須查詢v$system_parameter

可是有些參數值，使用v$parameter或v$system_parameter查詢時，都有顯示參數值
但是可能只是使用預設值或尤其它參數所衍生的參數值，必沒有真的被設定在參數檔中
所以可以使用v$spparameter來查詢某個參數到底是否有明確設定
例如剛剛使用show parameter顯示fast_start_mttr_target的值為0,可是這個參數真的有被明確設定為0嗎？
SQL>select value from v$spparameter where name='fast_start_mttr_target';

VALUE
-----

如果結果如上，表示在spfile中並沒有設定此參數，所以show parameter的結果只是使用此參數的預設值，而fast_start_mttr_target的預設值剛好為0

若結果為
VALUE
-----
0
則表示fast_start_mttr_target的參數值明確被設定為0

Oracle管理藝術

Oracle管理藝術 顧問級DBA的思維鍛鍊與經驗傳承

本書集結10年經驗而成，採教學法的寫作方式，著重實證，以確保內容的精準度。作者擔任資深Oracle DBA教練多年，擁有Oracle最高等級OCM認證，將他所能給予已擔任DBA工作者，或預備朝此專業發展者，最完整、且最具邏輯的知識體系。
本書特別適合有經驗的Oracle DBA閱讀，以做為精進至顧問級專家的階梯。

Function Based Index 與 Cursor_sharing 的搭配

今天有人問我，為何他所建立的Function Based Index並沒有被使用,即便使用Hint也依然使用Full Table Scan
我向他要了相關的資訊後,使用下列的範例來解釋這個問題
在使用function based index時要注意cursor_sharing的設定值,將可能造成無法使用索引的問題

1.首先建立測試環境
SQL> create table t1 as select * from all_objects;
SQL> create index t1_fidx on t1(substr(object_name,3,6);
SQL> execute dbms_stats.gather_table_stats('HR','T1',cascade=>true);

2.開始測試
SQL> select substr(object_name,3,6) from t1 where object_id=10000;

SUBSTR(OBJEC
------------
ER_ADV

SQL> select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV';
查詢結果省略

3.使用sql_id找出真正使用的執行計畫,如果使用explain plan或set autotrace有時候會與真正的執行計畫不同
SQL> select sql_id from v$sqlarea
2 where sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
fyt5sd2rt1zv6

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID fyt5sd2rt1zv6, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,3,6)='ER_ADV'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("T1"."SYS_NC00016$"='ER_ADV')


20 rows selected.
由以上的結果可以看出,Function based Index有被使用


4.現在將cursor_sharing設為force,在進行相關的測試
SQL> select substr(object_name,3,6) from t1 where object_id=20000;

SUBSTR(OBJEC
------------
565853

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='565853';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/25658537_StoreInstruction 20000
/25658537_StoreInstruction 39336

SQL> select substr(object_name,3,6) from t1 where object_id=30000;

SUBSTR(OBJECT_NAME,3,6)
------------------------------------------------------------
6d7f7a

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
注意現在的where條件已經被cursor_sharing=force的功能修改為substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
與所執行的where substr(object_name,3,6)不同
所以才會造成下面的結果,使用full table scan,而不使用function based index

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6w6hd3w7ju6v9, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

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

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

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

5.即便加上hint,強迫使用index,也無法使用
SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 9hnjcn7h3t35d, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 230 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 538 | 16678 | 230 (2)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

6.如果將cursor_sharing設為exact,則hint就可以發揮效果,當然若不使用hint在目前的環境,也是可以使用function based index
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select substr(object_name,3,6) from t1 where object_id=40000;

SUBSTR(OBJEC
------------
b93801

SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='b93801';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/7b938016_ProtocolVersion 20664
/7b938016_ProtocolVersion 40000

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
bbhcgkhrysz5s
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,3,6)='b93801'

9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------

0uywm521n21jj
select sql_id,sql_text from v$sqlarea where sql_text like 'select % from t1 where substr%'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID bbhcgkhrysz5s, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,3,6)='b93801'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("T1"."SYS_NC00016$"='b93801')


20 rows selected.