Oracle 性能優化-trigger問題

Oracle 性能優化-trigger問題

問題現象 :

謂詞通過唯一性索引，更新一條記錄，耗時很長；

通過 AWR 查看 TOP SQL ，這個UPDATE  SQL 語句邏輯讀，物理讀等都 非常高；

初步懷疑執行計劃出現變化， index unique scan 變成 table access full ，但是通過 DBA_HIST_SQL_PLAN 發現近期執行計劃并沒有改變；

通過 10046 查看 SQL 執行計劃，顯示這個更新語句耗時，邏輯讀，物理讀等都 非常低；

問題原因 :

觸發器導致的；

更新語句 A 執行前，觸發了 trigger B ，其中 trigger B 內部執行較慢，導致 A 一直在等待；

由于 trigger B 執行過程中占用大量的資源，在 AWR 中會將 trigger 消耗的資源也加到 update 語句上，有時可能會干擾問題診斷；

問題結論：

當發現某個語句執行突然變慢了，執行時間，邏輯讀，物理讀等突然飆升，

在排查數據量，執行計劃等都沒有異常，可以查看是否存在不合理的觸發器；

問題重現舉例 :

---1 創建測試用戶

SQL> create user c##chenjch identified by a;

SQL> grant connect,resource,dba to c##chenjch;

---2 創建測試表，索引并插入數據

SQL> create table test01(id number);

SQL> create table test02 as select * from dba_objects;

SQL>

begin

  for i in 1 .. 100000 loop

    insert into test01 values (i);

    commit;

  end loop;

end;

SQL> create unique index ui_test_id on test01(id); 

SQL> insert into test02 select * from test02; 

SQL> commit; 

/

/

......

select count(*) from test02;   ---2329536

---3 創建觸發器

SQL>

create or replace trigger TG_TEST01_UPDATE

  BEFORE UPDATE ON TEST01

  for each row

begin

  insert into test02

    SELECT * FROM TEST02;

end;

---4 生成快照

SQL>

begin

  dbms_workload_repository.create_snapshot;

end;

SQL>

select SNAP_ID, BEGIN_INTERVAL_TIME, FLUSH_ELAPSED, SNAP_LEVEL

  from dba_hist_snapshot

 order by snap_id desc;

---5  update test01 通過唯一性索引，更新一條數據

SQL> set timing on

SQL> set autotrace on

SQL> alter session set tracefile_identifier='10046';

Session altered.

Elapsed: 00:00:00.00

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.03

---耗時19秒

SQL> update test01 set id=1000000000 where id=1;

1 row updated.

Elapsed: 00:00:19.49

Execution Plan

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

Plan hash value: xxxxx

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

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time

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

|   0 | UPDATE STATEMENT   |            |     1 |    13 |     1   (0)| 00:00:01

|   1 |  UPDATE            | TEST01     |       |       |            |

|*  2 |   INDEX UNIQUE SCAN| UI_TEST_ID |     1 |    13 |     1   (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=1)

Statistics

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

        637  recursive calls

     351598  db block gets

     103565  consistent gets

      38393  physical reads

  374300700  redo size

        858  bytes sent via SQL*Net to client

        962  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

        102  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_56542_10046.trc

Elapsed: 00:00:00.06

SQL> @?/rdbms/admin/awrrpt.sql

AWR顯示update語句消耗資源很高

select * from table(dbms_xplan.display_cursor('afqfknn3nwwpw'));

10046 顯示update語句消耗的資源很少；

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！