deferred segment creation(延緩的區段建立)

Oracle 11g R2新增加一種功能:deferred segment creation(延緩的區段建立)
也就是在建立新區段時,並不會立刻配置區段.而是延緩到區段第一次被新增資料列時,才真正配置區段

SQL> show parameter deferred_segment_creation

NAME TYPE VALUE
------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE

SQL> create user frank identified by oracle;

User created.

SQL> grant create session,create table to frank;

Grant succeeded.

SQL> create table frank.t1 (a number,b varchar2(10)) tablespace users;

Table created.

frank並沒有被設定配額(Quota)在users表格空間上,但是還是可以成功地建立t1表格

SQL> insert into frank.t1 values(1,'A');
insert into frank.t1 values(1,'A')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

因為延緩的區段建立的機制,所以現在才試圖建立區段,所以才會出現上面的錯誤訊息

SQL> select object_id,data_object_id from dba_objects
2 where object_name='T1' and object_type='TABLE' and owner='FRANK';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
74695 74695

SQL> select segment_name,segment_type from dba_segments
2 where segment_name='T1' and owner='FRANK';

no rows selected

log_archive_dest_31 ?

--從Oracle 11g R2後,archivelog destination可以多達31
--log_archive_dest_n可以將archived log存放在本地端:location=/path/或遠端standby database:service=standbydb_service_name
--可以log_archive_dest_1到10可以設定為location或service
--但是log_archive_dest_11到31只能設定為service
SQL> show parameter log_archive_dest_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string

--Archiver的個數也多達30個
SQL> select count(*) from v$bgprocess where substr(name,1,3)='ARC';

COUNT(*)
----------
30

--log_archive_dest_1 到 log_archive_dest_10
--可以將archived logfile傳到local disk或遠端的standby database(使用net service name)
SQL> alter system set log_archived_dest_1='location=/home/oracle/orcl_archive1/';
--orcl_standby為net service name,可以連到standby database
SQL> alter system set log_archived_dest_5='serivce=orcl_standby';



--log_archive_dest_11到log_archive_dest_31
--只能用來將archived logfile傳到遠端的standby database

SQL> alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1';
alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_11 contains repeated or conflicting
attributes

SQL> alter system set log_archive_dest_11='service=orcl_standby';

System altered.

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.