Ian
Maybe just the semantics are causing bother..
Any parse call on a statement will always end up as a
parse (soft or hard) and many programmatic
environments (SQL Plus for example) always requests a
parse for every statement entered. The session cache
will make this process quicker.
However - due to that above fact that many
environments always parse every time you run a
statement, maybe some posters use the term "a
statement" and "execution of a statement"
interchangeably..and of course, the key for whizz-bang
performance is to separate the two and aim for
multiple executions of a single statement - something
that (for example) you could not simulate in SQL Plus,
but certainly could in PL/SQL
Cheers
Connor
- "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
wrote: > 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.
>
> ----------------------
> 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).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 07:03:21 CDT
