buffer_cache question

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

When we want to use “flush buffer_cache” then user who is running query must have “alter system” privilege.

This is hard to in normal life.

So I would recommend to have pre and post part of execution where we can place our special set of commands (connn with different user and then execute flush…).

This would really help if possible, if I missed something and such a thing exists?

Brg

Damir

2

Hi Damir,

Thank you for the suggestion. It sounds like something we can enhance in the product. Would you please provide more details about how you think this will work, especially the pre post part of execution you mentioned? I would like to make sure I understand this request so we can enhance the product to better help you.

Regards,

Alex

3

Hi Alex,

Sounds like a three block part:
pre_tune

tune (what we have now)

after_tune
Pre and after tune parts are totally free to pass any sql statements.

Let us say that generated sql will looks like (for each sql statements to try):

conn dba_user/dba_pwd@db1
alter system flush buffer cache;

conn tune_user/tune_pwd@db1
…run sql from editor (what we have now)

conn dba_user/dba_pwd@db1
write some code … for an example ASH or AWR

Text are for pre and after part should be SQL base commands only (similar you have in Benchmark factory I think)

Hope this is now more clear.

Brg
Damir

P.S.

There might be enormous number of combination when to run pre and after past (after each sql, only at the start or end of whole sql tune statements …etc)

4

Hi Damir,

Thank you for the detailed explanation. I have another thought: if we provide a new Test Run setting for you to specify the DBA connection to use to flush the buffer cache, will that satisfy your primary need? I am thinking that such a setting would be easier as you don’t need to create your script. However, you will not be able to do more than to flush the buffer cache as you have described the use of the pre and post script. So the question is whether you have a use case that would require the script. If yes, would you mind to share it.

Thanks,

Alex

5

Hi Alex,

Please do not think that people who are really using SQL Optimizer are not too experienced and they do not know really much about scripting.

So please leave us a way to put plain SQL statements-this will be the best.

Mine proposal is very flexible because when I open testing case (from TEST env, where I have DBA privilege) on PROD (where I do not have DBA) I just simply comment part that I do not need and test can run.

Please keep in mind that dba privilege is not always present and should somehow be disabled.

Also reconsider after SQL part … it will be really interesting as well.

Last but not least, try to implement execute for each run and execute for each run set (in a case we want to run same query 10 times and in between we do not want to flush cache) … imagine that when to run pre and after part might be really interesting.

Once again, this feature has been implemented in Benchmark factory, so please take a look there-it is really a good example.

6

Hi Damir,

Thank you again for the explanation. Your request is well expressed and is added to our product backlog to consider in future release.

Alex

7

Hi Alex,

Great.

If I have another proposal for product, could I sent it to you on mail or place it here publicly.

Brg

Damir

8

Hi Damir,

Please feel free to put it under Idea Pond publicly so other users may also feedback on that. If for any reason you want to send it to me directly in email, I will be equally happy to get that from email.

Thanks,

Alex