topic Re: GDB_TABLES_LAST_MODIFIED in Data Management Questions

GDB_TABLES_LAST_MODIFIED

SherrieKubis — Tue, 23 Aug 2016 15:26:11 GMT

Oracle RDBMS 12.1.0.2

SDE 10.2.2

We have many nightly update jobs that use arcpy, others use FME. Sporadically, some of the arcpy jobs fail. Since they run in batch mode, the developer is having trouble seeing what the returned error is. Sometimes the jobs all work without issue, other times maybe 5 to 10 of them fail.

To determine the failures I created a login trigger for the data owner between the hours of the nightly updates. I found

enq: TX - row lock contention on GDB_TABLES_LAST_MODIFIED. It was previously suggested to me to increase initrans from 4 to 8, but that made no difference. In the development environment, I increased it to 50 and it still made no difference, so I set it back to 4.

Has anyone had similar issues?

The failures happen with arcpy jobs, but not with FME jobs, both from the same Windows server as the client to the database.

Sherrie

Re: GDB_TABLES_LAST_MODIFIED

BruceHarold — Tue, 23 Aug 2016 15:31:27 GMT

You could instrument your code to write error conditions to a file.

The worker script in the below sample shows error trapping and file writing.

http://arcgis.com/home/item.html?id=b3c7c6273ef54e91aa57a073aa873eca

Regards

Re: GDB_TABLES_LAST_MODIFIED

SherrieKubis — Tue, 23 Aug 2016 19:08:19 GMT

Thanks Bruce, I'll pass that on to the developer.

From a database perspective (I'm the DBA), I'm looking for similar experiences with GDB_TABLES_LAST_MODIFIED bottlenecks. This is a small table, as is GDB_ITEMS, and updates should be very quick.

One line of thinking that I'm pursuing is how does arcpy do its updates to the database. Does it send chunks? When does GDB_ITEMS get updates, when does it commit? I see many SQLNet message to client entries in the trace files, meaning the database is doing its work and waiting for the arcpy to do something back. So process A comes in, starts to update GDB_ITEMS that in turn updates GDB_TABLES_LAST_MODIFIED, the additional processes queue up to do the same but eventually time out.