| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cursor Sharing| Soft Parsing
Thanks for sharing your interesting research Ian. If cached cursors obviate
the need for a parse call then why do they increment parse count (total)?
Despite all the list contributions it does not appear that this question has
been answered.
As Cary Millsap pointed out, there's a problem with definitions. AND... maybe Oracle statistics data is out of sync with the definitions and the shared pool/cached cursor features.
By definition a cursor is created by a parse (hard) of a valid SQL statement so if a cursor is cached then it follows that a parse is unnecessary. But the results of your query to v$sesstat contradict that.
To set the stage, here's some definitional material from the Oracle docs:
Concepts Manual:
"Oracle parses a SQL statement only if a shared SQL area for an identical
SQL statement does not exist in the shared pool. In this case, a new shared
SQL area is allocated and the statement is parsed."
"Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be executed repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be executed repeatedly without being reparsed."
Design/Tuning Manual:
"Oracle uses the shared SQL area to determine whether more than three parse
requests have been issued on a given statement. If so, Oracle assumes the
session cursor associated with the statement should be cached and moves the
cursor into the session cursor cache. Subsequent requests to parse that SQL
statement by the same session then find the cursor in the session cursor
cache."
It seems like there are three things going on here but only two Oracle stats. There's a parse (hard), there's a parse call (soft, and there are 3 kinds of "soft parses" according to Morle), and a "parse request" which may not result in any parse. It's like parse requests are incrementing parse count (total) whether or not a parse of any kind is actually being performed. Obviously I'm just guessing here.
So the unanswered question remains, if Oracle claims that a cached cursor hit obviates the need for either a "parse operation" OR a "parse call," then
why is "parse count (total)" incremented in v$sesstat ????????????????
Like Ian, I await an explanation!
Steve Orr
-----Original Message-----
Sent: Thursday, July 25, 2002 8:28 PM
To: Multiple recipients of list ORACLE-L
Importance: High
I didn't consider the invalidation possibilities. But here's more proof about Oracle still soft parsing with session_cached_cursors
The following was run directly after "session_cached_cursors" was set to 10.
select a.name, b.value from
v$sysstat a, v$sesstat b
where a.statistic# = b.statistic#
and a.statistic# in (179, 180, 181, 191)
and b.sid =16
/
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 12 parse count (hard) 0 execute count 12 session cursor cache hits 0 ---------------------------------------------------------------------------- -------------------
The following SQL was executed
select empno, ename, sal from scott.emp where empno = :v_empno;
and the session stats showed
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 25 parse count (hard) 2 execute count 27 session cursor cache hits 0
interate (2nd use of cursor)
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 26 parse count (hard) 2 execute count 28 session cursor cache hits 0
note hard parsing has stopped.
iterate (third use of cursor)
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 27 parse count (hard) 2 execute count 29 session cursor cache hits 0
interate (4th use of cursor)
SQL> /
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 28 parse count (hard) 2 execute count 30 session cursor cache hits 1
Hurray we finally got a cache cursor hit
interate (5th use of cursor)
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 29 parse count (hard) 2 execute count 31 session cursor cache hits 2
parse count is still increasing
one last try
interate twice (7th use of cursor)
NAME VALUE ---------------------------------------------------------------- --------- parse count (total) 31 parse count (hard) 2 execute count 33 session cursor cache hits 4 ---------------------------------------------------------------------------- --------------------
At first I was ready to state that session_cached_cursors do not stop soft parsing, then after my initial experiment I was ready to assert. I now proclaim it.
I also proclaim, "A statement is always soft parsed before any attempt in made to find it in cache. Using session_cached_cursors greatly reduces the cost of this search. It does not however stop soft parsing."
Again I await the proof to refute this proclamation.
Ian MacGregor
Stanford Linear Acclerator Center
ian_at_SLAC.Stanford.edu
-----Original Message-----
Sent: Thursday, July 25, 2002 4:43 PM
To: Multiple recipients of list ORACLE-L
Interesting. Sometimes you've got to test things and not just believe what you read.
> now if I revoke the permissions on the table.
Hmmm... if you modify a table all the associated shared SQL area is
invalidated. I wonder if something like that is going on when you alter user
privileges? Maybe the cached cursor is nolonger available?
Sometimes trying to figure out what Oracle is doing is like smashing sub-atomic particles together at the speed of light. You deduce the way it was put together by the way it broke into pieces. Kind of crude but what else can you do without the source code of the creator?
Steve Orr
-----Original Message-----
Sent: Thursday, July 25, 2002 3:58 PM
To: Multiple recipients of list ORACLE-L
Importance: High
I checked the Tom Kyte site. A soft parse comprises two operations. One
is a simple syntax check;
e.g. "select from dual;" would fail this soft parse as it is missing a
column list or a literal.
The other portion of a soft parse what he calls a semantics check is
checking to see if the tables and columns exist, that the person has the
proper permissions, that there are no ambiguities.
select deptno from emp, dept
where emp.deptno = dept.deptno
/
would fail this type of parse. My Kyte's definition of a soft parse jibes nicely with the one I used earlier. I didn't include the syntactical error portion as the statements in question are all valid SQL. However it is just as important. Semantic and syntactical checks are done; i.e.., a soft parse is done before the cache is checked.
Quoting from the article
"The next step in the parse operation is to see if the statement we are
currently
parsing has already in fact been processed by some other session. If it has
?
we may be in luck here, we can skip the next two steps in the process, that
of
optimization and row source generation. If we can skip these next two steps
in
the process, we have done what is known as a Soft Parse.
While writing this it has suddenly dawned on me what Suhen was talking about when said cursor_sharing = 'FORCE' avoids a hard parse at the cost of a soft.
If this is set
select * from emp where ename = 'KING';
will be soft parsed.
It will be changed to
select * from emp where ename = :bind_variable;
This statement will undergo soft parsing again.
If the statement can be found in cache; then no hard parsing is needed. The generation of the second SQL statement replacing the literal with a bind variables increases the likelihood of not having to hard parse.
1 select a.name, b.value
2 from v$sysstat a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.statistic# = 191
5* and b.sid = 8
SQL> /
NAME VALUE ---------------------------------------------------------------- --------- session cursor cache hits 10 ----------------------------------------------------------------------------
1* select ename from scott.emp where empno = :v_empno SQL> / ENAME
NAME VALUE ---------------------------------------------------------------- --------- session cursor cache hits 11
now if I revoke the permissions on the table.
SQL> /
select ename from scott.emp where empno = :v_empno
*
ORA-01031: insufficient privileges ---------------------------------------------------------------------------- ---------------
The article posted by Tom Kyte, does not state that session_cached_cursors avoids soft parses. It says they make finding the cursor less expensive. Particularly the expense of latching the shared pool and the library cache.
He runs a query 1000 times. Once without it being cached and again with it being cached and finds
NAME RUN1 RUN2 DIFF ---------------------------------------- ---------- ---------- ---------- LATCH.shared pool 2142 1097 -1045 LATCH.library cache 17361 2388 -14973 ============================================================================ ==================
The lesser latch count is for the query using session_cached cursors. Session_Cached_Cursors do save on resources and are important to scalability. But I have yet to see something which proves they stop soft parsing.
I saw Steve' Orr's contribution
"An entry is created
for the session's cursor cache and future cursor CLOSEs are ignored. Once in
the session cursor cache the SQL statement does not need to be reparsed.
This gives a significant performance boost!
Giving credit where due: The above was "inspired" from pages 277-280 in "Scaling Oracle8i" by James Morle."
I have posted material which refutes the above.
Again how does one avoid the soft parsing?
-----Original Message-----
Sent: Wednesday, July 24, 2002 11:43 PM
To: Multiple recipients of list ORACLE-L
Ian,
When coding you should parse once and execute the query many times rather than
loop
parse
bind
execute
close
end;
It can be seen that a parse operation is done on each iteration through the loop. You may have avoided hard parsing but the program is still soft parsing. It has to check the shared pool for the query executed each time.
When coding u should rather
parse
loop
bind
execute
end;
close;
So you would be parsing once and executing the query several times. Therefore reduction on latch contention which makes your application more scalable and hence better performance.
Check out
http://asktom.oracle.com/pls/ask/f?p=4950:8:1092060::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:2588723819082,%7Bsoft%7D%20and%20%7Bparsing%7D
Also see Bjorn's paper on bind variables
Cheers
Suhen
> Please define soft parsing. Oracle needs to check that the user
> submitting a SQL statement has permissions to run it. It has to do this
> every time a statement is run, bind variables or not. I thought the
> processing of the statement to check permissions to be soft parsing. But,
> perhaps I'm misinformed.
>
> When "cursor-sharing" converts a statement to use bind variables it
would
> save on hard parsing, if a match were found the pool; also, it could
lessen
> the number of statements present in the pool.
>
>
> >
>
>
>
>
>
>
>
>
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather INET: Suhen.Pather_at_strandbags.com.au -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Jul 26 2002 - 12:00:13 CDT
 |
 |