SDE Layer Usage information

[JamesAshton]

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

[VinceAngelo]

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

[JimaeHaynes]

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

[GeorgeSchenk]

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.

[VinceAngelo]

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