Re: bad ratio in parse_calls to executions of SQL

From: Harald Wakonig <wakonig_at_compuserve.com>
Date: Sun, 26 Dec 1999 14:43:59 +0100
Message-ID: <38661B9E.6287EDE1@compuserve.com>

Hallo,
Hi,

> Oracle does two types of parses - Hard Parse and Soft Parse.

as you are very knowledgable about parsing, I want to ask some add-on questions about that topic.

Q1) you distinct between "soft parses" and "hard parses" - which of them are shown in v$sqlarea? Only the "hard parses" ?

---

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA ORDER BY PARSE_CALLS; Q2) Steps during parsing (from Oracle-Concepts Manual)

---

Please can you determine, which steps are in a hard-parse and which are in a soft-parse ?

---

(1) translating a SQL statement, verifying it to be a valid statement

(2) performing data dictionary lookups to check table and column definitions

(3) acquiring parse locks on required objects so that their definitions do not
change during the statement’s parsing

(4) checking privileges to access referenced schema objects

(5) determining the optimal execution plan for the statement

(6) loading it into a shared SQL area

(7) for distributed statements, routing all or part of the statement to remote
nodes that contain referenced data

Some details which I try to understand:

I understand, that Step (4) needs to be done by each execution - it's possible, that a statement now executed by "user2" is identical with the statement which has been executed by "user1" , but "user2" just does not have the privilege to access one of the tables...

Step (1) definitely needs only be done once; an statement which is already in the shared pool is identified by comparing the hash-value of the new statement with hash-values of statements existing in the shared pool. I understand that.

Step (2) might be necessary for each execution - user2 might have a synonym "EMP" pointing to a different object or in general a public synonym "EMP" pointing to "app_user.emp" is used, but "user2" has an EMP-Table in his own schema .. (which has no index and would require an different execution plan)

Q3) Soft Parse and library cache latch and shared pool latch

---

you point out, that for each execution a soft-parse is necessary. Does this soft-parse require one of those two latches (assuming that there is enough Shared Pool size)?

I assume the shared pool latch is used in Step (6) , which is after step (4) - and (4) is very likely part of the soft parse. Or is (4) done without doing (6), if the statement is already there ?

Q4) Is Step (5) - determine the optimal execution plan - part of the "soft parse" or part of the "hard parse" ?

---

I am very interested in that answer in the context of the decission between Index-Range-Scan versus Full-Table-Scan, especially when using histograms and the statement uses bind-varibles ! (..WHERE sal > :v_sal );

Thank you,

Harald Received on Sun Dec 26 1999 - 07:43:59 CST