Oracle searches for real execution plans from the cache

For more information about Oracle execution plans, see Oracle Explain Plan.

1. view the SID of the current session
SYS @ anqing1 (rac1)> select userenv ('sid ') from dual;
USERENV ('sid ')
--------------
137
SYS @ anqing1 (rac1)> select sid from v $ mystat where rownum = 1;
SID
----------
137

2. view the Explain Plan in the cache
1) locate the HASH_VALUE and ADDRESS of the corresponding SQL statement from v $ SQL according to the SID.
/* Formatted on 17:38:20 (QP5 v5.163.1008.3004 )*/
SELECT a. SQL _text, a. address, a. hash_value
FROM v $ SQL a, v $ session B
WHERE a. hash_value = B. SQL _hash_value AND B. sid = & sid;

2) Find the actual execution plan from v $ SQL _plan Based on the hash_value and address values.
/* Formatted on 17:39:22 (QP5 v5.163.1008.3004 )*/
Set line 200;
COL protocol FORMAT a100;
Select lpad (latency, LENGTH (latency) + LEVEL * 2, '') latency, cost
FROM (SELECT object_name | ':' | operation | ''| options AS operation,
Cost,
Id,
Parent_id
FROM v $ SQL _plan
WHERE hash_value = '& hash_value' AND address = '& address ')
Start with id = 0
Connect by prior id = parent_id;
For example: