以sys使用者登入，執行如下的命令:

alter system set optimizer_index_cost_adj=10 scope=spfile
alter system set optimizer_dynamic_sampling=5 scope=spfile

optimizer_index_cost_adj取值0-1000，預設100 ;值越小就越趨向使用索引

optimizer_dynamic_sampling   配置oracle對沒有統計資訊的表會動態取樣，會得到更準確的統計資訊，同時讓最佳化器得到更準確的執行計劃。這個引數對臨時表尤其有用。

 

1.2.分析表

 

應該把所有沒有做過分析的表都做一下分析：

執行如下語句的返回結果。

select

'analyze table '||owner||'.'||table_name||' compute statistics; '

from dba_tables

where num_rows is not null

and owner not in ('SYS','SYSTEM')

and table_name not like '%$%'

and temporary = 'N'

如果針對SCHEMA裡的所有表做分析,以symbols為例

begin

dbms_stats.gather_schema_stats ('SYMBOLS');

end;

/

1.3.增加資料維護的job

1.3.1.   將表的監視開啟

 

 select ‘alter table ‘||owner||’.’||table_name||’ monitoring; ’ 

from dba_tables

where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

and table_name not like '%$%'

and TEMPORARY=’N’;

 

 這個監視是在記憶體中做的，一般情況下對效能不會有什麼影響，如果發現實在有影響的時候，

下面語句關閉監視，

select ‘alter table ‘||owner||’.’||table_name||’ nomonitoring; ’ 

from dba_tables

where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

and table_name not like '%$%'

and TEMPORARY=’N’;

 

1.3.2.   新增JOB

 

用sys使用者登入

