Friday 19 April 2019

TKPROF Concepts with Syntax and Real time example

Hey DBA-Mates, Topic: Tkprof Syntax with Example
We would like to share one more dba stuff Tkprof here as it is very easy and useful also… It is a very useful for any DBAs.

Tkprof is the basic performance diagnostic tool that help us to monitor and tune applications running against the oracle server.

It will be help us to know the accurate access the efficiency of the SQL statements.

TKPROF is used to format the content of the trace file and put output into readable format file.
It is also used for Determine the execution plan of SQL statements.

Below is the real time example for tkprof:

Syntax: tkprof <trace_filename> <output_format_filename> explain=apps/<apps_pwd> sort=exeela,prsela,fchela sys=yes

There are many more option but usually we use these options. It depends as per requester.

Here,
Exeela: Elapsed time spent Execution
Fchela: Elapsed time spent Fetching.
Prsela: Elapsed time spent Parsing.
SYS = Yes/No: it is used for enable or disable the list of SQL statements issued by the SYS user.

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump

$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989.trc
-rw-r--r--   1 sku   dba        278986564 Aug 12 23:17 ORA-DATA_ora_27722_ORADATA_CR695975989.trc

Command:

$tkprof <ORA-DATA_ora_27722_ORADATA_CR695975989.trc> <ORA-DATA_ora_27722_ORADATA_CR695975989.txt>  explain=apps/apps  sort=exeela,prsela,fchela sys=yes

TKPROF: Release 9.2.0.8.0 - Production on Thu Aug 13 15:39:13 2015
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump
$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989.txt
-rw-r--r--   1 sku   dba         162819 Aug 13 15:39 ORA-DATA_ora_27722_ORADATA_CR695975989.txt

$ $ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989*
ksh: $:  not found

$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989*
-rw-r--r--   1 sku   dba        278986564 Aug 12 23:17 ORA-DATA_ora_27722_ORADATA_CR695975989.trc
-rw-r--r--   1 sku   dba         162819 Aug 13 15:39 ORA-DATA_ora_27722_ORADATA_CR695975989.txt

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump
$ exit
logout
$ exit

Hope this may useful and helpful here.

Some more useful links:



Regards,

6 comments:

  1. I absolutely love your blog.. Pleasant colors & theme.

    Did you develop this site yourself? Please reply back as I'm
    hoping to create my own personal blog and want to learn where you got this from or what the theme is named.

    Appreciate it!

    ReplyDelete
  2. Wow, this piece of writing is nice, my sister is analyzing
    such things, therefore I am going to let know her. Wow!

    This blog looks just like my old one! It's on a totally different topic but it has pretty much the same layout and design.
    Excellent choice of colors! I have been surfing on-line greater than 3 hours these days, but I by no means found any attention-grabbing article like yours.
    It’s lovely worth sufficient for me. Personally, if all website owners and
    bloggers made just right content as you did, the net will likely
    be a lot more helpful than ever before. http://Foxnews.net/

    ReplyDelete
  3. And the struggle for the Iron Throne begins.

    ReplyDelete
  4. Many knowledge connected with un-ambiguity along with preserveness regarding important go through on the subject from unanticipated inner thoughts.

    ReplyDelete
  5. I have been exploring for a bit for any high quality articles or blog posts in this kind of area
    . Exploring in Yahoo I at last stumbled upon this website.
    Studying this info So i am satisfied to convey that I have a very excellent uncanny feeling I discovered exactly what I needed.
    I so much no doubt will make sure to don?t disregard this website and provides it a
    glance on a relentless basis.

    ReplyDelete
  6. Hey! I know this is kinda off topic however
    I'd figured I'd ask. Would you be interested
    in trading links or maybe guest authoring a blog post or
    vice-versa? My website discusses a lot of the same topics as yours and I feel we could
    greatly benefit from each other. If you happen to be interested feel free to
    shoot me an email. I look forward to hearing from you!
    Excellent blog by the way!

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.