Scripts to tune an Oracle instance for ArcSDE XML storage

ArcSDE for Oracle includes two scripts in the SDEHOME > Tools > oracle directory that help you configure your Oracle instance for optimum performance when storing ArcSDE XML data. The scripts are xml_lob_block_distribution.sql and xml_lob_cache_size.sql.

xml_lob_block_distribution

This script reports the distribution of XML data in a table. You input the name of the table with the XML column and the script returns the distribution of the XML data in the following blocks:

• Data less than 8 KB
• Data between 8 KB and 16 KB
• Data greater than 16 KB

Using this information, you can determine the size of the majority of your XML data. If most of your XML data is less than 8 KB but you are using a 16 KB block size, you could have a wasted space in the database, and each query response would require twice as much memory, half of which isn't being used. If you used an 8 KB block size instead, you would be able to pack twice as much data into each response and twice as much data into the cache and potentially have fewer reads from the database for a query that returned the same number of records. However, if the majority of your XML data is larger than 16 KB and you are using an 8 KB block size you might have twice as many blocks holding your data than if you were using a 16 KB block size. That means that every response needs to do twice as many reads from disk for the same number of records in the business table, which would slow down query performance.

xml_lob_cache_size

The xml_lob_cache_size script helps to estimate the cache requirements for XML data in a specified block size. As with the xml_lob_block_distribution script, you input the name of the table with the XML column. Plus, you can specify a block size. If no block size is specified, the current db_block_size is used for the estimate.

The information returned from this script tells you how large the LOB cache should be to optimize search performance.

Note:

You cannot change the block size of an existing Oracle database. If these scripts indicate you need a different block size to optimize your XML queries, you must create a backup of your existing database, create a new database with the correct block size, and move your data to the new database.