RE: buffer advisor (has become: should there be a private sql area?)
Date: Wed, 10 Sep 2014 10:07:40 -0700
Message-ID: <BLU179-W85631427C92284DB4DB8BFEBCF0_at_phx.gbl>
+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 Wed Sep 10 2014 - 19:07:40 CEST