教你如何成為Oracle 10g OCP - 第九章 物件管理(11) - 管理索引,sequence及resumable

管理索引

9.2.7 管理索引
管理索引包括建立索引，rebuild index及監控索引。
我們可以使用database control來建立索引。
create index idx_book_author on books(author) tablespace indx;

重建索引 --
alter index .... rebuild ;
從Oracle8.1.6以來，可以使用online 進行索引重建。
rebuild index使用原索引的葉子節點作為新索引的資料來源，I/O比
掃描表中的資料要少很多，還有，由於葉子節點索引條目已經排好序，
所以效率會高一些。rebuild index原理詳見：
http://space.itpub.net/35489/viewspace-594278

還可以透過parallel進行並行處理。加入compute statistics在索引重建過
程中就統計資訊。雖然索引可以提高查詢的效率，但是在更新資料的時候，
需要同時維護索引裡的索引條目，因此它會降低DML操作的效能，Oracle建議
一個表上的索引數量不要超過7個。

 

監控索引 --

如果有些索引不能被很好的使用，就需要刪除它，以免影響到DML
操作的效能，為此，Oracle提供了一個監控索引的方法，讓DBA知
道，某個索引是否被使用過了，啟用索引監控的方法：
alter index idx_books_emp monitoring usage ;
啟用之後，我們可以檢視v$object_usage檢視，used欄位說明該
欄位在監控的時間段中是否被使用過了。
之後我們可以關閉監控索引。
alter index idx_books_emp nomonitoring usage ;
一般我們可以在典型的業務執行過程中啟用索引監控。

 

9.2.8 其他物件
Oracle中常用的物件還有檢視和序列號。
使用檢視的好處：
1. 可以遮蔽SQL語句的複雜性。
2. 可以管理許可權。

對於Sequence來說，這是一個唯一數值發生器，用來生成唯一的數值。Oracle
在生成序列號時，會考慮併發的問題，即多個session同時申請序列號的值，每
個session獲得序列號的值是不一樣的。特別體現在單機和RAC上。 

ORACLE序列的使用 ---
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE許可權，

語法 (具體解釋見最下端)：
CREATE SEQUENCE [schema.]sequence
    [INCREMENT BY integer]
    [START WITH integer]
    [MAXVALUE integer | NOMAXVALUE]
    [MINVALUE integer | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE integer | NOCACHE]
    [ORDER | NOORDER]

例如：
CREATE SEQUENCE use1.emp_sequence
    INCREMENT BY 1  -- 每次加幾個
    START WITH 1    -- 從1開始計數
    NOMAXVALUE      -- 不設定最大值
    NOCYCLE       -- 一直累加，不迴圈
    CACHE 10
    NOORDER;   -- 並行時取得序列的順序需要用ORDER

一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的當前值
NEXTVAL=增加sequence的值，然後返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL

 

可以使用sequence的地方：
- 不包含子查詢、snapshot、VIEW的 SELECT 語句
- INSERT語句的子查詢中
- INSERT語句的VALUES中
- UPDATE 的 SET中  
可以看如下例子：
INSERT INTO emp VALUES  
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
SELECT empseq.currval     FROM DUAL;

- 第一次NEXTVAL返回的是初始值(比如1)；隨後NEXTVAL會自動增加你定義的
INCREMENT BY值，然後返回增加後的值。CURRVAL 總是返回當前SEQUENCE的
值，但是在第一次NEXTVAL初始化之後才能使用CURRVAL，否則會出錯。一次
NEXTVAL會增加一次 SEQUENCE的值. 

SQL> SELECT  emp_sequence.CURRVAL from dual;  
ORA-08002: 序列 EMP_SEQUENCE.CURRVAL 尚未在此會話中定義

SQL> SELECT  emp_sequence.NEXTVAL from dual; 
NEXTVAL
-------
1

SQL> SELECT  emp_sequence.CURRVAL from dual;
CURRVAL
-------
1

SQL> select * from user_sequences ; 

sequence_name  min_value   max_value   cache_size   last_number
------------------------------------------------------------------
emp_sequence       1          1E27         10            11 

可以看到資料字典中的last_number值為11，1～10被cache到shared pool中。
這時候如果例項crash或者alter system flush shared pool，序號會不連續，
後面會詳細說明。

SQL> SELECT  emp_sequence.NEXTVAL from dual; 
NEXTVAL
-------
2
再次執行發現NEXTVAL一直增加。

 

- 如果指定CACHE值，ORACLE就可以預先在記憶體裡面放置一些sequence，這
樣存取的快些。cache裡面的取完後，oracle自動再取一組 到cache。 使用
cache或許會跳號， 比如資料庫突然不正常down掉（shutdown abort),cache
中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止
這種情況。
     比如設定cache 為20，在第一次取序列號時，會將資料字典裡面
