Solved

Analyzing TKPROF - what does PARSE really mean

Posted on 2001-08-17
12
3,828 Views
Last Modified: 2012-08-13
I have been trying to understand what PARSE really means when looking at the results of TKPROF.  for example,

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      748      0.34       0.29          0          0          0           0
Execute    762      0.14       0.12          0          0          0           0
Fetch      648      0.14       0.12          2       2392          0         619
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2158      0.62       0.53          2       2392          0         619


Does Parse include looking in the Shared pool to see if this statement is already there?  Or if it increments the parse count, does this mean that teh shared pool was not used?  I am trying to understand if my shared pool is being used correctly or not.

Any help woudl be appreciated.
Mike
0
Comment
Question by:MJEngstrom
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6398205
Parse means how many times the statement had to be reparsed becoz it was no found in the Shared pool in the parsed form. So when ever it had to parse a statement it will increment the value by one. The parse od the sql statements is avoided if bind variables are used more and more and so sql statement is reused again again coz it is present in the shared pool in already parsed form. The statements need to be reparsed if there is even sightlest  of difference for exp select * from emp and select * from Emp will be parsed seperatly.
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6398251

Not quite:   the is the meaning of "PARSE Count" column, not of what PARSE means or does.

Using this example:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      748      0.34       0.29          0          0          0           0
Execute    762      0.14       0.12          0          0          0           0
Fetch      648      0.14       0.12          2       2392          0         619
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2158      0.62       0.53          2       2392          0         619



To quote the manual,
Oracle8i Designing and Tuning for Performance Release 2 (8.1.6) Part Number A76992-01
Chapter 6 -- Using SQL Trace and TKPROF
======================================================
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL column:

PARSE
This translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.  
 
EXECUTE
This is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.  
 
FETCH
This retrieves rows returned by a query. Fetches are only performed for SELECT statements.
 

The other columns of the SQL trace facility output are combined statistics for all parses, all executes, and all fetches of a statement. The sum of query and current is the total number of buffers accessed.
======================================================
COUNT
Number of times a statement was parsed, executed, or fetched.  
 
CPU
Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
 
ELAPSED
Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
 
DISK
Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
 
QUERY
Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Buffers are usually retrieved in consistent mode for queries.  

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

===================================================

So -- as you can see -- PARSE is:
1) a statement syntax check
2) a object check  (do the objects exists)
3) a security check -- does the user have access to the object referenced
4) does the statement exist in shared_pool (SQL AREA)
    if yes,  use that current execution plan
    if no,  generate an execution plan


so, UsamaMunir's explaination was only a piece of it's meaning.


Hope this helps answer your question & points you to a good reference in the oracle manual.

Sincerely,
dBalaski
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6398295
Thanx..Cleared my concepts!
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6398311
Just a Quick thought! Mr Dbalaski's explaination implies that each sql statement needs to be parsed (weather it is in Shared pool or not) then why isnt the number of parses and executions equal?.. just a Thought ?
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6398418
Hi,  

As I understand it,  certian steps are re-applied each time whether or not the statement is in shared pool.  (my points 1->4 are not the actual order in which things happen)

The order might actually be:
1) a) check if "Identical" statement is in shared_pool
   b) check if user has security to access objects

2) Assuming the security check passes:
 a) If found is in shared pool,
       then increament parse count & goto execution
   b) if not found  is not in shared pool
       then goto syntax check and execution plan generation


something like that...

dB


0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6398814
This is sql parsing flow diagram which will also help to understand more parsing at sql level


SQL Parsing Flow Diagram
========================

 Statement
 Submitted
 |
 Is it in an open cursor?--------------YES----V  
 |                                            |  
 NO                                           |  
 |                                            |
 Is SESSION_CACHED_CURSOR = Value             |  
 and cursor in           --------------YES----V   In these 3 cases we  
 Session Cursor cache?                        |   know that the cursor has
 |                                            |   already been parsed, so
 NO                                           |   re-parsing is
 |                                            |   unnecessary.
 Is HOLD_CURSOR=Y                             |
 and cursor in           --------------YES----V      
 Held cursor cache?                           |      
 |                                            |      
 NO                                           |        
 |                                            |              ^
 OPEN A CURSOR                                |  CLIENT SIDE |      
 |                                            | -------------|
 Statement is Hashed and compared             |  SERVER SIDE |
 with the Hashed value in the sql area        |              V
 |                                            V
 Is it in sql area? --YES-(Soft Parse)--> ---------
 |                                       |         |
 NO                                      | EXECUTE |
 |                                       |         |
 PARSE STATEMENT ('Hard' Parse)---------> ---------
 

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MJEngstrom
ID: 6399084
I guess I am still a little confused...  Does this mean then that my query did not match an existing query in the Shared Pool and had to re-parse it every time (or at least 748 times)?  OR does this not really tell us that.  If so, how do I know how many times my query really had to re-parse?  By Parse I mean the first 3 steps you identified (syntax check, data dictionary comparision, generate execution plan).

