ArcSDE logfile configuration for Oracle - global temps are the way to go

You have several options when it comes to ArcSDE logfile configuration for Oracle.  I'd like to focus on what I believe is the best configuration; shared log file configuration (default) with the global temporary table conversion.

Esri does a great job documenting and explaining the purpose of the log files, the different configuration options, and when you may consider using one configuration over another.   As a result, I won't go into too much detail on each of the available options.

ArcSDE log files are used to store temporary selection sets for your arcmap session.  The number of rows in these tables can be very volatile.  Esri goes into more detail here.

The different configuration options are:

SHARED - sessions using the same login will share the same set of persistent tables.

SESSION BASED - log file tables are created for each unique session that connects and are usually deleted when session terminates.

STAND ALONE - like session based but selection sets get their own table vs being lumped in one table.

POOL - pools of predetermined session based or stand alone logs to share for all incoming sessions.

Esri publishes the details on these different configuration options and when to use each here.

For each of the above option, you have to weigh costs in these four categories:

1. required user permissions to implement
2. level of monitoring and management required to implement
3. additional costs to the database to implement
4. performance

Stand alone log files don't work for read only users - so, for most of you, there is no point in considering this configuration option.  Additionally, they require monitoring and management to ensure that the maxstandalonelogs ArcSDE server configuration parameter is set adequately.

A pool of log files, like stand alone log files, require monitoring and management.  I have enough things to monitor and manage.

Session based log files are constantly creating and dropping tables.  DDL can be expensive.  Furthermore, the session based log file tables don't always get dropped, leaving you to clean them up.

Shared log files require more permissions up front (until at least 100 records are selected in the map) to create the files, but the tables are persistent.  There are no thresholds to worry about like with using a log file pool or the stand alone log configuration.  

The cons to using shared log files are:

1. You may experience contention on the tables if users share the same login since they will be sharing the same segments for storing their temporary selection sets.
   
2. The tables don't always get cleared out (delete from, NOT truncate since they are shared) once the session(s) terminate.  I've seen log file tables with 10 million orphaned rows.
   
3. If statistics are gathered on these tables, the potential for terrible execution plans and performance during things like reconcile is very high.  The row counts in these tables are volatile.  The selection set rows stored in the tables are joined to version query filters during operations such as reconcile.  If the optimizer thinks there are 0 rows based on statistics and there are 4000 rows of selection sets in the tables, you may see a very expensive nested loops operation where there should be a hash join.

The remedy to all 3 of the above cons is to convert each users log file tables from permanent tables to global temporary tables, AND create the global temporary versions of the these tables for new users when the user is created in Oracle.

With global temporary tables:

1. No more contention.  Each user has their own "copy" of the table when sharing logins.
   
2. When the session terminates, the global temp for that session is gone.  period.  No need to worry about orphaned row cleanup.
   
3. You can't gather statistics on a global temporary table.  Oracle will dynamically sample the table to determine the best execution plan, assuming you have not reduced the default optimizer_dynamic_sampling parameter for oracle 10g+ from 2.
   
4. Additionally, and as an added bonus, less redo is generated with global temporary tables than persistent tables.

Esri documents this conversion to global temporary tables here.

If you are still reading this, you may be wondering if their is added cost introduced by the dynamic sampling of the log file tables, and if significant.  To be honest, I've not yet done that research and testing. I can say that I've never seen the dynamic sampling of log files consume a lot of resources in the hundreds of level 12 traces or AWR reports I've reviewed.  Perhaps I'll get a chance to test and post my findings in the future. 

Map refreshes are slow...Enter MXDPERFSTAT

Every ArcSDE administrator/DBA has had to deal with slow ArcMap refresh times.  A properly scoped oracle trace or sqlserver profiler trace can quickly help you identify if most of your map refresh times are being spent in the database.  What if your end users claim 10 second refresh times and your database trace only accounts for 2 seconds worth of database work?

If you are a DBA who is very hands off on the client side, you could just say its not the database, and therefore its not my problem.  If I've just described you, feel free to stop reading now.  If you want to be able to identify common problems introduced by the application configuration and how to remedy them, please keep reading.

When most of the time is being spent in the database the root of the problem is often a missing index,stale statistics (bad execution plan), or a ill-maintained state tree for versioned environments.  It could also be a poorly written definition query for a layer.  The cause of slow refresh times outside of the database can be many different things and are usually harder to identify: network issues, resource contention on a heavily loaded citrix machine, etc, etc.  For these cases in which database time is far less than user experienced draw time, I'd like to focus on what I see most  - a poorly configured map document.

Based your map document configuration for any given layer, the bulk of the processing can be weighted towards the db or the client (or heavy on both).  The trick is knowing which changes affect which side of the equation.

Unlike some applications, ArcMap is very flexible.  It allows end users to:

1. Add whatever they want to the map (as many times as they want)
   
   Users tend to keep layers in the map that that they might need.  Often times, they are even left on to draw always.  This means useless database calls and client side processing to render the layer.
   
   Also, users can an add new (ad hoc) datasets to the map document that could be contributing to draw performance.
   
   
