ORA-12541

C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 22 16:04:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys/babu@keystone as sysdba
ERROR:
ORA-12541: TNS:no listener

C:> Set ORACLE_SID=KEYSTONE

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 22 16:04:24 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

TRSCESS

TRSCESS

Activating trace on multiple sessions means that trace information is spread throughout many trace files. For this reason Oracle 10g introduced the trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file. The trcsess usage is listed below.

EXPLAN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

· An ordering of the tables referenced by the statement

· An access method for each table mentioned in the statement

· A join method for tables affected by join operations in the statement

· Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

· Optimization, such as the cost and cardinality of each operation

· Partitioning, such as the set of accessed partitions

· Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

V$SQL_PLAN

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

Creating PLAN_TABLE

SQL> CONNECT ORTHO1648/ORTHO1648

Connected.

SQL> @%ORACLE_HOME%\RDBMS\ADMIN\UTLXPLAN.SQL

Table created.

Running EXPLAN_PLAN

SQL> EXPLAIN PLAN FOR SELECT * FROM G_USER_LANE_INT;

Explained.

Identifying Statement for EXPLAN PLAN

SQL> EXPLAIN PLAN SET STATEMENT_ID='BABU_E1' FOR SELECT * FROM G_USER_LANE_INT;

Explained.

PLAN_TABLE Output.

After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle to display the most recent plan table output:

· UTLXPLS.SQL

This script displays the plan table output for serial processing is an example of the plan table output when using the UTLXPLS.SQL script.

· UTLXPLP.SQL

This script displays the plan table output including parallel execution columns.

· DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output. You can specify:

o A plan table name if you are using a table different than PLAN_TABLE

o A statement Id if you have set a statement Id with the EXPLAIN PLAN

o A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL.

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:
SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ());

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));