Thanks,
Mike
0
 

Author Comment

by:MJEngstrom
ID: 6399149
I guess I am still a little confused...  Does this mean then that my query did not match an existing query in the Shared Pool and had to re-parse it every time (or at least 748 times)?  OR does this not really tell us that.  If so, how do I know how many times my query really had to re-parse?  By Parse I mean the first 3 steps you identified (syntax check, data dictionary comparision, generate execution plan).

Thanks,
Mike
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6399631
Hi,
As i already explained every SQL SATEMENT is parsed first time when it is entered in the SGA and its hashed value is calcullated .During this phase proper security authorization has done and checks for the existence of tables, columns, and other referenced objects.  

During second time when the same sql statement is requested, Oracle checks its hash value with the existing hash value present in the SGA. if both are same then statment is not reparsed again and it goes to second phase that is fetch or execute.  

The reason the hash value does not match is the sql statement is not exactly identical that is may be you are not using bind variables or sql statment  is different every time  in terms of case etc
or your shared pool size is small enough to keep the cursor open after first execution.

When the Hash value does not match , oracle assumes that it is new sql statement and  again parsed it.


Hope this helps a little bit to understand this internal algorithum of parsing.

Regards
Sameer




0
 
LVL 3

Accepted Solution

by:
ramkb earned 100 total points
ID: 6401786

Hi there,

The experts before me have given a good explanation of SQL processing and parsing steps.

Although i would like to add some points precisely on interpreting the tkprof output.  There are 2 stages of parsing - Soft parsing and Hard parsing.  Soft parse does not involve parsing the statement, it just hashes the statement and compares this with the hashed value in the SQL area.  "Hard" parses are real parses (SQL statement parsing).  Both the soft and hard parse register as a parse in tkprof and updates the parse count.

You can use tkprof to determine if there is problem with hard parsing:
Look in the TKPROF output for the lines displayed after the "Total" PARSE, EXECUTE, FETCH lines. In particluar, look for the following lines:

"Misses in library cache during parse: n"
The n says the number of hard parsing occured for your statement.  i.e. If your parse count is 748 out of which your hard parse is 1, this is okay.  The rest 747 are just soft parses.  If there are too many hard parses, then you may definitely want to look for the reason (whether lack of bind variable in SQL, inadequate shared pool size).  Soft parse in itself utilizises CPU but is not as costly as Hard parse.

Interpretation in the previous post "Every SQL Statement need to be parsed" is not always true.
You can still eliminate Soft parsing by usage of Shared Cursors.  If you closely observe the SQL Flow diagram  given by Sameer above, you can see that Oracle does not even go to the stage of soft parsing as long as your SQL is in one of the 3 states (Open cursor state, Session Cached or Cursor cache is held).  I'm not sure what kind of application you are using.  In PL/SQL you can easily avoid soft parses, because PL/SQL handles cached cursors pretty efficiently.

You can also consider setting SESSION_SHARED_CURSORS parameter.
Oracle first looks at the statements pointed to by your private session cache - if a sharable version of the statement exists it can be used. This provides a shortcut access to frequently parsed statements that uses less CPU and uses far fewer latch gets than a soft or hard parse.

To get placed in the session cache the same statement has to be parsed 3 times within the same cursor - a pointer to the shared cursor is then added to your session cache.

All said, the Parsing cost involved is relative to the CPU usage (given by "parse cpu" value of your tkprof output).  Hence the bottom line is the parse count in itself does not necessitate the need for tuning as long as the cost involved is acceptable for the number of rows returned.

Cheers,
Ramesh
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7034717
Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7057454
Zero response, thus finalized.
Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now