SQL Optimizer for Oracle 9.0 Beta Is Available

SQL Optimizer SQL Optimizer for Oracle
1

We are pleased to announce that there is a beta available now for SQL Optimizer for Oracle 9.0.

There are a couple of key enhancements that are available in the current Beta build:

  • Execution Plans can now be viewed using the DBMS_XPlan format.
  • When using Batch Optimize, SQL Optimizer will try and locate the bind variable values in the database when a bind variable needs to be input for a SQL statement to be Test Run. This feature will only work on 10g and higher databases.
    The beta can be downloaded here.

Please give it a try and let us know what you think!

Thanks!

Kevin

2

Hi,

couldn't install and check new options (have some problems with corporate security) but this is the only reason why asking without testing new beta.

will try and locate the bind variable values in the database
Could you explain what does it mean? Search in Oracle dictionary tables .... uf then I do not think this is so good idea.
Why?
Many of the tuning are initially performed on some "development" or "at most UAT based databases where many of problematic queries are even not run...so there are no binds as well. On the other hand many times I like to inspect only some specific bind values sets ... not all of them.

For me the better option would be, like in Dell Code Tester, create tables (easy master detail combination where sql_id may be a mater part of the primary key and some other "tag" should be other one...this tag may represent let say different database or market. Other 3 columns bind name, type and value are usual for detailed table) where we can place binds combinations that are interesting us.

Brg
Damir

3

Hi Damir,

We are getting the bind values from V_$SQL_BIND_CAPTURE.

You are right that the bind values may not be captured in database and sometimes the values there may not be the ones that you would like to use to tune the SQL. However, there are also cases where some users would just like to use the last used bind values to simplify their work. This is why we have made this bind value look up optional in Batch Optimize SQL. With the option, you can choose to let the product look up values for you. Or you can set the option to instruct the product to use the old workflow, i.e. to prompt you for a bind value to test run SQL.

Hope you find this make sense to you.

Thanks,

Alex

4

Hi,

I do not say that this is not improvement.

But what I’d like top see, I’m repeating my self but just because you do not answer directly on mine ask: How to put bind variables stored in some table as ordinary data?
imagine situation. Have 3-4 plan candidates and want to test them among 10-15 bind values sets (2-3 binds in each set)…how to achieve that now?

Brg
Damir

P.S.
This should not be hard and idea is from Code tester where I use that feature a lot!

5

I think you guys are talking different features, Damir is talking about multiple sets of bind values benchmarking for alternative plans. Alex is talking single set of bind values capture from SGA to help user for test running SQL alternative.

Damir, your idea is already in our researching project, it is call bind sensitive SQL tuning, it is a little complicated to describe here. Multipe sets of bind values for a SQL can be stored for test running, but I prefer to store it in local PC, there are some security issues that some users don’t want to create anything in their database, especially in production database.

6

Richard,

Whatever ... just to be able to run that. File, table ... whatever!

capture from SGA.
Guys I do not know but today databases are 60 G up to several hundreds G. I have never tried to test but how long your automated search SGA would takes place ... or when you say SGA you mean known Oracle dictionaries?

some users don't want to create anything in their database, especially in production database
For me this is very interesting ... becaue I would hardly ever start SQL Optimizer on production ... I mean to find better SQL. Hope you know what I mean ...

Btw, your current beta is already expired ... or I missed something?
Brg
Damir

7

Hi Damir,

The SQL Optimizer for Oracle 9.0 beta period ended in mid April, and the 9.0 version will be released soon.

Thanks,

Polly