Is it worth using IM SQL Tuning to fine tune a SQL that all relevant tables are already in in-memory?

Yes, it is still worth it to tune your SQL with IM SQL Tuning even all the tables are already in in-memory. Our IM SQL Tuning will check whether there are any tables are not necessary to put into in-memory to save your in-memory space. For certain situations; in-memory objects may deteriorate query plan by using unnecessary in-memory full table scan operations. IM SQL Tuning makes recommendations to reverse certain in-memory full table scans by SQL hints application.

TIM™ trial version limitations

Please note the following limitations when using this trial:

  1. IM SQL Tuning Auto Scenario can generate at most 10 auto scenarios.
  2. IM Advisor and IM Simulator accept at most 100 SQL from SGA or AWR.
  3. IM Advisor intelligence level can be set at most to 2 out of 5.

TIM™ – System Requirements

Before installing Tosska In-Memory Maestro (TIM™) for Oracle®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 100 MB of disk space for 32-bit installation
200 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 32-bit and 64-bit
Microsoft Windows® 8 32-bit and 64-bit
Microsoft Windows® 10 32-bit and 64-bit
.NET Framework Microsoft .NET Framework 4.5
PDF Viewer Adobe Acrobat Reader® 7.0 or later (for viewing the Installation Guide)
Database Client Oracle® SQL*Net
Oracle® v9 Client (Net9)
Oracle® v10 Client (Net10)
Oracle® v11 Client (Net11)
Oracle® 12c Client
Oracle® Instant Client
Database Server Oracle® database 11G R2, 12C R1, 12C R2

Who should use TIM™?

If you have one or more than one of the following questions in mind with the new Oracle® In-memory features, then you should consider using TIM™:

  • Within a limited size of in-memory, what is the tables’ population solution to put into in-memory?
  • What is the best tables’ population solution for specific time slot in AWR or current SQL workload in SGA?
  • Any solution that can work best with my pre-selected tables’ population?
  • Any solution with less in-memory requirement without sacrifice too much in performance?
  • Are there are any recommended solution that can give an exact plan changes and cost estimation for a give SQL workload down to the SQL level?
  • What is the impact for a specific SQL if I put those recommended tables into Oracle® in-memory?
  • If all tables put into in-memory may not be the best solution for a SQL statement, what is the uncompromised solution for a mission critical SQL statement?

TIM™ – Full Description

About Tosska In-memory Maestro (TIM™) for Oracle®

Tosska In-memory Maestro (TIM™) for Oracle® automates the In-memory SQL optimization process and provide In-memory objects recommendations for a given SQL workload with our proprietary artificial intelligent engine. It also provides user friendly In-memory simulation function for user to virtually evaluate their in-memory table objects for one SQL or a given SQL workload without actually populating those table objects.

Key Features

Key features of TIM™ includes the IM SQL Tuning module that optimizes SQL statement within In-memory environment with uncompromised performance tuning ability, the IM Advisor which uses Tosska’s proprietary artificial intelligent engine to provide unparalleled In-memory objects recommendations for a given SQL workload from AWR or SGA. With TIM™’s IM Simulator, users can do virtual In-memory objects simulation for a given SQL workload without actually populating the selected table objects. TIM™ also provide tools to make your life easier in managing your IM environment. IM Objects Manager is a graphical tool to help user to manage In-memory objects with just point and click while IM Configuration is a tool that helps user to review a database current In-memory setting and to enable or setup a new In-memory configuration.

Connecting and Configuring

To start using TIM™, users have to set up a connection with the targeted database using the Connection Manager. Users can then use the IM Configuration tool to review a database current In-memory status, usage in different size pools and setting as displayed in the In-Memory Usage Panel, and to enable or setup a new In-memory configuration using the In-Memory Option Panel.

IM SQL Tunning

For mission critical SQL statements, users may want to tune their SQL with Oracle In-memory feature to the best performance. TIM™ provides an innovative function to help user to evaluate and tune a SQL with relevant table objects hypothetically populating into in-memory or depopulating from in-memory. A proprietary artificial intelligent engine is embedded to thoroughly explore most combinations of table candidates that manual SQL tuning cannot achieve, it is especially helpful for complicated SQL statement tuning with many tables join.

IM SQL Tuning modules provides a SQL Text Panel for users to input or paste a SQL statement in this panel for tuning. Using User Scenario function, user can simulate the scenario of a SQL statement that if certain tables are putting into in-memory or removing from in-memory with specific retrieval operations and review what impact to the SQL’s execution plan.

Alternatively TIM™ provides the Auto Scenario option where users can press this button to try most in-memory objects combinations of the SQL statement with necessary hints application, unique execution plan scenarios will be displayed for bench-marking.

Users can select one or multiple scenarios to test run with the Test Run Options provided in TIM™ to actually execute scenarios for bench-marking. With the Compare SQL Scenarios function, users can review two SQL statements/scenarios side by side, the SQL text, execution plan and statistics are displayed for easy comparison. Any discrepancy in execution plans will also be highlighted.

IM Advisor

Due to the in-memory size limitation, user must be very careful to select what tables to populate into in-memory and work best for a given SQL Workload. A SQL workload is a set of SQL statements captured from Oracle AWR or SGA at a specific time, there may be up to thousands of SQL statements that are accessing more than hundreds of tables. So, how to select tables to populate into in-memory within a given memory size is always a difficult problem. Furthermore, user may want to know that if there any solutions that can provide comparable solutions with less memory requirement for a given workload.

A maximum memory size is given by user; TIM™ can recommend solutions with less In-memory requirement.

With Tosska’s proprietary artificial intelligent engine, the IM Advisor is able to find the best recommendation of IM objects within a certain quota or time period for SQL workload from AWR or SGA. The recommended IM objects will have the best impact to the given workload. It means user can use this module to improve performance for a specific time slot SQL workload from AWR or current SQL workload from SGA.

When a new advisor session is created, user will be asked to extract SQL from SGA or AWR to compose a SQL workload for recommendation of IM objects. Once the extract SQL is finished, the IM Advisor screen will be displayed, the number of SQL captured and valid SQL with successful explain plan will also be displayed. You can review the details of SQL by clicking the hyperlink SQL Details anytime on this screen.

IM Simulator

The IM Simulator is used to simulate how a number of virtually selected in-memory table objects that impacts a SQL workload captured from SGA or AWR. User can use the module to improve database performance for specific hours of a day; for example if a user want to improve the peak hours performance of a day, the user can capture the SQL workload from SGA by that time or extract the SQL from AWR within that time slots. A list of SQL relative tables will be displayed for the user to carry out impact simulation. User can select any tables and virtually put them into in-memory to see the impact to the captured SQL workload.

IM Objects Manager

TIM™ provides this handy tool for users to manage their IM objects by altering it into In-memory with various types of option or bring it down from their In-memory in “All Objects” page. An IM objects occupancy map on top is also be displayed for users to understand the objects’ size and distribution easily. Users also can use the “In-memory Objects” page to review those objects already put into In-memory.

Where to get SQL workload from SGA or AWR?

If you know when the bottleneck of your system is, you can use AWR to retrieve SQL workload from the specific time slots. If the current running SQL in SGA is good enough to represent your peak hour workload in your application, you should extract SQL from SGA directly for IM Advisor and IM Simulator recommendation.