Re: Slow running Delete query for same data volume

The spike in disk IO i was seeing in OEM is as below. It was mainly from "_OTHER_DATABASES_" . And i am struggling to understand what is that as i can't find that database in OEM when searching with target database name. So unable to understand how I can i dig into this.

[image: image.png]

On Tue, Apr 6, 2021 at 6:05 PM Pap <oracle.developer35_at_gmail.com> wrote:

> I think I got fooled might be, the way it's written there in the script is
> making all programs like say e.g. from perl_at_machine1(TNS V1-V3) to
> TNS(VN-VN). So it seems most of the occurrences were from application and
> few were from the Rman backup(which was basically archive log backup that
> lasted for only 10-15minutes).
>
> But again as i see in the oem exadata performance grid , out of ~9
> databases hosted in same box none of them were showing much activity during
> that period , however the hard disk utilization went from <20% to ~80%
> mostly by the activity logged against database "_OTHER_DATABASE_" and few
> were from "ASM". These two are showing up in oem disk IO performance tab
> apart from those ~9 application databases which are hosted in that exadata
> box. I am not sure if oracle is just representing or adding these two
> additional names in the disk IO performance grid but these are not really
> databases but are responsible for maintaining those ~9 databases. So
> wondering , how can i dig into further to see what exactly this
> "_OTHER_DATABASE_" is ? and what activity was happening in this which endup
> consuming ~80% disk IO and thus impacting application.
>
> CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\(
> [PJ]\d+\)') THEN
> REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
> ELSE
> '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)_at_(.*)(\(.*\))', '\1'),
> '\d', 'n')||')'
> END || ' ' program2
>
> On Tue, Apr 6, 2021 at 12:07 AM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank you. It seems like some program which moves backups to tape but
>> still putting load on storage cell IO performance. I will check the timing
>> with backup admin if that day it ran in some odd time causing such Io
>> slowness.
>>
>>
>> On Sun, Apr 4, 2021 at 11:49 PM Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> To tell you the truth, I don't know much about Veeam. I used to work for
>>> Commvault and Veeam was a competitor. It looks strange. Usually,
>>> database agent maps libobk.so provided by the 3rd party tool like
>>> Commvault into rman address space and what you see is an rman
>>> executable. What I see here are 2 processes, none of which is rman.
>>> Judging by the diagram:
>>>
>>>
>>> https://helpcenter.veeam.com/docs/backup/plugins/hiw_rman_plugin.html?ver=110
>>>
>>> The "vn-vn" program must be "data mover". You will have to ask your
>>> backup admin about the schedule. However "Backup MML commit" is a dead
>>> giveaway that this is some kind of backup.
>>>
>>> On 4/3/21 2:12 AM, Pap wrote:
>>> > Thank you Mladen. I have not heard of Veam backup in the past. Is
>>> > this file system backup but not DB backup? And how can I see the
>>> > schedule of this backup. Is there any View just like
>>> > v$rman_backup_job_detail which i can query to see the file
>>> > system backup schedule and can then confirm if that day(31st march)
>>> > the schedule changed causing the slow IO and thus application job were
>>> > impacted just that specific day?
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>> https://dbwhisperer.wordpress.com
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png