Oracle Gather stats on database level

Gather stats in Database level:-

EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
degree => 4,
OPTIONS => 'GATHER STALE',
GATHER_SYS => TRUE,
STATTAB => PROD_STATS);

=======================================================================
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will decide whether to collect index statistics or not.
DEGREE => 4 :Degree of parallelism.
OPTIONS:
=>’GATHER’ :Gathers statistics on all objects in the schema.
=>’GATHER AUTO’ :Oracle determines which objects need new statistics, and determines how to gather those statistics.
=>’GATHER STALE’:Gathers statistics on stale objects. will return a list of stale objects.
=>’GATHER EMPTY’:Gathers statistics on objects have no statistics.will return a list of no stats objects.
=>’LIST AUTO’ : Returns a list of objects to be processed with GATHER AUTO.
=>’LIST STALE’: Returns a list of stale objects as determined by looking at the *_tab_modifications views.
=>’LIST EMPTY’: Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the ‘SYS’ user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.

EXPLAIN PLAN of SQL in oracle

EXPLAIN PLAN OF SQL
====================

First connect sqlplus and generate plan like below, with out executing sql.

---i will take below sql for example---
select * from emp where empno = 202

---how to generate plan without execution.

EXPLAIN PLAN FOR
select * from emp where empno = 202;

---- how to display generated plan--

SET LINESIZE 300
SET PAGESIZE 300
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

--this sql will displays the plan.