Oracle trigger問題

chenoracle發表於2018-05-08
Oracle


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語句消耗的資源很少；

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2154156/，如需轉載，請註明出處，否則將追究法律責任。

相關文章