GETPERFSTATS

The GETPERFSTATS command returns performance statistics tables.

Syntax

GETPERFSTATS

Description

This command returns short, medium, and long performance statistics for the thread, database, and application. The statistics appear as tables in the ESSCMD window. To gather performance statistics, you must first enable statistics gathering using RESETPERFSTATS. You also use RESETPERFSTATS 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.

The Essbase Performance Statistics Tables

The Essbase system gathers a variety of statistics regarding the performance of the system and the connected applications. The output of GETPERFSTATS 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.

Click here to see an example performance statistics table.

The major tables are as follows:

* The Semaphore, Event, Spinlock, and Mutex tables contain synchronization statistics for internal Hyperion Essbase mechanisms. These tables are not immediately useful for tuning purposes. Keep these statistics available for diagnostic purposes, in case you need to call technical support.

The Hyperion Essbase kernel I/O Statistics table summarizes input/output for the entire application. There is one kernel I/O table per application.
Kernel I/O stats 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 Essbase 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.

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 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) Hyperion Essbase 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.

The Database Synchronous Input/Output table tracks synchronous input/output. Synchronous means that the thread or program waits for the I/O to finish before proceeding.

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 Input/Output table tracks asynchronous input/output. Asynchronous means no-wait: the I/O happens at an unknown time, while Hyperion Essbase performs other operations.

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 DC 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 DC cache configuration settings. For more information, refer to the DYNCALCCACHEMAXSIZE setting in the ESSBASE.CFG documentation.

DC Cache table 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.

Example

GETPERFSTATS;

This example uses Sample Basic. This command returns some or all of the following tables (measurements will vary): 
Hyperion Essbase Performance Statistics

Persistence/Scope of this table: med/server 
EK I/O Statistics       Read       Write						
# Index I/O                4           2  
# Data I/O                78           2  
# Fground I/O              0           0  
# Index bytes      32768.000   16384.000  
# Data bytes      647168.000   16384.000  
Av byte/dat I/O     8297.025    8192.000

The Hyperion Essbase kernel Cache Statistics table assists you in determining how to size Hyperion Essbase caches. The Hyperion Essbase 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
 

DB:   0, Basic
EK Cache Statistics    Index   Data file        Data
# blocks                   4          80         374  
# replacements             0           0           0  	
# dirty repl               0           0           0    
log blk xfer in            0           3         374

The Cache Endtrans Statistics table measures DBWriter efficiency. DBWriter is an asynchronous (or no-wait) Hyperion Essbase thread, which searches the cache finding information that needs to be written to a disk.
Persistence/Scope of this table: med/db 

DB:   0, Basic
Cache Endtrans stats Svr Thd        DBWR
Data cache xfer            3           0  
Data writes                2           0  
Ph Data blk wrt            2           0  
Idx writes                 1           1

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 

DB:   0, Basic
DataBase Synch I/O     Count       bytes Ttotal (us)   Tave (us)					
Index Read             4.000   32768.000  372942.000   93235.500  
Index Write            0.000       0.000       0.000       0.000  
Data Read             78.000  647168.000  4.231e+006   54238.422  
Data Write             0.000       0.000       0.000       0.000

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 

DB:   0, Basic
DataBase Asynch I/O    Count       bytes Ttotal (us)   Tave (us)   Twait(us)
Index Read             0.000       0.000       0.000       0.000       0.000  
Index Write            2.000   16384.000  147480.000   73740.000   84935.000  
Data Read              0.000       0.000       0.000       0.000       0.000  
Data Write             2.000   16384.000  276968.000  138484.000   56607.000

The Semaphore, Event, Spinlock, and Mutex tables contain synchronization statistics for internal Hyperion Essbase mechanisms. Keep these statistics available for diagnostic purposes, in case you need to call Technical Support.
Persistence/Scope of these tables: short/server 

Semaphore            Gets      Tget(us)      Ave(us)
Used Semaphore         0.000       0.000       0.000  
Free Semaphore         0.000       0.000       0.000  
Debug allocs           0.000       0.000       0.000  
Pipe write             2.000      87.000      43.500  
Log file               0.000       0.000       0.000  
Request Server         0.000       0.000       0.000  
Global Data           41.000    2220.000      54.146  
Listening              0.000       0.000       0.000  
Server log file      373.000   14915.000      39.987  
Error message          0.000       0.000       0.000  
Ddebug.out             0.000       0.000       0.000  
Temp filename          0.000       0.000       0.000  
Sub variable           0.000       0.000       0.000  
Page File              0.000       0.000       0.000  
Shutdown               0.000       0.000       0.000  

