Re: buffer advisor (has become: should there be a private sql area?)
- From: Seth Miller <sethmiller.sm@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Fri, 12 Sep 2014 15:47:13 -0500
Mark,
I like your line of thinking but isn't this essentially what the recycle
cache is for?
Seth Miller
On Fri, Sep 12, 2014 at 2:08 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> Ah, my good friends. It **could** scale well for scalable applications
> yet go private for plans unlikely to scale OR be sharable. And as a
> bail-out versus failing.
>
>
>
> Oh, and do let’s toss out some granules of buffer cache at the boundary to
> avoid “out of shared pool” errors as if that is more scalable than a
> private parse.
>
>
>
> And while I disagree with Oracle’s design choice in this matter, I do
> understand it may well have been a total code size compromise.
>
>
>
> Of course IF Oracle was pga parse capable, I’d also vote for the hint --+
> private_parse so that when you did control the code and knew in advance you
> didn’t want to either wait to share (even latch to check the pool) and you
> wanted a fresh parse specific to your query (possibly containing binds),
> then that is what you’d get. And of course if you did this to all your low
> volume sql, then the sql with a high fan-in for your application mix would
> likely stick around. Anticipating that it would stay around, you might even
> pre-parse everything you did want in the shared pool before peak load, not
> worrying about a little extra load when you have free CPU. That, I think,
> would really be scalable for a lot of places. And fail only when under a
> sustained attack.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Iggy Fernandez
> *Sent:* Friday, September 12, 2014 2:45 PM
> *To:* tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: buffer advisor (has become: should there be a private sql
> area?)
>
>
>
> I have to disagree with you vehemently dear Tim (because I just flunked a
> job interview and am in a combative mood this morning).
>
>
>
> <vehement counterattack>
>
> Why is "operate in degraded mode" such a bad thing? Why is "give the
> customer a choice" such a bad thing? Why is "kill a critical business
> process" the right thing?
>
> </vehement counterattack>
>
>
>
> Iggy
>
>
>
> ------------------------------
>
> Date: Fri, 12 Sep 2014 08:01:11 -0600
> From: tim@xxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: buffer advisor (has become: should there be a private sql
> area?)
>
> Not really mind-boggling. This is a scalability mechanism, so it's rarely
> only one session that will be unable to share, it will be undoubtedly be
> many.
>
> You don't hear people saying, "it's only *one* person with Ebola, just
> stabilize 'em and send 'em home".
>
>
>
> On 9/10/14, 11:07, Iggy Fernandez wrote:
>
> +42
>
>
>
> It boggles the mind that a session must die if it cannot share #ShareOrDie
>
>
>
> Iggy
>
>
> ------------------------------
>
> From: mwf@xxxxxxxx
> To: mark.powell2@xxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: RE: buffer advisor (has become: should there be a private sql
> area?)
> Date: Wed, 10 Sep 2014 13:01:43 -0400
>
>
>
> The design choice is not limited to converting versus flooding; you leave
> out the alternative: “versus not searching the pool, not using latches, and
> just parsing it privately.”
>
>
>
> When parsing is a significant part of the total query cost and there is a
> high fan in, sharing is essential to scalability and mapping tends to work.
>
>
>
> When parsing is an insignificant part of the total query cost sharing is
> not relevant to performance.
>
> When no similar query is likely to be used, sharing is probably just
> overhead. Likewise, you don’t define outlines for things that have not be
> run before.
>
> When sharing drives an avoidable error, that is the ultimate performance
> penalty.
>
>
>
> Since private parsing has not been available since 6 (when it was the only
> choice), it is correct to ignore it as a trouble-shooting mechanism. It is
> not irrelevant in the context of an enhancement request to avoid shared
> pool contention and space errors or reduce the time to parse of simple
> queries containing literals. MANY literal queries retrieve one row by an
> obvious one row choice index. IF plan creation started with the heuristic:
> Is there an obvious plan that delivers cost < epsilon or cost < psi where
> epsilon is some multiple of the cost to look for a better plan and psi is
> the cost to search the shared pool, then avoiding sharing could be a gated
> scaling benefit.
>
>
>
> Flooding the shared pool with un-sharable SQL is clearly a bad idea.
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Powell, Mark
> *Sent:* Wednesday, September 10, 2014 12:24 PM
> *To:* 'ORACLE-L'
> *Subject:* RE: buffer advisor
>
>
>
>
>
> And sometimes you do not. The question becomes would automatically
> converting constants to bind variables in all cases be more beneficial to
> the overall system verse flooding the shared pool with un-sharable SQL? In
> the case where the code runs better with a constant then there are Outlines
> and SQL Profiles to assist with tuning. Then again if Oracle works this
> way bind variable peeking might just peek on every execution for statements
> where it might make a difference.
>
>
>
>
>
> *From:* Mark W. Farnham [mailto:mwf@xxxxxxxx <mwf@xxxxxxxx>]
> *Sent:* Wednesday, September 10, 2014 11:19 AM
> *To:* Powell, Mark; 'ORACLE-L'
> *Subject:* RE: buffer advisor
>
>
>
> Because sometimes you get a superior plan with a literal (by a lot), and
> then we’d have to hint one way or the other and we cannot do that because
> sometimes (often) we do not control the source code.
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Powell, Mark
> *Sent:* Wednesday, September 10, 2014 11:03 AM
> *To:* 'ORACLE-L'
> *Subject:* RE: buffer advisor
>
>
>
> Why not automatically convert all constants to bind variables as part of
> the parse? This would make nearly identical SQL statements where only the
> value of the constant changes into identical statements supporting sharing.
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Mark W. Farnham
> *Sent:* Tuesday, September 09, 2014 11:09 PM
> *To:* Hemant-K.Chitale@xxxxxx; 'ORACLE-L'
> *Subject:* RE: buffer advisor
>
>
>
> There is no question that reusing reusable sql parses was a huge advance
> for scalability. But tossing non-reusable sql in the shared pool just puts
> extra pressure on the latches.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Chitale, Hemant K
> *Sent:* Tuesday, September 09, 2014 10:57 PM
> *To:* ORACLE-L
> *Subject:* RE: buffer advisor
>
>
>
> The shared pool came at the same time as database server pl/sql --- if I
> remember correctly. [there was forms pl/sql available earlier]
>
> Was there co-development or dependency ?
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Mark W. Farnham
> *Sent:* Wednesday, September 10, 2014 10:48 AM
> *To:* iggy_fernandez@xxxxxxxxxxx; jeremy.schneider@xxxxxxxxxxxxxx
> *Cc:* 'Seth Miller'; 'Oracle-L Freelists'
> *Subject:* RE: buffer advisor
>
>
>
> In fact, why not parse all sqls with literals privately? It worked just
> fine in V6.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Iggy Fernandez
> *Sent:* Tuesday, September 09, 2014 9:25 PM
> *To:* jeremy.schneider@xxxxxxxxxxxxxx
> *Cc:* Seth Miller; Oracle-L Freelists
> *Subject:* RE: buffer advisor
>
>
>
> I wish that the ORA-4031 issue could be solved permanently
> #IfWishesWereHorses
>
>
>
> Is a solution to this problem beyond the capabilities of mortals?
> #ShoutingIntoTheWind
>
>
>
> Why is it necessary for a session to die if it cannot write to the shared
> pool? Why not just do what it needs to do even if it cannot share?
> #AskingTheObvious
>
>
>
> Iggy
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>
>
>
Other related posts:
