Performance Statistics in MaxL

Query database returns medium and long performance statistics for the database and application. The statistics appear as tables in the MaxL output. To gather performance statistics, you must first enable statistics gathering using alter database <dbs-name> set performance statistics enabled. You also use alter database to return to zero the statistical persistence (length) and scope (granularity).

Collecting and analyzing performance statistics can help you understand whether the databases are in good running condition or could use modifications to improve performance.

This topic contains the following sections:

The Analytic Services Performance Statistics Tables

The Analytic Services system gathers a variety of statistics regarding the performance of the system and the connected applications. The output of query database can vary depending on what the system has just done, how long statistics have been gathered and the persistence of the gathered statistics. The tables below give information on a typical set of statistics tables. It can be very helpful to compare two sets of statistics gathered at similar points in the server's operation, such as after two comparable updates or after two restructure operations. Statistics should be gathered at intervals and compared to each other to identify differences. Compare the statistics gathered before and after any changes to the system and if the system performance changes.

Note: Depending on the calculations you choose to perform, if any, some tables may or may not be displayed in your output log.

The major tables are as follows:

The Kernel I/O Statistics table summarizes input/output for the entire application. There is one kernel I/O table per application.

Persistence/Scope of this table: med/server

Kernel I/O Read (The OS reads from disk.) Write (The OS writes to disk.)
# Index I/O Number of reads that occurred through the index cache. Number of writes that occurred through the index cache.
# Data I/O Number of reads that occurred through the data cache. Number of writes that occurred through the data cache.
# Fground I/O Number of data reads that occurred in the foreground (while a process waited for data to be read). Number of data writes that occurred in the foreground (while a process waited for data to be written).
# Index bytes Number of bytes read from .IND files. Number of bytes written to .IND files.
# Data bytes Number of bytes read from .PAG files. Number of bytes written to .PAG files.
Av byte/dat I/O Average byte size of data reads. A high number is preferable. Average byte size of data writes. A high number is preferable.

The Kernel Cache Statistics table assists in sizing database caches. Make caches only as large as necessary for optimum performance. Note that cache sizes are listed in order of importance: index, data file, data.

The Kernel Cache Statistics table assists you in determining how to size Analytic Services caches. The Analytic Services kernel uses these caches to manage memory. As a rule, data that is useful to processes should be kept in memory rather than on a disk. Replacements occur when something needed for a process is moved from disk to cache and something in the cache is thrown away to make room for it.

Use this table to help you decide how to size your caches. Make the caches as small as possible; however, if replacements for a cache are greater than 0, the cache may be too small. Appropriate sizing of the Index cache is the most important for optimal performance; appropriate sizing of the Data cache is the least important.

Persistence/Scope of this table: long/db

Kernel Cache statistics Description
# Blocks Number of blocks actually in the Index cache, Data file cache, and Data cache. The block size multiplied by the number of blocks equals the amount of cache memory being used. Compare this figure to the block estimation you initially used to size your database (see the Database Administrator's Guide).
# Replacements Number of replacements per cache. Replacements occur when data moves from disk to cache and something in the cache is deleted to make room. If the number or replacements is low or zero, the cache might be set too large.
# Dirty repl Number of dirty replacements per cache. A dirty replacement is one that requires a write to the disk before cache memory can be reused by a process. The data needed for the process is "dirty" because it was modified in memory but not saved to the disk. Dirty replacements are inefficient and expensive. They indicate that a cache might be too small.
Log blk xfer in Number of logical blocks transferred to the Data file cache and Data cache (this measurement is not applicable for the Index cache.) If you are changing cache sizes, it may be instructive to study this statistic and note changes in data traffic.

The Cache End-Transaction Statistics table measures DBWriter efficiency. DBWriter is an asynchronous (or no-wait) Analytic Services thread, which searches the cache finding information that needs to be written to a disk.

The Cache End-Transaction Statistics table shows the cleanup state at the end of a transaction. These statistics are designed to measure DBWriter efficiency. DBWriter is an asynchronous (or no-wait) thread, which searches the cache and finds information that needs to be written to a disk. Because the DBWriter only operates during idle times, measuring the DBWriter activity can give an idea of the amount of idle time. This number should be high, indicating that the DBWriter had enough idle time to support the database effectively. Keep these statistics available for diagnostic purposes, in case you need to call technical support.

Persistence/Scope of this table: med/db

The Database Synchronous I/O table tracks synchronous input/output. Synchronous means that the thread or program waits for the I/O to finish before proceeding. The Tave (us) column shows the bandwidth (bytes/Ttotal).

Persistence/Scope of this table: med/db

DataBase Synch I/O Count Bytes Ttotal (ms) (micro-seconds) Tave (ms) (micro-seconds)
Index Read
An occurrence of the OS reading index information from a .IND file on the disk.
Number of times the OS went to the disk to read a .IND file. Number of bytes the OS read from .IND files. Total amount of time the OS took to complete index reads. Average amount of time the OS took to complete one index read. This equals Ttotal (ms)/Count.
Index Write
An occurrence of the OS writing index information to a .IND file.
Number of times the OS wrote information to a .IND file. Number of bytes the OS wrote to .IND files. Total amount of time the OS took to complete index writes. Average amount of time the OS took to complete one index write. This equals Ttotal (ms)/Count.
Data Read
An occurrence of the OS reading information from a .PAG file on the disk.
Number of times the OS went to the disk to read to a .PAG file. Number of bytes the OS read from .PAG files. Total amount of time the OS took to complete data reads. Average amount of time the OS took to complete one data read. This equals Ttotal (ms)/Count.
Data Write
An occurrence of the OS writing data to a .PAG file.
Number of times the OS wrote information to a .PAG file. Number of bytes the OS wrote to .PAG files. Total amount of time the OS took to complete data writes. Average amount of time the OS took to complete one data write. This equals Ttotal (ms)/Count.

