Re: Slow running Delete query for same data volume

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