--  FILE:	xpln92.sql
--
--  AUTHOR:     Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--              Copyright (C) 2003-2004 AppsDBA Consulting
--  DATE:	06/26/03
--
--  DESCRIPTION:
--		Script to output an explain plan for a given SQL statement.
--
--
--  REQUIREMENTS:
--              Requires access to a plan table
--              ($ORACLE_HOME/rdbms/admin/utlxplan.sql).
--
--              Uses the dbms_xpln package.
--
--
--  MODIFICATIONS:
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET LINESIZE 150;
--
DELETE FROM plan_table;
COMMIT;
--
explain plan
set statement_id = 'xpln'
for
-----------------------------------------------------------------------------
-- Place SQL here
-----------------------------------------------------------------------------



/
-----------------------------------------------------------------------------
-- End of SQL
-----------------------------------------------------------------------------
/* This script will display the results of Explain Plan in a nice format */
SET PAGESIZE 9999;
SET LINESIZE 130;
--
DEFINE STMT_ID = 'xpln';
--
SELECT * 
  FROM table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','&STMT_ID','TYPICAL'));
--
UNDEFINE STMT_ID;