的值修改為21，因為1～20都cache到記憶體中了，當記憶體中的20個號都是用完
了，再次取20個號到記憶體中，資料字典裡面的值改為41，以此類推，也即是
這可能引起不連號的現象，比如21~40已經在記憶體中，這20個序列號使用了5
個，當前記憶體中最小的序列號是26，如果這時例項崩潰，下次例項啟動後，
26到40之間的14個號都丟失了，序列號會從資料字典記錄的41開始，因此
對於欄位連號的需求來說，不推薦使用sequence, 當然cache設定為0，可以
保證不刪除記錄的情況下會連號，因為每次取號都是從資料字典獲得，不是
從記憶體，所以效能非常差。這裡的cache是在shared pool中分配的。

一般系統cache為預設的20已經足夠了，對於RAC系統，如果多個session在
不同節點併發迴圈間斷取大量的值，那麼設定cache大一些，比如1000，效能
改善比較明顯，單例項測試也一樣，只是沒有RAC明顯而已。所以cache大小
增加與否要看是否影響到效能。

排序引數： 預設是NOORDER，如果設定為ORDER；在單例項中沒有影響，在RAC
環境，多例項實際快取相同的序列，此時在多個例項併發取該序列的時候，會
有短暫的資源競爭來在多例項之間進行同步。因次效能相比noorder要差，所以
RAC環境非必須的情況下不要使用ORDER， 尤其要避免NOCACHE ORDER組合；

 

2、Alter Sequence
你或者是該sequence的owner，或者有ALTER ANY SEQUENCE 許可權才能改動
sequence. 可以alter除start至以外的所有sequence引數.如果想要改變
start值，必須 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
    INCREMENT BY 10
    MAXVALUE 10000
    CYCLE    -- 到10000後從頭開始
    NOCACHE ;

影響Sequence的初始化引數：
SEQUENCE_CACHE_ENTRIES =設定能同時被cache的sequence數目。 
可以很簡單的Drop Sequence
DROP SEQUENCE order_seq;

特別注意：

如果你正在parallel 模式下使用Oracle parallel server選項，且指定了
ORDER選項，sequence值將不會被cache, 不管是否你指定cache或nocache。

僅僅在parallel 模式下使用Oracle parallel server 選項的情況下，ORDER
選項對於保證順序生成序列號才有必要。 如果你正在使用exclusive mode，
sequence 號碼總是按照順序的。

----------------------------------

 

SYNTAX:

CREATE SEQUENCE [schema.]sequence
    [INCREMENT BY integer]
    [START WITH integer]
    [MAXVALUE integer | NOMAXVALUE]
    [MINVALUE integer | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE integer | NOCACHE]
    [ORDER | NOORDER]

where:

INCREMENT BY
    specifies the interval between sequence numbers. This value can be
    any positive or negative Oracle integer, but it cannot be 0. If
    this value is negative, then the sequence descends. If the
    increment is positive, then the sequence ascends. If you omit this
    clause, the interval defaults to 1.

    指定序列號的interval, 可以是正負整數值，但是不能是0，如果是負值，那
    麼序列號會降低，如果是正值，序列號會上升，如果忽略這個選項，interval
    預設是1. 

MINVALUE
    specifies the sequence's minimum value.

NOMINVALUE
    specifies a minimum value of 1 for an ascending sequence or -10
    for a descending sequence.
    The default is NOMINVALUE.
   
    不指定最小值，這時預設升序序列號最小值是1，降序的最小值是-10，預設設
    置是NOMINVALUE .

MAXVALUE
    specifies the maximum value the sequence can generate.

NOMAXVALUE
    specifies a maximum value of 10 for a descending sequence.
    The default is NOMAXVALUE.
   
    不指定最大值，這時預設降序的序列號最大值為10，預設為NOMAXVALUE.

START WITH
    specifies the first sequence number to be generated. You can use
    this option to start an ascending sequence at a value greater than
    its minimum or to start a descending sequence at a value less than
    its maximum. For ascending sequences, the default value is the
    sequence's minimum value. For descending sequences, the default
    value is the sequence's maximum value. 

    指定一個生成的第一個序列號碼，可以使用這個選項去開始一個升序,值要
    大於它的最小值，或者開始一個降序序列號，值要小於它的最大值，對於升
    序，start with的預設值就是序列號的minimum value, 對於降序序列，預設
    值就是設定的maximum value .

CYCLE
    specifies that the sequence continues to generate values after
    reaching either its maximum or minimum value. After an ascending
    sequence reaches its maximum value, it generates its minimum value.
    After a descending sequence reaches its minimum, it generates its
    maximum. 
    指定在達到最大或最小值後繼續生成值，降序序列達到它的最大值後，重新
    從最小值開始，升序序列達到它的最小值，那麼重新從最大值開始。

