TimesTen - Is Generation Of Optimizer Statistics On SYS Tables Supported? (Doc ID 1366045.1)

Last updated on JUNE 07, 2017

Applies to:

Oracle TimesTen In-Memory Database - Version 7.0.0.0.0 to 11.2.1 [Release 7.0 to 11.2]
Information in this document applies to any platform.
This Note is of interest to all TimesTen DBAs and application developers.

***Checked for relevance on 23-Apr-2013***


Goal

A customer has raised the question as to whether it is supported to generate optimizer statistics for tables in the TimesTen SYS schema.

The SYS schema includes the TimesTen data dictionary. Customer observed that SQL queries on tables in the SYS schema which were generated automatically by the TimesTen background daemons tended to result in inefficient query plans, specifically in the generation of temporary indexes which didn't seem reasonable given the cardinalities of the tables. Customer then determined that if he generated optimizer statistics for tables in the SYS schema that the resulting query plans seemed were more efficient and did not generate temporary indexes. Customer then raised the question with TimesTen Technical Support as to whether the generation of optimizer statistics on the SYS schema was supported and necessary.

Subsequent research on test data stores in-house reproduced the customer's experience using the queries the customer had identified. Not only did the query plans appear to be more efficient but execution timing tests  indicated that query performance was significantly faster when optimizer statistics existed for these tables. Subsequent testing against various demo programs which TimesTen showed some general improvement in transaction throughput.

All DBMSs generate and execute SQL and DML statements against their dictionary tables; in Oracle this is referred to as 'recursive' SQL and such statements are necessary for query generation, space management and other adminstrative functions. Clearly, efficient functioning of the data dictionary SQL and DDL can have an impact on performance.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms