Skip Headers
Oracle® TimesTen Application-Tier Database Cache User's Guide
11g Release 2 (11.2.2)

E21634-12
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

10 Using the Cache Advisor

The following sections describe and demonstrate how to use the TimesTen Application-Tier Database Cache Advisor (TimesTen Cache Advisor):

Cache Advisor overview

The TimesTen Cache Advisor enables Oracle Database customers to determine whether the performance of an existing Oracle Database application can be improved if the application is used with TimesTen Cache, also referred to as a TimesTen database.

Cache Advisor generates recommendations of TimesTen cache group definitions based on the SQL usage in the Oracle Database application. It does this by evaluating either a captured SQL workload from the application or an existing SQL tuning set. Cache Advisor analyzes this information along with the schema definitions of the Oracle Database objects to determine table and column usage patterns. Cache Advisor also analyzes application performance for specified TimesTen Cache sizes, so the cache group recommendations may differ depending on the size of the specified cache. For information on SQL tuning sets, see "Managing SQL Tuning Sets" in the Oracle Database 2 Day + Performance Tuning Guide.

When evaluating the captured SQL application workload or SQL tuning set, Cache Advisor recommends either using asynchronous writethrough (AWT) cache groups or read-only cache groups in the TimesTen application. It determines the type of cache groups to use based on the number of SQL statement executions in the Oracle Database application that change data values relative to the number of SQL SELECT statement executions.

Note:

Cache Advisor evaluates DML statements (SELECT, INSERT, UPDATE and DELETE) for execution porting issues, but evaluates only SELECT statements on both TimesTen and Oracle Database for the performance comparison.

Cache Advisor evaluates each SQL statement in isolation from any other statement. If necessary, it performs a ROLLBACK after each statement completes to preserve data. If Cache Advisor were to commit all DML changes, then the data would change, which would alter the behavior of any subsequent Cache Advisor evaluations. For example, if Cache Advisor evaluates and commits a DELETE statement on Oracle Database, then when the Cache Advisor performs the evaluation again, there would be no rows to delete.

After analyzing the application workload or SQL tuning set, and comparing its performance between Oracle Database and TimesTen Cache, Cache Advisor generates an HTML report that contains performance statistics comparing Oracle Database and TimesTen Cache, definitions of the recommended cache tables in the TimesTen cache group that the application accesses, and the SQL statements that reference the cache tables. The report also shows which statements from the workload or SQL tuning set can be executed in TimesTen Cache with no changes, and which statements require modification before they can be executed. See Appendix C, "Compatibility Between TimesTen and Oracle Databases" for information about differences that may be encountered.

Cache Advisor also generates a ttIsql script that can be used to implement the recommended cache group definitions. The user-editable script contains SQL statements such as CREATE CACHE GROUP, LOAD CACHE GROUP, CREATE INDEX, CREATE SYNONYM, and CREATE VIEW.

Cache Advisor requires the use of up to three databases:

  • A target Oracle database on which the user application runs and where the application schema resides. This is where the SQL workload is captured. The workload executing on this database should be as close to the production database workload as possible. In addition, the Cache Advisor relies on statistics in the Oracle target database to calculate the table sizing in TimesTen. Users should ensure statistics in the target Oracle database are collected and are up to date.

  • A repository Oracle database where Cache Advisor performs analysis of the workload of SQL statements that are executed on the target Oracle database.

    Note:

    If the target database is part of a production system, place the Cache Advisor repository on a separate, non-production database. If the target database is part of a test system, place the Cache Advisor repository on the same database as the target to simplify setup and operation.
  • A TimesTen database, also referred to as an TimesTen Cache, where Cache Advisor defines and evaluates the recommended cache groups whose cache tables correspond to the tables in the target Oracle database that the application workload accesses.

Figure 10-1 Demonstration of the three databases used by the Cache Advisor

Description of Figure 10-1 follows
Description of "Figure 10-1 Demonstration of the three databases used by the Cache Advisor"

Setting up the Oracle Database and TimesTen host systems

Before you can use Cache Advisor, you must first install TimesTen and then configure the Oracle Database and TimesTen systems.

Note:

See the Oracle TimesTen In-Memory Database Installation Guide for information about installing TimesTen.

The following sections provide an example to show how to configure each database and host in order to execute the Cache Advisor. This example uses one of the Quick Start sample programs as a demonstration for the application that executes the SQL workload.

To set up the Oracle Database and TimesTen hosts and databases, complete the following tasks:

  1. Configure the target Oracle database

  2. Configure the repository Oracle database

  3. Configure the TimesTen database

See "Cache Advisor configuration options and usage guidelines" for details on the configuration options and usage guidelines when installing and configuring each host and database included in the Cache Advisor environment.

Configure the target Oracle database

