TKProf Utility

-

TKProf Utility

TKProf (an acronym for the Transient Kernel PROFile) is an Oracle 
database utility used to format SQL Trace output into human 
readable format. The TKProf executable is located in 
the ORACLE_HOME/bin directory.

TKPROF reports each statement executed with the resources it 
has consumed, the number of times it was called, and the number 
of rows which it processed. This information lets you easily 
locate those statements that are using the greatest resource. 
experience or with baselines available, you can assess whether 
the resources used are reasonable given the work done.

You can ran a query to get your SPID, (server process ID) – 
this is very useful in identifying the trace file. 
Finally you can get the trace file in the directory on 
the database server specified by the USER_DUMP_DEST init.ora parameter.
You can retrieve the value of this parameter online by querying 
the V$PARAMETER view or by issuing show parameter dump at sql prompt.

The query is as follows:

select a.spid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(ʹsessionidʹ)
/


Syntax is:
tkprof input_file_name.trc output_file_name.txt [options]

Example:
$ tkprof orcl102_ora_3064.trc output.txt

Note: The trace file should be in your current working directory 
and your output file will be placed in the same directory.
You should check the trace_file/output_file as it contains 
information that will tell you whether you are looking 
at the correct file or not.


TKPROF has many command line options and if you just type TKPROF 
on the command line, you’ll get to see them all:

C:\Documents and Settings\....\Desktop>tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  ………………………………………………………………..
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:

    prscnt  number of times parse was called 
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    and the list goes on …..





Comments

Post a Comment

Back To Top

Popular posts from this blog

How to save video from Internet Explorer

-
How to save video from Internet Explorer Here I am going to share an experience with you, where I was required to get the video file that I was playing in Internet explorer out of Temporary directory and save it to some permanent disk folder. Note: It is far better if you use video download tools with the web browser. Internet Explorer Version: 7.0.6xxx [Open Internet Explorer – Help – About Internet Explorer] Operating System: Windows Vista I was not playing file from you tube [I do not see any tmp file while watching video from youtube.] I found the file in following directory: C:\Users\<UserName>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low [ Although the directory is not visible in my computer system, so I directly put it in address bar/location bar in windows explorer. ] File name was fla3870.tmp [May be in your system It will be by different name] You have to find the video file by trick technique for example: 1.
Read more

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more