Shulou(Shulou.com)06/01 Report--

Oracle performance Optimization-trigger problem

Problem phenomenon:

It takes a long time for predicates to update a record through the uniqueness index.

Check TOP SQL through AWR, this UPDATE SQL statement is very high in logical reading, physical reading and so on.

It was initially suspected that the implementation plan had changed, and index unique scan became table access full, but it was found through DBA_HIST_SQL_PLAN that the implementation plan had not changed in the near future.

Viewing the SQL execution plan through 10046 shows that the time consuming of this update statement, logical read and physical read are all very low.

The cause of the problem:

Caused by the trigger

Before the update statement An is executed, trigger B is triggered, in which trigger B executes slowly, causing A to wait all the time.

Because the execution of trigger B takes up a lot of resources, the resources consumed by trigger will also be added to the update statement in AWR, which may sometimes interfere with problem diagnosis.

Conclusion of the question:

When it is found that the execution of a statement suddenly slows down, the execution time, logical read, physical read, etc., suddenly soar.

There is no exception in troubleshooting the amount of data, execution plan, etc. You can check whether there are unreasonable triggers.

Examples of problem recurrence:

-1 create a test user

SQL > create user c##chenjch identified by a

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

-2 create a test table, index and insert data

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 create a trigger

SQL >

Create or replace trigger TG_TEST01_UPDATE

BEFORE UPDATE ON TEST01

For each row

Begin

Insert into test02

SELECT * FROM TEST02

End

-4 generate a snapshot

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 updates a piece of data through the uniqueness index

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

-it takes 19 seconds

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 shows that update statements consume a lot of resources.

Select * from table (dbms_xplan.display_cursor ('afqfknn3nwwpw'))

10046 shows that update statements consume very few resources

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.