Re: Increased runtime and 4 xids for one insert

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: _at_OracleSK

> Petr Novak <dmarc-noreply_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l