| 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
After 3 soft parses, session cached cursors kicks in. I think that that is
documented some where. The best thing to do is rewrite the apps (not only
the softparsing will get fixed but also network traffic will get reduced
(some times significantly, because we don't send the sql statement text over
every time)).
Wouldn't it be nice to have a client side fix without changing the apps? Who would be interested in that? Drop me an email ;-)
Anjo.
> 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
> --------------------------------------------------------------------------
--Received on Fri Jul 26 2002 - 03:53:19 CDT
> ------------
>
> 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.
> --------------------------------------------------------------------------
--
> ----------------------
> Now about session_cached_cursors. First checking the hits
>
> 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
>
>
> --------------------------------------------------------------------------
--
> ------------
> running the statement
>
> 1* select ename from scott.emp where empno = :v_empno
> SQL> /
>
> ENAME
> ----------
> MILL
>
> If I run the query to ge the session cached cursors statement. I see it
> has been incremented.
>
> NAME VALUE
> ---------------------------------------------------------------- ---------
> session cursor cache hits 11
>
> now if I revoke the permissions on the table.
>
>
============================================================================
> ====================
> I get
>
> SQL> /
> select ename from scott.emp where empno = :v_empno
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> --------------------------------------------------------------------------
--
> ---------------
> Sure seems like the statement is undergoing a semantics check despite the
> availability of a cached cursor.
>
> 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.
> >
> > Ian MacGregor
> > Stanford Linear Accelerator Center
> > ian_at_SLAC.Stanford.edu
> >
> > -----Original Message-----
> > Sent: Wednesday, July 24, 2002 9:23 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Mike, Kirti,
> >
> > Try page 441
> >
> > CURSOR_SHARING=FORCE does improve badly written applications that use
lots
> > of literals.
> > However coding should be done using bind variables in almost all
> occasions.
> >
> > CURSOR_SHARING=FORCE reduces the hard parsing.
> >
> > What CURSOR_SHARING=FORCE does is rewrites all queries to use bind
> > variables before parsing.
> >
> > eg. select ename from emp where empno = 10;
> > rewritten as
> > select ename from emp where empno =:SYS_B_0
> > or in 8.1.6 , 8.1.7
> > select name from emp where empno =:"SYS_B_0"
> >
> > So it substitutes the literal with bind variables but incurs the cost of
> > soft parsing the statement.
> > Soft Parsing too frequently limits the scalability of applications and
> > sacrifices optimal performance which could have been achieved in the
first
> > place if written using bind variables.
> >
> > Parse once and execute as many times as we like.
> >
> > Also check out Bjorn's paper on bind variables and cursor sharing at
> > http://technet.oracle.com/deploy/performance/pdf/cursor.pdf
> >
> > So CURSOR sharing is not the "silver bullet" as one may expect.
> >
> > Regards
> > Suhen
> >
> > On Thu, 25 Jul 2002 10:23, you wrote:
> > > Mike,
> > > What is the version of the database? Some versions of 8.1.7 had a few
> > > bugs when this parameter was set to FORCE. I suggest searching
Metalink.
> > > But it does work as advertised in later releases. I would also
recommend
> > > reviewing Tom Kytes' book to read about his views in using this
> parameter
> > > at the instance level (my boss is reading my copy, so I can't give you
> > > page #s).
> > >
> > > - Kirti
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, July 24, 2002 6:08 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Has anyone set Cursor Sharing to Force ?
> > > I have a new system that we have to support
> > > and there is alot literals filling up the
> > > pool. I have never changed this parameter
> > > from the default as many seemed to think the
> > > jury was still out on it. However, due to
> > > my situation, I figured I would try it out.
> > > If anyone has any experience with this one
> > > I would be curious to know what happened.
> > >
> > > Mike
> --
> 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: Anjo Kolk INET: anjo_at_oraperf.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).
 |
 |