Chapter 4 Cache Query (2)

Runtime plan selection

Runtime plan selection (RTPC) is a configuration option that allows the SQL optimizer to take advantage of outlier information at runtime (when the query is executed). Runtime plan selection is a system-wide SQL configuration option.

When RTPC is activated, preparing a query involves detecting whether the query contains conditions on fields with outliers. If PREPARE detects one or more outlier field conditions, the query is not sent to the optimizer. Instead, SQL generates a runtime plan selection stub. At execution time, the optimizer uses this stub to select the query plan to execute: a standard query plan that ignores outlier status, or an alternative query plan that is optimized for outlier status. If there are multiple outlier conditions, the optimizer can choose from multiple alternative run-time query plans.

  • When a query is prepared, SQL determines whether it contains an outlier field condition. If so, it will defer the selection of the query plan until the query is executed. On preparation, it creates a standard SQL statement and (for dynamic SQL) the corresponding cached query, but chooses whether to use this query plan or create a different one until the query executes. When prepared, it creates what looks like a standard SQL statement, as follows:DECLARE QRS CURSOR FOR SELECT Top ? Name,HaveContactInfo FROM Sample.MyTest WHERE HaveContactInfo=?, using question marks to represent words instead of variables. However, if you look at the SQL statement details, the query plan is prepared with the statement “Execution may cause different plans to be created.” Dynamic SQL queries also create seemingly standard cache queries. However, the cache query shows the plan option usedSELECT %NORUNTIMEKeyword displays query text, indicating that this is not usedRTPCQuery plan of.
  • When the query is executed (opened in embedded SQL), SQL creates a second SQL statement and the corresponding cache query. The SQL statement has a hash generated name and generates an RTPC stub as follows:DECLARE C CURSOR FOR %NORUNTIME SELECT Top :%CallArgs(1) Name,HaveContactInfo FROM Sample.MyTest WHERE HaveContactInfo=:%CallArgs(2). It is then used by the optimizer to generate the corresponding cache query. If the optimizer determines that the outlier information does not provide a performance advantage, it creates and executes a cache query identical to the one created on preparation. However, if the optimizer determines that using outlier information provides a performance advantage, it creates a cached query to disallow literal substitution of the outlier field in the cached query. For example, ifHaveContactInfoThe field is the outlier field (most records have a value of ‘Yes’), the querySELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=?Will result in a cached query:SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=(('Yes')).

Note that the display of the RTPC query plan varies depending on the source code of the SQL code:

The MANAGEMENT portal SQL interface Display plan button may display another runtime query plan because this display plan gets its SQL code from the SQL interface text box.

When the SQL statement is selected, the statement details, including the query plan, are displayed. This query plan does not display an alternative run-time query plan, but contains the text “Execution may cause different plans to be created” because it gets its SQL code from the statement index.

If the RTPC is not activated, or the query does not contain the appropriate outlier field condition, the optimizer creates the standard SQL statement and the corresponding cache query.

If an RTPC stub is frozen, all associated standby runtime query plans are also frozen. Even with the RTPC configuration option turned off, RTPC processing remains active for frozen queries.

When writing queries, you can manually suppress text substitutions by specifying parentheses: SELECT Name,HaveContactInfo FROM T1 WHERE HaveContactInfo=((‘Yes’)). RTPC is not applied to the query if literal substitution for outlier fields is suppressed in the condition. The optimizer creates a standard cached query.

Activate the RTPC

RTPC can be configured system-wide using an administrative portal or class method. Note that changing the RTPC configuration Settings clears all cached queries.

Using the MANAGEMENT portal, configure system-wide optimization queries based on parameter value SQL Settings. This option sets up the appropriate combination of run-time plan selection (RTPC) optimization and deviation query as outlier (BQO) optimization. Select System Administration, Configuration, SQL and Object Settings, SQL to view and change this option. The options available are:

  • Assume that the query parameter values are not field outliers (BQO=OFF.RTPC=OFF, initial default)
  • Assume that query parameter values often match field outliers (BQO=ON.RTPC=OFF)
  • Tuning actual query parameter values at run time (BQO=OFF.RTPC=ON)

To determine the current Settings, call $SYSTEM. SQL. CurrentSettings ().

$SYSTEM. SQL. Util. SetOption () method can be within the scope of the SYSTEM the RTPC activate all process, as follows: SET the status = $SYSTEM. SQL. Util. SetOption (” RTPC “flag,. Oldval). The flag parameter is a Boolean value for setting (1) or unsetting (0)RTPC. The oldValue parameter returns the previous RTPC setting as a Boolean value.

