oracle優化之生產系統不改程式碼解決SQL效能問題的幾種方法

 資料庫效能很大一部分跟SQL寫法有關, 有些SQL是必須改程式碼才能優化的, 如果不能改程式碼, 需要刪歷史記錄,回收空間,讓表始終保持相對較小,才能消耗較少的資源,得到能夠接受的響應時間.如前公眾號文章所述:  必須通過改寫SQL才能提升效能的一些情況  ( 列舉了10個需要改寫的SQL).

    出於各種原因, 需要在不改SQL程式碼的情況下對資料庫進行優化,下面就列舉了一些情況,可以 在不改程式程式碼的情況下, 改善SQL的執行效率:   

    

1-增加索引(沒有風險)

      普通索引:

        這個不多解釋, 大部分sql效能提升立竿見影, 幾百倍,上千倍甚至上萬倍的提升都是正常的.

      函式索引

          解決隱式型別轉換:  

    欄位型別是varchar2型別, 變數型別是number型別, 比如 phone_nbr=13812345678 , phone_nbr(varchar2型別)欄位上的索引是用不上的(不要抬槓說index fast full scan還有可能), 這就需要再建立一個  to_number(phone_nbr)的函式索引來提高效率;

       類似的情況還有 varchar2型別(和char型別)的欄位, 變數型別是nvarchar2(和nchar),這種情況需要建立 to_nchar函式索引;

      注意: date型別的欄位, 遇上timestamp的變數, 不能通過建立to_timestamp函式索引來解決,需要使用方法3. 

         解決欄位上使用函式,做運算: 

      to_char(cdate,'yyyymmdd')=:b1 

     可以建立to_char(cdate,'yyyymmdd')函式索引

          xxx is null的寫法,可以讓xxx與常量0組成聯合索引

    

2-調整執行計劃(沒有風險)

    sql執行計劃選擇錯誤,不需要在程式程式碼的sql中增加hint, 強大的oracle有辦法在後臺控制SQL的執行計劃;

    情況1:執行計劃有好有差, 直接使用sql profile或sql plan baseline固定好的執行計劃;

    情況2:sql沒有好的執行計劃,需要手工加hint生成好的執行計劃,然後再用sql profile或sql plan baseline固定;

    上面兩種情況都可以使用 coe_load_sql_profile.sql指令碼完成,都是簡單輸入幾個引數即可. 

    對於情況2,很多書上介紹使用 coe_xfr_sql_profile.sql, 這種方法比較麻煩,還容易出錯,建議大家拋棄這個方法,改用簡單的coe_load_sql_profile.sql

    

    補充一個hint使用特例, /*+   bind_aware  */這個hint, 需要使用 sql patch進行追加. 

    寫這篇公眾號文章的起因就是源於前同事的一個問題: 

    一個系統工具(logminer)使用的sql, 使用了並行度為208的 parallel_index , 這麼高的並行度是不可接受的,想取消sql的並行. 因為無法修改SQL,嘗試使用no_parallel_index(t) 不生效,最終通過 sql patch應用了一個full(T@SEL$1)的hint,避免了使用index fast full scan,也就沒有了並行:

3-改欄位型別 (有一定風險,建議做好充分測試)

    主要針對date型別欄位, 遇到timestamp型別的變數,做隱式型別轉換無法使用索引的情況,如:

    select count(*) from tt where created>=:b1 and created<=:b2;

    如果created欄位是date型別,b1和b2是timestamp型別, 會發生隱式型別轉換,無法使用created欄位上的索引; 而且這種情況無法建立to_timestamp函式索引來補救;

    如果不改程式碼解決,只能修改欄位型別date為timestamp(0):

    alter table tt modify  created   timestamp(0); 

    這個語句只修改資料字典, 不需要修改每行記錄(如果是反過來timestamp(0)改成date型別,就要逐行修改每條記錄,大表時間會比較長)

    timestamp(0) 與 date 型別的主要區別在於 兩個timestamp型別相減得到的是 interval型別; 而兩個date型別相減, 得到的是 number型別, 如果沒有這種謂詞條件或返回列, 可以嘗試這個方法.

4-改表結構(沒有風險)

    改成分割槽表

    適用: 統計分析類SQL,如果是對一個月的資料做統計分析, 表中有5年的資料量, 分割槽後, 資料訪問量為原來的1/60

5-改引數(有一定風險,建議做好充分測試)

    OLTP高併發環境沒有使用繫結變數, 大量硬解析, 修改程式碼的工作量是巨大的, 簡單方法就是修改資料庫初始化引數  cursor_sharing=force (預設值為exact), 有一定的風險,做好測試.(有一些bug需要注意,公眾號文章:  11.2.0.3版本升級到18c之前的各版本，可能遇到嚴重效能問題  )

