In the Oracle Performance Tuning Guide for 10.2, it is written "To use stored outlines when Oracle compiles a SQL statement, set the system parameter
USE_STORED_OUTLINES to true or to a category name." So, I check the Reference Guide for information about this parameter (can I set it at the session level, is it dynamic, etc.), but I cannot find an entry for it. Nor does it appear when I use the SQL*Plus command "show parameter use_stored_outlines".So, off to Metalink I go... I search on "use_stored_outlines" and the first hit returned is Note 68642.1 Session Parameter:USE_STORED_OUTLINES. Where it notes that "This is NOT an init.ora parameter". It can only be set by using the ALTER SYSTEM or ALTER SESSION commands. Since it is not an init.ora parameter, it seems pretty clear that the setting will not survive an instance restart (though I have not tested this).
Why would you have a system parameter that cannot be set in the init.ora? To me, an important piece of being a system parameter is being set in the init.ora.
Oh...and at the bottom, there is a link to Note 68462.1 Overview of Parameter Reference Notes. Which is a note that tells you that there are Parameter Reference Notes.
5 comments:
I agree. This does seem to be anomaly. In Siebel, we occasionally revert to stored outlines to tune stubborn queries (if dancing over sheep's entrails fails).
Once we have created the outline and checked it is actually being used, we then have to convince the DBA that they will need to issue an ALTER SYSTEM following every instance restart.
Are there no database startup triggers?
Joel
Now that's a very funny story. I told the customer comment #1. They were uneasy and asked for a fully automated solution without the need for manual intervention. I understood this unease and presented them with:
create or replace trigger db_enable_outline
after startup on database
begin
execute immediate('alter system use_stored_outlines=myoutlines');
end;
'What ?! A database trigger on instance startup. What will happen if it goes wrong ? What if... ? Who will.. ?'
Sometimes you just have acknowledge defeat and give up.
The requirement to use a database startup trigger to set a system parameter adds needless complication. That additional step does introduce the chance for failure (albeit probably very remote).
Setting the parameter in the init.ora is the simplest method. It is also the one that all DBAs are (well, should be) using to set up the system.
Is there a solid reason why this parameter cannot be set in the init.ora?
The doc id should be: 68642.1
Post a Comment