SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 24

This practice enables RDF to properly map files between primary and backup systems. If you allow the

system to generate the names, there will be incompatible names between your primary and secondary

systems.

Using SQLMXBUFFER to Improve Database Performance

SQL/MX Session Data Space and Data Cache Guidelines

When a SQL statement is compiled, a query plan is created. Depending on the SQL statement, a query

plan can execute against a single non-partitioned table, several partitions of a table, or several tables in

the case of a multi-table join. The part of the query plan that can be executed by the Disk Process (DP2)

is stored in the SQL/MX buffer. The piece of the query plan executed by a specific DP2 is called a query

fragment. For a partitioned or multi-table query, query fragments are sent to the DP2 where the data can

reside. In addition to the query fragment, DP2 can store state and temporary working data in the SQL/MX

buffer. Therefore, it is important to have reserved enough SQL/MX buffer space to execute the query.

SQL/MX buffer space competes for the same virtual memory space with DP2 data cache and lock

tables. DP2 allocates all segment spaces based upon the user configuration, except for DP2 data cache.

The default size of SQL/MX buffer space is currently 128 MB. This leaves about 850 MB of virtual space for a

maximum data cache with a default configuration.

If the cache configuration is larger than the available space, DP2 automatically reduces the cache

configuration to match the remaining virtual space. The default size of the SQLMX buffer space may be

insufficient, and you can increase the size by using SCF. If you increase the size, the data cache may be

reduced, if needed.

SQL/MX buffers can be reused when a query is repeated. This avoids the need to resend the query plan

when the application re-executes the query. If the reuse fails, it indicates that the query plan was stolen

due to insufficient SQLMX buffer configuration. This results in an increase in messages between the

applications and DP2. As a result, SQL/MX performance will be sub-optimal.

SQL/MX buffers can be monitored through SCF. Below is an example:

SCF - T9082G02 - (04MAY04) (07APR04) - 05/12/2005 22:26:46 System \DRIS1

(Invoking \DRIS1.$SYSTEM.STARTUP.SCFCSTM)

1-> stats disk $D*,sqlmx

STORAGE - Stats DISK \DRIS1.$D01001

SQL/MX Statistics:

Session Data bytes....... 786432 KB Max Data bytes... 786432 KB

Total Sessions........... 1708 Active Sessions.. 0

- 4KB Blocks - - - - - - - - - - - - Reuse - - - - - - - - - -

Max...................... 195840 Attempts......... 60155

Number................... 195840 OK............... 58214

In Use................... 53621 Failed FST....... 12

Failed ID........ 1929

Total Sessions The number of query fragments residing in SQL/MX buffer space. A Session is

a query fragment.

Session Data bytes The amount of SQL/MX buffer space in use by the Total Sessions.

Max Data bytes The amount of SQL/MX buffers configured for the disk. In this example, this

disk was configured for the maximum amount.