9 Using the Cache Advisor

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.

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

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:

[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 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;

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

• Restrictions and assumptions

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
   

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