Skip Headers
Oracle® In-Memory Database Cache User's Guide
11g Release 2 (11.2.2)

Part Number E21634-05
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
PDF · Mobi · ePub

9 Using the Cache Advisor

The following sections describe and demonstrate how to use the Oracle In-Memory Database (IMDB) Cache Advisor:

Cache Advisor overview

The Oracle IMDB 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 Oracle IMDB 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 Oracle IMDB cache sizes, so the cache group recommendations may differ depending on the size of the specified cache. For information on SQL tuning sets, see "Automatic SQL Tuning" in the Oracle Database Performance Tuning Guide.

When evaluating the 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 for the performance comparison.

Cache Advisor evaluates each SQL statement in isolation from any other statement and performs a ROLLBACK after each statement completes. 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, 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 Oracle IMDB Cache, Cache Advisor generates an HTML report that contains performance statistics comparing Oracle Database and Oracle IMDB 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 Oracle IMDB Cache with no changes, and which statements require modification before they can be executed. See Chapter 13, "Compatibility Between TimesTen and Oracle" 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 three databases:

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

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

Setting up the Oracle and TimesTen host systems

Before you can use Cache Advisor, you must first install TimesTen and then configure the Oracle 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 and TimesTen hosts and databases, complete the following tasks:

  1. Configure the target Oracle database and host system

  2. Configure the repository Oracle database and host system

  3. Configure the TimesTen database and host system

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 and host system

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

Log onto the system where the target database resides and create a directory where Cache Advisor can export workload and schema information to dump files using Data Pump. 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 target 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 tgtusr and the directory is /local/tgtusr/targetdir.

% mkdir /local/tgtusr/targetdir

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/tgtusr/targetdir directory resides on is /dev/sda1.

Cache Advisor must be able to access the contents of the dump files from the repository 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

Change the permissions on the directory so that only the target 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 target Cache Advisor user and the operating system user that is running the Oracle Database server on the target system (typically the oracle user). On Linux systems, ACLs can be set by running the operating system setfacl command.

% chmod 700 /local/tgtusr/targetdir
% setfacl -m u:tgtusr:rwx /local/tgtusr/targetdir
% setfacl -m d:u:tgtusr:rwx /local/tgtusr/targetdir
% setfacl -m u:oracle:rwx /local/tgtusr/targetdir
% setfacl -m d:u:oracle:rwx /local/tgtusr/targetdir

Next, set up the target database for use by Oracle IMDB Cache. Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the target database as the Oracle 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 will be used by both the Oracle timesten user and the cache administration user. This tablespace must only be used to store objects for Oracle IMDB 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 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 user owns the Oracle objects that will be accessed by the SQL workload application and are candidates for caching in a TimesTen database. The target Oracle 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 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 user and the cache administration user must be different users. In addition, when you create the repository Cache Advisor user, this user also must 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
  2  DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
SQL> GRANT SELECT ANY TABLE, DELETE ANY TABLE
  2  INSERT ANY TABLE, UPDATE ANY TABLE TO cacheuser;
SQL> @grantCacheAdminPrivileges "cacheuser"

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

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

  • Create or specify an Oracle directory object used for file operations into and out of the target database. The ttca_sysdbaSetupTarget.sql script associates the 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 directory objects.

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

SQL> @ttca_sysdbaSetupTarget
...
Please enter a target Oracle database user name to access the target database:
 oratt
...
Press ENTER for a list of existing directory objects on the target database 
<Enter>
<existing directory objects on the target database are shown>
Please press ENTER to continue <Enter>

********************************************************************************
*** Please do one of the following:
*** 1. Enter an existing directory object on the target Oracle database
***    using a directory object name listed above that is not defined 
***    on a network-mounted path
*** 2. Enter a new directory object name to be created
********************************************************************************
? target_dir

********************************************************************************
*** Creating new directory object TARGET_DIR.
*** Enter the directory path on the target system to use in the definition
*** of TARGET_DIR
********************************************************************************
? /local/tgtusr/targetdir
...
SQL> exit

Configure the repository Oracle database and host system

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 specified by the user. The repository database is a scratch database to be used only by Cache Advisor and should not be shared with other applications.

To use Cache Advisor, you must first install and configure a repository database of Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later. The version of the repository database must also be the same or later than the version of the target Oracle database.

Log onto the system where the repository database resides and create a directory where Cache Advisor will import workload and schema information from dump files using Data Pump. This directory can be created by any operating system user on the repository system. The owner of this directory will be referred to as the repository Cache Advisor user. In this example, the repository Cache Advisor user is reposusr.

The directory must be created on a device that is local to the repository system and not network mounted. In this example, the directory is /local/reposusr/repositorydir.

% mkdir /local/reposusr/repositorydir

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/reposusr/repositorydir directory resides on is /dev/sfa1.

Cache Advisor must be able to access the contents of the dump files from the repository system. 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 repository system.

As the operating system root user, enable the setting of ACLs on the file system, if is not already enabled.

# mount -o remount,acl /dev/sfa1

Change the permissions on the directory so that only the repository 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 repository Cache Advisor user and the operating system user that is running the Oracle Database server on the repository system (typically the oracle user). On Linux systems, ACLs can be set by running the operating system setfacl command.

% chmod 700 /local/reposusr/repositorydir
% setfacl -m u:reposusr:rwx /local/reposusr/repositorydir
% setfacl -m d:u:reposusr:rwx /local/reposusr/repositorydir
% setfacl -m u:oracle:rwx /local/reposusr/repositorydir
% setfacl -m d:u:oracle:rwx /local/reposusr/repositorydir

Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the repository database as the Oracle 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

Run the SQL*Plus script TimesTen_install_dir/oraclescripts/ttca_sysdbaSetupRepository.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 directory object used for file operations into and out of the repository database. The ttca_sysdbaSetupRepository.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 directory objects.

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

SQL> @ttca_sysdbaSetupRepository
...
Press ENTER to create the repository Oracle database user with user name
 TTCACHEADVISOR, or enter an alternative user name for the repository database 
 user: <Enter>
Please enter a password for the TTCACHEADVISOR user: ttca
Please confirm the password for the TTCACHEADVISOR user: ttca
...
Press ENTER for a list of existing directory objects on the repository database 
<Enter>
<existing directory objects on the repository database are shown>
Please press ENTER to continue <Enter>

********************************************************************************
*** Please do one of the following:
*** 1. Enter an existing directory object on the repository Oracle database
***    using a directory object name listed above that is not defined on a
***    network-mounted path
*** 2. Enter a new directory object name to be created
********************************************************************************
? repository_dir

********************************************************************************
*** Creating new directory object REPOSITORY_DIR.
*** Enter the directory path on the repository system to use in the definition
*** of REPOSITORY_DIR
********************************************************************************
? /local/reposusr/repositorydir
...
SQL> exit

Configure the TimesTen database and host system

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 this example, NFS is the network connection that will be used to transfer files, such as the Data Pump dump files that contain workload and schema information, between the target Oracle database system, repository Oracle database system, and TimesTen database system. On the target system, export the directory that was created by the target Cache Advisor user to NFS clients. Similarly on the repository Oracle system, export the directory that was created by the repository Cache Advisor user to NFS clients. On Linux systems, add an entry to the /etc/exports file for each directory to be exported to NFS clients. This file must be updated on both the target system and the repository system. Then run the exportfs -a operating system command as root on both systems for updates to the files to take effect.

As the TimesTen instance administrator user, log onto the system where the TimesTen database will reside and create two directories.

% mkdir /home/ttuser/targetdir
% mkdir /home/ttuser/repositorydir

As the operating system root user, network mount the first directory to the directory on the target system that was created by the target Cache Advisor user. Then, network mount the second directory to the directory on the repository system that was created by the repository Cache Advisor user.

# mount -t nfs targethost:/local/tgtusr/targetdir /home/ttuser/targetdir
# mount -t nfs reposhost:/local/reposusr/repositorydir
 /home/ttuser/repositorydir

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 is used to cache 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 Oracle IMDB 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 will be used to cache data from the target Oracle database.

