Alter session statements

SQL Optimizer SQL Optimizer for Oracle
1

Hi I would like to test alter statement such as
alter session set hash_join_enabled = false;
alter session set “_optimizer_sortmerge_join_enabled” = false;
as they will typically alter the explain plan of the SQL.
However as they are not listed in the Alter statemet nwwindows they are not available , is ther a workaround ?

2

Hi Jean,

In the Tuning Lab, there is a “Session Parameters” window which you can set the session property values

To popup the window:

  1. Right click on Scenario Explorer in the Tuning Lab session.
  2. Click “Alter Session Parameters…”

For the Current Version of the Session Parameters window, we did not support the two parameters, “hash_join_enabled” and “_optimizer_sortmerge_join_enabled”.

There is no workaround for setting these two parameters, but we’ll add them back in the list as enhancement in the future version.

Please let me know if you have any more problems about the session parameters settings.

Regards,
Polly

3

Hi,
unfortunately, this do not work for “test with binds”.
What can we do in taht case against same problem?
Brg

Damir

4

As we replied before, the list of hints support doesn’t include “hash_join_enabled” and “_optimizer_sortmerge_join_enabled”. It is also same in “Bind Var test”. By now, the only workaround is to create an alternative as USER DEFINED, and put the hints into the SQL manually. It is not an ideal solution, but it may the only way to go by now.

Thanks,

Tony Ng

5

wrong thread, sorry

6

wrong thread, again.

sorry

7

sorry, wrong thread.

8

Sorry, wrong thread.

9

It seems you talk about another SQL tuning application. I believe I am not qualified to answer your question.

Sorry,

Tony Ng

10

sorry, wrong thread

11

The alternatives eliminate from explain plan. As you mention statistics may cause problem. However, We have concerned about releaseing all alternatives, it is really hard for user to handle huge SQL mentally. Actually we have a solution to capture the actual explain plan from SGA without execute SQL. It may solve the problem.

Thanks,

Tony Ng

12

sorry, wrong thread

13

About the bug from your last reply, I’m so confuse about it. You screen dump show Tosska as am example and mention about the bug happened from last version. So your actually meaning the same parsing problem happened in our product as well. If so, I am sorry about my misunderstanding. I will make a test on it.

thank you

Tony Ng

14

I work for Quest, not from Tosska. And this is not the screen dump from our product.

15

I must say again, your screen dump is not from our product. I work for Quedt and the product called Quest SQL optimiser. Are we talk about the same thing?

16

sorry, wrong thread

17

As you mentioned a few bugs in this thread, Let me figure out one by one.

The two monitors system problem happened in Quest SQL Optimizer.

What’s about the parsing problem? Do you mean it happened in QSO as well?

18

Tosska SQL Tuning Expert is an application from another company and nothing related to Quest. Just wondering why you keep show us it’s screenshot Tosska. Do you want to tell me the bug they get, the same bug we get? I really go through this thread serval times, If I still misunderstand it, I would like to apployg. Just want you to know your idea and help is very important for us.

Thanks,

Tony Ng

19

Wait a sec.

You are not tony, working for “Tosska SQL Tuning Expert”???

Obviously wrong thread.

Sorry

20

I am Tony Ng from Quest SQL Optimizer. I think there is miscommunication between us.