執行如下語句，mynextdate,mynextSatdate需要手工更改為下一日和下一個週六。

 define mynextdate='2004-03-03'; --to_char(sysdate + 1,'yyyy-mm-dd');

 define mynextSatdate='2004-03-06'; --to_char(NEXT_DAY(sysdate,7),'yyyy-mm-dd');

 --每天早上3點左右執行flush monitor info

 call sys.dbms_job.remove(101);

 call sys.dbms_job.isubmit( 101,

                      'sys.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();',

                      to_date('&mynextdate03:00:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate03:00:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');

 commit;                      

 --統計資料庫資訊

 call sys.dbms_job.remove(102);

 call sys.dbms_job.isubmit( 102,

                      'sys.dbms_stats.gather_database_stats (options => ''GATHER STALE'');',

                      to_date('&mynextdate03:10:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate03:10:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');

 commit;                      

 --每週六下午6:30把執行對那些被更新特別頻繁的表的索引做rebuild

 call sys.dbms_job.remove(201);

 call sys.dbms_job.isubmit( 201,

                      'execute immediate ''alter INDEX history.PK_INCREMENT_STOCK_BALANCE_HIS rebuild nologging'';'||

                      'execute immediate ''alter INDEX history.ind_inc_stock_balance_1 rebuild nologging'';'||

                      'execute immediate ''alter INDEX history.ind_inc_stock_balance_2 rebuild nologging'';'||

                      'execute immediate ''alter INDEX history.PK_TB_INCREMENT_BALANCE_HIS rebuild nologging'';'||

                      'execute immediate ''alter INDEX history.Ind_inc_balance rebuild nologging'';',

                      to_date('&mynextdate18:30:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate18:30:00'',''YYYY-MM-DD HH24:MI:SS'') + 7');

 commit;  

 

1.4.刪除重複的索引

 

查詢重複的索引

select *

   from

( select   index_owner,

index_name,

table_owner,

table_name,

            max(decode(p, 1,    c,NULL)) ||

            max(decode(p, 2,', '||c,NULL)) ||

            max(decode(p, 3,', '||c,NULL)) ||

            max(decode(p, 4,', '||c,NULL)) ||

            max(decode(p, 5,', '||c,NULL)) ||

            max(decode(p, 6,', '||c,NULL)) ||

           max(decode(p, 7,', '||c,NULL)) ||

           max(decode(p, 8,', '||c,NULL)) ||

           max(decode(p, 9,', '||c,NULL)) ||

           max(decode(p,10,', '||c,NULL)) ||

           max(decode(p,11,', '||c,NULL)) ||

           max(decode(p,12,', '||c,NULL)) ||

           max(decode(p,13,', '||c,NULL)) ||

           max(decode(p,14,', '||c,NULL)) ||

           max(decode(p,15,', '||c,NULL)) ||

           max(decode(p,16,', '||c,NULL)) index_cols

      from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p

                 from dba_ind_columns )

     group by index_owner,index_name,table_owner,table_name ) A,

  ( select index_owner,

index_name,

table_owner,

table_name,

            max(decode(p, 1,    c,NULL)) ||

           max(decode(p, 2,', '||c,NULL)) ||

           max(decode(p, 3,', '||c,NULL)) ||

           max(decode(p, 4,', '||c,NULL)) ||

           max(decode(p, 5,', '||c,NULL)) ||

           max(decode(p, 6,', '||c,NULL)) ||

           max(decode(p, 7,', '||c,NULL)) ||

           max(decode(p, 8,', '||c,NULL)) ||

           max(decode(p, 9,', '||c,NULL)) ||

           max(decode(p,10,', '||c,NULL)) ||

           max(decode(p,11,', '||c,NULL)) ||

           max(decode(p,12,', '||c,NULL)) ||

           max(decode(p,13,', '||c,NULL)) ||

           max(decode(p,14,', '||c,NULL)) ||

           max(decode(p,15,', '||c,NULL)) ||

           max(decode(p,16,', '||c,NULL)) index_cols

      from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p

                 from dba_ind_columns )

     group by index_owner,index_name,table_owner,table_name ) B

  where a.table_owner=b.table_owner

and a.table_name=b.table_name

and (a.index_owner<>b.index_owner

or a.index_name <> b.index_name )

and a.index_cols like b.index_cols || '%'

and a.owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

and a.table_name not like '%$%'

出來的結果中，排在後面的索引是可以刪除的。

1.5.做為一項常規任務，每個月做一次停下應用的維護

檢查哪些表碎片比較多，對這些碎片很多的表做move同時將失效的索引全部重建。

這一項任務是在已經對錶都做了分析的情況下來做。

做scrīpt0，對錶全部做分析

begin

dbms_stats.gather_schema_stats (‘JZ21’);

end;

/

針對所有的schema都做一次。

--檢視哪些表的空間碎片比較大，目前是認為大於30%就做整理。

select * from (

Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

from dba_tables where owner in (‘JZ21’) 

and TEMPORARY=’N’

) tt

where tt.bb<0.7 and blocks>10;

 

--生成整理指令碼scrīpt1

 

select ‘alter table ‘||owner||’.’||table_name||’ move; ’ from (

Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

from dba_tables where owner in (‘JZ21’) 

and TEMPORARY=’N’

) tt

where tt.bb<0.7 and blocks>10;

 

--預備好分析指令碼，不過這個指令碼應該最後做scrīpt3

 

select 'exec dbms_stats.gather_table_stats( '''||owner||''','''||table_name||''' ,method_opt => ''for all columns'', cascade => true ); ' from (

Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

from dba_tables where owner in (‘JZ21’) 

and TEMPORARY='N'

) tt

where tt.bb<0.7 and blocks>10;

 

--生成重建索引指令碼scrīpt2

select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’) and status='UNUSABLE' ;

步驟：先執行scrīpt0

結束後，生成scrīpt1,scrīpt2,scrīpt3

按順序執行，scritp1,scrīpt2,scrīpt3。

1.6.特殊情況下對資料庫重新整理

1.6.1.   對錶做move

alter table test_move move ;

 select ‘alter table ‘||owner||’.’||table_name||’ move; ’ from dba_tables where owner in (‘JZ21’) and TEMPORARY=’N’;

這裡用需要做move的schema替換。

1.6.2.   對index做rebuild

alter INDEX idx_test_move rebuild nologging;

select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’);

1.6.3.   對所有的表做分析

begin

dbms_stats.gather_schema_stats (‘JZ21’);

end;

/

針對所有的schema都做一次。

1.7.對索引開啟監視開關，看哪些索引沒有被使用(慎用)

select ‘alter index ‘||owner||’.’||index_name||’ monitoring usage;’ from dba_indexes

where owner not in (‘SYS’,’SYSTEM’);

在執行了一個很完整的週期以後(對我們來說，例如2個完整的交易日)，檢查有哪些索引沒有被使用

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage where used='NO';

如果這些索引不是主鍵或唯一索引，一般情況下這些索引可以去掉。

檢查完一次以後，將索引監視關閉，不需要老是監視索引的使用：

select ‘alter index ‘||owner||’.’||index_name||’ nomonitoring usage;’ from dba_indexes where owner in (‘WHT’);

2.  語句調優

2.1.PL/SQL中的語法解釋工具

語句的執行效率是否符合需要，一個是簡單的把語句提取出來直接執行一下，看效果如何，但很多情況下，語句的執行是根據實際的條件執行的，所以有時候我們寫完語句有要先分析一下語句是否是最優的，這時候我們就可以用PL/SQL的explain視窗（或直接選種語句後，按F5），有時侯，我們已知語句比較慢，要尋找為什麼會慢，也可以如此做。

在EXPLAIN視窗，我們需要關注幾個重要的東西，COST（成本），如果這個COST數字很大，說明語句效率不高，需要想辦法；有無TABLE ACCESS FULL，如果有，只要資料量大一些，效率就不會高。我們要儘量避免產生table access full,方法之一就是建索引，另一個方法是改寫連線方式，使能夠用到索引。另一個需要關注的是join方式，nest loops outer和hash join outer，這兩種方式的效果是不一樣的，有時有天壤之別，目前我沒有類似的經驗可以寫，以後碰到後補充。

2.2.ORACLE提供的調優工具

ORACLE本身提供了很好的客戶端的效能除錯和跟蹤工具。ORACLE的工具非常好，可以幫助我們很容易的找到系統效能的瓶頸語句在哪裡。ORACLE效能調整工具的位置在：

開始—程式—Oracle – Orahome92 – Enterprise Management Packs –Diagnostics –Performance Manager，登陸後找到資料庫欄，然後連線就可以看到。