% ttIsql cacheadv

Use ttIsql to create a cache manager user. 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, which is the same name as the Oracle cache administration user that was created in the target Oracle database.

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 users in the TimesTen database".

See "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen users and privileges.

Next, use ttIsql to call the ttCacheUidPwdSet built-in procedure to set the Oracle 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 Oracle In-Memory Database (IMDB) 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:

  • 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 11g Release 2 (11.2.0.2) or later, and the TimesTen database all reside on separate host systems. The target Oracle database cannot be a later version than the repository Oracle database. This configuration is preferred, because Cache Advisor operations (such as workload analysis and report generation) have minimal impact on the target database, which can be a production database. It accurately evaluates performance for both the target database and the TimesTen database.

  • The repository Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, and the TimesTen database reside on the same host system. The target Oracle database, which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, resides on a separate host system. The target Oracle database cannot be a later version than the repository Oracle database. This configuration accurately evaluates performance for the target database and can accurate evaluate performance for the TimesTen database.

  • A single Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, serves as both the target and repository database. The TimesTen database resides on a separate host system. This configuration is convenient, but does not accurately evaluate performance for the target database. It accurately evaluates performance for the TimesTen database. The target Oracle database should be a test database and not a production database.

  • 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 11g Release 2 (11.2.0.2) or later, where both reside on the same host system. The target Oracle database cannot be a later version than the repository Oracle database. This configuration can be used to demonstrate Cache Advisor on a laptop computer and identify porting issues, but does not accurately evaluate performance for the target database nor the TimesTen database. The target Oracle database should be a test database and not a production database.

Restrictions and assumptions

The design of the cache schema recommended by Cache Advisor assumes that the user application will establish 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 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 user is oratt. The password of the oratt user is oracle. The application table will be 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" \
-oraDirObject target_dir -oraDirNfs /home/ttuser/targetdir \
-oraRepository -oraConn "ttcacheadvisor@repositorydb" \
-oraDirObject repository_dir -oraDirNfs /home/ttuser/repositorydir \
-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:

If the passwords are not specified in the connection strings for each database, the ttCacheAdvisor utility will prompt 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 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 user-name_host-name_timestamp. In this example, the task name is overridden with the -task option.

The ttCacheAdvisor utility also generates an implementation script file named ttCacheAdvisor_task-name_timestamp.sql in the directory where the 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 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.

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:

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 IMDB 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 9-2 Cache Advisor report home page

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

Figure 9-3 Cache Advisor findings and recommendations

Description of Figure 9-3 follows
Description of "Figure 9-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 9-4 Viewing the number of SQL statements executed in the workload

Description of Figure 9-4 follows
Description of "Figure 9-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 will see the following information about the SELECT statement:

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

Description of Figure 9-5 follows
Description of "Figure 9-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 9-6 Cache group details

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

Figure 9-7 SQL statements used for cache group

Description of Figure 9-7 follows
Description of "Figure 9-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 IMDB 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:

Figure 9-8 Implementation script example

Description of Figure 9-8 follows
Description of "Figure 9-8 Implementation script example"

Figure 9-9 Continuation of implementation script example

Description of Figure 9-9 follows
Description of "Figure 9-9 Continuation of implementation script example"

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 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 9-10 Configuration overview page

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

Figure 9-11 Repository and client configuration information

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

Cleaning up the Oracle and TimesTen databases and host systems

Complete the following tasks to restore the Oracle 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 user (if you created this user because it did not exist prior to 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 target_dir directory object.

    SQL> DROP ROLE TT_CACHE_ADMIN_ROLE;
    SQL> DROP ROLE TTCA_TARGET_ROLE;
    SQL> DROP DIRECTORY target_dir;
    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

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 sys.wri$_adv_sqla_tt_tabcols table and the repository_dir directory object.

    SQL> DROP TABLE sys.wri$_adv_sqla_tt_tabcols;
    SQL> DROP DIRECTORY repository_dir;
    
  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