Notes:
Bandwidth = bytes/Ttotal.
Average bandwidth= bytes/Tave

The Database Asynchronous I/O table tracks asynchronous input/output. Asynchronous means no-wait: the I/O happens at an unknown time, while the program does other things. The effective bandwidth for the application is determined by bytes/Twait.

Persistence/Scope of this table: med/db

DataBase Asynch I/O Count Bytes Ttotal (ms) (micro-seconds) Tave (ms) (micro-seconds) Twait (ms)(micro-seconds)
Index Read
An occurrence of the OS reading index information from a .IND file on the disk.
Number of times the OS went to the disk to read a .IND file. Number of bytes the OS read from .IND files. Time elapsed between request for an index read, and verification of its completion. Average time elapsed between requests for index reads, and verification of their completion. Wait time if the OS had not completed index reads at the time polled.
Index Write
An occurrence of the OS writing index information to a .IND file.
Number of times the OS wrote information to a .IND file. Number of bytes the OS wrote to .IND files. Time elapsed between request for an index write, and verification of its completion. Average time elapsed between requests for index writes and verification of their completion. Wait time if the OS had not completed index writes at the time polled.
Data Read
An occurrence of the OS reading information from a .PAG file on the disk.
Number of times the OS went to the disk to read to a .PAG file. Number of bytes the OS read from .PAG files. Time elapsed between request for a data read, and verification of its completion. Average time elapsed between requests for data reads, and verification of their completion. Wait time if the OS had not completed data reads at the time polled.
Data Write
An occurrence of the OS writing data to a .PAG file.
Number of times the OS wrote information to a .PAG file. Number of bytes the OS wrote to .PAG files. Time elapsed between request for a data write, and verification of its completion. Average time elapsed between requests for data writes and verification of their completion. Wait time if the OS had not completed data writes at the time polled.

Notes:

The Dynamic Calc Cache table shows where blocks that are expanded to contain calculated members (BigBlks) are calculated: in dynamic calculator cache (DCC), or in regular memory (nonDCC). By viewing the total number of big blocks allocated versus the maximum number of big blocks held simultaneously, and by analyzing block wait statistics, you can determine the efficiency of your dynamic calc cache configuration settings. For more information, refer to the DYNCALCCACHEMAXSIZE setting in the essbase.cfg documentation.

dynamic calc cache Description
BigBlks Alloced The number of big block allocations that have been requested, so far, irrespective of where the system got the memory (DC cache or regular). For three queries Q1, Q2, and Q3 executed, requiring 25, 35, and 10 big blocks, respectively, BigBlks Alloced would be 70. This does not mean that Q1 needed all 25 blocks at the same time. It may have used some blocks for a while, then released some of them, and so on, until the query finished and released all remaining blocks (returned to DC cache or regular memory).
Max BigBlks Held The maximum number of big blocks simultaneously held, so far. For each query Qi executed so far, there will be a number Ni, which gives the maximum number of big blocks that the query needed to have at the same time (includes both DCC and regular memory blocks). MaxBigBlksHeld under the Total column is the maximum over all values of Ni. The values under the DCC and non-DCC columns are similar except that they restrict themselves to the maximum blocks held in the respective portions of memory.
DCC Blks Waited The number of dynamic calculator blocks that the system had to wait for.
DCC Blks Timeout The number of times that the DYNCALCCACHEBLKTIMEOUT configuration setting was exceeded.
DCC Max ThdQLen If the configuration setting, DYNCALCCACHEWAITFORBLK is TRUE, it is possible for queries (really, the threads executing them) to sit in a queue, waiting for DC cache memory to be freed by other threads currently using the memory. DCC MaxThdQLen tells how long this queue ever got (maximum number of threads simultaneously waiting), giving a sense of how critical the dynamic calculator cache became as a resource.

MaxL Script Example

The following MaxL script creates an output file of performance statistics tables.

/* to execute:
   essmsh scriptname username password
*/
login $1 $2;
spool on to 'c:\mxlouts\pstatsouts.txt';
alter database sample.basic set performance statistics enabled;
execute calculation 
  'SET MSG ERROR;
   CALC ALL;'
on Sample.basic;
alter database sample.basic set performance statistics mode to medium persistence server scope;
query database sample.basic get performance statistics kernel_io table;
alter database sample.basic set performance statistics mode to long persistence database scope;
query database sample.basic get performance statistics kernel_cache table;
alter database sample.basic set performance statistics mode to medium persistence database scope;
query database sample.basic get performance statistics end_transaction table;
query database sample.basic get performance statistics database_synch table;
query database sample.basic get performance statistics database_asynch table;
spool off;
logout;