explain in tkprof

Eugene

unread,

Aug 1, 2007, 12:14:29 PM8/1/07

to

Hi all,
I am running Oracle 8.1.6.3 on Win NT4 and having a prblem displaying
explain plan in tkprof.
-----------------------------------------------------------------------
set oracle_sid=sid
tkprof ora00128.trc tk_out.txt sort=exeela,fchela explain=sys/
pass@my_db;
-----------------------------------------------------------------------
I am getting an output file just fine, but w/out explain plan.
File has the fillowing error it:ORA-12154: TNS:could not resolve
service name,
I can login using same usernam/pass in SQL*Plus however.

Thanks,
Eugene

fitzj...@cox.net

unread,

Aug 1, 2007, 12:47:31 PM8/1/07

to

The tkprof utility accepts user and password for explain; it doesn't
accept TNS aliases. Please read the documentation:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch14_str.htm#1256

David Fitzjarrell

Charles Hooper

unread,

Aug 1, 2007, 12:59:58 PM8/1/07

to

David Fitzjarrell likely provided the answer that you need.

There is an interesting comment in Tom Kyte's "Expert Oracle One on
One" book, pages 461-62, that relate to the use of EXPLAIN with
TKPROF:
"I prefer to never use the EXPLAIN= however and would recommend the
same for you. The reason is that the explain plan query may differ
radically from the actual query used at run time. The only plan than
can be trusted is the plan saved in the trace file itself."

If the "Row Source Operation" heading appears in the TKPROF output,
that is the real plan. If it does not, directly open the trace file
and search for "STAT #" (without the quotes). The STAT lines only
print when the cursor is closed.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Brian Peasland

unread,

Aug 1, 2007, 12:32:49 PM8/1/07

to

Since you specified "@my_db", is my_db defined in your tnsnames.ora
config file? My bet is that 1) it is not defined there or 2) your
session is not using that tnsnames.ora config file.

If you can log in with SQL*Plus without using "@my_db", then do not
specify it with tkprof either. Otherwise, either define this entry in
your tnsnames.ora or use the TNS_ADMIN environment variable to point to
the directory holding this file.

HTH,
Brian

--
===================================================================

Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

darji....@gmail.com

unread,

Aug 2, 2007, 11:51:08 AM8/2/07

to

Use the following to point to the correct tnsnames.ora and try again.

set TNS_ADMIN=C:\ORANT\network\admin

Thanks
Nilesh

Steve Howard

unread,

Aug 2, 2007, 5:00:15 PM8/2/07

to

On Aug 1, 12:14 pm, Eugene <epi...@gmail.com> wrote:

I'm not sure and didn't test, but any connection as sys usually
requires SYSDBA qualifier (with the exception of RMAN). It may be
something to check, at least, trying another user.

HTH,

Steve

hpuxrac

unread,

Aug 2, 2007, 8:30:43 PM8/2/07

to

On Aug 1, 12:14 pm, Eugene <epi...@gmail.com> wrote:

You might want to consider using orasrp. Much more comprehensive
output and more accurate than tkprof.

See Cary Millsap's book "Optimizing Oracle Performance" or www.hotsos.com