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'));

TKPROF

SQL Trace and TKPROF

SQL Trace and TKPROF, Accrately assess the efficiency of the SQL Stat. an application runs.

SQL Trace:

Trace facility provide performance information on individual SQL Stat... It’s generating the following stat. for each stat.

1. Parse, execute and fetch counts

2. CPI and Elapsed Times

3. Physical Reads and logical Reads

4. Number of rows processed

5. Misses on the library cache

6. Username under which each parse occurred

7. Each commit and rollback

8. Wait event for each SQL Statement and a summary for each trace file.

TKPROF :

If you run TKPROF program to format the contents of the trace file and place the output into a readable output file

1. Create a SQL Script that stores the statistics in the database.

2. Determine the execution plan of SQL Stat.

Initialization Parameter to check before enables SQL Trace:

1. TIMED_STATISTICS = TRUE

2. MAX_DUMP_FILE_SIZE = 500 (By default)

3. USER_DUMP_DEST

4. SQL_TRACE= TRUE


DBMS_SESSION.SET_SQL_TRACE Procedure or

ALTER SESSION SET SQL_TRACE=TRUE

Identify Trace file:

            SELECT s.sid, s.serial#,  pa.value || '/' ||  LOWER(SYS_CONTEXT('userenv','instance_name'))

                        || '_ora_' || p.spid || '.trc' AS trace_file   FROM   v$session s, v$process p,  v$parameter pa

            WHERE  pa.name = 'user_dump_dest'   AND    s.paddr = p.addr

            AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Output :



SID SERIAL#

---------- ----------

TRACE_FILE

--------------------------------------------------------------------------------

159 3

F:\ORACLE\PRODUCT\10.2.0\ADMIN\KEYSTONE\UDUMP/keystone_ora_4788.trc

Execute TKPROF Tool:

F:\oracle\product\10.2.0\admin\KEYSTONE\udump>tkprof keystone_ora_4788.trc babu1.txt explain=babu/babu TABLE=babu.plan_table sys=no waits=yes

TKPROF: Release 10.2.0.1.0 - Production on Mon Jun 11 18:35:52 2007

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

Babu1.txt file output:

TKPROF: Release 10.2.0.1.0 - Production on Mon Jun 11 18:49:51 2007

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

Trace file: keystone_ora_4788.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

The following statements encountered a error during parse:

SELECT S.SID,S.SERIAL#,PA.VALUE || '\' || LOWER(SYS_CONTEXT('USERENV','INSTANCE_NAME')) || '_ORA' || P.SPID || '.TRC'|| AS TRACE_FILE

FROM V$SESSION S, V$PROCESS P, V$PARAMETER PA

WHERE PA.NAME = 'USER_DUMP_DEST'

AND S.PADDR = P.ADDR

AND S.AUDSID = SYS_CONTEXT ('USERENV','SESSIONID')

Error encountered: ORA-00936

--------------------------------------------------------------------------------

SELECT s.sid, s.serial#, pa.value || '/' ||

LOWER(SYS_CONTEXT('userenv','instance_name'))

|| '_ora_' || p.spid || '.trc' AS trace_file

FROM v$session s,

v$process p,

v$parameter pa

WHERE pa.name = 'user_dump_dest'

AND s.paddr = p.addr

AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Error encountered: ORA-00911

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 10 0.20 0.32 0 0 0 0

Execute 11 0.00 0.04 0 0 0 0

Fetch 16 0.03 0.07 0 0 0 19

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 37 0.23 0.44 0 0 0 19

Misses in library cache during parse: 7

Misses in library cache during execute: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 5 0.00 0.00

SQL*Net message from client 4 1208.32 1208.43

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 7 0.00 0.03 0 0 0 0

Execute 12 0.03 0.09 0 0 0 0

Fetch 12 0.00 0.01 1 29 0 5

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 31 0.03 0.14 1 29 0 5

Misses in library cache during parse: 5

Misses in library cache during execute: 3

11 user SQL statements in session.

14 internal SQL statements in session.

25 SQL statements in session.

0 statements EXPLAINed in this session.

********************************************************************************

Trace file: keystone_ora_4788.trc

Trace file compatibility: 10.01.00

Sort options: default

1 session in tracefile.

11 user SQL statements in trace file.

14 internal SQL statements in trace file.

25 SQL statements in trace file.

13 unique SQL statements in trace file.

435 lines in trace file.

3839 elapsed seconds in trace file.

Thanks & Regards

Babu B