RE: buffer advisor (has become: should there be a private sql area?)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Seth Miller'" <sethmiller.sm@xxxxxxxxx>
- Date: Fri, 12 Sep 2014 20:53:42 -0400
buffer cache versus sql pool
For buffers, we do have direct read to the pga now.
For sql, we do not. Logically similar ideas. (although you still have to do
block cleanout and read consistency in the pga for buffers),
for private sql you wouldn’t need to latch the shared pool and the parse is
fresh.
mwf
From: Seth Miller [mailto:sethmiller.sm@xxxxxxxxx]
Sent: Friday, September 12, 2014 4:47 PM
To: Mark W. Farnham
Cc: Iggy Fernandez; Tim Gorman; Oracle-L Freelists
Subject: Re: buffer advisor (has become: should there be a private sql area?)
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] 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]
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] 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] 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] 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] 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] 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:
