-- To collect on all tables/indexes in schema, with default options
dbms_stats.gather_schema_stats('USER',CASCADE=>TRUE);
-- To collect on specific table along with its indexes
dbms_stats.gather_table_stats('USER','TABLE',Cascade=>TRUE);

exec dbms_stats....

begin
dbms_stats..
end;

   set pagesize 25
   set linesize 121

   EXPLAIN PLAN FOR
   < Your Query >;


    SELECT * FROM TABLE(dbms_xplan.display);

SQL> EXPLAIN PLAN FOR  select count(1) from dual;

Explained.

SQL>  SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1143908517

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SELECT
		p.COMPANYNUMBER ,
		p.PERIOD ,
		COALESCE(p.FULLYEARBASE , 0) ,
		COALESCE(p.FULLYEARENTERPRISE , 0) ,
		COALESCE(p.OPENBALANCEBASE , 0) ,
		COALESCE(p.OPENBALANCEENTERPRISE , 0) ,
		COALESCE(p.CLOSINGBALANCEBASE , 0) ,
		COALESCE(p.CLOSINGBALANCEENTERPRISE , 0)
	FROM
		(
			SELECT
					dp.* ,
					fy.PERIOD
				FROM
					vwDIMENSIONPERIOD_MCDK_PILOT dp
				INNER JOIN vwFISCALYEAR_MCDK_PILOT fy
				ON
					dp.FISCALYEAR          = fy.FISCALYEARSTART
					AND fy.FISCALYEARSTART = To_Date('01-01-2009' , 'dd-MM-yyyy')
					AND dp.COMPANYNUMBER   = '1100'

  vwDIMENSIONPERIOD_MCDK_PILOT 
  vwFISCALYEAR_MCDK_PILOT fy

   EXPLAIN PLAN FOR
   < Your Query >;


    SELECT * FROM TABLE(dbms_xplan.display);