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
> > > >
> > > > 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
> > > >
> > > > Fat City Network Services -- (858) 538-5051
>
> > > FAX: (858) 538-5051
> > > > San Diego, California -- Public
> Internet
> > > access / Mailing Lists
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> --
> 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).
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 - 10:58:22 CDT
