Customizable features to achieve extremely flexible and lightweight in Orabbix, the source was able to feel a refreshing wind.
I added on the basis of the original template orabbix on several monitoring items, a flashback is to monitor the utilization zone, there is a switching frequency monitoring archiving, these two functions may seem trivial, but the reaction will be in the details the database for any obvious abnormal behavior.
Noon noticed flashback area utilization and a library archive frequency is relatively high. There are some unusual, but from my side of the information point of view, there is no feedback from developers say what needs to be done to change the data.
There are some unusual situations. This library is a statistical library, case for concurrent views should not be high, but now from a point in time in the morning to start archiving a sharp increase, almost touching the alarm line.
At this time also with questions about the library to check the results to see the view through the ash, a large number of concurrent operation is in progress, the results do not have any active session, and then check the database load, and high.
He grabbed a awr report view. I was surprised to find top1's turned out to be a sql update statement.
Elapsed Elapsed Time
Time (s) Executions per Exec (s)% Total% CPU% IO SQL Id
---------------- -------------- ------------- -------- ----- ------ -------------
3,604.9 0 N / A 26.9 32.5 68.2 3jggcpxmv6w8g
Module: firstname.lastname@example.org (TNS V1-V3)
update "xxxx". "TEST_BILLING" set "ID" = 'xxxxxxxxxx'
This statement can clearly see there are some problems, because this is a large table, the amount of data in one hundred million, for such a dml operation, the price is still quite large.
To find out who in the end we look at the implementation of such a dml.
Results of a look or something of a surprise, turned out to be in the local sys operation, but also points to the problem yourself, because this library developers do not have any permission to access directly.
USERNAME SID SERIAL # PROGRAM MACHINE
--------------- ---------- ------------------------- -------------------- -----------------------
SYS 22 50043 sqlplus @ stat.xxxxxxxxxx, com (TNS V1-V3) xxxxxxxx.cyou.com
SYS 3560 63187 sqlplus @ stat.xxxxxxxxxx, com (TNS V1-V3) xxxxxxxx.cyou.com
But I really did not do any action, and will not say which script very magical performed?
With questions and colleagues conducted the investigation, and finally found that this statement is doing dml log miner resolve when something went wrong.
Because some data to consider synchronization, need to synchronize some data from another core library into the statistical database, but do not want to make any additional configuration directly in the main library, this time using the log miner to customize extracting archive sql statement, and then deployed in this statistical library. This process is triggered by crontab.
But in the log miner parsing process is a little problem resolved, there is an update statement does not result where the words applied directly to the statistical database, the results generated a lot of redo, archive switching is also very frequent.
For example, parsing log miner's view, the default is to use the following method.
SELECT sql_redo FROM v $ logmnr_contents WHERE seg_owner = 'XXXX' AND TABLE_NAME IN ( 'XXXX', 'XXXXXX') AND OPERATION in ( 'INSERT', 'UPDATE', 'DELETE')
The results of a statement in the resolution process of a problem, resulting in the kind of non-standard update statement, when applied to the statistical database of the impact magnified.
Can be filtered through pay half, it can also be filtered directly in the sql. For example, modify the following form.
SELECT sql_redo FROM v $ logmnr_contents WHERE seg_owner = 'XXXXX' AND TABLE_NAME IN ( 'XXXX', 'XXXX') AND OPERATION in ( 'INSERT') or (operation in ( 'DELETE', 'UPDATE') and upper (sql_redo) like '% WHERE%')
For this update, and delete operations are necessary filter hi, those conditions do not specify delete, update operations can be shielded.
Of course, an emergency repair on this issue is very clear, is to kill the long run the session.
Kill session after the effect of the following, you can see the utilization of a sudden flashback area drop down. Archiving frequency also drop down.
This problem can be seen through, customize their own monitoring items can still play a good role in monitoring to some extent. For some unusual circumstances or do not take it lightly.