7. SQL Plan Cache#

This chapter describes the concepts and features of Altibase's SQL Plan Cache and Result Cache features.

Overview of the SQL Plan Cache#

Altibase uses the SQL plan cache to share SQL execution plans necessary for executing SQL statements among sessions. Whenever a shared SQL plan is executed, the execution context can be reused and performance enhancement can be anticipated.

Structure of the SQL Plan Cache#

Altibase has cache areas that are shared by all sessions. These cache areas comprise the SQL plan cache, the stored procedure cache and the meta cache. The information stored and shared in each cache is as follows:

Shared SQL Plan Cache

Whenever new SQL execution plans are created, they are stored in the SQL plan cache so that they can be shared by all sessions.

Stored Procedure Cache

Whenever new stored procedure execution plans are created, they are stored in the stored procedure cache so that they can be shared.

Meta Cache

Meta data (information about database objects) is stored in the meta cache so that it can be accessed quickly.

Advantages of the SQL Plan Cache#

The use of the Altibase SQL Plan Cache confers the following advantages:

Cost reduction in preparing and improved performance in direct execution

environments Direct execution is the most basic way to execute a statement. In environments where queries are directly executed because plans that have already been prepared can be shared and reused, the cost of preparing execution plans can be reduced.
Drastic reduction of prepare memory in prepared execution environments

Prepared execution is a way to reduce the parsing and compiling overhead associated with repeatedly executing a SQL statement. Moreover, in environments that use prepared execution only, the SQL plan cache confers not only the benefit of reducing the cost of preparing, but also the benefit of reducing the total amount of prepare memory that is required.
Check-in method.
Replacement policy which considers the number of times and the frequency with which plans are referred to.

Managing the SQL Plan Cache#

Altibase uses a plan-sharing-oriented check-in method. The SQL plan cache has a replacement policy which considers not only which plans were most recently referred to, but also the frequency with which plans are referred to. Thus, it is necessary to modify the property according to its purpose of use..

SQL Plan Cache Policies#

Execution plans within the size specified for SQL_PLAN_CACHE_SIZE are registered.

Altibase registers new shared execution plans (within the total size specified for the SQL_PLAN_CACHE_SIZE property) to the SQL plan cache. If a new plan is registered and the limit is exceeded, obsolete plans are searched for and deleted to make space for the new plan. The default value for the SQL_PLAN_CACHE_SIZE property is 64MB and it is modifiable.

Referenced execution plans cannot be deleted.

SQL plan cache's replacement policy takes into account the most recently and frequently referenced execution plans. Additionally, when the cache becomes sparse, the most frequently referenced plan cache objects are protected. If a new plan is registered and the limit specified for the SQL_PLAN_CACHE_SIZE property is not only exceeded but the plans within the SQL plan cache are being referenced by other statements, no execution plan within the cache can be deleted. In this case, a new plan fails to be registered to the SQL plan cache and the CACHE_IN_FAIL_COUNT value in the V$SQL_PLAN_CACHE performance view increases.

Controlling the SQL Plan Cache#

By default, the Altibase server adheres to a replacement policy and automatically manages execution plans. However, the DBA can manually manage execution plans when necessary.

The following statement is a control statement which uses the ALTER SYSTEM statement to initialize the SQL Plan Cache.

Deletes unused execution plans from the SQL Plan Cache.

ALTER SYSTEM COMPACT SQL_PLAN_CACHE;

Deletes unused execution plans from the SQL Plan Cache and initializes statistics.

ALTER SYSTEM RESET SQL_PLAN_CACHE;

For more detailed information about each SQL statement, please refer to the SQL Reference.

Statements that Use the SQL Plan Cache#

Not all statements have their execution plans stored in the SQL plan cache. Altibase stores new execution plans of the following statements in the SQL plan cache and all sessions share them. For further information about each statement, please refer to the SQL Reference.

SELECT (SELECT FOR UPDATE)
INSERT (INSERT SELECT)
UPDATE
DELETE
MOVE
ENQUEUE
DEQUEUE

To use the SQL plan cache, it is necessary to modify the values of some of the properties in the altibase.properties file accordingly. The properties that are related to the SQL plan cache are as follows. For more detailed information about each property, please refer to the General Reference > Chapter 2. Altibase Properties.

