"Rewrite" option for sql optimization queries - are occured changes in the databases?

SQL Optimizer SQL Optimizer for Oracle
1

Hello, I have some queries in our production environment that I believe that can be optimized

I have installed this product and I would like to ask if I choose to optimize SQL and copy the statement there and choose the “Rewrite” option, will I have anything changed to my database?

For example will any indexes be added or anything modified?

I do not want to change anything. I just want to see if there are any alternatives and check if they are more effecient, but not to change something in the database.

Thank you!

2

Hi Chris,

The only thing that SQL Optimizer needs to create permanently in your database is a plan table in order for it to get plan when optimizing SQL. If you really don’t want the product to create its own plan table, then there is also an option to specify a pre-existing plan table for it to use.

Apart from the plan table, SQL Optimizer doesn’t create permanent objects in your database. During rewrite, it will only interacts with your database to get plan. And when you do test run, it will send the SQL to your database to execute. If your SQL is a DML (INSERT, UPDATE, DELETE, or MERGE statement), it will rollback the changes after test run so nothing will be modified in your database. If you are doing index related work in SQL Optimizer (e.g. generating index recommendations in Optimize Indexes, or evaluating the impact of additional indexes in Analyze Impact), then virtual indexes will be created temporarily during the index evaluation. There virtual indexes are created only for a short time during the evaluation and they will be dropped after the process. When you test run index alternatives, then the product will have to create the corresponding indexes to test run. You will be prompted for this index creation. Once the test run is done, the created indexes will be dropped.

I have listed all the details about how the product interacts with your database in the above. So in general, you can just perform the SQL optimization action in the product without worrying about it changing your database. Once you have found a better SQL or an index recommendation, you can review them in the product first and then manually deploy them in a later time.

Thanks,

Alex

3

Thank you for your answer Alex. I would like to ask about the plan table. What does it mean that it creates its own plan table? What should I worry about?

4

In order to explain plan in Oracle, you need to have a plan table in your database. When you first connect to a database in SQL Optimizer, the product will check if there exists a plan table with a specific prefix. If it is found and can be used, SQL Optimizer will use this plan table to get plan. If not, then it will create a new table using the prefix. So a plan table is really created once for the first time. The same plan table will be reused in subsequent connection,

Thanks,

Alex

5

Thank you very much Alex. How can I check in the database if there is a plan or if it is created, since I have already connected the database with SQL Optimizer.

6

If you have already connected to the database, you should most likely have the plan table created in your database. The prefix of the plan table is “QUEST_SL_TEMP_EXPLAIN” so you may look for any table name starting with this prefix, like QUEST_SL_TEMP_EXPLAIN1 for example.

Thanks,

Alex

7

Thank Alex! However, you said that “there is also an option to specify a pre-existing plan table for it to use.” I saw that there is a “TOAD_PLAN_TABLE” table in the database. Can I use this table or modify the specific prefix? Thank you.

8

Sorry to tell you that there is no way to change the prefix. You can open the Options window in SQL Optimizer and check under the General | Options tab. From there, under “Specify the plan table used to retrieve the execution plan”, you should see the 2 options to either allow SQL Optimizer to create its own plan table (using the specific prefix) or to use the table you specified. If you want to use the TOAD_PLAN_TABLE, choose the second option and enter the schema and table name there.

Note that different versions of Oracle may have different versions of the plan table with more or less columns. SQL Optimizer will make sure that the specified plan table is a valid one to use and that it is of the same version expected in your database version. If this is not the case, then you will be notified when you connect to the database.

Thanks,

Alex