Database Management 101

Cleaning the Gunk Out of Your Oracle GDB

November 9, 2013 — Skye Perry

Have you ever been browsing through your Oracle geodatabase via TOAD or SQL Query Analyzer and noticed a certain calcification of KEYSET tables? These might exist in your SDE schema and/or other user schemas in the database. If you’re like me, you’ve noticed them but never paid them much attention.

But what are they? And how are they affecting the geodatabase storage, performance, and organization?

Oracle Keyset TablesArcGIS KEYSET tables are used by the geodatabase for quickly traversing relationship classes where the input set exceeds 100 objects. As a reminder, when you create an Esri Relationship Class in the geodatabase, it is not creating a physical relationship in the underlying database. That would be far too cumbersome for the geodatabase, especially when you factor in the additional ADD tables created by versioning your data.

But in the ArcMap application, the relationship class acts like a real database relationship and is able to show all related records for a selected object (i.e. all transformer units for a transformer bank OR all gas mains related to a cathodic protection system).

To achieve effective performance in rendering these relationships in the user interface, Esri creates a temporary KEYSET table for the current session that provides tabular linkage for large relationships. This allows it to act like there is a physical relationship in the database by emulating it via a join table.

The owner of the table (SDE or a user account) is the user who was connected and used the relationship in ArcMap. OR in some cases – where individual users don’t have the Oracle privileges to create KEYSET tables – they may ALL end up under the SDE user schema. The numeric value at the end of the table name corresponds to a specific connected user session within the GIS.

So if these are temporary join tables, why are they sticking around?

In SQL Server databases, the keyset table is indeed created as a temporary table. In Oracle, however, the KEYSET tables are created within user schemas as described above. And when a session terminates abnormally, the KEYSET table may not be dropped from the database, thus orphaning it and beginning the stalagmite growth that you may now find in the bowels of your geodatabase.

We work with a lot of Oracle customers and have regularly seen a TON of these orphaned tables. To check out your system, run the following SQL query:

select count(*) from all_tables where table_name like ‘KEYSET_%’;

In one of my customer test databases from a mid-sized client, there were 6,668 orphaned KEYSET tables! Any thoughts about ignoring these little nuggets of joy should go out the window. Once orphaned, they are never needed again and can be safely removed from the database.

To make this an automated process, we have created a new SSP Nightly Batch Suite (NBS) application that can clean up these tables on a weekly or even nightly basis. The application validates that each KEYSET table is indeed orphaned by comparing the owner and session id against the active SDE process table.

If there’s no reference to the KEYSET table then the app whacks it, mafia style. It then kicks out a nice report into our batch log file:

Keyset Log Files

The end result? A clean and efficient database. If you combine this cleanup app with our other Oracle indexing and statistics NBS apps you are sure to provide your geodatabase with a much-needed performance boost. Not to mention you won’t spend all day scrolling through those pesky tables when you are exploring your SDE schema!

So say goodbye to the KEYSETS and say hello to… I’ve run out of silly puns in this article, but you get the idea!

A special thanks to our resident DBA, Jeff Buturff, for coming up with the idea for this app. I love working with people who are always striving to improve the systems we work on just for the fun of it.

Happy geodatabasing.

We Wrote the Book

The Indispensible Guide to ArcGIS Online

Download It for Free

Skye Perry is the CEO for SSP Innovations and has provided technical architecture, development, and management solutions for GIS-centered implementations since 2000. Skye’s roots tie back to Convergent Group, Miner & Miner, and Enspiria Solutions where he has focused on implementing Esri GIS customizations and system integrations. Today Skye is Read more

What do you think?

Leave a comment, and share your thoughts

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>