The target Oracle database is where the application schema is defined. This is the database that the user application accesses. Cache Advisor requires that the version of the target database be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later.

To set up the target database for use by TimesTen Cache, start SQL*Plus from an operating system shell on the TimesTen database system and connect to the target database as the Oracle Database sys user. In this example, the net service name of the target database is targetdb.

% cd TimesTen_install_dir/oraclescripts
% sqlplus sys@targetdb as sysdba
Enter password: password

Use SQL*Plus to create a default tablespace that is be used by both the Oracle Database timesten user and the cache administration user. This tablespace must only be used to store objects for TimesTen Cache and should not be shared with other applications. In this example, the name of the default tablespace is cachetblsp. For more information about the timesten user, see "Create users in the Oracle database".

Run the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql to create the timesten user and its metadata tables and the TT_CACHE_ADMIN_ROLE role that defines privileges to be granted to this user. Pass the default tablespace as an argument to the initCacheGlobalSchema.sql script.

SQL> CREATE TABLESPACE cachetblsp DATAFILE 'datfttuser.dbf' SIZE 100M;
SQL> @initCacheGlobalSchema "cachetblsp"

Next, use SQL*Plus to create a target Oracle Database user, if this user does not already exist.

Note:

Since this example is using the Quick Start sample program, the example creates oratt as the schema owner.

The target Oracle Database user owns the Oracle Database objects that are to be accessed by the SQL workload application and are candidates for caching in a TimesTen database. The target Oracle Database user is the same as the schema user that is described in "Create users in the Oracle database".

Grant this user at least the minimum set of privileges required to create tables in the Oracle database to be cached in a TimesTen database. In this example, the target Oracle Database user is oratt.

SQL> CREATE USER oratt IDENTIFIED BY oracle;
SQL> GRANT CREATE SESSION, RESOURCE TO oratt;

Then use SQL*Plus to create a cache administration user. Run the SQL*Plus script TimesTen_install_dir/oraclescripts/grantCacheAdminPrivileges.sql to grant the cache administration user the minimum set of privileges required to process cache group operations. For information on cache groups, see "Cache groups and cache tables".

Note:

The target Oracle Database user and the cache administration user must be different users. In addition, when you create the repository Cache Advisor user, this user must also be a different user.

Pass the cache administration user name as an argument to the grantCacheAdminPrivileges.sql script. In this example, the cache administration user is cacheuser and the name of its default tablespace is cachetblsp. For more information about the cache administration user, see "Create users in the Oracle database".

SQL> CREATE USER cacheuser IDENTIFIED BY oracache
   DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
SQL> GRANT SELECT ANY TABLE, DELETE ANY TABLE,
   INSERT ANY TABLE, UPDATE ANY TABLE TO cacheuser;
SQL> @grantCacheAdminPrivileges "cacheuser"

Run the SQL*Plus script TimesTen_install_dir/oraclescripts/ttca_setupTarget.sql to perform the following operations:

  • Create the TTCA_TARGET_ROLE role that defines privileges to be granted to the target Oracle Database user.

  • Create an Oracle directory object named TTCA_DIRECTORY that is used for file operations into and out of the target database. The ttca_setupTarget.sql script associates the TTCA_DIRECTORY directory object with the local directory that the target Cache Advisor user created earlier. See "CREATE DIRECTORY" in the Oracle Database SQL Language Reference for information about Oracle Database directory objects.

After running the ttca_setupTarget.sql script, exit the SQL*Plus session.

The following example shows the output when the TTCA_DIRECTORY object has not yet been created:

SQL> @ttca_setupTarget

This script sets up your target Oracle database for use with the TimesTen
Cache Advisor.
 
The target Oracle database is the application database that you wish to cache
using TimesTen Application-Tier Database Cache.
 
This script performs the following actions:
1. Create the TTCA_TARGET_ROLE role, if it does not already exist
2. Create a new directory object on the target Oracle database,
   if directed by you to do so
3. Grant read and write access on a new or existing target Oracle database
   directory object to the TTCA_TARGET_ROLE role
4. Grant the TTCA_TARGET_ROLE role to the target Oracle database user
 
Run this script as SYSDBA on the target database.
 
(You will also need to configure a repository Oracle database that you set up
with the ttca_setupRepository.sql script.)
 
Please enter a target Oracle database user name to access the target database:
 oratt
 
The TimesTen Cache Advisor requires the use of a directory object on the
target Oracle database for DataPump and file operations.
 
An Oracle directory object is created with the CREATE DIRECTORY Oracle Data
Definition Language (DDL) statement. A directory object is identified by an
Oracle object name (up to 30 alphanumeric characters) and is associated with
a host-platform-specific directory path (up to 4000 characters).
 
