We use Explain to collect the information abt the access path used by the optimizer for the statement which is explainable.
Suppose you have a stored procedure having hundred's of SQL statements.Then u can use Explain for the sql's whose access path u want to see.Eg:- whether the predicates that u have specified in the select clause are using proper indexes or not.
We assign a query number through which we identify that statement in user created PLAN_TABLE.
Eg-: exlpain all set query number = nn for select.......(explainable statement)
All the informations derived by the Explain are stored in PLAN_TABLE.
For the description of the columns of Plan_table you can hv a look at SQL reference(Statements-Explain).