RE: buffer advisor (has become: should there be a private sql area?)
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Fri, 12 Sep 2014 11:45:22 -0700
Message-ID: <BLU179-W48083B3DC27BE098C04D2EEBCD0_at_phx.gbl>
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 11:45:22 -0700
Message-ID: <BLU179-W48083B3DC27BE098C04D2EEBCD0_at_phx.gbl>
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_at_evdbt.com
To: oracle-l_at_freelists.org
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_at_rsiz.com
To: mark.powell2_at_hp.com; oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_rsiz.com]
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Tuesday, September 09, 2014 11:09 PM
To: Hemant-K.Chitale_at_sc.com;
'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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Wednesday, September 10, 2014 10:48
AM
To: iggy_fernandez_at_hotmail.com;
jeremy.schneider_at_ardentperf.com
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Iggy Fernandez
Sent: Tuesday, September 09, 2014 9:25 PM
To: jeremy.schneider_at_ardentperf.com
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 12 2014 - 20:45:22 CEST