Using Optimizer Hints
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 |
---|---|---|
|
Duplicate distribution scheme table Distributed by hash table Distributed by reference table |
r r/e (Assumes uniform distribution) r/e (Assumes uniform distribution) |
|
Duplicate distribution scheme table Distributed by hash table Distributed by reference table |
e*k*r k*r k*r |
Examples include:
-
Use TT_GridQueryExec on a Duplicate Distribution Scheme Table
-
Use TT_GridQueryExec on a Reference Distribution Scheme Table
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.