1. Reference
  2. Built-In Procedures
  3. ttCacheAutorefreshStatsGet

ttCacheAutorefreshStatsGet

This procedure returns information about the last ten autorefresh transactions on the specified cache group. This information is only available when the AUTOREFRESH state is ON or PAUSED, and the cache agent is running.

The information returned by this built-in procedure is reset whenever:

  • The cache agent is restarted

  • The state is set to OFF and then back to ON or PAUSED

  • The cache group is dropped and recreated

Required Privilege

This procedure requires no privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in both TimesTen Classic and TimesTen Scaleout.

Related Views

This procedure has no related views.

Syntax

ttCacheAutorefreshStatsGet ('cgOwner', 'cgname')

Parameters

ttCacheAutorefreshStatsGet has the parameters:

Parameter Type Description

cgOwner

VARCHAR2 (30)

Name of the cache group owner.

cgName

VARCHAR2 (30) NOT NULL

Name of the cache group for which autorefresh information should be returned.

Result Set

The ttCacheAutorefreshStatsGet built-in procedure returns only a subset of column information for a cache group with autorefresh mode FULL. A column value of 0 returns for information that is not available.

ttCacheAutorefreshStatsGet returns the results:

Column Type Description Returned for full autorefresh

cgId

TT_BIGINT

The cache group ID.

Y

startTimestamp

TT_TIMESTAMP

Timestamp when autorefresh started for this interval.

Y

cacheAgentUpTime

TT_BIGINT

Number of cache agent clock ticks in milliseconds at the time the autorefresh transaction started for this interval. This value is cumulative and is reset when the cache agent process starts.

Y

autorefNumber

TT_BIGINT

Autorefresh number for a cache group indicates the number of times this cache group has been incrementally refreshed since the cache agent started. This number is initialized to 0 when the cache agent is started.

Y

autorefDuration

TT_BIGINT

The number of milliseconds spent in this autorefresh transaction.

Y

autorefNumRows

TT_BIGINT

The number of rows autorefreshed in this autorefresh. This includes all rows, including those in the root table and the child tables.

If there are cache groups with multiple tables, child table rows get updated multiple times. Therefore, the number of rows autorefreshed may be more than the number of rows updated on the Oracle database.

N

numOracleBytes

TT_BIGINT

The number of bytes transferred from the Oracle database in this autorefresh transaction.

N

autorefNumRootTblRows

TT_BIGINT

The number of root table rows autorefreshed in this autorefresh transaction.

Y

autorefQueryExecDuration

TT_BIGINT

The duration in milliseconds that it takes for the autorefresh query to execute on the Oracle database.

N

autorefQueryFetchDuration

TT_BIGINT

The duration in milliseconds that it takes for the autorefresh query to fetch rows from the Oracle database.

N

autorefTtApplyDuration

TT_BIGINT

The duration in milliseconds that it takes for TimesTen to apply the autorefresh.

N

totalNumRows

TT_BIGINT

The total number of rows autorefreshed since the cache agent started.

The total number of rows autorefreshed may not be the same as number of rows updated on the Oracle database. This is because of a delay in marking the log; some updates may get autorefreshed and counted multiple times.

N

totalNumOracleBytes

TT_BIGINT

The total number of bytes transferred from the Oracle database since the cache agent started.

N

totalNumRootTblRows

TT_BIGINT

The total number of root table rows autorefreshed since the cache agent started.

Y

totalDuration

TT_BIGINT

The total autorefresh duration in milliseconds since the cache agent started.

Y

status

VARCHAR2 (128)

A string description of the status of the current autorefresh. Supported values for this field are:

Complete

inProgress

Failed

Y

numlogrows

TT_BIGINT

Number of rows fetched from the Oracle database in this autorefresh.

Y

totalnumlogrows

TT_BIGINT

The cumulative number of rows fetched from the Oracle database in this autorefresh.

Y

autorefLogFragmentationPct

TT_BIGINT

A low-water mark for table usage by percentage. If less than the specified percent of the table is used, the table is compacted.

Y

autorefLogFragmentationTs

TT_TIMESTAMP

The timestamp when the last utilization/ fragmentation ratio was calculated

Y

autorefLogDefragGcnt

TT_BIGINT

The number of times the table has been compacted.

Y

Notes

  • Most of the column values reported above are collected at the cache group level. For example, autorefDuration and autorefNumRows only include information for the specified cache group. Exceptions to this rule are column values cacheAgentUpTime, startTimestamp and autorefreshStatus. These values are reported at the autorefresh interval level.

  • StartTimestamp is taken at the beginning of the autorefresh for the autorefresh interval. A cache group enters the in progress state as soon as the autorefresh for the interval starts. It is not marked complete until the autorefresh for all cache groups in the interval are complete.

Examples

In this example, testcache is a READONLY cache group with one table and an incremental autorefresh interval of 10 seconds. 

Command> call ttcacheautorefreshstatsget('user1','testcache');

< 1164260, 2011-07-23 15:43:52.000000, 850280, 44, 
0, 75464, 528255, 75464, 310, 110, 6800, 1890912, 
12439795, 1890912, 160020, InProgress, 2, 74 >
< 1164260, 2011-07-23 15:43:33.000000, 831700, 43, 
13550, 108544, 759808, 108544, 1030, 230, 12290, 1815448, 
11911540, 1815448, 160020, Complete, 2, 72 >
< 1164260, 2011-07-23 15:43:12.000000, 810230, 42, 
17040, 115712, 809984, 115712, 610, 330, 16090, 1706904, 
11151732, 1706904, 146470, Complete, 2, 70>
< 1164260, 2011-07-23 15:42:52.000000, 790190, 41, 
14300, 94208, 659456, 94208,560, 320, 13410, 1591192, 
10341748, 1591192, 129430, Complete, 2, 68 >
< 1164260, 2011-07-23 15:42:32.000000, 770180, 40, 
12080, 99328, 695296, 99328,450, 290, 11340, 1496984, 
9682292, 1496984, 115130, Complete, 2, 66 >
< 1164260, 2011-07-23 15:42:12.000000, 750130, 39, 
10380, 86016, 598368, 86016,430, 230, 9720, 1397656, 
8986996, 1397656, 103050, Complete, 2, 64 >
< 1164260, 2011-07-23 15:41:52.000000, 730130, 38, 
13530, 112640, 700768, 112640, 530, 220, 12780, 1311640, 
8388628, 1311640, 92670, Complete, 2, 62 >
< 1164260, 2011-07-23 15:41:32.000000, 710120, 37, 
9370, 56320, 326810, 56320, 310, 160, 8900, 1199000, 
7687860, 1199000, 79140, Complete, 2, 60 >
< 1164260, 2011-07-23 15:41:22.000000, 700120, 36, 
2120, 10240, 50330, 10240, 50, 200, 1870, 1142680, 
7361050, 1142680, 69770, Complete, 2, 58 >
< 1164260, 2011-07-23 15:41:12.000000, 690110, 35, 
0, 0, 0, 0, 0, 0, 0, 1132440, 7310720, 1132440, 
67650, Complete, 2, 56 >
10 rows found.
This procedure is available only for cache operations.