Re: Slow running Delete query for same data volume

Have a look at the DELTA_READ_IO_BYTES column (and related columns) in ASH to determine what is using your IO bandwidth (there might be more relevant columns for exadata). Those direct path read temp waits could easily be hammering you here but we should get some numbers first. It’s worth graphing out total IO per second and average response times for your important data intensive queries to see at what level it starts to drop off a cliff.

I don’t think the difference is related to foreign key validation - that would give you extra IO which you would see on the monitor reports.

Hope that helps,
Andrew

On Fri, 2 Apr 2021 at 13:23, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> Hi Pap,
>
> If you have a FK on the deleted table, check if the column of the FK in
> the source table is indexed.
>
> Regards
> Eriovaldo
>
>
> Em qui., 1 de abr. de 2021 às 07:58, Pap <oracle.developer35_at_gmail.com>
> escreveu:
>
>> 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