SQL

execution plan oracle

-- Get sql_id and cursor_child_no from session
SELECT s.SQL_ID, s.SQL_CHILD_NUMBER,
       s.USERNAME, s.SCHEMANAME, s.OSUSER, s.PROGRAM,
       s.SQL_EXEC_START, s.SID
FROM GV$SESSION s
WHERE s.STATUS = 'ACTIVE' AND s.TYPE <> 'BACKGROUND';
-- Get hash plan from SQL_ID and SQL_CHILD_NUMBER
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'sql_id',
                                              cursor_child_no => 0));

oracle show execution plan

-- Without execution
EXPLAIN PLAN FOR select ...;
SELECT * FROM TABLE(dbms_xplan.display);
-- With execution
SELECT /*+ gather_plan_statistics */  ...;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));