Converting the values improves cursor sharing and can affect the execution plans of SQL statements. See " About the Life Cycle of Shared Cursors".Ĭonverts literal values in SQL statements to bind variables. You can set this parameter at the SYSTEM or SESSION level. For this reason, a cursor may run with a suboptimal plan until it is recompiled, and may incur small execution-time overhead. Deferred invalidation reduces the number of cursor invalidations and spreads the recompilation workload over time. This is the default.ĭEFERRED allows an application to take advantage of the reduced cursor invalidation for DDL without making any application changes. IMMEDIATE sets the same cursor invalidation behavior for DDL as in releases before Oracle Database 12c Release 2 (12.2).
Provides the default cursor invalidation level for DDL statements. Table 19-2 Initialization Parameters That Control Optimizer Behavior Initialization Parameter For example, you can set optimizer goals using both initialization parameters and hints. In some cases, multiple techniques optimize the same behavior. This chapter does not cover SQL plan management. SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans. A SQL profile can correct suboptimal optimizer estimates discovered during SQL tuning. Conceptually, a SQL profile is to a SQL statement what a set of object-level statistics is to a table or index. This chapter does not cover DBMS_STATS.Ī SQL profile is a database object that contains auxiliary statistics specific to a SQL statement. The more accurate the statistics, the better the optimizer estimates. This package updates and manages optimizer statistics. " Influencing the Optimizer with Initialization Parameters"Ī hint is a commented instruction in a SQL statement. Parameters influence many types of optimizer behavior at the database instance and session level. Table 19-1 Optimizer Techniques Technique