SQL_PLAN_CACHE_BUCKET_CNT

The number of buckets in the hash table.
SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO

The percentage of a hot area in an LRU list in an SQL plan cache to which frequently referenced execution plans are saved.
SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT

The number of execution contexts initially created when execution plans are generated.
SQL_PLAN_CACHE_SIZE

The maximum size of the SQL Plan Cache.

SQL plan cache-related performance views provide information on the SQL plan cache. For more detailed information about the columns in the performance views, please refer to the General Reference > Chapter 3. The Data Dictionary.

V$SQL_PLAN_CACHE_PCO

Displays information of plan cache objects.
V$SQL_PLAN_CACHE_SQLTEXT

Information about SQL statements registered in the Plan Cache. You can check the utilization of SQL statements by joining with V$SQL_PLAN_CACHE_PCO.

Restrictions#

To reuse an execution plan registered in the SQL Plan Cache, the statement and parameter values of the SQL statement must be identical. Since the SQL Plan Cache was implemented to eliminate parsing costs, the Altibase server assumes SQL statements with non-identical parameter values as different statements.

For example, the SQL Plan Cache assumes tthat the following two statements are different.

INSERT INTO t1 VALUES( 1, 2 );
INSERT INTO t1 VALUES( 2, 3 );

To reuse execution plans registered in the SQL Plan Cache and enhance performance, a SQL statement must be written as below.

INSERT INTO t1 VALUES( ?, ? )

Example#

<Query 1> Output how many times execution plans in the SQL Plan Cache have been reused.

iSQL> SELECT MAX_CACHE_SIZE
     , CACHE_HIT_COUNT
     , CACHE_MISS_COUNT
  FROM V$SQL_PLAN_CACHE;
MAX_CACHE_SIZE       CACHE_HIT_COUNT      CACHE_MISS_COUNT     
-------------------------------------------------------------------
67108864             67288                769437

If the percentage for CACHE_MISS_COUNT is higher than CACHE_HIT_COUNT, the user is recommended to increase SQL_PLAN_CACHE_SIZE or change to a prepare-execute environment.

<Query 2> Output the reason why execution plans in the SQL Plan Cache are not reused and newly generated.

iSQL> SELECT CREATE_REASON
           , COUNT(*)
        FROM V$SQL_PLAN_CACHE_PCO
       GROUP BY CREATE_REASON;
CREATE_REASON                 COUNT
------------------------------------------------------
CREATED_BY_CACHE_MISS         1241
CREATED_BY_PLAN_TOO_OLD       32
CREATED_BY_PLAN_INVALIDATION  16

If the value for CREATE_BY_CACHE_MISS is high, it is possible that the size for SQL Plan Cache is small or literal SQL has been highly used.

If the value for CREATE_BY_PLAN_INVALIDATION is high, it is possible that object definitions (tables, indexes) have been changed frequently.

<Query 3> Output how many times execution plans in the SQL plan cache have been used.

iSQL> SELECT SQL_TEXT
     , CHILD_PCO_COUNT
     , HIT_COUNT
     , REBUILD_COUNT
  FROM V$SQL_PLAN_CACHE_SQLTEXT A
     , V$SQL_PLAN_CACHE_PCO B
 WHERE A.SQL_TEXT_ID = B.SQL_TEXT_ID
 ORDER BY 2 DESC;
 SQL_TEXT            CHILD_PCO_COUNT     HIT_COUNT     REBUILD_COUNT 
-------------------------------------------------------------------- 
SELECT * FROM t1     8                   0             7

If the Value for CHILD_PCO_COUNT is high, the user should check whether the SQL is the same but the object owners are different, or whether the referenced object is frequently changed.

Overview of the Result Cache#

The Result Cache is a function retrieving intermediate or final results of the initially executed query that are stored in the server when a query identical to the initial query is executed.

Altibase supports the Result Cache in which the intermediate result can be reused, and the Top Result Cache, which can also reuse the final results.

Result Cache Concept#

The execution plan(execution plan, plan tree) of intermediate result should be stored in the server in order to omit the subordinate execution plan of the identical query, which might be executed once more for the next time. But the related tables must stay the same with the stored execution plan. Rerun the query, and discard the stored values in the server if the Execution plan possible to use Result Cache are as follows.

