Oracle trigger failed

Source: Internet
Author: User

Oracle trigger failed

Routine checks on the AWR Report of the database. An update statement is executed multiple times for over 30 seconds each time. This SQL statement is very simple, that is, modifying data based on primary key conditions, the number of primary keys ranges from 1 to 100. This problem has been around for a long time and only occasionally occurs. The primary key is varchar2, similar to the sequence. Because data has been migrated before, the primary key is marked for the migrated data, and the distribution of the primary key is uneven.

The first diagnosis: This table has 1.5 million of the data, and the execution is slow because the primary key index is not reached during the update, so I checked the distribution of the histogram. There are only two buckets, as a result, the histogram information of the primary key is collected again, with 250 buckets. Prepare to observe the AWR for one day and watch the AWR for the next day.

The second diagnosis: I heard the developer say that there is a trigger on this table. The test showed that it was a trigger problem. All the resources consumed by the trigger are recorded in the update statement, which is puzzling. The modification method is to implement the trigger service through SQL, and the entire function is much faster. Abstract and experiment the following problems:

1. initialize data and create a trigger

Drop table test1 purge;

Drop table test2 purge;
Create table test1 as select * from dba_objects;
Insert into test1 select * from dba_objects;
Commit;
Create table test2 as select * from dba_objects;
Create index ind_t1_object_id on test1 (object_id) nologging;
Create index ind_t2_object_id on test2 (object_id) nologging;
Exec dbms_stats.gather_table_stats (user, 'test1', cascade => true );

Exec dbms_stats.gather_table_stats (user, 'test2', cascade => true );

Create or replace trigger t_trigger
BEFORE update ON test1
FOR EACH ROW
BEGIN
Update test2 t
Set t. object_name =: old. object_name
Where t. object_id =: old. object_id;
END;

SQL> set autotrace traceonly
SQL> set timing on

2. Executing the update statement triggers the trigger.
SQL> update test1 set object_name = ''| object_name;
140300 rows have been updated.
Used time: 00: 00: 15.21
Execution Plan
----------------------------------------------------------
Plan hash value: 160929213
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | update statement | 140K | 4110K | 384 (1) | 00:00:06 |
| 1 | UPDATE | TEST1 |
| 2 | table access full | TEST1 | 140K | 4110K | 384 (1) | 00:00:06 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
140739 recursive cballs
427013 db block gets
282079 consistent gets

0 physical reads
120365752 redo size
718 bytes sent via SQL * Net to client
498 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
140300 rows processed
SQL> commit;
Submitted.


3. disable trigger
SQL> alter trigger t_trigger disable;

4. Executing the update statement will not trigger
SQL> update test1 set object_name = ''| object_name;
140300 rows have been updated.
Used time: 00: 00: 01.67
Execution Plan
----------------------------------------------------------
Plan hash value: 160929213
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | update statement | 140K | 3425K | 384 (1) | 00:00:06 |
| 1 | UPDATE | TEST1 |
| 2 | table access full | TEST1 | 140K | 3425K | 384 (1) | 00:00:06 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
389 recursive cballs
144840 db block gets
2216 consistent gets

0 physical reads
50003740 redo size
721 bytes sent via SQL * Net to client
498 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
140300 rows processed

Conclusion: The two experiments show that the resource consumption difference is very large, and the resources consumed by the trigger are calculated as update. A trigger is triggered on each line. To be efficient, you must handle the trigger in batches. If the developer did not tell me how to solve this problem, it would be hard to find the trigger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.