RE: Slow running Delete query for same data volume

9 different databases. ok. Please also dole out to us how many different containers you have and how many PDBs share the container you are using as well as whether you can access the statistics for each PDB and Container on your system. Finally, are there other applications or maintenance operations (OS backups for example) that are going on differently between “fast” and “slow” periods.  

Is any sort of disk farm rebalancing or failed disk replacement operation in play?  

Shared resources requires shared resource scheduling if the aggregate load possible exceeds the capacity of anything in any directions if you want predictable response time.  

Even just a shared disk or other media farm amongst hosts can require that.  

As for backups, whether or to that affects your i/o depends on whether or not you co-habit paths from memory to persistent memory, including all levels of cache.  

Oh, and if someone is running performance tests on your media farm that is a classic oops when if they don’t realize they share i/o capacity with you just because they might be on different logical volumes that happen to be on the same physical media as you. Likewise network bandwidth between host and media and virtual LANs that don’t quite do QOS correctly.  

Good luck.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Friday, April 02, 2021 6:47 AM
To: Laurentiu Oprea
Cc: Oracle L
Subject: Re: Slow running Delete query for same data volume  

Thank you. So the program (TNS vn-vn) is the one for backup. But, I am not able to see any DB incremental backup run from v$rman_backup_job_detail in this database during that time. And we are using ZDLRA , so all are incremental backup only. I see there was an archivelog backup running between ~10:20PM till 10:30PM. Can that impact to such extent?  

 This exadata full rack box hosting ~9 different databases though, so wondering if something bad running in another database can be the cause. How to debug that?  

Regards

Pap  

On Fri, Apr 2, 2021 at 12:09 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

Well looks obvious (I think), you run backups during the slow period.  

În vin., 2 apr. 2021 la 09:28, Pap <oracle.developer35_at_gmail.com> a scris:

Thank you.  

Attached is the DASH output for both the period.I do see more activity during slow periods. But is this confirming that , because of some other factor impacting overall database read activity during slow periods, how to get that?  

Actually in the sql monitor(which i posted in the initial thread) it shows the fast execution period was able to perform ~67GB bytes read in ~10minutes whereas during slow execution period, the same ~67GB took ~30minutes. So wanted to understand how can i find the root cause of the slowness here?  

Regards

Pap  

On Thu, Apr 1, 2021 at 11:26 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

Indeed looks like it is all related with storage response time.  

Just to have some fun you can post the ash numbers:  

sql> _at_dashtop program2,event2 1=1 "TIMESTAMP'2021-03-30 22:16:57'" "TIMESTAMP'2021-03-30 22:30:07'"

sql> _at_dashtop program2,event2 1=1 "TIMESTAMP'2021-03-31 22:16:57'" "TIMESTAMP'2021-03-31 22:30:07'"  

https://github.com/tanelpoder/tpt-oracle/blob/master/ash/dashtop.sql    

În joi, 1 apr. 2021 la 13:58, Pap <oracle.developer35_at_gmail.com> a scris:

Hello all, We are using version 12.1.0.2.0 of Oracle exadata X3 machine. Seeing variable performance for a DELETE query even though the plan is the same. Though the number of rows are closely the same, we are seeing the runtime is increasing from ~15minutes to ~30minutes. As its version 12C, so I am able to fetch the sql monitor from these past two executions as attached here. But i am not seeing any information in the activity detail section of the report.  

However I fetched the activity data from dba_hist_active_sess_history and it says the "cell multiblock physical read" is quite high for the slow execution. And also the objectid is pointing to the table partition which means it was also not on UNDO. So does it mean that we must be suffering from overall system slowness which impacted this query because of slow overall response time for "cell multiblock physical read" in database level?  

DELETE /*+ full(TAB1) */ FROM TAB1 TAB1 WHERE ETC = 'XX' AND ID = 3 AND OTCD = 'YY' AND PART_DT = :B1

Select event, current_obj#, count(*) from dba_hist_active_sess_history group by event, current_obj# order by count(*) desc;

For faster run :-

EVENT CURRENT_OBJ# COUNT(*) cell multiblock physical read TAB1 73

    TAB1 5 gcs drm freeze in enter server mode TAB1 1  

For slower run:-

EVENT CURRENT_OBJ# COUNT(*) cell multiblock physical read TAB1 170

    TAB1 14

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