Re: buffer advisor

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 10 Sep 2014 10:45:14 -0500
Message-ID: <CAJvnOJbwE7EsnW-yfHiM65-zyq66sh-QoORajehsfAn2WB1d1w_at_mail.gmail.com>

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_at_rsiz.com> 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_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 <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 <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 <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 <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
> .
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 10 2014 - 17:45:14 CEST