Using Optimizer Hints

The TimesTen query optimizer is a cost-based optimizer that determines the most efficient way to run a given query by considering possible query plans. A query plan in TimesTen Scaleout is affected by the distribution scheme and the distribution keys of a hash distribution scheme as well as the column and table statistics, the presence or absence of indexes, the volume of data, the number of unique values, and the selectivity of predicates. You can manually examine a query plan by running the ttIsql explain command. See The TimesTen Query Optimizer in Oracle TimesTen In-Memory Database Operations Guide.

You can use optimizer hints to influence the execution plan generated by the optimizer. There are two optimizer hints that are specific to TimesTen Scaleout. These hints are valid at the statement and the connection levels. At the statement level, the hints are valid for SELECT statements only:

See Optimizer Hints Supported in TimesTen Scaleout Only in Oracle TimesTen In-Memory Database SQL Reference for information on the optimizer hints specific to TimesTen Scaleout. See Use Optimizer Hints to Modify the Execution Plan in Oracle TimesTen In-Memory Database Operations Guide for more information on all optimizer hints.

TT_GridQueryExec

The TT_GridQueryExec optimizer hint enables you to specify whether the query should return data from the local element or from all elements, including the elements in a replica set when K-safety is set to 2.

If you do not specify this hint, the query is run in one logical data space. It is neither local nor global. Exactly one full copy of the data is used to compute the query.

Valid options for this hint are LOCAL and GLOBAL:

LOCAL: TimesTen Scaleout runs the queries in the local element only. Data is retrieved locally from the element to which you are connected. If the local element does not have a full copy of the data, TimesTen Scaleout returns partial results.
GLOBAL: TimesTen Scaleout retrieves data from all elements, including copies of the rows from all tables from all replica sets to generate the results. This results in duplicate data returned if K-safety is set to 2 or if tables have a duplicate distribution scheme.

As with all queries, the element that you are directly connected to and issue the SQL query from prepares the query and sends it to all other elements in the grid. The request is run on elements that are up and the results are reported locally on the connected element.

See TT_GridQueryExec Optimizer Hint in Oracle TimesTen In-Memory Database SQL Reference for information on the syntax and semantics for this hint.

The distribution scheme is a determining factor in the number of rows returned. For example, Table 7-2 shows the number of rows used in query for the three distribution schemes. k represents the number of copies (k=2 in our example), e represents one element from each replica set (e=3 in our example), and r represents the number of rows in the table.

Table 7-2 TT_GridQueryExec Optimizer Hint

Option Table type Number of rows used in query

Option	Table type	Number of rows used in query
`LOCAL`	Duplicate distribution scheme table Distributed by hash table Distributed by reference table	r r/e (Assumes uniform distribution) r/e (Assumes uniform distribution)
`GLOBAL`	Duplicate distribution scheme table Distributed by hash table Distributed by reference table	ekr kr kr

LOCAL

Duplicate distribution scheme table

Distributed by hash table

Distributed by reference table

r/e (Assumes uniform distribution)

GLOBAL

Duplicate distribution scheme table

Distributed by hash table

Distributed by reference table

e*k*r

k*r

Examples include:

Note:

Reads do not get a distributed lock and return committed data. For the examples that use the TT_GridQueryExec(GLOBAL) optimizer hint, if a write to a replica set happens between the reads to its replicas, it is possible that the count will not match for all replicas. This is expected behavior because each replica is afforded read committed isolation.

Use TT_GridQueryExec on a Hash Distribution Scheme Table

This example uses the ttIsql describe command on the customers table to illustrate the table is distributed by hash. The example runs a SELECT COUNT (*) query on the customers table to return the number of rows in the table (1000). From the connection that is connected to element 4, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hints to return the number of rows. The rows returned differ based on whether Local or Global was specified in the TT_GridQueryExec hint.

Command> describe customers;
 
Table SAMPLEUSER.CUSTOMERS:
  Columns:
   *CUST_ID                         NUMBER (10) NOT NULL
    FIRST_NAME                      VARCHAR2 (30) INLINE NOT NULL
    LAST_NAME                       VARCHAR2 (30) INLINE NOT NULL
    ADDR1                           VARCHAR2 (64) INLINE
    ADDR2                           VARCHAR2 (64) INLINE
    ZIPCODE                         VARCHAR2 (5) INLINE
    MEMBER_SINCE                    DATE NOT NULL
  DISTRIBUTE BY HASH (CUST_ID)
 
1 table found.
(primary key columns are indicated with *)

Command> SELECT COUNT (*) FROM customers;
< 1000 >
1 row found.

Issue a SELECT elementId# FROM dual query to determine the local element connection (4).

Command> SELECT elementId# FROM dual;
< 4 >
1 row found.

From this connection, issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 333 rows to be returned (1000/3).

Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*), elementId#
         FROM customers GROUP BY elementId#;
< 326, 4 >
1 row found.

Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 2000 rows returned (k=2 * r=1000 = 2000). Validate the results by using the SUM function to calculate the total rows returned for all 6 elements.

Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*), elementId#
         FROM customers GROUP BY elementId# 
         ORDER BY elementId#;
< 338, 1 >
< 338, 2 >
< 326, 3 >
< 326, 4 >
< 336, 5 >
< 336, 6 >
6 rows found.

Command> SELECT SUM (338+338+326+326+336+336) FROM dual;
< 2000 >
1 row found.

Validate the total count using the TT_GridQueryExec(GLOBAL) hint.

