Sets the maximum number of blocks that a query can access before the query is terminated.
This setting does not apply to aggregate storage databases.
QRYGOVEXECBLK [appname [dbname]] n
appname—Optional. Applies the query block limit to the application specified. If you specify appname, you must also specify a value for n, or Essbase Server ignores QRYGOVEXECBLK. If you do not specify an application, you cannot specify a database, and the query block limit applies to all applications and databases on the server. If you specify a value for appname and do not specify a value for dbname, the query time limit applies to all databases in the specified application.
dbname—Optional. Must be used with appname and n, or Essbase Server ignores QRYGOVEXECBLK. If you specify dbname, appname, and n, the query block limit is applied only to the specified database.
n—The value of n specifies the number of blocks that Essbase Server allows a query to access before the query is terminated. You must specify this parameter or the server ignores QRYGOVEXECBLK. If you do not specify appname or dbname, the query block limit applies to the entire server.
QRYGOVEXECBLK specifies the maximum number of blocks that a query can retrieve before Essbase Server terminates that query (a request for information sent to a database). You can apply this setting to an entire server, to all the databases in a single application, or to a single database.
When a query exceeds the block limit and is terminated, an error message is written to the application log of the application accessed for the query.
Restarting Essbase Server after adding or changing this setting activates the new setting values.
Use QRYGOVEXECBLK to prevent these types of queries:
A long-running query against a database that accesses atrributes at a high level, forcing many dynamic calculations to occur.
A query that uses the zoom-in "Drill to bottom" option in a large dimension.
A query that uses the zoom-in "Drill to all levels" option in a a large dimension.
Use QRYGOVEXECBLK, for example, if you have users who try to retrieve so much data in a single query that their query appears to hang for minutes at a time. A query launched against the database involving attribute dimensions, for example, may be larger than the user realizes.
If you use an invalid value (such as a negative number, a letter, a word, or a special character) for n, Essbase Server ignores QRYGOVEXECBLK.
Query governor settings are ignored during data load and calculation. You can leave query governor settings in the configuration file whether you are performing these operations or querying against the data.
If a query involves one or more Hybrid Analysis Relational Sources, QRYGOVEXECBLK is disabled upon encountering the first relational member.
QRYGOVEXECBLK Sample Basic 3
Sets three blocks as the maximum number of blocks that a query to Sample Basic can access before being terminated. A block is created for each unique combination of sparse dimension members. If a user issues a query that accesses four unique combinations of sparse dimensions, Essbase Server terminates the query and writes a message to the application log.
QRYGOVEXECBLK 5
Sets five blocks as the maximum number of blocks that a query can access before being terminated. The query time limit applies to all applications and databases on Essbase Server that correspond to the essbase.cfg file containing this setting.
For more information about the application log, see the Oracle Essbase Database Administrator's Guide.