Application RTPC

The system applies RTPC to SELECT and CALL statements. It does not apply RTPC insert, update, or delete statements.

When an outlier is specified in the following query context, the system applies RTPC to any field identified by the tuning table.

Specifies an outlier field in a condition to compare with a literal. This comparison could be:

  • Use equality (=), unequal (! =),INor%INLISTThe predicateWHEREClause conditions.
  • Be equal to (=), unequal (! =),INor%INLISTThe predicateONClause join conditions.

If RTPC is applied, the optimizer determines at run time whether to apply a standard or alternative query plan.

If the query contains unresolved? Input parameters.

If the query specifies a literal value enclosed in double parentheses, RTPC is not applied, thus inhibiting literal substitution.

RTPC is not applied if the text is supplied to the outlier field condition by a subquery. However, if an outlier field condition exists in a subquery, RTPC is applied.

Overriding RTPC

You can override the RTPC of a particular query by specifying the %NORUNTIME RESTRICT keyword. SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? SELECT %NORUNTIME Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? The RTPC is overridden to produce a standard query plan.

Cache the query result set

When a cached query is executed, it creates a result set. The cached query result set is an object instance. This means that the value specified for the literal replacement input parameter is stored as an object property. These object properties are referenced using the I %PropName syntax.

List cache query

Compute cache query

The current number of cached queries for the table can be determined by calling the GetCachedQueryTableCount() method of the %Library.SQLCatalog class. The following example shows this:

/// w ##class(PHA.TEST.SQL).CountingCachedQueries()
ClassMethod CountingCachedQueries(a)
{
	SET tbl="Sample.Person"
	SET num=##class(%Library.SQLCatalog).GetCachedQueryTableCount(tbl)
	IF num=0 {
		WRITE "No cached query",tbl 
	} ELSE {
		WRITE tbl,"Associated with the following.",num,"Cache query" 
	}
	q ""
}
Copy the code
DHC-APP>w ##class(PHA.TEST.SQL).CountingCachedQueries(a)Sample.PersonAssociated with 2 Cache queriesCopy the code

Note that queries that reference more than one table create a single cache query. However, each of these tables separately counts the number of cached queries. Therefore, the number of cached queries counted by table may be greater than the actual number of cached queries.

Displays cached queries

The contents of the query cache can be viewed (and managed) using the IRIS management portal. From System Explorer, select SQL. Select a namespace using the toggle option at the top of the page; This displays a list of available namespaces. Open the Cached Queries folder on the left side of the screen. Selecting one of the cache queries displays the details.

The query type can be one of the following values:

  • %SQL.Statement Dynamic SQLUse:%SQL.StatementDynamic SQL query.
  • Embedded cached SQL: embedded cache SQL
  • ODBC/JDBC Statement: Dynamic queries from ODBC or JDBC

After the SQL statement is successfully prepared, a new class is generated that implements the statement. If the Retention Cached Query source-System-wide configuration option has been set, the Source code for the generated class is retained and can be opened for inspection using Studio. To do this, go to the IRIS administrative portal. From system management, select Configuration, SQL, object Settings, and SQL. From this screen, you can set the query source option to keep the cache. If this option is not set (the default), the class is generated and deployed, and the source code is not saved.

Can also use $SYSTEM. SQL. Util. SetOption () method to set the SYSTEM range of options, as shown below: SET the status = $SYSTEM. SQL. Util. SetOption (” CachedQuerySaveSource “flag,. Oldval). The Flag parameter is a Boolean value used to reserve (1) or not reserve (0) the query source code after compiling the cached query; The default value is 0. To determine the current Settings, please call $SYSTEM. SQL. CurrentSettings ().

List cached queries using ^rINDEXSQL

  ZWRITE ^rINDEXSQL("sqlidx".2)
Copy the code

Typical global variables in this list look like this:

^rINDEXSQL("sqlidx".2."% SQLCQ. USER. Cls4.1"."oRuYrsuQDz72Q6dBJHa8QtWT/rQ=") ="".
Copy the code

The third subscript is position. For example, “% sqlcq.user.cls4.1 “is a cached query in the USER namespace; Sample.mytable.1” is an SQL statement. The fourth subscript is the statement hash.

Export cached queries to a file

The following utility lists all cached queries for the current namespace into a text file.

ExportSQL^%qarDDLExport(file,fileOpenParam,eos,cachedQueries,classQueries,classMethods,routines,display)
Copy the code
  • fileThe file pathname to list the cache query. Specifies a quoted string. If the file does not exist, the system creates it. If the file already exists, InterSystems IRIS overwrites it.
  • fileOpenParamOptional – Open mode parameter for the file. Specifies a quoted string. The default value is"Being"."W"Specifies that the file is being opened for writing."N"Specifies that a new sequential file is created with this name if the file does not exist."S"Specifies the stream format with a carriage return, line feed, or page feed as the default terminator.
  • eosOptional – The end-of-statement delimiter used to separate the various cached queries in the list. Specifies a quoted string. The default value is"GO".
  • cachedQueriesOptional – Export all SQL queries from the query cache to a file. A Boolean flag. The default value is 1.
  • classQueriesOptional – Export all SQL queries from SQL class queries to a file. A Boolean flag. The default value is 1.
  • classMethodsOptional – Export embedded SQL queries to files from class methods. A Boolean flag. The default value is 1.
  • routinesOptional – Export embedded SQL queries to files from MAC routines. This listing does not include system routines, cached queries, or generated routines. A Boolean flag. The default value is 1.
  • displayOptional – Displays the export progress on the terminal screen. A Boolean flag. The default value is 0.

Here is an example of calling the cache query export tool:

  DO ExportSQL^%qarDDLExport("C:\temp\test\qcache.txt"."WNS"."GO".1.1.1.1.1)
Copy the code

When you run display=1 on the terminal command line, the export progress is displayed on the terminal screen, as shown in the following example:

Export SQL Text for Cached Query: %sqlcq.USER.cls14..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls16..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls17..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls18..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls19..                Done
Export SQL statement for Class Query: Cinema.Film.TopCategory...        Done
Export SQL statement for Class Query: Cinema.Film.TopFilms...           Done
Export SQL statement forClass Query: Cinema.FilmCategory.CategoryName... Done ExportSQL statement for Class Query: Cinema.Show.ShowTimes...          Done

20 SQL statements exported to script file C:\temp\test\qcache.txt
Copy the code

The created export file contains the following entries:

  -- SQL statement from Cached Query %sqlcq.USER.cls30
  SELECT TOP ? Name , Home_State , Age , AVG ( Age ) AS AvgAge FROM Sample . Person ORDER BY Home_State
GO
Copy the code
  -- SQL statement from Class Query Cinema.Film.TopCategory
#import Cinema
SELECT TOP 3 ID, Description, Length, Rating, Title, Category->CategoryName
  FROM Film
  WHERE (PlayingNow = 1) AND (Category = :P1)
  ORDER BY TicketsSold DESC
GO
Copy the code
  -- SQL statement(s) from Class Method Aviation.EventCube.Fact.%Count
#import Aviation.EventCube
SELECT COUNT(*) INTO :tCount FROM Aviation_EventCube.Fact
GO
Copy the code

This cached list of queries can be used as input to the query optimization plan utility.

Perform cache query

  • From dynamic SQL:%SQL.StatementPreparation operation (%PrepareClassQuery()or%ExecDirect()Create a cache query. Use the same instance dynamicSQL%Execute()Method to perform the recently prepared cache query.
  • Slave terminal: Available$SYSTEM.SQLOf the classExecuteCachedQuery()Method to perform a cache query directly. This method allows you to specify input parameter values and limit the number of lines to output. Dynamic SQL can be executed from the terminal command line%SQL.StatementCache query or xDBC cache query. This method is primarily used to test existing cached queries on a limited subset of data.
  • On the SQL page of the MANAGEMENT portal: Perform operations described in Show Cached queries. From the directory details TAB of the selected cache query, click the Execute link.

Cache query lock

When the cached query metadata is updated, issuing a PREPARE or PURCESS statement automatically requests an exclusive system-wide lock. SQL support $SYSTEM. SQL. Util. SetOption () method of system-wide CachedQueryLockTimeout options. This option controls the lock timeout when attempting to obtain a lock on cached query metadata. The default value is 120 seconds. This is much longer than the standard SQL lock timeout, which defaults to 10 seconds. System administrators may need to modify this cache query lock timeout on systems with a large number of concurrent prepare and clear operations, especially on systems that perform bulk sweeps involving a large number (thousands) of cache queries.

SET the status = $SYSTEM. SQL. Util. SetOption (” CachedQueryLockTimeout “, seconds, oldval) method SET up the SYSTEM range of timeout value:

SetCQTimeout
   SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout".150,.oldval)
   WRITE oldval," initial value cached query seconds",!!!!! SetCQTimeoutAgain SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout".180,.oldval2)
   WRITE oldval2," prior value cached query seconds",!!!!! ResetCQTimeoutToDefault SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",oldval,.oldval3)
Copy the code

CachedQueryLockTimeout Sets the system-wide cache query lock timeout for all new processes. It does not change the cache query lock timeout for existing processes.

Clear cached queries

Whenever a table definition is modified (changed or deleted), any queries based on that table are automatically cleared from the query cache on the local system. If you recompile a persistent class, any queries that use that class are automatically cleared from the query cache on your local system.

You can use one of the clear cache query options to explicitly clear the cache query through the administrative portal. You can use the SQL command PURGE Cached Queries to explicitly clear the Cached Queries. You can use SQL Shell cleanup commands to explicitly clear cached queries.

You can explicitly clear recently unused cache queries using the $system.sqL.push (N) method. Specifying n days clears all cache queries in the current namespace that have not been used (prepared) in the past n days. Specifying a value of n of 0 or “” clears all cached queries in the current namespace. For example, if the $system.sqL.push (30) method was issued on May 11, 2018, it would only clear cached queries last prepared before April 11, 2018. Cached queries that were last prepared exactly 30 days ago (April 11 in this case) are not cleared.

You can also clear cached queries using the following methods:

  • $SYSTEM.SQL.PurgeCQClass()Clears one or more cache queries by name in the current namespace. You can specify the cached query names as comma-separated lists. Cache query names are case-sensitive; The namespace name must be specified in all uppercase letters. The specified cache query name or list of cache query names must be enclosed in quotes.
  • $SYSTEM.SQL.PurgeForTable()Clears all cached queries in the current namespace that reference the specified table. Schema and table names are case insensitive.
  • $SYSTEM.SQL.PurgeAllNamespaces()Clears all cached queries in all namespaces on the current system. Note that when a namespace is deleted, the cache queries associated with it are not cleared. performPurgeAllNamespaces()Check if there are any cache queries associated with namespaces that no longer exist; If so, these cached queries are cleared.

To clear all cached queries in the current namespace, use the admin portal to clear all query options for this namespace.

A query that clears the cache also clears the associated query performance statistics.

A query that clears the cache also clears the associated SQL statement list entries. SQL statements listed in the administrative portal may not be cleared immediately, and you may need to press the Clear stale button to clear these entries from the LIST of SQL statements.

Note: When you change the system-wide default schema name, the system automatically clears all cached queries in all namespaces on the system.

The remote system

A query that clears the cache on the local system does not clear a copy of that cached query on the mirror system. Copies of cleared cache queries must be manually cleared on the remote system.

When a persistence class is modified and recompiled, local cached queries based on that class are automatically cleared. IRIS does not automatically clear copies of queries cached on remote systems. This may mean that some queries cached on remote systems are “obsolete” (no longer valid). However, when a remote system tries to use a cached query, it checks to see if any persistent classes referenced by the query have been recompiled. If a persistent class is recompiled on the local system, the remote system will automatically clean up and recreate outdated cached queries before attempting to use it.

No cached SQL commands

The following non-query SQL commands are not cached; They are cleared immediately after use:

  • Data Definition Language (DDL) :CREATE TABLE.ALTER TABLE.DROP TABLE.CREATE VIEW.ALTER VIEW.DROP VIEW.CREATE INDEX.DROP INDEX.CREATE FUNCTION.CREATE METHOD.CREATE PROCEDURE.CREATE QUERY.DROP FUNCTION.DROP METHOD.DROP PROCEDURE.DROP QUERY.CREATE TRIGGER.DROP TRIGGER.CREATE DATABASE.USE DATABASE.DROP DATABASE
  • Users, Roles, and Rights:CREATE USER.ALTER USER.DROP USER.CREATE ROLE.DROP ROLE.GRANT.REVOKE.%CHECKPRIV
  • Lock:LOCK TABLE.UNLOCK TABLE
  • Other:SAVEPOINT.SET OPTION

Note that if one of these SQL commands were issued from the MANAGEMENT portal query execution interface, the performance information would include the following text: Cache query: % sqLCq.user.cls16. This will be displayed in, representing the query name of the allocated cache. However, this cache query name is not a link. No cache query is created and no incremental cache query number is reserved. Cls16. SQL assigns this cached query number to the next issued SQL command.