What is the best way use SQL Optimizer after running a workload to generate Index scenarios and to determine the best Index?
The Optimize Indexes module is designed to provide index recommendations for a group of SQL statements based on their workload. It means that the module understands that some SQL statements may run more frequently than others and it will take into account their frequency when recommending indexes.
To get started, click on the "Define New SQL Workload" link under the Optimize Indexes tab. One will see the four types of sources that one can specify your SQL statements.
Click the "SGA" button to get your SQL statements from SQL Area. Note that the "SGA" button will extract all SQL statements that are in SGA at the time one clicks the button. It will not monitor the SGA for incoming SQL so it won't run for hours even if one’s load will be.
In this case, one should generate your load first and wait until it finished, then come to Optimize Index and click on the "SGA" button to get all SQL loaded to SQL Area. One caveat is that one will need to have sufficient memory to store all the SQL statements under one’s load.
Another alternative way is to use the "Scan Code" button to extract SQL statements directly from a file (or multiple files). If one has all of their SQL statements available in one or more files, one can click on the "Scan Code" button and from the "Scan from File" link to add your file. Optimize Indexes will then use the "Scan" technique to extract the SQL inside the file. The Scan Code button works not only for scanning stored procedures or other database objects.
After adding the one’s SQL statements, click on the "Configure Search Process" button to continue and follow the instructions on the screen to start generating index recommendations. One doesn't need to concern about the bind variables in your SQL statements. Optimize Indexes would not test run one’s SQL so it would not need to provide bind variable values when generating index recommendations.
