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

PROCEDURE convert_byte_to_char

CREATE OR REPLACE PROCEDURE convert_byte_to_char IS
  v_module_name    SGA_W_LOG.procedure_name%TYPE;
  v_msg_text       SGA_W_LOG.data%TYPE;
  v_sql            VARCHAR2(1000);    
    
  -- Cursor to find all VARCHAR2 columns defined as BYTE
  CURSOR byte_cols_cur IS
  SELECT table_name, column_name, data_length
    FROM user_tab_columns
   WHERE data_type = 'VARCHAR2'
     AND char_used = 'B'
     AND virtual_column = 'NO'
  ORDER BY table_name, column_name;
        
BEGIN
  v_module_name := 'convert_byte_to_char';
  -- Log the start of the procedure
  INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
  VALUES (v_module_name, 'Procedure started.', SYSDATE);
  COMMIT;
  FOR byte_cols_rec IN byte_cols_cur LOOP
    BEGIN
      -- Construct the dynamic DDL statement
      -- e.g., ALTER TABLE MY_TABLE MODIFY (MY_COL VARCHAR2(50 CHAR))
      v_sql := 'ALTER TABLE ' || DBMS_ASSERT.enquote_name(byte_cols_rec.table_name) || 
               ' MODIFY (' || DBMS_ASSERT.enquote_name(byte_cols_rec.column_name) || 
               ' VARCHAR2(' || byte_cols_rec.data_length || ' CHAR))';
           
      -- Execute the DDL
      EXECUTE IMMEDIATE v_sql;
            
      -- Log successful modification
      v_msg_text := 'Successfully altered ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name || ' to VARCHAR2(' || byte_cols_rec.data_length || ' CHAR).';
                         
      INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
      VALUES (v_module_name, v_msg_text, SYSDATE);
            
      EXCEPTION
        WHEN OTHERS THEN
          -- Log any specific errors encountered per table/column so the loop can continue
          v_msg_text := 'ERROR altering ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name ||': ' || SQLERRM;
                             
          INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
          VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
      END;
        
      -- Commit the log entry for the current column
      COMMIT;
  END LOOP;
  -- Log completion
  INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
  VALUES (v_module_name, 'Procedure finished successfully.', SYSDATE);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Catch-all for unexpected global failures
    ROLLBACK;
    v_msg_text := 'Unexpected Error: CRITICAL FAILURE: ' || SQLERRM;
    INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
    VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
    COMMIT;
    RAISE;
END convert_byte_to_char;
/