6-特殊案例

  原SQL:

    select PM_JOB_SEQUENCE.nextval job_id

    from (select 1 from   all_objects  where rownum <= 13);

  借用資料字典檢視all_objects生成一段sequence 序列, 頻繁的執行,消耗系統大量的CPU,平均每次執行平均buffer gets 169~1362(6個執行計劃):

如果能改程式碼,可以改成:

    select PM_JOB_SEQUENCE.nextval job_id   from dual connect by level<=13;

      這種寫法不消耗buffer gets;

如果不能改程式碼, 可以建立一個同名表:

create table  all_objects  (id number not null);

insert into  all_objects  select 0 from dual connect by level<=100;

commit;

create index idx_all_objects on all_objects(id);

--因為同義詞synonym和表同時存在時,優化訪問表, 這時只需要2個buffer gets:

select PM_JOB_SEQUENCE.nextval job_id

from (select 1 from   all_objects  where rownum <= 13);

如果要訪問真正的系統檢視all_objects,則需要加上sys的schema:  sys .all_objects

7- DBMS_ADVANCED_REWRITE  (方法6,如果只是換表,也可以用這種方法,但是不支援sequence)

  這個方法在10g版本就有了,用一段SQL程式碼,替換另一段SQL程式碼(不支援帶繫結變數的SQL),功能很強大,大家有興趣可以在網上搜尋更多的案例.

    對應的資料字典:  DBA_REWRITE_EQUIVALENCES

    下面是一個簡單例子:

引數說明:

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE  (

   name                 VARCHAR2,   ---名字

   source_stmt          CLOB,       ---原SQL程式碼

   destination_stmt     CLOB,       ---替換SQL程式碼

   validate             BOOLEAN   := TRUE,  --預設值true, 會比較兩個SQL結果集, 如果不相同, 不會建立成功

   rewrite_mode         VARCHAR2  := 'TEXT_MATCH' --預設值,簡單轉換; 還有高階的general, 更高階的recursive (disabled: 禁用)

);

如果要刪除: 

exec sys.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ('&equ_name');

--例子開始:

--建立REWRITE_EQUIVALENCE

begin

   sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

   'tiger_test_equivalence',

   'select ename from emp',

   'select dname from dept'   

   ,validate =>   false

   );

end;

/

--修改引數才能使用:query_rewrite_integrity 預設值: enforced

SQL> alter session set   query_rewrite_integrity = trusted;

--下面SQL,實際上執行的是select dname from dept:

SQL>select ename from emp;

ENAME

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

ACCOUNTING

RESEARCH

SALES

OPERATIONS

--把引數query_rewrite_integrity改回預設值

alter session set   query_rewrite_integrity = enforced;

--再次執行,返回正常結果:

SQL> select ename from emp;

ENAME

----------

SMITH

ALLEN

......

JAMES

FORD

MILLER

實戰案例模擬:

--原SQL,無法使用status欄位上的索引(條件寫到了having 部分):

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

--等價改寫SQL,可以使用status欄位上的索引(條件在where部分):

select m.object_type, count(1) unread_count

from t1 m   where m.status = 'INVALID' and m.owner = 'SYS'

group by m.object_type, m.status, m.owner;

想用等價改寫的SQL,替換原SQL,下面是簡單的幾個步驟:

setup case:    

    create table t1 as select * from dba_objects;

    create index idx_t1_status on t1(status);

--建立REWRITE_EQUIVALENCE

begin

   sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

      'test_rewrite2'

    , q'[select m.object_type, count(1) unread_count

      from t1 m

      group by m.object_type, m.status, m.owner

      having m.status = 'INVALID' and m.owner = 'SYS']'

    , q'[select m.object_type, count(1) unread_count

       from t1 m where m.status = 'INVALID' and m.owner = 'SYS'

       group by m.object_type, m.status, m.owner]'

    ,   validate => true

    , rewrite_mode => 'general'

   );

end;

/

--執行原SQL, 執行計劃還是使用全表掃描(query_rewrite_integrity 引數沒有設定)

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

--設定引數, 再次執行原SQL:

alter session set   query_rewrite_integrity = trusted;

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

(相當於執行了改寫後的SQL,實現了優化目的)

8- 非常規方法, 慎用! 

改二進位制程式碼裡面的sql (jar,exe等檔案,sql程式碼一般也是字串儲存) ;大部分情況,這種方法應該也沒問題.但是,不到萬不得已, 不要用這一招.做好測試.

    

    舉個簡單例子: 

        sql程式碼有各種日期型別的格式轉換,下面情況需要建立3個不同的函式索引:

        to_char(created,'yyyy-mm-dd') to_char(created,'yyyy/mm/dd') to_char(created,'yyyymmdd')

        這種情況,可以在二進位制程式碼中,找到這些字串,統一改成 to_char(created,'yyyymmdd')  , 這樣只需要建立一個函式索引即可.

        前面兩個寫法, 改完後字串長度變短, 可以在後面補兩個空格.