Database Reference
In-Depth Information
Something that should be checked but is frequently forgotten is the impact of hints during upgrades. Because
hints are convenient workarounds in situations where the query optimizer doesn't manage to automatically provide
efficient execution plans but their effect depends on the type of decision tree (see Figure
11-1
) used by the query
optimizer, whenever hinted SQL statements are executed from another database version (and, therefore, from
another query optimizer version), they should be carefully checked. In other words, while validating an application
against a new database version, the best practice is to reexamine and retest all SQL statements containing hints. For
testing purposes, you might also want to disable all hints by setting the
_optimizer_ignore_hints
undocumented
initialization parameter to
TRUE
at the session level. Be careful—you should avoid setting it at the system level because
of the many hints used by the database engine itself.
Because views may be used in different contexts, specifying hints in views is usually not recommended. If you
really have to add hints in views, make sure the hints make sense for all modules using them.
Altering the Execution Environment
Chapter 9 describes how to configure the query optimizer. The configuration is the default execution environment
used by all users connected to the database engine. Consequently, it must be suitable for most of them. When a
database is used by multiple applications (for example, because of database server consolidation) or by a single
application with different requirements that depend on the module in use (for example, OLTP during the day and
batch during the night), it's not uncommon for a single environment to not be adequate in every situation. In such
cases, altering the execution environment at the session level or even at the SQL statement level could be appropriate.
How It Works
Altering the execution environment at the session level is completely different from doing it at the SQL statement
level. Because of this, I describe the two situations in two distinct subsections. In addition, I describe several dynamic
performance views displaying the environment related to a database instance, a single session, or a child cursor.
Session Level
Most initialization parameters described in Chapter 9 can be changed at the session level with the
ALTER SESSION
statement. So, if you have users or modules requiring a particular configuration, you should simply change the
defaults at the session level. For instance, to set up the execution environment, depending on the user connecting to
the database, you could use a configuration table and a database trigger, as shown in the following example. You can
find the SQL statements in the
exec_env_trigger.sql
script:
CREATE TABLE exec_env_conf (username VARCHAR2(30),
parameter VARCHAR2(80),
value VARCHAR2(512))
CREATE OR REPLACE TRIGGER execution_environment AFTER LOGON ON DATABASE
BEGIN
FOR c IN (SELECT parameter, value
FROM exec_env_conf
WHERE username = sys_context('userenv','session_user'))
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET ' || c.parameter || '=' || c.value;
END LOOP;
END;
