Hello Petr,
if I understand you correct, you execute the SQL (insert into T_TARGET select *
from T_SOURCE) only once, T_TARGET is a range partitioned table with 5 local
indexes (same partitioned key as the table) and the ASH output (SQL_EXEC_ID
16777216 - 16777219) is the one below, right?
This definitely looks like DML restart, which can have a lot of reasons, e.g.
*
https://oracle-randolf.blogspot.com/2016/01/dml-operations-on-partitioned-tables.html
You can trace DML restarts (for verification) as described here:
*
https://mahmoudhatem.wordpress.com/2018/10/05/write-consistency-and-dml-restart/
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: www.soocs.de
Twitter: @OracleSK
Petr Novak <dmarc-noreply@xxxxxxxxxxxxx> hat am 10.04.2024 18:34 CEST--
geschrieben:
XID SQL_EXEC_ID SQL_EXEC_START Co(Sid) Co(Ser) Di(In) Di(Sid) COUNT(*)
Min(Sample_Time) Max(Sample_Time)
---------------- -------------- ------------------- ------- ------- ------
------- -------------- -------------------- --------------------
16777216 05.04.2024 20:13:40 1141 11657 1 5 50 05.04 20:13:44 06.04 04:26:03
2E00180051D62200 16777216 05.04.2024 20:13:40 1141 11657 1 1 12532 05.04
20:14:25 06.04 14:04:03
20001C0008633B00 16777217 06.04.2024 14:04:10 1141 11657 1 1 24486 06.04
14:04:13 08.04 00:55:20
16777217 06.04.2024 14:04:10 1141 11657 1 3 30 06.04 14:08:29 07.04 01:10:29
15001100CD95C100 16777218 08.04.2024 00:55:22 1141 11657 1 1 8986 08.04
00:55:31 08.04 13:42:29
16777218 08.04.2024 00:55:22 1141 11657 1 4 28 08.04 00:59:06 08.04 03:59:52
03001200F4F1B600 16777219 08.04.2024 13:42:30 1141 11657 1 1 4178 08.04
13:42:39 08.04 19:39:05
16777219 08.04.2024 13:42:30 1141 11657 1 4 24 08.04 13:48:48 08.04 18:20:44
So there were different SQL_EXEC_IDs.Most samples have XID filled and only 1
Session.
There 24-50 samples for every SQL_EXEC_ID without XID, 3-5 different
Sessions, about 0.26 percent of all samples.
For me it looks like serial execution.
I dont understand different SQL_EXEC_IDs - should this mean, there was some
app error and statement was restarted ?
But there were no errors in alert.log , no snapshot too old error in
dba_hist_undostat
Active Extents in dba_hist_undostat start
on 5th April 8PM from 4GB, reach 87 GB on 6th April 5 AM,
remain constant till 6th April 2 PM (9 hours) , then abruptly go back to 9
GB, reach 98GB on 6th April 10 PM,
remain constant till 8th April 3 AM (29 hours), then abruptly to 77 GB, reach
89GB on 8th April 4AM,
remain constant till 8th April 1 PM (9 hours), then abruptly to 1G, reach 111
GB on 8th April at 8 PM, then goes to 0.
Dissmiss of active extents is related to start of new XID.
Could these long hours with constant number of active extents be related to
work on compression ?
Best Regards,
Petr
