Oracle體繫結構知識點的運用

標籤：

體繫結構方面的最佳化問題：

1. 設資料庫很大，訪問量非常高，共用池很小：這樣共用池裡面就無法儲存很多解析過得sql語句，導致很多硬解析，這樣資料庫就非常緩慢。這個時候要加大共用池。如果是自動管理，就加大SGA的大小。
2. 設：某平時不經常訪問的資料庫的主機才4G記憶體，去開闢了3G的SGA，500m的PGA，由於OS作業系統記憶體不足，導致主機運行緩慢，我們要減少SGA大小。
3. 如果由於資料緩衝區過小而產生的大量物理讀，則要增大SGA。
4. 如果排序使用了暫存資料表空間，就說明PGA過小，如果系統有額外的大量記憶體，可以考慮多分配一部分給PGA（一般是OLAP系統）
5. 如果資料庫有大量的更新操作，產生大量的日誌導致日誌切換頻繁，在日誌切換過程中，資料庫會停滯運行，為了提高效能，需要加大記錄檔的大小。
6. 如果某個應用因為老是查出ORA-01555錯誤而無法把值給下一個模組使用，導致生產出現故障。需要檢查為什麼這個sql執行這麼慢。最佳化的方法：加索引，清理曆史資料，讓表的記錄小一點，或者增大undo_retention的值（這個值只是建議值，非強制），也可以增大undo資料表空間。

具體的sql最佳化：

1. 構造環境 + 未最佳化（單車速度：40+秒

   sqlplus drop table t purge;create table t (x int);alter system flush shared_pool;set timing on

   create or replace procedure proc1as begin    for i in 1..100000    loop        execute immediate        ‘insert into t values(‘||i||’)’;    commit;    end loop;end;/

   exec proc1;

   col sql_text format a30

   set pagesize 1000

   select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like ‘%insert into t values%‘ and rownum<100;

##resetpool.sql:
drop table t purge;create table t(x int);alter system flush shared_pool;set pagesize 1000col sql_text format a30

2. 綁定變數，摩托速度:8+秒

   create or replace procedure proc2asbegin        for i in 1..100000        loop                execute immediate                ‘insert into t values( :x )‘ using i;                commit;        end loop;end;/

   select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like ‘%insert into t values%‘;

3. 靜態改寫，汽車速度：6+秒

   create or replace procedure proc3asbegin        for i in 1..100000        loop                insert into t values( i );                commit;        end loop;end;/

4. 批量提交，動車速度:2秒

   create or replace procedure proc4asbegin        for i in 1..100000        loop                insert into t values( i );        end loop;commit;end;/

5. 集合寫法，飛機速度：.14秒

   insert into t select rownum from dual connect by level <=100000;

6. 直接路徑，火箭速度：.89秒，在10萬的時候是.23所以需要量才能看得出來快了。

   create table t as select rownum x from dual connect by level <= 1000000;

7. 並行設定，飛船速度：需要有多cpu：本機實測1.05秒比6效能差，一般在機器空閑而且效能強大時用。

   create table t nologging parallel 4 as select rownum x from dual connect by level <=1000000;

Oracle體繫結構知識點的運用