NOCYCLE
    specifies that the sequence cannot generate more values after
    reaching its maximum or minimum value.
    The default is NOCYCLE.

    達到最大或最小值之後，不能重新生成。預設為NOCYCLE.

CACHE
    specifies how many values of the sequence Oracle preallocates and
    keeps in memory for faster access. The minimum value for this
    parameter is 2. For sequences that cycle, this value must be less
    than the number of values in the cycle.
   
    指定Oracle預分配多少個sequence值儲存在記憶體中，這樣會得到更快的訪問
    速度，這個引數最小值是2。 對於迴圈的sequences，這個值必須小於在這個
    cycle 中的值的數目 。

NOCACHE
    specifies that values of the sequence are not preallocated.

    If you omit both the CACHE parameter and the NOCACHE option, Oracle
    caches 20 sequence numbers by default. However, if you are using
    Oracle with the Parallel Server option in parallel mode and you
    specify the ORDER option, sequence values are never cached,
    regardless of whether you specify the CACHE parameter or the NOCACHE
    option.

    指定sequecne的值不能被預分配，如果忽略cache和nocache選項，Oracle
    預設會cache 20 個sequence numbers 。 但是如果你正在parallel 模式
    下使用Oracle parallel server選項，且你指定了ORDER選項，sequence值
    將不會被cache, 不管是否你指定cache或nocache與否。

ORDER
    guarantees that sequence numbers are generated in order of request.
    You may want to use this option if you are using the sequence
    numbers as timestamps. Guaranteeing order is usually not important
    for sequences used to generate primary keys.

    保證sequence數值按照請求的順序生成，如果你正在使用sequence numbers
    作為 timestamps，你可能想要使用這個選項。保證順序對於用於生成PK的
    sequence而言常常不是重要的。ORDER對於效能有一定的影響。

NOORDER
    does not guarantee sequence numbers are generated in order of
    request.

    If you omit both the ORDER and NOORDER options, Oracle chooses
    NOORDER by default. Note that the ORDER option is only necessary to
    guarantee ordered generation if you are using Oracle with the
    Parallel Server option in parallel mode. If you are using exclusive
    mode, sequence numbers are always generated in order.
   
    不按照順序生成sequence, 如果忽略ORDER或NOORDER選項，預設是NOORDER,
    注意，僅僅在parallel mode下使用Oracle parallel server 選項的情況下，
    ORDER選項對於保證順序生成序列號才有必要。 如果你正在使用exclusive mode，
    sequence 號碼總是按照順序的。

PREREQUISITES:
    To create a sequence in your own schema, you must have CREATE
    SEQUENCE privilege.

    先決條件： 要建立sequence,必須有create sequence的許可權。

    To create a sequence in another user's schema, you must have CREATE
    ANY SEQUENCE privilege. If you are using Trusted Oracle in DBMS MAC
    mode, your DBMS label must dominate the creation label of the owner
    of the schema to contain the sequence.

    要在其他使用者schema下建立sequence,需要有create any sequence許可權。

 

9.2.9  可恢復的空間分配(resumable space allocation) 

我們經常因為沒有規劃好而在做大操作接近尾聲的時候，發現空間不足，全部回滾，
這非常讓人沮喪，在ORACLE10g 中引入了一個新特性： 可恢復的空間分配(resumable
space allocation)， 可以讓我們發現空間不足等問題時，可以讓操作暫停並掛起，
在加入新的空間後繼續。

因為某使用者操作掛起時，會導致使用者獲得的資源長時間不能釋放，因此能夠進行掛
起的使用者需要有 resumable 的系統許可權。 

需要注意，發生空間耗盡問題後，如果一定時間內不處理，插入操作還是要失敗，這
個時間段就叫做掛起超時。 掛起時間由 resumable_timeout決定(預設是0秒)。

SQL> grant resumable to tony ;
SQL> alter system set resumable_timeout=0 ;
SQL> insert into test1 select * from test2 ; 
發現空間不足時，會報錯空間不足。接下來我們啟用可恢復操作。

SQL> alter system set resumable_timeout=10 ;
SQL> insert into test1 select * from test2 ; 
如果空間不足，然後我們在10秒內沒有處理完，那麼會顯示掛起超時及空間不足
的錯誤。

下面我們在session級別顯示啟用可恢復操作，同時指定timeout為600秒.
SQL> alter session enable resumable timeout 600 ;
SQL> insert into test1 select * from test2 ; 
檢視dba_resumable檢視可以看到相關掛起資訊。
然後我們加入空間。
回到插入操作的掛起的session上，命令很快執行完畢。