Re: buffer advisor
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: andrew.kerber@xxxxxxxxx
- Date: Fri, 12 Sep 2014 08:01:59 +0100
The feature also has a long, long history of not playing nicely with other
Oracle features. A search of the MOS bug database will reveal a number of
reasons that might preclude it in your environment.
On 10 Sep 2014 16:47, "Andrew Kerber" <andrew.kerber@xxxxxxxxx> wrote:
> Cursor_sharing = force does force all constants to bind variables. It
> often works, and can really cut down on the number of hard parses.
> However, there are several performance issues associated with this method,
> in particular when your indexed data is skewed toward just a few values.
>
> On Wed, Sep 10, 2014 at 10:19 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
>> 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 <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.
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
Other related posts:
