10 Using the Cache Advisor

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 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 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 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 Database user, if this user does not already exist.

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".

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 Oracle In-Memory 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:

[cacheadv]
DataStore=/users/OracleCache/cacheadv
PermSize=64
OracleNetServiceName=targetdb
DatabaseCharacterSet=AL32UTF8
CacheGridEnable=0

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 is to 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 Database 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;

Next, use ttIsql to call the ttCacheUidPwdSet built-in procedure to set the Oracle Database 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

• Restrictions and assumptions

Improve performance by using NFS or FTP for transferring data

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.

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
   

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
   

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