参数use_stored_outlines
SQL> alter system set use_stored_outlines=aaa;
System altered.
可以设置成功但是不能通过show parameter查看。
SQL> show parameter create_stored_outlines
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
create_stored_outlines string FALSE
解释:
Session Parameter: USE_STORED_OUTLINES [ID 68642.1] <===这是一个session级别的参数
Parameter: USE_STORED_OUTLINES
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is NOT an init.ora parameter but can be set using the command: <===不能再init.ora或spfile中设置,即使在系统级别设置该参数,重启DB之后即失效
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
-or-
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
Introduced: Oracle8i
Description
~~~~~~~~~~~
In Oracle8i it is possible to create stored plans (or outline plans) for
SQL statements. USE_STORED_OUTLINES controls whether the session uses
stored plans or not.
Stored outlines can be generated for a single statement or at the session or the instance level
=========
ALTER SESSION set create_stored_outlines = {TRUE|category|FALSE}
ALTER SYSTEM set create_stored_outlines = {TRUE|category|FALSE} [NOOVERRIDE]
CREATE OUTLINE outline [FOR CATEGORY category] ON statement
=========
More detailed information about stored outline, see the following document:
Using Stored Outlines [ID 132547.1]
How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1):
Create the following trigger to be run on startup of the database:
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
Note: <===Oracle在将来的版本可能不支持stored outline
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.
For more information see:
Document 1359841.1 Plan Stability Features (Including SPM) Start Point
Automatic SQL Tuning - SQL Profiles. [ID 271196.1]
How To Use SQL Profiles for Queries Using Different Literals [ID 1253696.1]
SQL> alter system set use_stored_outlines=aaa;
System altered.
可以设置成功但是不能通过show parameter查看。
SQL> show parameter create_stored_outlines
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
create_stored_outlines string FALSE
解释:
Session Parameter: USE_STORED_OUTLINES [ID 68642.1] <===这是一个session级别的参数
Parameter: USE_STORED_OUTLINES
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is NOT an init.ora parameter but can be set using the command: <===不能再init.ora或spfile中设置,即使在系统级别设置该参数,重启DB之后即失效
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
-or-
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
Introduced: Oracle8i
Description
~~~~~~~~~~~
In Oracle8i it is possible to create stored plans (or outline plans) for
SQL statements. USE_STORED_OUTLINES controls whether the session uses
stored plans or not.
Stored outlines can be generated for a single statement or at the session or the instance level
=========
ALTER SESSION set create_stored_outlines = {TRUE|category|FALSE}
ALTER SYSTEM set create_stored_outlines = {TRUE|category|FALSE} [NOOVERRIDE]
CREATE OUTLINE outline [FOR CATEGORY category] ON statement
=========
More detailed information about stored outline, see the following document:
Using Stored Outlines [ID 132547.1]
How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1):
Create the following trigger to be run on startup of the database:
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
Note: <===Oracle在将来的版本可能不支持stored outline
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.
For more information see:
Document 1359841.1 Plan Stability Features (Including SPM) Start Point
Automatic SQL Tuning - SQL Profiles. [ID 271196.1]
How To Use SQL Profiles for Queries Using Different Literals [ID 1253696.1]