Query Using DBMS_SQL Automatically set _optim_peek_user_binds false in Outline data
(Doc ID 2977764.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Version 19.4.0.0.0 and laterInformation in this document applies to any platform.
Goal
Observation of a SQL having a bad execution plan. When checking the SQL monitor report we see in the outline data the following parameter; _optim_peek_user_binds=false.
This parameter was not set at a database level, SQL Profile, or SQL Plan Baseline. The parameter is also not being set at a session level through the application.
This is unknown as to why it is set in the outline data but this clearly is showing as set to false.
What is needed is an explanation of where the SQL statement can pull an OPT_PARAM hint with _optim_peek_user_binds=false given the following:
1. ALTER SESSION auditing over an extended period of time (> 1 week) logged no execution of parameter changes at session level
2. v$sql_optimizer_env shows no "_optim_peek_user_binds=false" setting for any of the affected statements
3. v$ses_optimizer_env shows no "_optim_peek_user_binds=false" for any session on the system
4. No SQL Plan Baselines were used
5. No SQL Plan Profiles were used
6. No SQL Patches were used
7. No Outlines were used
8. No ALTER SESSION statement in the PL/SQL source code running the affected statements
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Goal |
Solution |
References |