Database Reference
In-Depth Information
Dropping Stored Outlines
With the
DROP OUTLINE
statement or the
drop_by_cat
procedure in the
dbms_outln
package, you're able to drop
stored outlines. While the former drops a single stored outline, the latter drops all stored outlines belonging to one
category:
DROP OUTLINE outline_from_text
execute dbms_outln.drop_by_cat(cat => 'TEST')
To drop private stored outlines, you have to use the
DROP PRIVATE OUTLINE
statement.
Privileges
The system privileges required to create, alter, and drop a stored outline are
create any outline
,
drop any outline
,
and
alter any outline
, respectively. No object privileges exist for stored outlines.
By default, the
dbms_outln
package is available only to users who either have the
dba
or
execute_catalog_role
role. Instead, the
dbms_outln_edit
package is available to all users (the
execute
privilege is granted to
public
).
End users don't require specific privileges to use stored outlines.
■
You should never need to log in with the
outln
account. therefore, for security reasons, you should either lock
it or change its default password. this is especially important because it owns a dangerous system privilege:
execute
any procedure
.
Tip
When to Use It
You should consider using stored outlines in two situations. First, consider using it whenever you're optimizing a
specific SQL statement and you can't change it in the application (for example, when adding hints isn't an option).
Second, you should consider using it when, for whatever reason, you're experiencing troublesome execution plans
instability. Because the aim of stored outlines is to force the query optimizer to choose a specific execution plan for
a given SQL statement, use this technique only when you want to explicitly restrict the choice of query optimizer to a
single execution plan.
Stored outlines are deprecated in favor of SQL plan management beginning in version 11.1. Therefore, as of
version 11.1, it's sensible to use stored outlines in Standard Edition only.
Pitfalls and Fallacies
Oddly enough, the
use_stored_outlines
initialization parameter can't be specified in an initialization file (
init.ora
or
spfile.ora
). Consequently, the parameter must be set either at the system level every time an instance is bounced,
or at the session level every time a session is created. In both cases, you can set the initialization parameter through a
database trigger. For example, the following trigger sets the
use_stored_outlines
initialization parameter only for the
user named Joze:
CREATE OR REPLACE TRIGGER enable_outlines AFTER LOGON ON joze.SCHEMA
BEGIN