2. Draw data at whatever scales they desire
   
   Users can add something very large like streets or elevation contours to the map, and choose to draw it at a scale of 1:1,000,000.  Visually there would be no added value but such a configuration may incur massive amounts of IO on the database for every refresh of the map.  If you have scale dependent rendering configured for a layer to prevent this, users still have the flexibility to turn it off.
   
   
3. Use very complex symbology
   
   The more complex your symbology, the more time to render on the client side.  This link describes this in detail far better than I can and comes directly from ESRI.
   
   
4. Symbolize the data based on data attributes
   
   In cases where users are symbolizing by column values where there are hundreds of distinct values, this can be a problem as the client takes additional time and cpu to render the data based on this filter.
   
   
5. Label the map features based on map attributes
   
   Labeling incurs additional overhead on the client.  Rendering and placement of labels requires cpu on the client, and can perform terribly at scales in which many features are drawn and have to be labeled.
   
   Users have many options when it comes to labeling.  It can be as simple as labeling map features based on the raw values in a column, or setting up multiple labeling classes for a given layer, and/or writing custom post processing logic via vbscript or jscript if they wish to manipulate/filter the data before labeling occurs.  Based on the configurations they choose, additional client processing may be necessary, as well as additional calls to the database during every refresh.
   
   
6.  Join map layers to other sources of information
   
   Arcmap joins are a great way to label, symbolize, and filter based on a column gained from the join, however, it will add to the time required to do the labeling,symbolizing and filtering based on a column that is native to the layer.  Also, users have the option to make the join an inner (keep only matching records) or outer join (keep all records), which are handled very differently.  An inner join takes place in the db, where the outer join method does the join on the client after retrieving all the necessary rows.
   
   See the "performance tips for joining data" section under this link for more information.
   
   
7.  Filter the results with a definition query
   
   I love this capability for reducing the number of rows to be retrieved from the database.  Unfortunately, it also provides the ability for users to submit their own where clause, no matter how poorly written.  This is where I usually see SQL written that suppresses index use.  Also, the column(s) they are referencing in the definition query may not be indexed, which could result in high database IO and poor draw performance.
   
   
8. Reproject data on the fly.
   
   A user can pull data from many sources into a map document.  Those sources may not all have the same spatial reference information; coordinate systems and/or projections could be different.  ArcMap is robust enough to reproject them all on the fly to match the spatial reference defined for your data frame - at a rendering cost to the client.

For many of you seasoned ArcSDE administrators, you may be able to identify many of the above by simply looking at the sql submitted to the database.   For the rest of you, lets talk about one of my favorite free tools, MXDPERFSTAT.

MXDPERFSTAT is available via ESRI's Arcscripts site.   You can get to the download page here.  At the time of this posting two builds of MXDPERFSTAT are available:  One for ArcGIS 92/93, and another for ArcGIS 931 and 10.

In a nutshell, MXDPERFSTAT is a stand alone executable that takes a map document as input, along with the scales you want to test, and outputs a report of draw times per scale and per layer.  An ArcEngine app runs during execution, capturing metrics at each scale for each layer by turning them on and off one at a time.  For each layer, the times spent within different "phases" are listed allowing you to see where most of the refresh time is coming from.  You'll also be able to see the number of features and vertices drawn per layer per scale and if a definition query was being supplied for that layer as the sql will be listed.  Additionally, if you have the additional privileges granted per the user guide, you'll also get database cpu, physical io, and logical io per layer.  If all that info wasn't enough, the phases are documented in the user guide and the report will also list recommendations for improvement based on thresholds.  If you are reprojecting on the fly, it will say so in the recommendations part of the report for each layer.

This tool comes with a well written users guide, and I've found that the author, Andrew S, has been VERY responsive to questions I've submitted via the download page for the tool in ArcScripts.

There are some system requirements for this tool, but as long as you have ArcGIS (licensed), the mxd you want to run through mxdperfstat, rdbms credentials for a connection to the geodatabase for which you want to test, and some additional rdbms privileges that allow you to see performance counter metrics for your session, you should be good.  The specific system requirements are listed on the download page for the tool.

In addition to helping you identify the phase which is contributing to the bulk of the refresh time for a given layer during a crisis, MXDPERFSTAT is also good for tracking progress during general database, Geodatabase and map document tuning.  I like to use it to capture a baseline, then repeat tests after tweaking database parameters, geometry types, spatial indexes, and map document layer configurations, etc.  I also find it useful to capture oracle traces (with a logon trigger) or sqlserver profiler traces while MXDPERFSTAT is running to get detailed info as to what the database is doing (exec plans, rowsource operation costs, lio,pio,cpu, etc) to correlate with metrics provided via the mxdperfstat report.  What I have yet to do is to capture performance monitor counters (perfmon) on the client, which could prove useful for correlating certain map document configurations with specific types of resource consumption.

End users have a job to do and depend on the flexibility of Arcmap and your enterprise GIS to get answers.  Unfortunately, sometimes that means you get a phone call or an email because draw times are slow.  In my experience, good communication with the end users is key, as well as having the tools available to help identify the source of the problem when its clearly not in the database.  Thank you Andrew for publishing MXDPERFSTAT to the masses.

-Sancho