topic Re: SDE Layer Usage iinformation in Data Management Questions

SDE Layer Usage information

JamesAshton — Thu, 24 Jun 2010 14:41:34 GMT

Oracle 10
SDE 9.3 SP1

I am looling for information to pass onto our DBA on how to record and track table usage information for layers in the SDE/Oracel database. There used to be an Oracle stats pak posted under the old site.

I am not looking for realtime who is using SDE, ie SDEMONITOR.

I am looking for collecting/querying what layers are being accessed most frequently within the SDE/Oracle, by whom, and when.

Thanks,

James

Re: SDE Layer Usage iinformation

VinceAngelo — Thu, 24 Jun 2010 15:07:32 GMT

This sort of information is not normally collected by ArcSDE. While you can use either
database or ArcSDE logging and trace capabilities to collect much of what you want,
the performance burden on your server will be noticeable (possibly annoyingly so for
end users), and you'll have a significant task in reducing the raw data to usable form
(grep/awk/sed, Perl, or Python skill would be invaluable).

Good luck.

- V

Re: SDE Layer Usage iinformation

JimaeHaynes — Thu, 01 Jul 2010 16:57:10 GMT

You know, having a way to know how many "hits" each layer is getting over some definable period of time would be a great tool. Our GIS group gets asked for metrics to justify our existence on a regular basis; mostly we just guess. This would give us real numbers for the manager types and for our own administrative purposes. If someone knows of a way to "count uses" on layers through SDE, I'd like to hear about it. Thanks, Jimae

Re: SDE Layer Usage information

GeorgeSchenk — Wed, 14 Jul 2010 13:30:55 GMT

This type of report isn't standard functionality but it is "easy" to implement.

Whenever a table or feature class is accessed an entry is made into the sde.table_locks table. In this table you can see the registration_id and the sde_id for the lock.

The registration_id can be linked to the sde.table_registry table for the table_name.
The sde_id can be linked to sde.process_information for logon_time, logon_node etc.

You need to create a trigger /query that combines the information and insert it into a to be created "logging" table.

Re: SDE Layer Usage information

VinceAngelo — Wed, 14 Jul 2010 14:04:13 GMT

That's clever, but not all ArcSDE clients grab locks for each query, so you'll want to use
the information generated by this approach carefully (certainly not for security auditing).

- V