《Oracle編程藝術》學習筆記

1) SQL*Plus的login.sql 檔案
使用SQL*PLUS登陸時，SQL*PLUS會按照如下的順序讀取並執行指令碼：
· $Oracle_home/sqlplus/admin/glogin.sql
· 目前的目錄下的login.sql，或者設定一個環境變數SQLPATH(Windows下面通過註冊表)來指定一個目錄
[sql]

1. define _editor=vi  
2. set serveroutput on size 1000000  
3. set trimspool on  
4. set long 5000  
5. set linesize 100  
6. set pagesize 9999  
7. column plan_plus_exp format a80  
8. column global_name new_value gname  
9. set termout off  
10. define gname=idle  
11. column global_name new_value gname  
12. select lower(user) || '@' || substr( global_name, 1,  
13. decode( dot, 0, length(global_name), dot-1) ) global_name  
14. from (select global_name, instr(global_name,'.') dot from global_name );  
15. set sqlprompt '&gname> '  
16. set termout on  

指令碼說明：
· DEFINE_EDITOR=VI：設定SQL*Plus使用的預設編輯器。可以把預設編輯器設定為你最喜歡的文字編輯器（而不是文書處理器），如記事本（Notepad）或emacs。
· SET SERVEROUTPUT ON SIZE 1000000：這會預設地開啟DBMS_OUTPUT（這樣就不必每次再鍵入這個命令了）。另外也將預設緩衝區大小設定得儘可能大。
· SET TRIMSPOOL ON：假離線輸出文本時，會去除文本行兩端的空格，而且行寬不定。如果設定為OFF（預設設定），假離線輸出的文本行寬度則等於所設定的LINESIZE。
· SET LONG 5000：設定選擇LONG 和CLOB 列時顯示的預設位元組數。
· SET LINESIZE 100：設定SQL*Plus顯示的文本行寬為100個字元。
· SET PAGESIZE 9999：PAGESIZE可以控制SQL*Plus多久列印一次標題，這裡將PAGESIZE設定為一個很大的數（所以每頁只有一組標題）。
· COLUMN PLAN_PLUS_EXP FORMAT A80：設定由AUTOTRACE得到的解釋計劃輸出（explain plan output）的預設寬度。A80通常足以放下整個計劃。
· 之後這部分用於建立SQL*Plus提示符：COLUMN GLOBAL_NAME NEW_VALUE GNAME 指令告訴SQL*Plus 取得GLOBAL_NAME 列中的最後一個值，並將這個值賦給替換變數GNAME。接下來，從資料庫中選出GLOBAL_NAME，並與我的登入使用者名稱串連。這樣一來，就能知道我是誰，還有我在哪兒。

2）設定SQL*Plus的AUTOTRACE
（1）cd [ORACLE_HOME]/rdbms/admin；
（2）作為SYSTEM登入SQL*Plus；
（3）運行@utlxplan；
（4）運行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE；
（5）運行GRANT ALL ON PLAN_TABLE TO PUBLIC。
下一步是建立並授予PLUSTRACE角色：
（1）cd [ORACLE_HOME]/sqlplus/admin；
（2）作為SYS 或SYSDBA登入SQL*Plus；
（3）運行@plustrce；
（4）運行GRANT PLUSTRACE TO PUBLIC。

通過設定AUTOTRACE系統變數可以控制這個報告：
· SET AUTOTRACE OFF：不產生AUTOTRACE報告，這是預設設定。
· SET AUTOTRACE ON EXPLAIN：AUTOTRACE報告只顯示最佳化器執行路徑。
· SET AUTOTRACE ON STATISTICS：AUTOTRACE 報告只顯示SQL語句的執行統計資訊。
· SET AUTOTRACE ON：AUTOTRACE報告既包括最佳化器執行路徑，又包括SQL語句的執行統計資訊。
· SET AUTOTRACE TRACEONLY：這與SET AUTOTRACE ON類似，但是不顯示使用者的查詢輸出（如果有的話）。

另外，也可以利用explain plan語句來得到執行計畫，預設情況下執行計畫存入plan_table中，可以使用DBMS_XPLAN包來查看結果。例如：
explain plan for select *from emp where deptno=10;
select * from table(dbms_xplan.display);
 
3）配置statspack
安裝
只有作為SYSDBA 串連時才能安裝Statspack。
只需運行[ORACLE_HOME]\rdbms\admin目錄下的spcreate.sql指令碼。
安裝過程中，需要將建立的PERFSTAT使用者，需要指定使用什麼密碼，預設資料表空間和暫存資料表空間是什嗎？
如果需要重新安裝Statspack，之前應該先用spdrop.sql 刪除使用者（PERFSTAT）和已經安裝的視圖。

