Well looks obvious (I think), you run backups during the slow period.
În vin., 2 apr. 2021 la 09:28, Pap <oracle.developer35@xxxxxxxxx> 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@xxxxxxxxx> wrote:
Indeed looks like it is all related with storage response time.
Just to have some fun you can post the ash numbers:
sql> @dashtop program2,event2 1=1 "TIMESTAMP'2021-03-30 22:16:57'"
"TIMESTAMP'2021-03-30 22:30:07'"
sql> @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@xxxxxxxxx> 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
