Describes the Optimizer Simulator (OSIM) tool, which is useful for debugging plan choice issues in Trafodion queries.
Introduction
A Trafodion user who is experiencing performance issues can share plan information with a Trafodion developer using this tool.
Capturing OSIM Data
The user can capture OSIM data using the following steps:
From sqlci, do the following:
>> control osim capture LOCATION '<directory>'; >> prepare xx from <query> >> control osim capture stop;
This set of commands captures the DDL, histogram statistics and query text associated with the <query> given. The OSIM utility captures this information into the <directory> specified.
The files in this directory can now be gathered into a tar file and attached to a Trafodion JIRA. Do note that the act of doing so makes all of this information public.
Using OSIM Data
The Trafodion developer can download this tar file and un-tar it to a <directory>. The information can then be used as follows:
From sqlci, do the following:
>> control osim load from '<directory>'; >> control osim simulate start; >> prepare xx from <query>
At this point, the objects described in the DDL have been simulated in the Trafodion instance, along with their histogram data. One can then use debugging tools such as gdb to debug the Trafodion Optimizer for this particular <query>. When done, one can remove the simulation environment by doing:
>> control osim unload 'osim' ;
Tip: If your OSIM data involves a table with many salted partitions (say, 100 or more), and you have an index on the table that has "SALT_LIKE_TABLE" in its DDL, the CREATE INDEX command will use LOAD under the covers to load it. This can be very slow on a workstation because it will fire up one ESP per partition. This of course is unnecessary as the table is actually empty. To work around this inefficiency, do the following: CQD TRAF_LOAD_USE_FOR_INDEXES 'OFF'. Do this before doing CONTROL OSIM LOAD. Another way to do this is to insert this CQD into the "_MD_".DEFAULTS table before doing CONTROL OSIM LOAD.