[Table 7-1] Execution Plan with Result Cache enabled

Execution Plan	Contents
SORT	Order by, Join, Group by
DISTINCT	Distinct
MATERIALIZATION	View, Connect by
GROUP-AGGREGATION	Group Aggregation
GROUP-CUBE	Group by cube
GROUP-ROLLUP	Group by rollup
HASH	Join
SET-DIFFERENCE	Minus
SET-INTERSECT	Intersect
WINDOW-SORT	Over clause
LIMIT-SORT	Limit

Top Result Cache Concept#

If you add an execution plan with stored final result of query to existing execution plan and store it to the server; then, the subordinate execution plan can be omitted.

The tables related to the stored execution plan must remain the same in order to use Result Cache. Rerun the query, and discard the values stored in the server If the related tables are modified.

[Table 7-2] Comparison of Result Cache and Top Result Cache

	Result Cache	Top Result Cache
Execution Plan	An execution plan is not modified.	An execution plan is modified.
Execution Speed	The initial performance speed stays the same as previous speed of not using cache.	The initial performance speed might be slowed due to the addition of final results added in the execution plan; however, it can be faster than the result cache in the second time.
Cache Range	Some parts of a query can execute the cache.	Only the final result can get the cache executed.
Effect on Table modification	The function of cache is not necessary when the tables used in a query are modified.

RESULT_CACHE#

Cache intermediate results by aligning t1 table with i1.

SELECT /*+ RESULT_CACHE */ * FROM t1 ORDER BY i1;

TOP_RESULT_CACHE#

Cache the final results by aligning t1 table with i1

SELECT /*+ TOP_RESULT_CACHE */ * FROM t1 ORDER BY i1;

Some of the properties in the altibase.properties file should be appropriately modified to serve the purpose. Refer to the General Reference > Chapter 2. Altibase Properties for detailed information on each property

RESULT_CACHE_ENABLE#

This property sets the Result_Cache function available for use.

RESULT_CACHE_MEMORY_MAXIMUM#

This property sets the maximum of storage memory when using the Result Cache and Top Result Cache.

TOP_RESULT_CACHE_MODE#

This property sets the caches which the final result of a query is available for use.

Restrictions#

The restrictions in which you cannot use the Result Cache are in the followings.

The Result Cache cannot be used in the functions specified below.
- Non-deterministic Function
- RANDOM
- RANDOM_STRING
- SYS_GUID
- SYS_GUID_STR
- SYSDATE
- SYSTIMESTAMP
- UNIX_DATE
- UNIX_TIMESTAMP
The Result Cache cannot be used in the tables and performance views stipulated below.
- Temporary Table
- Fixed Table
- Disk Temp Table
- Dump Table
- The remote table used in DB link
The Result Cache in a _PROWID column cannot be used.
The Result Cache cannot be used in the disk tables in which NO_PUSH_PROJECTION hint is used.
The Result Cache cannot be used after activating use REDUCE_TEMP_MEMORY property.
The Result Cache cannot be used for encrypted columns.
The Result Cache cannot be used when it is used in Prior a column or a LOB column.
The Result Cache cannot be used when the column value of disk partitioned table is used in the temporary table.

Precautions#

The usable range of Result Cache varies depending on the Commit mode.

2nd Session (Right) 1st Session (Bottom)	AUTO COMMIT ON	AUTO COMMIT OFF
AUTO COMMIT ON	Possible to use.	Impossible to use. Re-execute after free up cache in the memory.
AUTO COMMIT OFF	Impossible to use. Reuse after clearing cache	Possible to use within identical transactions.

Example#

<Query 1> Check to see if the execution plan has been changed by using the Result Cache.

iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_MTRNODE = 1;
Alter success.

iSQL> SELECT /*+ RESULT_CACHE */ e.e_lastname
           , e.eno
        FROM employees e
       ORDER 2 BY e.dno, e.e_firstname 
       LIMIT 3;
E_LASTNAME            ENO
-------------------------------------
Kobain                3
Chen                  16
Jones                 13
3 rows selected.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
[ TARGET INFO ]
sTargetColumn[0] : [0, 1],sTargetColumn->arg[X, X]
sTargetColumn[1] : [0, 0],sTargetColumn->arg[X, X]
 LIMIT-SORT ( ITEM_SIZE: 16, ITEM_COUNT: 20, STORE_COUNT: 3, ACCESS: 3, COST: 0.02 )
 [ RESULT CACHE HIT: 0, MISS: 0, SIZE: 65536, STATUS: INIT ]
 [ myNode NODE INFO, SELF: 1, REF1: 0, REF2: -1 ]
 sMtrNode[0] : src[0, ROWPTR],dst[1, 0]
 sMtrNode[1] : src[0, *5],dst[1, 1]
 sMtrNode[2] : src[0, *2],dst[1, 2]
  SCAN ( TABLE: SYS.EMPLOYEES E, FULL SCAN, ACCESS: 20, COST: 0.02 )
  [ SELF NODE INFO, SELF: 0 ]
-----------------------------------------------------------
* AUTO STATISTICS USED: 2

iSQL> SELECT /*+ RESULT_CACHE */ e.e_lastname
           , e.eno
        FROM employees e
       ORDER BY e.dno, e.e_firstname 
       LIMIT 3;
E_LASTNAME            ENO
-------------------------------------
Kobain                3
Chen                  16
Jones                 13
3 rows selected.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
[ TARGET INFO ]
sTargetColumn[0] : [0, 1],sTargetColumn->arg[X, X]
sTargetColumn[1] : [0, 0],sTargetColumn->arg[X, X]
 LIMIT-SORT ( ITEM_SIZE: 16, ITEM_COUNT: 20, STORE_COUNT: 3, ACCESS: 3, COST: 0.02 )
 [ RESULT CACHE HIT: 1, MISS: 0, SIZE: 65536, STATUS: HIT ]
 [ myNode NODE INFO, SELF: 1, REF1: 0, REF2: -1 ]
 sMtrNode[0] : src[0, ROWPTR],dst[1, 0]
 sMtrNode[1] : src[0, *5],dst[1, 1]
 sMtrNode[2] : src[0, *2],dst[1, 2]
  SCAN ( TABLE: SYS.EMPLOYEES E, FULL SCAN, ACCESS: 0, COST: 0.02 )
  [ SELF NODE INFO, SELF: 0 ]
-----------------------------------------------------------
* AUTO STATISTICS USED: 2

<Query 2> Check to see if the execution plan has been changed by using the Top Result Cache.

iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_MTRNODE = 1;
Alter success.

iSQL> SELECT /*+ TOP_RESULT_CACHE */ e.e_lastname
           , e.eno
        FROM employees e
       ORDER BY e.dno, e.e_firstname
       LIMIT 3;
E_LASTNAME            ENO
-------------------------------------
Kobain                3
Chen                  16
Jones                 13
3 rows selected.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
[ TARGET INFO ]
sTargetColumn[0] : [3, 0],sTargetColumn->arg[X, X]
sTargetColumn[1] : [3, 1],sTargetColumn->arg[X, X]
 MATERIALIZATION ( ITEM_SIZE: 40, ITEM_COUNT: 3, COST: 0.00 )
 [ RESULT CACHE HIT: 0, MISS: 0, SIZE: 65536, STATUS: INIT ]
 [ myNode NODE INFO, SELF: 3, REF1: 0, REF2: -1 ]
 sMtrNode[0] : src[2, 0],dst[3, 0]
 sMtrNode[1] : src[2, 1],dst[3, 1]
  VIEW ( ACCESS: 3, COST: 0.00 )
  [ MTRNODE INFO ]
  sNode[0] : [2, 0]
  sNode[1] : [2, 1]
   PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
   [ TARGET INFO ]
   sTargetColumn[0] : [0, 1],sTargetColumn->arg[X, X]
   sTargetColumn[1] : [0, 0],sTargetColumn->arg[X, X]
    LIMIT-SORT ( ITEM_SIZE: 16, ITEM_COUNT: 20, STORE_COUNT: 3, ACCESS: 3, COST: 0.02 )
    [ myNode NODE INFO, SELF: 1, REF1: 0, REF2: -1 ]
    sMtrNode[0] : src[0, ROWPTR],dst[1, 0]
    sMtrNode[1] : src[0, *5],dst[1, 1]
    sMtrNode[2] : src[0, *2],dst[1, 2]
     SCAN ( TABLE: SYS.EMPLOYEES E, FULL SCAN, ACCESS: 20, COST: 0.02 )
     [ SELF NODE INFO, SELF: 0 ]
