asked on

Analyzing TKPROF - what does PARSE really mean

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

UsamaMunir

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.

dbalaski

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

UsamaMunir

Thanx..Cleared my concepts!

UsamaMunir

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 ?

dbalaski

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

Wadhwa

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)---------> ---------

MJEngstrom

ASKER

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

MJEngstrom

ASKER

Wadhwa

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

ASKER CERTIFIED SOLUTION

ramkb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Moondancer

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.

Moondancer

Zero response, thus finalized.
Moondancer - EE Moderator