v$sql_plan stores the SQL execution plan for each child version of an SQL statement. Each of these statements is identified by the values in the two columns sql_id and child_number. Thus, the plan can be joined to v$sql using sqlid and child_number.
Querying plan with indented operations
The following select statements selects an execution plan with indented operations from v$sql_plan:
select
-- sqp.id,
-- sqp.parent_id,
lpad('| ', (sqp.depth-1) * 2, '| ') || sqp.operation || case when sqp.options is not null then ' (' || sqp.options || ')' end op,
-- sqp.options,
sqp.object_owner,
sqp.object_name,
sqp.access_predicates,
sqp.filter_predicates,
sqp.object_alias,
sqp.qblock_name,
sqp.projection,
sqp.time,
sqp.object_type,
sqp.position,
sqp.plan_hash_value
-- sqp.*
from
v$sql_plan sqp
where
--
-- Plan is identified either by
-- o sql_id and child_number, or
-- o plan_hash_value
--
sqp.sql_id = '808j9a4rqjc9t' and sqp.child_number = 0
-- sqp.plan_hash_value = …
order by
sqp.id;
Some basic figures about plans found in v$sql_plan
The following query returns a record for each execution plan that is found in v$sql_plan. Each plan is identified by the combination of sql_id and child_number. Some figures that are returned list
A plan's cost
The number of steps (or operations) of which a plan consists (cnt_steps_in_plan)
optimzier
The number of query blocks in a plan (cnt_qblock)
The total number of plans (cnt_plans)
with pln as (
select
count(*) over() cnt_plans,
count(*) cnt_steps_in_plan,
max(case when id = 0 then optimizer end) optimizer,
max(case when id = 0 then operation end) stmt_type,
max(case when id = 0 then cost end) cost,
count(distinct qblock_name) cnt_qblock,
sql_id,
child_number
from
v$sql_plan
group by
sql_id,
child_number
)
select
pln.cnt_steps_in_plan,
pln.optimizer,
pln.stmt_type,
pln.cost,
pln.cnt_qblock,
pln.sql_id,
pln.child_number,
count(*) over (partition by pln.sql_id) cnt_sql_id,
sql.parsing_schema_name,
sql.module,
sql.sql_fulltext,
pln.cnt_plans
from
pln left join
v$sql sql on pln.sql_id = sql.sql_id and
pln.child_number = sql.child_number
order by
-- cnt_sql_id desc
pln.cnt_qblock desc
-- pln.cost desc
-- cnt_steps_in_plan desc
;
Using CTE recursive queries to select a plan
Alternatively, with CTE recursive queries, the plan for an SQL statement can also be queried recursively like so:
with p ( sql_id, id, operation, options, object_owner, object_name, object_alias, lvl) as (
select
p.sql_id, p.id, p.operation, p.options, p.object_owner, p.object_name, p.object_alias, 0
from v$sql_plan p
where p.sql_id = '29m0un7am4sr6' and
p.parent_id is null
union all
select c.sql_id, c.id, c.operation, c.options, c.object_owner, c.object_name, c.object_alias, p.lvl+1
from p join v$sql_plan c
on p.sql_id = c.sql_id and
p.id = c.parent_id
)
search depth first by id set "dummy"
select
lpad('| ', p.lvl*3, '| ') || p.operation || ' ' || p.options operation,
p.object_name,
p.object_owner,
p.object_alias,
p.id,
p.lvl + 1
from p;
The value of position corresponds to the sequence number of the execution plan step with the same parent step except if id = 0, in which case position equals to the value of costs. Thus, the following SQL statement does not return a single record, at least in 19c:
select *
from
v$sql_plan
where
id = 0 and
nvl(position, -1) <> nvl(cost,-1)
;