Here are some beneficial Queries on Sqlplan Management in oracle
How to check the sql plan parameter
show parameter sql_plan
How to check the settings for Automatic plan capture
If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true, then we can use the below query to find out the settings of the automatic plan capture
COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME LIKE '%AUTO%';How to check the sql baseline for a sql_id
col sql_id format a14
col sql_handle format a22
col plan_name format a32
col sql_text format a40
col enabled format a8
col accepted format a8
select
DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
sql_handle ,
plan_name ,
enabled ,
accepted
from
dba_sql_plan_baselines
where
DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '&sql_id';
How to check the sql baseline for a sql text
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE sql_text LIKE '%YOUR_SQL_STATEMENT%';
How to check the explain plan for the Sql baseline
We can check the explain plan using the below sql statements
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=> '&sql_plan_name')); Or select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=> '&sql_handle'));
How to check if SQL baseline is used
select sql_id
, to_char(exact_matching_signature) sig
, plan_hash_value
, sql_plan_baseline
from v$sql
where sql_id = '&sql_id'
I hope you like this post on Queries on Sqlplan Management in Oracle
Related Articles
SQL Tuning Set in Oracle
how to create sql baseline in Oracle 19c
how to create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle
how to create sql baseline from AWR repository without STS
Sql plan Management in Oracle