See execution plan for sql_id

Say you need to understand execution plan of this SQL:

SELECT * FROM XXX WHERE CHECK = 'YYY';

SELECT sql_id, sql_text FROM V$SQL 

 WHERE sql_text = 'SELECT * FROM XXX WHERE CHECK = ''YYY''';

See execution plan for sql_id from Cursor Cache
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3rczgmwhnnv37', 0, 'ALL'));


See execution plan for sql_id from AWR Repository
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3rczgmwhnnv37'));

Forcing a New Explain Plan
EXPLAIN PLAN FOR 
SELECT * FROM XXX WHERE CHECK = 'YYY';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

To purge a specific execution plan from Shared Pool:

SELECT address||','||hash_value addr, sql_text, sql_id, last_active_time, executions, 

       disk_reads, buffer_gets, user_io_wait_time

FROM V$SQLAREA 

WHERE sql_id IN ('gt1tfvux90xa5','47azq87rq9j8b');

BEGIN

  DBMS_SHARED_POOL.PURGE ('0000000335851528,784912309','C');

  DBMS_SHARED_POOL.PURGE ('0000000335851528,784912309','C');

END;

/

Hints

/*+ ORDERED */ - access tables in the order they appear in SQL statement

/*+ LEADING(MISSING_SQLS) USE_NL(VSQL) */- Use Nested Loops, with table MISSING_SQLS as the leading table