********************************************************************************
*** Enter the directory path on the target system to use in the definition
*** of TTCA_DIRECTORY
********************************************************************************
? /mysystem/mydirectory/ttca_directory
 
 
Create TTCA_DIRECTORY directory object succeeded.
 
Grant READ and WRITE on TTCA_DIRECTORY directory object to TTCA_TARGET_ROLE role
succeeded.
 
Grant READ and WRITE on TTCA_DIRECTORY directory object to scott role succeeded.
To revoke privileges granted by this script:
REVOKE ttca_target_role FROM oratt;
DROP ROLE ttca_target_role;
REVOKE READ, WRITE ON DIRECTORY TTCA_DIRECTORY FROM oratt;
No errors.
 
*******************
**** All done! ****
*******************

If the TTCA_DIRECTORY object has already been created, the script notices and acknowledges that it already exists:

*** The directory object used by cache Advisor (TTCA_DIRECTORY) already exists.
*** Please press ENTER to continue
********************************************************************************
?

Configure the repository Oracle database

The repository Oracle database is where Cache Advisor performs analysis of the SQL workload that is being run on the target Oracle database. Cache Advisor also does report and script generation in the repository database, as well as store tasks. A task is an object that contains information about the workload, performance results, and Cache Advisor options that are specified by the user.

  • If the target database is part of a production system, place the Cache Advisor repository on a separate, non-production database. First install and configure a repository database of Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later. The version of the repository database must be the same or later than the version of the target Oracle database. Because the Cache Advisor copies schema definitions from the target database to the repository database when using separate target and repository databases, the separate repository database should be devoted to the Cache Advisor. When you are ready to clean up the Cache Advisor repository, simply drop the database.

  • If the target database is part of a test system, place the Cache Advisor repository on the same database as the target for simplification of setup and operation. When you are ready to clean up the Cache Advisor repository, execute the DROP USER TTCACHEADVISOR CASCADE statement.

Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the repository database as the Oracle Database sys user. In this example, the net service name of the repository database is repositorydb.

% cd TimesTen_install_dir/oraclescripts
% sqlplus sys@repositorydb as sysdba
Enter password: password

You must connect to the repository database with the SYSDBA privilege.

Run the SQL*Plus script TimesTen_install_dir/oraclescripts/ttca_setupRepository.sql to perform the following operations:

  • Create a user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database, and create the ttca_ts tablespace used to store these objects.

  • Create or specify an Oracle Database directory object used for file operations into and out of the repository database. The ttca_setupRepository.sql script associates the directory object with the local directory that the repository Cache Advisor user created earlier. See "CREATE DIRECTORY" in Oracle Database SQL Language Reference for information about Oracle Database directory objects.

After running the ttca_setupRepository.sql script, exit the SQL*Plus session.

SQL> @ttca_setupRepository

This script sets up your repository Oracle database for use with the TimesTen
Cache Advisor.
 
The repository Oracle database is used by the TimesTen Cache Advisor as
an analytical workspace.
 
This script performs the following actions:
1. Create repository Oracle database user TTCACHEADVISOR (or other user name
   that you specify)
2. Grant read and write access on a new or existing repository Oracle database
   directory object to the repository database user
3. Create tablespace TTCA_TS, if it does not exist
4. Grant required privileges to the repository database user
5. Create required tables and views owned by the repository database user
 
Run this script as SYSDBA on the repository database.
 
(You will also be using a target Oracle database that you set up
with the ttca_setupTarget.sql script.)
 
Press ENTER to create the repository Oracle database user with
user name TTCACHEADVISOR, or enter an alternative user name for
the repository database user:
Please enter a password for the TTCACHEADVISOR user:
Please confirm the password for the TTCACHEADVISOR user:
 
The TimesTen Cache Advisor requires the use of a directory object on the
repository Oracle database for DataPump and file operations.
 
An Oracle directory object is created with the CREATE DIRECTORY Oracle Data
Definition Language (DDL) statement. A directory object is identified by an
Oracle object name (up to 30 alphanumeric characters) and is associated with
a host-platform-specific directory path (up to 4000 characters).
 
********************************************************************************
*** Enter the directory path on the repository system to use in the definition
*** of TTCA_DIRECTORY
********************************************************************************
? /mysystem/mydirectory/ttca_directory
 
Create TTCA_TS tablespace succeeded.
 
Create TTCACHEADVISOR user succeeded.
 
Create TTCA_DIRECTORY directory object succeeded.
 
*******************
**** All done! ****
*******************
 
To employ other directories as directory objects on the repository
database, grant READ,WRITE ON DIRECTORY <directory_name> TO TTCACHEADVISOR

If the TTCA_DIRECTORY object has already been created, the script notices and acknowledges that it already exists:

*** The directory object used by cache Advisor (TTCA_DIRECTORY) already exists.
*** Please press ENTER to continue
********************************************************************************
?

If the TT_CS tablespace has already been created, the following statement does not appear in the output:

Create TTCA_TS tablespace succeeded.

Configure the TimesTen database

The TimesTen database is where Cache Advisor defines and evaluates the recommended cache groups whose cache tables correspond to the tables in the target Oracle database. The TimesTen database is a test database to be used only by Cache Advisor and should not be shared with other applications.

In the following data source name (DSN) example, the net service name of the target Oracle database is targetdb and its database character set is AL32UTF8. The TimesTen database character set must match the database character set of the target Oracle database. You can determine the database character set of an Oracle database by executing the following query in SQL*Plus as any user:

SQL> SELECT value FROM nls_database_parameters 
 WHERE parameter='NLS_CHARACTERSET';

In the .odbc.ini file that resides in your home directory or the TimesTen_install_dir/info/sys.odbc.ini file, create a TimesTen DSN cacheadv and set the following connection attributes:

Note:

In this example, Cache Advisor sets the CacheGridEnable attribute to 0, so that the user is not required to create a grid. For more details, see "CacheGridEnable" in the Oracle TimesTen In-Memory Database Reference.
[cacheadv]
DataStore=/users/OracleCache/cacheadv
PermSize=64
OracleNetServiceName=targetdb
DatabaseCharacterSet=AL32UTF8
CacheGridEnable=0

Note:

See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that caches data from an Oracle database.

See "Managing TimesTen Databases" in the Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen DSNs.

Set up the TimesTen database for use by TimesTen Cache. Start the ttIsql utility on the TimesTen system from an operating system shell and connect to the cacheadv DSN as the TimesTen instance administrator user to create the TimesTen database that is to be used to cache data from the target Oracle database.

% ttIsql cacheadv

Use ttIsql to create a cache manager user, where there is a companion Oracle Database user with the same name. Grant this user at least the minimum set of privileges required to create and perform operations on cache groups. In the following example, the cache manager user name is cacheuser and the cache administration user is acting as its companion Oracle Database user.

Command> CREATE USER cacheuser IDENTIFIED BY ttcache;
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;

Note:

For more information about the cache manager user, see "Create the TimesTen users".

Next, use ttIsql to call the ttCacheUidPwdSet built-in procedure to set the cache administration user name and password. Then, exit the ttIsql session.

Command> call ttCacheUidPwdSet('cacheuser','oracache');
Command> exit

The cache administration user name and password need to be set only once in a TimesTen database. See "Set the cache administration user name and password" for information about how this setting is used in the TimesTen database.

Cache Advisor configuration options and usage guidelines

The following sections describe supported configuration options and guidelines for using the Cache Advisor:

Supported configuration options for hosts and databases

Cache Advisor supports the following configuration options for hosts and databases included in the Cache Advisor environment:

  • A single Oracle database, which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, serves as both the target and repository databases. The target Oracle database must be a test database and not a production database.

    The TimesTen database can reside either on a separate host system for a more accurate performance analysis or on the same host as the Oracle database for demonstration purposes.

    This is the preferred configuration as it offers the simplest setup and operation.

  • A single Oracle database, which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, serves as the target database with no repository or TimesTen databases.

    The target database can be part of a live, production system or part of a test system. This configuration is supported only with the -export command-line option.

    This configuration can capture an application SQL workload and schema definitions with deferred analysis. Use the -import option with one of the other supported configurations to perform the deferred analysis.

  • The target Oracle database (which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later), the repository Oracle database (which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later), and the TimesTen database are all separate databases residing on the same or separate host systems.

    The target Oracle database cannot be a version later than the repository Oracle database. For performance analysis with the -evalSqlPerf command-line option, the TimesTen database should reside on a separate host and the target Oracle database must be part of a test system, not a live production system. The repository database must be devoted to Cache Advisor in order to enable clean up.

    This configuration offers flexibility in the allocation of resources.

Restrictions and assumptions

The design of the cache schema recommended by Cache Advisor assumes that the user application establishes a connection to the TimesTen database and a separate connection to the target Oracle database.

Cache Advisor supports most TimesTen DSN attribute settings. However, Cache Advisor does not support the following attribute settings:

  • Temporary=1 (temporary or non-persistent TimesTen database)

  • TypeMode=1 (TimesTen data type mode)

  • DDLCommitBehavior=1 (do not automatically commit DDL statements)

  • DuplicateBindMode=1 (consider dynamic parameters with the same name as identical)

  • PLSQL=0 (disable the use of TimesTen PL/SQL)

  • DynamicLoadEnable=0 (disable dynamic loading of data from Oracle Database tables into TimesTen cache tables)

Running a SQL workload application

This example uses the OCI version of the throughput benchmark (tptbmOCI) to generate a SQL workload on the target Oracle database.

Build and run the demo program as any operating system user on the TimesTen system. The net service name of the target database is targetdb. The target Oracle Database user is oratt. The password of the oratt user is oracle. The application table is populated with 252 = 625 rows and the maximum number of SQL statements per transaction is 1000.

% cd TimesTen_install_dir/quickstart/sample_code/oci
% make tptbmOCI
% tptbmOCI -service targetdb -user oratt -key 25 -max 1000
Enter password for oratt : password
...
Load the oratt.vpn_users table with 625 rows of data
Run 10000 txns with 1 process: 80% read, 20% update, 0% insert, 0% delete

Running the Cache Advisor

While the tptbmOCI workload application is running on the target Oracle database, in a separate window run the ttCacheAdvisor utility on the TimesTen system from an operating system shell as the instance administrator user. Specify the target Oracle database, repository Oracle database, and TimesTen database involved in the evaluation.

% ttCacheAdvisor -oraTarget -oraConn "oratt@targetdb" \
 -oraRepository -oraConn "ttcacheadvisor@repositorydb" \
 -ttConn "DSN=cacheadv;UID=cacheuser" \
 -report /home/ttuser/CAreport -task sampletask -captureCursorCache 10 \
 -evalSqlPerf

Enter password for Oracle user oratt@targetdb: password
31.16:21:03 Info: beginning Oracle batch operation checkAuthorization on 
oratt@targetdb
31.16:21:03 Info: Oracle batch operation checkAuthorization completed
Enter password for Oracle user ttcacheadvisor@repositorydb: password
31.16:21:05 Info: beginning Oracle batch operation checkAuthorization on 
ttcacheadvisor@repositorydb
31.16:21:06 Info: Oracle batch operation checkAuthorization completed
31.16:21:06 Info: beginning Oracle batch operation checkOraUser on 
oratt@targetdb
31.16:21:06 Info: Oracle batch operation checkOraUser completed
31.16:21:06 Info: beginning TimesTen batch operation checkUserExists on 
"dsn=cacheadv;uid=cacheuser"
31.16:21:07 Info: TimesTen batch operation checkUserExists completed

Enter password for TimesTen user cacheuser (dsn=cacheadv): password
31.16:21:10 Info: beginning TimesTen batch operation checkTTuserAuthorization 
on "dsn=cacheadv;uid=cacheuser"
31.16:21:11 Info: TimesTen batch operation checkTTuserAuthorization completed
Enter password for Oracle user cacheuser@targetdb: password
31.16:21:14 Info: beginning Oracle batch operation checkTToraclepwdAttribute 
on cacheuser@targetdb
31.16:21:14 Info: Oracle batch operation checkTToraclepwdAttribute completed
31.16:21:14 Info: beginning Oracle batch operation verifyTargetConfig on 
oratt@targetdb
31.16:21:25 Info: Oracle batch operation verifyTargetConfig completed
...

The previous example used the ttCacheAdvisor utility options as follows:

  • The -oraTarget option identifies the target Oracle database. The -oraConn option for -oraTarget specifies the target Oracle Database user and the net service name of the target database in the connection string.

  • The -oraRepository option identifies the repository Oracle database. The -oraConn option for -oraRepository specifies the user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database and the net service name of the repository database in the connection string.

  • The -ttConn option identifies the TimesTen database. Specify the DSN and the cache manager user in the connection string.

  • The -report option overrides the default directory location where the report files reside.

  • The -task option overrides the default task name.

  • The -captureCursorCache option specifies that the ttCacheAdvisor utility capture the SQL workload running on the target database for a capture window duration of 10 minutes. If a percentage of SQL statements escape capture, use a longer capture duration.

  • The -evalSqlPerf option is specified to generate a performance comparison between the workload run on the target Oracle database and on the TimesTen database.

If the passwords are not specified in the connection strings for each database, the ttCacheAdvisor utility prompts for the passwords of each user connecting to the TimesTen and Oracle databases used in the Cache Advisor evaluation.

For this example, the following user passwords are requested:

  • The password for the target Oracle database user. In this example, the password of oratt@targetdb is oracle.

  • The password for the user that owns the objects in the repository Oracle database used to analyze the SQL workload run on the target Oracle database. In this example, the password of ttcacheadvisor@repositorydb is ttca.

  • The password for the TimesTen cache manager user. In this example, the password of cacheuser is ttcache.

  • The password of the cache administration user. In this example, the password of cacheuser@targetdb is oracache. This password is requested because the -evalSqlPerf option is specified to generate a performance comparison between the workload run on the target Oracle database and on the TimesTen database.

The ttCacheAdvisor utility generates periodic status messages as it analyzes the application workload running on the target database.

When ttCacheAdvisor completes, it creates an HTML report showing performance statistics as well as information such as which SQL statements from the workload can and cannot be executed in TimesTen. By default, the files that constitute the report reside in the task-name directory where the utility was invoked. In this example, the directory is specified with the -report option. To view the report, open the index.htm file in the report files directory from a web browser. The task name, by default, is userName_hostName_timestamp. In this example, the task name is overridden with the -task option.

The ttCacheAdvisor utility also generates an implementation script file named ttCacheAdvisor_taskName_timestamp.sql in the directory specified with the -report option. This script can be run with the ttIsql utility to create objects in the TimesTen database used to implement the caching of the Oracle Database objects that were accessed by the application.

% ttIsql -f ttCacheAdvisor_sampletask_20120531164101.sql
 "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"

For more information about the report and implementation script, see "Viewing the Cache Advisor reports".

For information about the syntax for ttCacheAdvisor, see "ttCacheAdvisor" in the Oracle TimesTen In-Memory Database Reference.

Improve performance by using NFS or FTP for transferring data

Note:

For details on the options mentioned in this section, see "ttCacheAdvisor" in the Oracle TimesTen In-Memory Database Reference.

Cache Advisor moves data into or out of the database using DataPump. DataPump requires an Oracle directory object with an associated directory path that is local and not network mounted. By default, Cache Advisor uses the TTCA_DIRECTORY Oracle directory object, which is created with the setup scripts, with the -export and -import options to export and import information using DataPump. Also, by default, Cache Advisor uses the OCI connection specified with the -oraConn option to transfer files. While more convenient and easy to use, the mechanism that Cache Advisor uses to transfer files over the OCI connection can be approximately three times slower than NFS or ftp. File transfer performance is only affected with the -export option, -import option, or the -captureCursorCache option when the target and repository are on different databases.

Note:

Configuring data file transfer options with the -oraDirNfs or -ftp options are optional and only relevant if you want to improve file transfer performance.

To improve file transfer performance, you can optionally use the -oraDirNfs or -ftp options. The TTCA_DIRECTORY directory object is not configured for use with the -oraDirNfs or -ftp options. So, you must specify and configure an alternate directory object with the -oraDirObject option for use with the -oraDirNfs or -ftp options.

To configure the directory,

  1. Log onto the Oracle database and create a new directory object. To create a directory object, execute the following command:

    CREATE DIRECTORY dir_name AS /local_dir_path/subdir_path;
    

    The directory path you specify must be a local directory and cannot be network mounted.

  2. Log onto the host system where the target or repository Oracle database resides and create a directory that matches the directory path associated with the Oracle directory object. This directory can be created by any operating system user on the target system. The directory must be created on a device that is local to the host system and not network mounted.

    The owner of the directory is referred to as the target Cache Advisor user. In the following example, the target Cache Advisor user is ca_usr and the directory is /local/ca_usr/ca_dir.

    % mkdir /local/ca_usr/ca_dir
    
  3. Determine the file system that the directory resides on. On Linux systems, this information can be obtained by running the df operating system command. In this example, the file system that the /local/ca_usr/ca_dir directory resides on is /dev/sda1.

  4. Cache Advisor must be able to access the contents of the DataPump dump files from the host system to perform its analysis. However, the permissions placed on those files by Data Pump prevent them from being accessed through NFS or transferred to the repository system using ftp. To access the dump files from the repository system, set an access control list (ACL) on the directory where the files will reside on the target system.

    As the operating system root user, enable the setting of ACLs on the file system.

    # mount -o remount,acl /dev/sda1
    
  5. Change the permissions on the directory so that only the Cache Advisor user can read from and write to it. Then, set ACLs on the directory and any files created in the directory to read, write, and execute for the Cache Advisor user and the operating system user that is running the Oracle Database server on the host system (typically the oracle user). On Linux systems, ACLs can be set by running the operating system setfacl command.

    % chmod 700 /local/ca_usr/ca_dir
    % setfacl -m u:ca_usr:rwx /local/ca_usr/ca_dir
    % setfacl -m d:u:ca_usr:rwx /local/ca_usr/ca_dir
    % setfacl -m u:oracle:rwx /local/ca_usr/ca_dir
    % setfacl -m d:u:oracle:rwx /local/ca_usr/ca_dir
    

Viewing the Cache Advisor reports

This section provides examples of the report pages generated by the Cache Advisor. The report can be viewed using the following Web browsers:

  • Firefox 3.6 or later

  • Chrome 7 or later

  • Safari 4 or later

To view the report, open the index.htm file in the report files directory from a Web browser.

