RE: buffer advisor (has become: should there be a private sql area?)
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Sep 2014 10:07:40 -0700
+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:
