I’m trying to query the execution plan information that I see in Oracle Enterprise Manager (EM) using SQL. I have been pulling data from the V$SQL_PLAN view, but I’m unsure which child cursor to select when there are multiple CHILD_ADDRESS entries. Should I be selecting the one with the latest TIMESTAMP?
Additionally, I’m struggling to replicate the sort order that EM uses. I’ve tried using ORDER BY ID, DEPTH, PARENT_ID, but the ID order doesn’t seem to match what I see in EM.
Here’s the query I’m currently using:
<code>SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = 'cgaryqcan3bh4'
AND PLAN_HASH_VALUE = '1099020371'
ORDER BY ID, DEPTH, PARENT_ID;
</code>
<code>SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = 'cgaryqcan3bh4'
AND PLAN_HASH_VALUE = '1099020371'
ORDER BY ID, DEPTH, PARENT_ID;
</code>
SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = 'cgaryqcan3bh4'
AND PLAN_HASH_VALUE = '1099020371'
ORDER BY ID, DEPTH, PARENT_ID;
Can someone explain how Oracle EM retrieves and orders the plan data?