Re: Slow running Delete query for same data volume

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