Re: Slow running Delete query for same data volume

The _OTHER_DATABASES_ category is "other I/O" reasons not *directly* tied to any single database instance. For example, asynchronously destaging blocks from flash write-cache to spinning disks is not specific to a single DB and shows up as _OTHER_DATABASES_. Possibly things like ASM disk resilvering (and rebalance) go under this category too, I don't remember off the top of my head. You could dive into cell level metrics for more detail.

Note that this particular chart shows you IORM *wait* times, not necessarily who generated the most I/O (and caused other lower priority tasks to wait more). So you'd need to dive into other metrics to see whether there was any spike in IO rates too.

And you should do a "hardware healthcheck" too, just in case some of the spikes are caused by a flash card reporting errors or a disk controller battery (that protects the write cache in front of spinning HDDs) going bad.

Some of these checks can be done via querying various V$CELL* views:

https://tanelpoder.com/2013/05/24/list-exadata-storage-cell-disk-summary-with-cellpd-sql-and-cellpdx-sql-scripts/
https://tanelpoder.com/2013/03/21/listing-exadata-storage-cells-and-their-configuration-info-from-vcell_config/
https://tanelpoder.com/2013/05/23/vcell_thread_history-ash-for-exadata-storage-cells/

--
Tanel Poder
#vConf2021: Troubleshooting Very Complex Oracle Performance Problems
https://tanelpoder.com/conference/

On Wed, Apr 7, 2021 at 2:53 AM Pap <oracle.developer35_at_gmail.com> wrote:



>

> 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