SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 217

The preceding parameter can be embedded into the application; yet a better method would be to specifically
execute this for a group of users accessing a schema using a logon trigger. Having this at the database level (as a
database logon trigger) instead of hard coding such specific conditions in the application will reduce the application
maintenance should the session-level parameter change.

CREATE OR REPLACE TRIGGER ALTER_SESSION_TRG
AFTER LOGON ON DATABASE
BEGIN
>>> validate the user connected is eligible for the session level change
>>> execute immediate ' ALTER SESSION SET < PARAMETER NAME >> = << VALUE>>;
END;
/

Similar to the OPTIMIZER_MODE parameter, there are several optimizer-related parameters that can be changed at
the session level. Table 7-1 gives a list of optimizer parameters that can be changed at the session level.

Table 7-1. Optimizer Parameters

Parameter Description

DB_FILE_MULTIBLOCK_READ_COUNT Specifies the number of blocks that will be read in a single I/O. The

optimizer uses the value to calculate the cost of full table scans and index
fast full scans. The unit for this parameter is in blocks and is set to the
maximum I/O size that the database can perform efficiently.

CURSOR_SHARING Converts literals used in SQL statements to bind variables. This helps

improve cursor sharing and reduce parsing.

RESULT_CACHE_MODE Helps determine if all the queries will use the result cache feature or only

queries that are hinted. If set to MANUAL , a hint is expected for the queries
to cache the result.

RESULT_CACHE_REMOTE_EXPIRATION Specifies the number of minutes for which a result that depends on

remote database objects remains valid.

OPTIMIZER_MODE Sets the optimizer mode at database instance startup.

STAR_TRANSFORMATION_ENABLED

Enables the optimizer to cost a start transformation for the star queries.

OPIMTIMIZER_INDEX_COST_ADJ Adjusts the cost of index; the default value of this parameter is 100. When

set to 100, optimizer evaluates indexes as an access path based on the
normal cost model.

OPTIMIZER_INDEX_CACHING Controls the cost analysis of an index with a nested loop.

OPTIMIZER_ADAPTIVE_REPORTING_ONLY

Controls the reporting mode for automatic reoptimization and adaptive
plans.

SQL Automatic Tuning
In Oracle Database 10g, a new feature was introduced called automatic query tuning. With this feature, the query-
tuning advisor will advise on what needs to be done with the query to improve performance. The tuning suggestions
could be as simple as the optimizer statistics are stale and need to be generated again or as complex as the query
needs to changed/rewritten.