Home » RDBMS Server » Performance Tuning » TKPROF output analysis (windows 2012, oracle 12c)
TKPROF output analysis [message #640089] Wed, 22 July 2015 02:20 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

I have some query to be guided.
Hope no one get annoy.

In the attached screen shot of the TKPROF output.

/forum/fa/12772/0/

I have understand it in the below manner, kindly correct me.

1) The single query has Parsed (Hard + Soft) 12 Times.
2) Out of the 12 times Parsing, the queries syntax and other basic check were success, hence exactly 12 Times it had been Executed successfully.
3) FETCH process happened 25 Times. This should be 12 only as same count as PARSE. Am I right?
4) DISK read (Hard Parsing) for FETCHING is 5 Times and Soft Parsing is 87 Times. And the no of rows fetched is 22.
5) If the SQL Statement is just a SELECT statement, then how the count could be 4 against the PARSE column?

Thanks and Regards.
Muktha
Re: TKPROF output analysis [message #640090 is a reply to message #640089] Wed, 22 July 2015 02:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off, if you're copying a text report then you should copy and paste it as text in code tags rather than paste an image.
1) yes
2) yes
3) Why would it be 12? Say you have a cursor loop, you open the cursor, fetch a row and then loop until you run out of rows - in that case fetch will equal the number of rows fetched as you issue one fetch command per row. One the other hand, if you fetch the contents of the cursor using a single bulk collect then fetch will be 1.
4) disk read has nothing to do with hard-parsing
Re: TKPROF output analysis [message #640091 is a reply to message #640089] Wed, 22 July 2015 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

3) Wrong, fetch and parse are not related. If you have an array size of 1 you fetch for each row (+1 to return "no more data").
4) Disk reads and hard parses are not directly related, here you have disk reads only when you fetch (whatever was the parsing step). Query is not soft parses it is logical reads.
5) It is the number of logical reads to parse.

For an explanation of this chart read SQL Trace Statistics for Parses, Executes, and Fetches

For a detailed explanation about TKPROF read Database SQL Tuning Guide, section 18.4 Generating Output Files Using SQL Trace and TKPROF.

[Updated on: Wed, 22 July 2015 02:50]

Report message to a moderator

Re: TKPROF output analysis [message #640149 is a reply to message #640091] Thu, 23 July 2015 01:37 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi ALL,

Thanks for the reply.

As per the document

"CURRENT Column - Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE."


In my above question, The SQL Statement was a QUERY, which is just reading.
Then Why it is showing the No of 4 against CURRENT Column?

Regards
Muktha
Re: TKPROF output analysis [message #640150 is a reply to message #640149] Thu, 23 July 2015 01:39 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is explained in the link I gave you.

Previous Topic: Tuning data deletion that is taking long time
Next Topic: A way to identify the top sql other than AWR
Goto Forum:
  


Current Time: Thu Mar 28 05:41:32 CDT 2024