產生報告
執行statspack.snap可以產生系統快照，運行兩次，然後執行@spreport.sql就可以產生一個基於兩個時間點的報告。(使用perfstat或者sys使用者登入，否則很多表無許可權訪問)

可以使用spauto.sql來定義自動收集資料任務。spauto.sql的關鍵內容如下
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
來看看dbms_job.submit的定義：

PROCEDURE SUBMIT
參數名稱                       類型                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          OUT
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN     DEFAULT
 INTERVAL                       VARCHAR2                IN     DEFAULT
 NO_PARSE                       BOOLEAN                 IN     DEFAULT
 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 
因此，上面這條語句的意思就是從目前時間的下一個整點之後，每隔1小時執行1次statspack.snap。
可以通過修改spauto.sql來修改間隔。
執行後可用下列語句查看任務啟動並執行時間：
select job, next_date, next_sec from user_jobs where job = :jobno;

應及時移除收集任務，通過下面的語句：
execute dbms_job.remove(:jobno)

刪除曆史資料
如需刪除曆史資料，可以用@sptrunc.sql指令碼刪除所有的資料。也可以通過刪除stats$snapshot表來進行，其他表中的資料也會相應的串聯刪除。
declare
    snapid number;
begin
    select max(snap_id) into snapid from stats$snapshot;
    delete from stats$snapshot where snap_id <= snapid;
end;
/

收集資料選項
Statspack有兩種類型的收集選項
·層級（level）：控制收集資料的類型
    共有三種快照層級，預設值是5。
    a.level 0: 一般效能統計。包括等待事件、系統事件、系統統計、復原段統計、行緩衝、SGA、會話、鎖、緩衝池統計等等。
    b.level 5: 增加SQL語句。除了包括level0的所有內容，還包括SQL語句的收集，收集結果記錄在stats$sql_summary中。
    c.level 10: 增加子鎖存統計。包括level5的所有內容。並且還會將附加的子鎖存存入stats$lathc_children中。在使用這個層級時需要謹慎，建議在Oracle support的指導下進行。
   
    可以通過以下語句修改預設的層級設定，如果只是改變本次收集level，則不需指定i_modify_parameter參數。
    execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');

·門限（threshold）：設定收集的資料的閾值。
    快照門限只應用於stats$sql_summary表中擷取的SQL語句。有以下門限值：
    a. executions_th這是SQL語句執行的數量(預設值是100)
    b. disk_reads_tn這是SQL語句執行的磁碟讀入數量（預設值是1000）
    c. parse_calls_th這是SQL語句執行的解析調用的數量（預設值是1000）
    d. buffer_gets_th這是SQL語句執行的緩衝區擷取的數量（預設值是10000）
    任何一個門限值超過以上參數的SQL就會被收集併產生一條記錄。
   
    通過調用statspack.modify_statspack_parameter函數可以改變門限的預設值。例如：
    execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);

1 2 3 4 5 6 7 8 9.. 40 下一頁

【內容導航】
第1頁：配置環境	第2頁：開發成功的Oracle應用
第3頁：SELECT FOR UPDATE SKIP LOCKED	第4頁：Oracle體繫結構
第5頁：Oracle中的檔案	第6頁：Oracle中的檔案-參數檔案
第7頁：Oracle中的檔案-Trace檔案	第8頁：Oracle中的檔案-警告記錄檔
第9頁：Oracle中的檔案-資料檔案	第10頁：SGA,PGA和UGA
第11頁：Oracle進程	第12頁：Oracle的鎖
第13頁：Oracle的並發與多版本控制	第14頁：寫一致性
第15頁：事務原子性	第16頁：事務持久性
第17頁：DEFERRABLE約束	第18頁：REDO和UNDO
第19頁：資料庫記錄模式	第20頁：塊清除
第21頁：ORA-01555:snapshot too old 錯誤	第22頁：表類型
第23頁：段	第24頁：堆組織表
第25頁：索引組織表	第26頁：索引聚簇表
第27頁：散列聚簇表	第28頁：有序散列聚簇表
第29頁：暫存資料表和CBO	第30頁：B*樹索引
第31頁：什麼情況下適合使用B*樹索引	第32頁：位元影像索引（bitmap index）
第33頁：基於函數的索引	第34頁：資料類型-字串和二進位串
第35頁：資料類型-數值	第36頁：資料類型-時間日期
第37頁：資料類型-LOB	第38頁：表分區
第39頁：索引分割區	第40頁：分區的優點