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