Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

TKPROF’s Argument PDBTRACE

21 March 2018 5 Comments Written by Christian Antognini

If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them (here the output generated by version 18.1.0):

$ 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.
  pdbtrace=user/password   Connect to ORACLE to retrieve SQL trace records.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT 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
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

$ 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. pdbtrace=user/password Connect to ORACLE to retrieve SQL trace records. aggregate=yes|no insert=filename List SQL statements and data inside INSERT 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 prsqry number of buffers for consistent read during parse prscu number of buffers for current read during parse prsmis number of misses in library cache during parse execnt number of execute was called execpu cpu time spent executing exeela elapsed time executing exedsk number of disk reads during execute exeqry number of buffers for consistent read during execute execu number of buffers for current read during execute exerow number of rows processed during execute exemis number of library cache misses during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchela elapsed time fetching fchdsk number of disk reads during fetch fchqry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchrow number of rows fetched userid userid of user that parsed the cursor

If you carefully check the output, you can notice an argument that does exist only from version 12.2 onward: “pdbtrace”. If I correctly remember, since the introduction of TKPROF in Oracle7, only another time Oracle added a new argument. It was “waits” in Oracle9. Interestingly, the documentation provides no information about it.

So, the question is: what does the new argument do?

Since its name contains “pdb”, one might think that it is related to Multitenant. But, after a number of tests, it does not seem to be related to it. Simply put, “pdbtrace” can be used to process a trace file without requiring direct access to the OS where the trace file is stored. Instead, when “pdbtrace” is specified, the trace file is read through one of the dynamic performance views introduced in version 12.2 to access them (e.g. V$DIAG_TRACE_FILE and V$DIAG_TRACE_FILE_CONTENTS; have a look to this post for some basic information about them).

For example, the following command instructs TKPROF to connect a database instance with the specified user and password, to read the input file DBM1810S_ora_18264.trc, and produce the output file DBM1810S_ora_18264.txt locally.

$ tkprof DBM1810S_ora_18264.trc DBM1810S_ora_18264.txt pdbtrace=chris/secret@pdb1

$ tkprof DBM1810S_ora_18264.trc DBM1810S_ora_18264.txt pdbtrace=chris/secret@pdb1

All in all, except for the name, a good addition to TKPROF.

Addenda 2018-03-22

Be careful that when the “pdbtrace” argument is specified, the input trace file can’t specify the directory where the trace file is located. If a directory name or a non-existent trace file is specified, no interactive error message is shown. Instead, an empty output file is generated. Similarly, if an invalid user, password, or connect string is specified, an empty output file is generated without any interactive error message. Instead, an error like the following will be found in the output file just before the header:

error connecting to database using: scott/lion
ORA-01017: invalid username/password; logon denied

error connecting to database using: scott/lion ORA-01017: invalid username/password; logon denied

12cR2, 18c, Documentation, TKPROF
Scripts to Download Oracle Database 18c Documentation
Partition-Wise Operations – New Features in 12c and 18c

5 Comments

1 Ping/Trackback

  1. Connor McDonald Connor McDonald
    22 March 2018    

    And just to add…this solves the time old problem of server access.

    If you give developers tkprof on their *client* machines, then they can get a local tkprof-formatted file without ever gone near the server.

    Very cool!

    Reply
  2. Daniel Oderbolz Daniel Oderbolz
    26 March 2018    

    I sinply *love* undocumented features!
    Thanks for the writeup!

    Cheers
    Daniel

    Reply
  3. TKPROF’s Argument PDBTRACE - SSWUG.ORG TKPROF’s Argument PDBTRACE - SSWUG.ORG
    10 April 2018    

    […] Read More (Community […]

  4. Andrea Andrea
    28 September 2018    

    Hi.
    A limitation I have found is that it is not RAC aware.
    In the sense that, TKPROF has to connect to the same instance where the trace was generated, or you won’t have any output (i.e.: it queries V$DIAG_TRACE% instead of GV$DIAG_TRACE%).

    Do you know if it is possible to bypass this behaviour?
    Thanks,

    Reply
    • Christian Antognini Christian Antognini
      1 October 2018    

      Hi Andrea

      Thank you for pointing out that limitation.
      I didn’t notice that and, as a result, I’m not aware of a workaround.

      Best,
      Chris

      Reply
  1. TKPROF’s Argument PDBTRACE - SSWUG.ORG on 10 April 2018 at 07:00

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.