If the -evalSqlPerf option was specified when the ttCacheAdvisor utility was executed, the report shows the average response time for the SQL SELECT statements that were executed in the target Oracle database. It also shows the average response time for these statements when executed in the TimesTen database with the user-specified cache size. The complete TimesTen Cache size is the minimum TimesTen database size required to cache all of the objects that were accessed by the SQL workload and can be supported by TimesTen.

Figure 10-2 Cache Advisor report home page

Description of Figure 10-2 follows
Description of "Figure 10-2 Cache Advisor report home page"

Figure 10-3 Cache Advisor findings and recommendations

Description of Figure 10-3 follows
Description of "Figure 10-3 Cache Advisor findings and recommendations"

You can view the SQL statements that were executed in the workload by clicking the link under the SQL Statements column on the home page that indicates the number of statements in the workload. In this case, click the link of the first 2 where it says "2 of 2".

Figure 10-4 Viewing the number of SQL statements executed in the workload

Description of Figure 10-4 follows
Description of "Figure 10-4 Viewing the number of SQL statements executed in the workload"

You can click an individual SQL statement to see the response time and other statistics for that statement. In this example, when you click the link of the second statement, you see the following information about the SELECT statement:

Figure 10-5 Information for a specific SQL statement executed during Cache Advisor evaluation

Description of Figure 10-5 follows
Description of "Figure 10-5 Information for a specific SQL statement executed during Cache Advisor evaluation"

You can click the name of the cache group to see the definition of the cache group and its cache tables, as well as the SQL statements that referenced the cache group. In this example, the following report page appears when you click the CG1_USERSPECCACHE link:

Figure 10-6 Cache group details

Description of Figure 10-6 follows
Description of "Figure 10-6 Cache group details"

Figure 10-7 SQL statements used for cache group

Description of Figure 10-7 follows
Description of "Figure 10-7 SQL statements used for cache group"

From the home page, you can access the text of the implementation script by clicking the "Configure an TimesTen Cache for your application" link. Then, from the next page, click the "Implementation Script" link.

Note:

For more details on the implementation script, see "Running the Cache Advisor".

The following shows an example of an implementation script:

SHOW ERRORS;
SET ECHO OFF;
SET DEFINE ON;
SET SERVEROUTPUT ON;
PROMPT Welcome to the TimesTen CacheAdvisor (ttCacheAdvisor).
PROMPT
PROMPT ttCacheAdvisor generated this script to implement its recommendations. 
PROMPT The first step is to create the necessary TimesTen user accounts to
PROMPT receive the recommended cache groups and associated indexes, views, 
PROMPT materialized views and sequences. This script will now prompt you for
PROMPT the password for each account to be created. Be sure to use the same 
PROMPT password for each TimesTen user account as on Oracle.
PROMPT
PROMPT NOTE: If an error occurs or for any reason, you can abort this script at
PROMPT any time by pressing the control and C keys (^C) simultaneously. 
PROMPT Sometimes it is necessary to press ^C multiple times followed by 
PROMPT pressing ENTER.
ACCEPT ORAUSER_pwd CHAR PROMPT 'Enter user ORAUSER password (use same password
as on Oracle)? 'HIDE
ACCEPT ORAUSER_pwd2 CHAR PROMPT 'Confirm user ORAUSER password? ' HIDE
COMMIT;
EXEC createUser ('ORAUSER','&&ORAUSER_pwd','&&ORAUSER_pwd2');
COMMIT;
DROP PROCEDURE createUser;
ACCEPT answer CHAR PROMPT 'Press ENTER to continue, ^C to abort:'
EXEC execImmediate ('CALL ttCacheStart');
EXEC execImmediate ('CALL ttRepStop');
EXEC execImmediate ('DROP CACHE GROUP CG1_USERSPECCACHE');
COMMIT;
COMMIT;
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CG1_USERSPECCACHE
 FROM ORAUSER.VPN_USERS
 ( VPN_ID NUMBER(5,0) NOT NULL,
   VPN_NB NUMBER(5,0) NOT NULL,
   DIRECTORY_NB CHAR(10 BYTE) NOT NULL,
   LAST_CALLING_PARTY CHAR(10 BYTE) NOT NULL,
   DESCR CHAR(100 BYTE) NOT NULL,
   PRIMARY KEY (VPN_ID, VPN_NB)
 );
COMMIT;
LOAD CACHE GROUP CG1_USERSPECCACHE COMMIT EVERY 256 ROWS;
COMMIT;
CALL ttOptUpdateStats ('ORAUSER.VPN_USERS',1);
COMMIT;
COMMIT;
COMMIT;
EXEC execImmediate ('CALL ttRepStart');
COMMIT;

The name of the script is ttCacheAdvisor_task-name_timestamp.sql and it resides in the directory where the ttCacheAdvisor utility was invoked. This script can be run with the ttIsql utility to create objects in the TimesTen database used to implement the caching of the Oracle Database objects that were accessed by the application.

