This page contains the top 3 recommendations for schema usage to achieve optimal performance with Oracle TimesTen in-memory databases
 |
|
Create Appropriate Indexes
The TimesTen in-memory database has a cost-based optimizer. The optimizer chooses the best plan for query execution. Knowing how many indexes to create for good database performance is not always easy, but it's generally straight forward with proper review of the query plans.
The TimesTen database supports two types of indexes: hash index and range index. Hash index is used for exact match lookups, and range index is used for range lookups. A well-tuned hash index is faster than the corresponding range index for exact match lookups, but hash indexes cannot be used for range queries. Range indexes can be used for both exact match and range lookups, and for sorts, such as for SQL queries involving ORDER BY, GROUP BY or DISTINCT).
If you don’t specify a unique hash index on your primary key, a range index is automatically created for you. If you specify a unique hash index, be sure to set the index with appropriate size. Use the “PAGES=” option of the CREATE TABLE statement to specify the expected size of the table. Divide the number of rows expected in the table by 256 for the number of pages to specify. Example:
CREATE TABLE EMP (
ID NUMBER NOT NULL PRIMARY KEY,
NAME VARCHAR2(100)
) UNIQUE HASH ON (ID) PAGES=500;
Hash indexes provide better performance over range indexes for equality predicates in cases where either index can be used. However, hash indexes require more space than range indexes. Specifying too many pages for the index wastes space. Specifying too few pages hurts the performance because of hash buckets overflow. Hash indexes that are in-appropriately sized can be much slower than a range index. Also, if your table content increased significantly, you should resize your hash index; this is done with the ALTER TABLE statement.
TimesTen Index Advisor is a tool that evaluates a given SQL workload and recommends optimal indexes to improve the performance of the associated queries. For more information on Index Advisor, refer to the section on How to Run the TimesTen Index Advisor.
Update Statistics
The TimesTen query optimizer generally chooses good query plans. However, the optimizer needs additional information about the tables involved in complex queries in order to choose good plans.
Table statistics are an essential aid to the optimizer in choosing a good plan. By knowing the number of rows and data distribution of column values for a table, the optimizer has a much better chance of choosing an efficient query plan to access that table.
Generally, it is a good idea to update statistics on all tables in your database before preparing queries that access those tables.
If you update statistics on your tables before they are populated, then the queries are optimized with the assumption that the tables contain no rows (or very few rows). If you later populate your tables with millions of rows and then execute the queries, the plans that worked well for tables containing few rows may now be very slow as the optimization was not done for the current set of data in the table.
For optimal performance, you should update statistics on your tables to reflect the current population in the tables, in particular, after you have added or deleted a significant number of rows in the tables. You should prepare your queries after you have updated the table statistics.
For more information about updating statistics, consult the descriptions of the built-in procedures ttOptUpdateStats and ttOptEstimateStats in the “Built-In Procedures” section in the Reference manual.
Review Query Plan and Act Accordingly
If you find that a specific query runs more slowly than expected, it is possible that the TimesTen optimizer is not choosing the optimal query plan to answer that query. You should generate the query plan and review it. The Oracle TimesTen In-Memory Database Operations Guide has detailed documentation on how to generate a query plan and how to view the plan.
To display the optimizer plan from the ttIsql command line utility, use the commands:
autocommit 0;
showplan 1;
When reviewing the query plan, pay attention to the predicates that are participating in the query evaluation but are not indexed. If you can create an index for the non-indexed predicates, it helps your query performance. Now let's review a sample query.
In the example below, we extracted one of the steps in a test query where the main table (SHIPMENT) is relatively big. It has over 10 million rows. When we first ran the query, it took about 800 seconds to complete, which was too slow and not what was expected. We then generated the query plan and saw that one of the steps had the following plan::
STEP: 10
LEVEL: 8
OPERATION: TblLkRangeScan
TBLNAME: SHIPMENT
IXNAME: SHIPMENT_IDX1
INDEXED: <NULL>
NOT INDEXED: TBL1.OB_FLG <> 'Y'
AND TBL1.SHIPMENT_ID = CLIENT.SHIPMENT_ID
AND TBL1.SHIPMENT_QUAL = CLIENT.SHIPMENT_QUAL
AND TBL1.CARRIER = CLIENT.CARRIER
AND TBL1.ROLE = 'Y'
Several predicates were listed in the “NOT INDEXED” line, and in fact, there were no predicates listed in the “INDEXED” line. We did not have an index for the SHIPMENT_ID, SHIPMENT_QUAL and the CARRIER columns and the optimizer had to scan the entire table to evaluate each of the predicates. We fixed the issue by creating an index for those 3 columns, and we got a much improved plan for step 10 as shown below:
STEP: 10
LEVEL: 8
OPERATION: RowLkRangeScan
TBLNAME: SHIPMENT
IXNAME: SHIPMENT_IDX0
INDEXED: TBL1.SHIPMENT_ID = CLIENT.SHIPMENT_ID
AND TBL1.SHIPMENT_QUAL = CLIENT.SHIPMENT_QUAL
AND TBL1.CARRIER = CLIENT.CARRIER
NOT-INDEXED: TBL1.OB_FLG <> 'Y' AND TBL1.ROLE = 'Y'
This is a much better plan. The optimizer chooses the row lock with the appropriate index to evaluate the predicates, resulting in a 400 times improvement in the query time. The modified query ran for 2 seconds (instead of 800 seconds prior to the change).
As with all performance tuning, your mileage varies. The important point here is that you should take the time to read the query plan and make necessary changes for better query performance.