| 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
I'll admit I'm gullible, and I do look to you as more knowledgeable
than me about internals.
On the other hand, I'm a born and bred New York City girl.... and I've never "bought a bridge" yet. guess I'll have to live with "because" as the answer
Get on with you.... :)
Rachel
--- Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
> Its three because the mapping of the memory structures
> to the pga is most efficient when the hashing function
> involved uses the lowest prime that is not a power of
> two.
>
> (long pause)
>
> ...and if you believe that, you'll believe anything
> :-)
>
> hee hee hee
>
> Connor
>
> --- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> > Connor,
> >
> > Well, technically that answers the "why"... with
> > Oracle's "because I
> > said so" :)
> >
> > the REAL question is why 3 and not 2 or 4 or 10
> > or..... ?
> >
> > Rachel
> >
> >
> > --- Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
> > > "RTM" :-)
> > >
> > > Performance Guide 9.2
> > >
> > > "Caching Session Cursors
> > >
> > > (blah blah blah)
> > >
> > > Oracle checks the library cache to determine
> > whether
> > > more than three parse requests have been issued on
> > a
> > > given statement.
> > >
> > > (more blah blah blah)"
> > >
> > > hth
> > > connor
> > >
> > >
> > > --- Jared.Still_at_radisys.com wrote: > I wasn't
> > aware
> > > of it requiring three calls before
> > > > being useful.
> > > >
> > > > Why is that?
> > > >
> > > > Jared
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Cary Millsap" <cary.millsap_at_hotsos.com>
> > > > Sent by: root_at_fatcity.com
> > > > 07/25/2002 07:58 AM
> > > > Please respond to ORACLE-L
> > > >
> > > >
> > > > To: Multiple recipients of list
> > ORACLE-L
> > > > <ORACLE-L_at_fatcity.com>
> > > > cc:
> > > > Subject: RE: Cursor Sharing| Soft
> > > > Parsing
> > > >
> > > >
> > > > Well, three times, right? I think it takes three
> > > > parse calls before
> > > > session_cached_cursors begins to help. But 3 is
> > > > still O(1). Once per
> > > > call is O(#executions).
> > > >
> > > >
> > > > Cary Millsap
> > > > Hotsos Enterprises, Ltd.
> > > > http://www.hotsos.com
> > > >
> > > > Upcoming events:
> > > > - Hotsos Clinic, Jul 23-25 Chicago
> > > > - Miracle Database Forum, Sep 20-22 Middlefart
> > > > Denmark
> > > > - 2003 Hotsos Symposium on OracleR System
> > > > Performance, Feb 9-12 Dallas
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > Still
> > > > Sent: Thursday, July 25, 2002 2:38 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > > On Wednesday 24 July 2002 22:08, MacGregor, Ian
> > A.
> > > > wrote:
> > > > > 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.
> > > >
> > > > No, code that uses bind variables need only
> > parse
> > > > SQL statements
> > > > once if session_cached_cursors is set. Further
> > > > executions of the same
> > > > SQL don't require a hard or soft parse.
> > > >
> > > > Jared
> > > >
> > > > > 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
> > > > >
>
=== message truncated ===
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: wisernet100_at_yahoo.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:54:34 CDT
 |
 |