-----------------------------------------------------------
* AUTO STATISTICS USED: 2

iSQL> SELECT /*+ TOP_RESULT_CACHE */ e.e_lastname
           , e.eno
        FROM employees e
       ORDER BY e.dno, e.e_firstname 
       LIMIT 3;
E_LASTNAME            ENO
-------------------------------------
Kobain                3
Chen                  16
Jones                 13
3 rows selected.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
[ TARGET INFO ]
sTargetColumn[0] : [3, 0],sTargetColumn->arg[X, X]
sTargetColumn[1] : [3, 1],sTargetColumn->arg[X, X]
 MATERIALIZATION ( ITEM_SIZE: 40, ITEM_COUNT: 3, COST: 0.00 )
 [ RESULT CACHE HIT: 1, MISS: 0, SIZE: 65536, STATUS: HIT ]
 [ myNode NODE INFO, SELF: 3, REF1: 0, REF2: -1 ]
 sMtrNode[0] : src[2, 0],dst[3, 0]
 sMtrNode[1] : src[2, 1],dst[3, 1]
  VIEW ( ACCESS: 0, COST: 0.00 )
  [ MTRNODE INFO ]
  sNode[0] : [2, 0]
  sNode[1] : [2, 1]
   PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 28, COST: 0.03 )
   [ TARGET INFO ]
   sTargetColumn[0] : [0, 1],sTargetColumn->arg[X, X]
   sTargetColumn[1] : [0, 0],sTargetColumn->arg[X, X]
    LIMIT-SORT ( ITEM_SIZE: 0, ITEM_COUNT: 0, STORE_COUNT: 0, ACCESS: 0, COST: 0.02 )
    [ myNode NODE INFO, SELF: 1, REF1: 0, REF2: -1 ]
    sMtrNode[0] : src[0, ROWPTR],dst[1, 0]
    sMtrNode[1] : src[0, *5],dst[1, 1]
    sMtrNode[2] : src[0, *2],dst[1, 2]
     SCAN ( TABLE: SYS.EMPLOYEES E, FULL SCAN, ACCESS: 0, COST: 0.02 )
     [ SELF NODE INFO, SELF: 0 ]
-----------------------------------------------------------
* AUTO STATISTICS USED: 2

Administrator

Reference

Developer

Tools

External Tools

7. SQL Plan Cache#

Overview of the SQL Plan Cache#

Structure of the SQL Plan Cache#

Advantages of the SQL Plan Cache#

Managing the SQL Plan Cache#

SQL Plan Cache Policies#

Controlling the SQL Plan Cache#

Statements that Use the SQL Plan Cache#

Restrictions#

Example#

Overview of the Result Cache#

Result Cache Concept#

Top Result Cache Concept#

RESULT_CACHE#

TOP_RESULT_CACHE#

RESULT_CACHE_ENABLE#

RESULT_CACHE_MEMORY_MAXIMUM#

TOP_RESULT_CACHE_MODE#

Restrictions#

Precautions#

Example#

7. SQL Plan Cache#

Overview of the SQL Plan Cache#

Structure of the SQL Plan Cache#

Advantages of the SQL Plan Cache#

Managing the SQL Plan Cache#

SQL Plan Cache Policies#

Controlling the SQL Plan Cache#

Statements that Use the SQL Plan Cache#

SQL Plan Cache-related Properties#

SQL Plan Cache-related Performance Views#

Restrictions#

Example#

Overview of the Result Cache#

Result Cache Concept#

Top Result Cache Concept#

Result Cache-related Hints#

RESULT_CACHE#

TOP_RESULT_CACHE#

Result Cache-Related Properties#

RESULT_CACHE_ENABLE#

RESULT_CACHE_MEMORY_MAXIMUM#

TOP_RESULT_CACHE_MODE#

Restrictions#

Precautions#

Example#