Re: Slow running Delete query for same data volume

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