% ttIsql -f ttCacheAdvisor_sampletask_20120531164101.sql 
 "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"

You can obtain database and system information about the target Oracle database (Workload Collection), repository Oracle database and TimesTen database (Client) by clicking the "Click here for information about the configuration that was used to generate this report" link from the home page.

Figure 10-8 Configuration overview page

Description of Figure 10-8 follows
Description of "Figure 10-8 Configuration overview page"

Figure 10-9 Repository and client configuration information

Description of Figure 10-9 follows
Description of "Figure 10-9 Repository and client configuration information"

Cleaning up the Oracle and TimesTen databases and host systems

Complete the following tasks to restore the Oracle Database and TimesTen systems to their original state after you have finished evaluating the application workload that was run on the target Oracle database:

  1. Clean up the target Oracle database and host system

  2. Clean up the repository Oracle database and host system

  3. Clean up the TimesTen database and host system

Clean up the target Oracle database and host system

Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the target Oracle database as the sys user. Then, use SQL*Plus as follows to clean up the target Oracle database and its host system:

  1. Drop the timesten user, the oratt target Oracle Database user (if you created this user because it did not exist before configuring the target database), and the cacheuser cache administration user.

    % sqlplus sys@targetdb as sysdba
    Enter password: password
    SQL> DROP USER timesten CASCADE;
    SQL> DROP USER oratt CASCADE;
    SQL> DROP USER cacheuser CASCADE;
    

    Note:

    Specifying CASCADE in a DROP USER statement drops all objects, such as tables owned by the user, before dropping the user itself.
  2. Drop the TT_CACHE_ADMIN_ROLE role, the TTCA_TARGET_ROLE role, and the TTCA_DIRECTORY directory object.

    SQL> DROP ROLE TT_CACHE_ADMIN_ROLE;
    SQL> DROP ROLE TTCA_TARGET_ROLE;
    SQL> DROP DIRECTORY TTCA_DIRECTORY;
    SQL> exit
    
  3. Drop the cachetblsp default tablespace used by the timesten user and cache administration user, including the contents of the tablespace and its data file. Exit the SQL*Plus session.

    SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES;
    SQL> exit
    

Note:

The above steps do not drop the schemas that were created for the workload by the Cache Advisor. You can keep the schemas for use by another application workload, if they use the same schemas, or if you want to re-execute the same workload after re-creating the user and tablespace. If not, you can either manually drop the schemas created or, if the target database is a test database, destroy the database.

Clean up the repository Oracle database and host system

If the repository resides on a separate, devoted Oracle database, drop the repository database.

If the repository resides in the same Oracle database as the target, start SQL*Plus from an operating system shell on the TimesTen database system and connect to the repository Oracle database as the sys user. Use SQL*Plus as follows to clean up the repository Oracle database and its host system:

  1. Drop the ttcacheadvisor user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database.

    % sqlplus sys@repositorydb as sysdba
    Enter password: password
    SQL> DROP USER ttcacheadvisor CASCADE;
    
  2. Drop the TTCA_DIRECTORY directory object.

    SQL> DROP DIRECTORY TTCA_DIRECTORY;
    
  3. Drop the ttca_ts tablespace used by the ttCacheAdvisor user, including the contents of the tablespace and its data file. Exit the SQL*Plus session.

    SQL> DROP TABLESPACE ttca_ts INCLUDING CONTENTS AND DATAFILES;
    SQL> exit
    

Clean up the TimesTen database and host system

Start the ttIsql utility and connect to the cacheadv DSN as the TimesTen instance administrator user. Perform the following to clean up the TimesTen database:

  1. Use ttIsql to grant the DROP ANY TABLE privilege to the cache manager user so that this user can drop the underlying cache tables when dropping the cache groups. Then, exit this ttIsql session.

    % ttIsql cacheadv
    Command> GRANT DROP ANY TABLE TO cacheuser;
    Command> exit
    
  2. Start the ttIsql utility and connect to the cacheadv DSN as the cache manager user. The password of the TimesTen cache manager user cacheuser is ttcache. Use ttIsql to call the ttRepStop built-in procedure to stop the replication agent on the TimesTen database. Drop the cg1_userspeccache AWT cache group. Call the ttCacheStop built-in procedure to stop the cache agent on the TimesTen database. Exit this ttIsql session.

    % ttIsql "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"
    Enter password for 'cacheuser': password
    Command> call ttRepStop;
    Command> DROP CACHE GROUP cg1_userspeccache;
    Command> call ttCacheStop;
    Command> exit
    
  3. Use the ttDestroy utility to connect to the cacheadv DSN and destroy the TimesTen database.

    % ttDestroy cacheadv