Command> SELECT/*+TT_GridQueryExec(GLOBAL)*/ COUNT(*) FROM customers;
< 2000 >
1 row found.

Use TT_GridQueryExec on a Duplicate Distribution Scheme Table

This example uses the ttIsql describe command on the account_status table to illustrate the table is a duplicate distribution scheme. The example runs a SELECT COUNT (*) query on the account_status table to return the number of rows in the table (5). From the connection that is connected to element 2, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hints to return the number of rows. The rows return differ based on whether Local or Global was specified in the TT_GridQueryExec hint.

Command> describe account_status; 
Table SAMPLEUSER.ACCOUNT_STATUS:
  Columns:
   *STATUS                          NUMBER (2) NOT NULL
    DESCRIPTION                     VARCHAR2 (100) INLINE NOT NULL
 DUPLICATE
 
1 table found.
(primary key columns are indicated with *)

Command> SELECT count (*) FROM account_status;                                  
< 5 >
1 row found.

Command> SELECT elementId# FROM dual;
< 2 >
1 row found.

Issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 5 rows to be returned (r = 5).

Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*),elementId#
         FROM account_status GROUP BY elementId#;
< 5, 2 >
1 row found.

Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 30 rows returned (e=3 *k=2 * r=5= 30).

Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*),elementId#
         FROM account_status GROUP BY elementId# 
         ORDER BY elementId#;
< 5, 1 >
< 5, 2 >
< 5, 3 >
< 5, 4 >
< 5, 5 >
< 5, 6 >
6 rows found.

Validate the total count using the TT_GridQueryExec(GLOBAL) hint.

Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*) FROM account_status;
< 30 >
1 row found.

Use TT_GridQueryExec on a Reference Distribution Scheme Table

This example uses the ttIsql describe command on the accounts table to illustrate the table is distributed by reference. The example runs a SELECT COUNT (*) query on the accounts table to return the number of rows in the table (1010). From the connection that is connected to element 1, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hint to return the number of rows. The rows returned differ based on whether Local or Global was specified in the TT_GridQueryExec hint.

Command> describe accounts;
Table SAMPLEUSER.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (15) INLINE NOT NULL
    ACCOUNT_TYPE                    CHAR (1) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
    CURRENT_BALANCE                 NUMBER (10,2) NOT NULL
    PREV_BALANCE                    NUMBER (10,2) NOT NULL
    DATE_CREATED                    DATE NOT NULL
    CUST_ID                         NUMBER (10) NOT NULL
  DISTRIBUTE BY REFERENCE (FK_CUSTOMER)
1 table found.
(primary key columns are indicated with *)

Command> SELECT COUNT (*) FROM accounts;
< 1010 >
1 row found.

Command> SELECT elementId# FROM dual;
< 1 >
1 row found.

Issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 336 rows to be returned (1010/3).

Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*), elementId#
         FROM accounts GROUP BY elementId#;
< 339, 1>
1 row found.

Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 2020 rows returned (k=2 * r=1010 = 2020). Validate the results by using the SUM function to calculate the total rows returned for all 6 elements.

Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*), elementId#
         FROM accounts GROUP BY elementId# 
         ORDER BY elementId#;
< 339, 1 >
< 339, 2 >
< 332, 3 >
< 332, 4 >
< 339, 5 >
< 339, 6 >
6 rows found.

Command> SELECT SUM (339+339+332+332+339+339) FROM dual;
< 2020 >
1 row found.

Validate the total count using the TT_GridQueryExec(GLOBAL) hint.

Command> SELECT/*+TT_GridQueryExec(GLOBAL)*/ COUNT(*) FROM accounts;
< 2020 >
1 row found.

TT_PartialResult

The TT_PartialResult optimizer hint enables you to specify whether the query should return partial results or error if data is not available.

Use TT_PartialResult(1) to direct the query to return partial results if all elements in a replica set are not available.

Use TT_PartialResult(0) to direct the query to return an error if the required data is not available in the case where all elements in a replica set are not available. If at least one element from each replica set is available or the data required by the query is available, the optimizer returns the query result correctly without error.

The default is TT_PartialResult(0).

See TT_PartialResult Optimizer Hint in Oracle TimesTen In-Memory Database SQL Reference for information on the syntax and semantics for this hint.

Examine Results Using TT_PartialResult

In this example, select the elementId#, replicaSetId#, and dataspaceId# pseudocolumns to locate the row of data involved in the query. Force elements 3 and 4 to be unavailable. Set TT_PartialResult to 0 to return an error if the replica set is unavailable. Then, set TT_PartialResult to 1 to return partial results from the elements that are available.

Command> SELECT elementId#,replicasetId#,dataspaceId#, last_name,first_name 
         FROM customers WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.

Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
         WHERE last_name like ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.

Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
         WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.

Element 4 is no longer available. Expect same results. Element 3 is available.

Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
          WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 3, 2, 1, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 3, 2, 1, White, Dona >
< 3, 2, 1, White, Ellyn >
< 3, 2, 1, White, Nora >
< 3, 2, 1, White, Phylis >
8 rows found.

Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
         WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 3, 2, 1, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 3, 2, 1, White, Dona >
< 3, 2, 1, White, Ellyn >
< 3, 2, 1, White, Nora >
< 3, 2, 1, White, Phylis >
8 rows found.

Now element 3 becomes unavailable. Replica set 2 is unavailable. Expect TT_PartialResult set to 1 to return partial results. Expect TT_PartialResult set to 0 to return an error.

Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
         WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
3 rows found.

Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
           last_name,first_name FROM customers
         WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
 3723: Replica set 2 down
The command failed.