Event               Waits      Twait(us)     Ave(us)
DBWriter wake          2.000  6.147e+006  3.074e+006  
Flush complete         2.000   33362.000   16681.000  
DBWriter start         2.000  709705.000  354852.500  
Lock manager           1.000    6494.000    6494.000  
Cleanup wake           1.000  6.025e+006  6.025e+006  
Cleanup stop           1.000     611.000     611.000  
Transaction            0.000       0.000       0.000  
Server signal         48.000  2.018e+006   42034.875  

Spinlock             Gets      Tget(us)      Ave(us)
Waiter Chain           0.000       0.000       0.000  
Hash Table             0.000       0.000       0.000  
DB stat                0.000       0.000       0.000  
Sparse stat            0.000       0.000       0.000  
Trace                  0.000       0.000       0.000  
Locking system      1919.000   68126.000      35.501  
Database lock          0.000       0.000       0.000  
Catalog list           0.000       0.000       0.000  
request Q              0.000       0.000       0.000  
TCT header             0.000       0.000       0.000  
TCT write              0.000       0.000       0.000  
TCT entry              0.000       0.000       0.000  
TCT chain              0.000       0.000       0.000  
ATC pool               0.000       0.000       0.000  
TMG chain              0.000       0.000       0.000  
Free pool              0.000       0.000       0.000  
TMG endtrans           0.000       0.000       0.000  

Mutex                Gets      Tget(us)      Ave(us)
File Lock              0.000       0.000       0.000  
Replication Loc        0.000       0.000       0.000  
Locking SQL            0.000       0.000       0.000  
RPL                    1.000      47.000      47.000  
Perf Stat             13.000     536.000      41.231  

DC Cache                  DCC      nonDCC      Total
BigBlks Alloced          102           0         102
Max BigBlks Hel          101           0         101
DCC Blks Waited            0           0           0
DCC Blks Timeou            0           0           0
DCC Max ThdQLen            0           0           0

See also

RESETPERFSTATS

RESETPERFSTATS

The RESETPERFSTATS command resets statistics gathering for a specified persistence and scope. Each of the statistics tables available using the GETPERFSTATS ESSCMD command has a pre-defined persistence and scope. When you issue RESETPERFSTATS without parameters, statistics-gathering is reset for all of the tables.

Collecting and analyzing performance statistics can assist you in determining whether databases are in good running condition, or could use modifications to improve performance.

Syntax

RESETPERFSTATS persistence scope

persistence
[default=long]
  • disable
    Turn off performance-statistics gathering.

  • enable
    Turn on performance-statistics gathering. You might do this when you want to tune the system, change hardware configuration, or monitor I/O. The measurement begins for current processes as soon as you enable it. Any subsequent queries for statistics return measurements spanning from the time of enablement to the time of the query.

  • short
    Reset tables that measure transient events. Short measurements need frequent resetting. You might use this to measure how many x events happened during an operation; for example, how many reads or writes occurred during a calculation? Did the calculation speed up or slow down by a factor of n?
    Examples: Semaphore, Event, Spinlock, and Mutex tables.

  • medium
    Reset tables that measure medium-length events. Examples:

  • long (default)
    Reset tables that measure events over the course of the entire session. Long measurements rarely need to be reset. Example: Hyperion Essbase kernel Cache Statistics table.

scope
[default=all]
  • thread
    Reset per-thread statistics tables. There are currently no per-thread tables available in ESSCMD.
    A thread is the scope of one Hyperion Essbase transaction; for example, a load operation. Many threads are at work within Hyperion Essbase processes.

  • db
    Reset per-database statistics tables.

  • server
    Reset per-application statistics tables.

  • all (default)
    Reset all statistics tables: for threads, databases, and applications.

Description

Depending on your database and production needs, you create a statistical measurement profile by resetting the appropriate levels of persistence (length of events to measure) and scope (granularity of the entity to measure).

Note

Example

RESETPERFSTATS ENABLE;

RESETPERFSTATS MEDIUM SERVER;

See also

GETPERFSTATS