Optimizer Environment | Oracle Scratchpad

Optimizer Environment | Oracle Scratchpad: "Optimizer Environment
Filed under: Troubleshooting — Jonathan Lewis @ 8:28 pm BST May 1,2007
From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.

Oracle 10g allows us to see the optimizer environments at the system, session, and individual SQL level, through v$sys_optimizer_env, v$ses_optimizer_env, and v$sql_optimizer_env. This can be very helpful when tracking down anomalies, for example:

select
        first_load_time, child_number, last_load_time,
        plan_hash_value, loads, executions
from
        v$sql
where
    sql_id = 'g4pkmrqrgxg3b'
;    "



'via Blog this'