Re: Can resizing datafiles affect the shared pool?

From: Tanel Poder <tanel_at_poderc.com>
Date: Tue, 22 Sep 2009 22:19:17 +0800
Message-ID: <4602f23c0909220719x57ab1e20j909286dc0118c609_at_mail.gmail.com>



It's not as simplistic as that!

Read my mail carefully, I have explained exactly how and why this can happen (on databases using ASM)

This is a special case (hopefully rare case), but illustrates that you should *never say never* when talking about a complex beast such as Oracle.

--
Tanel Poder
http://blog.tanelpoder.com


On Tue, Sep 22, 2009 at 6:11 PM, Balakrishna Y <krishna000_at_gmail.com> wrote:


> Hi Tanel,
>
> *Thus, increasing datafile size may indirectly flush out cursor heaps from
> shared pool and cause new child cursors to be hard parsed / loaded back.*
>
> Can you give me some example on this it will be appreciated , since i have
> tested but it didn't work for me.
>
> Regards
>
> Bala
>
>
>
>
> On Tue, Sep 22, 2009 at 3:17 PM, Tanel Poder <tanel_at_poderc.com> wrote:
> > Oracle is so complex beast so that's almost impossible to say anything
> for
> > sure.
> >
> > For example, when you run on ASM and increase the datafile size, this
> means
> > that your instance which performs the formatting will have to allocate
> the
> > ASM extent to block mapping structures in shared pool. If this allocation
> is
> > big enough (and shared pool happens to be fragmented enough with little
> free
> > space) then this may flush out some library cache objects which would not
> be
> > flushed out during normal operations.
> >
> > Thus, increasing datafile size may indirectly flush out cursor heaps from
> > shared pool and cause new child cursors to be hard parsed / loaded back.
> >
> > Tanel.
> >
> > On Mon, Sep 21, 2009 at 5:22 PM, Niall Litchfield
> > <niall.litchfield_at_gmail.com> wrote:
> >>
> >> Bala
> >>
> >> Whilst I agree with you that resizing datafiles shouldn't invalidate a
> >> cursor, it makes no sense that it would, and - as an oakie - heartily
> >> commend the use of testing and demonstrations to make the case, you
> haven't
> >> shown that resizing datafiles will *never* cause cursor invalidation,
> merely
> >> that resizing your users datafile did not invalidate any of the cursors
> in
> >> the shared pool that you had loaded at the time. In principle one might
> >> argue that your test doesn't cover the relevant factors (this is why
> testing
> >> is good of course, people can refine and improve). In particular you
> don't
> >> cover resizing the temp tablespace (and maybe undo) which could be
> argued as
> >> a possible factor for the optimizer to take into account when deciding
> if a
> >> particular plan (maybe one involving large numbers of hash joins or
> sorts
> >> say) needed reparsing.
> >>
> >> Just to be clear I'm not actually disagreeing with your conclusion, just
> >> that it's too strongly stated right now. In particular we know that the
> bad
> >> explain has something to do with sorting/hashing - because of the direct
> >> path write temp - and that your test doesn't cover the temp tablespace.
> If
> >> we are going to claim something never (or conversely always) happens we
> >> better be very very careful about our evidence (and be prepared to run
> >> across a situation next week that shows we are wrong, one of Huxley's
> ugly
> >> facts).
> >>
> >> Niall
> >>
> >> On Mon, Sep 21, 2009 at 6:32 AM, Balakrishna Y <krishna000_at_gmail.com>
> >> wrote:
> >>>
> >>> Hi,
> >>>
> >>> Resiging datafiles will never causes cursor invalidation.
> >>>
> >>> SQL> select count(1) from t where id=1;
> >>>
> >>> COUNT(1)
> >>> ----------
> >>> 1
> >>>
> >>> SQL> select substr(sql_text,1,30),child_number,invalidations from v$sql
> >>> where sql_text like 'select count(1) from t where id=
> >>> 1%';
> >>>
> >>> SUBSTR(SQL_TEXT,1,30) CHILD_NUMBER INVALIDATIONS
> >>> ------------------------------ ------------ -------------
> >>> select count(1) from t where i 0 0
> >>>
> >>> SQL> select name from v$datafile;
> >>>
> >>> NAME
> >>>
> >>>
> ------------------------------------------------------------------------------------------------------------------------
> >>> C:\ORACLE_DATABASE\ORCL\SYSTEM01.DBF
> >>> C:\ORACLE_DATABASE\ORCL\UNDOTBS01.DBF
> >>> C:\ORACLE_DATABASE\ORCL\SYSAUX01.DBF
> >>> C:\ORACLE_DATABASE\ORCL\USERS01.DBF
> >>>
> >>> SQL> alter database datafile 'C:\ORACLE_DATABASE\ORCL\USERS01.DBF'
> resize
> >>> 120m;
> >>>
> >>> Database altered.
> >>>
> >>> SQL> select substr(sql_text,1,30),child_number,invalidations from v$sql
> >>> where sql_text like 'select count(1) from t where id=
> >>> 1%';
> >>>
> >>> SUBSTR(SQL_TEXT,1,30) CHILD_NUMBER INVALIDATIONS
> >>> ------------------------------ ------------ -------------
> >>> select count(1) from t where i 0 0
> >>>
> >>> SQL>
> >>>
> >>>
> >>> Requesting to please try in your test environment and prove yourself
> with
> >>> your questions and you will definitely have answer for that .
> >>>
> >>> Regards
> >>>
> >>> Bala
> >>>
> >>> On Thu, Sep 17, 2009 at 6:11 PM, neil kodner <nkodner_at_gmail.com>
> wrote:
> >>>>
> >>>> Recently we had an issue where queries received a non-desirable
> explain
> >>>> plan, after weeks and weeks of production use. This lead to hundreds
> of
> >>>> sessions waiting on direct path write temp. I suspect this might be
> related
> >>>> to bind variable peeking but am not 100% sure.
> >>>>
> >>>> It is interesting to note that this happened shortly after a
> half-dozen
> >>>> datafiles were resized(by someone else of course :D). Does resizing
> >>>> datafiles during heavy production use invalidate cursors in the shared
> pool?
> >>>> This is on a single-instance database with about 2000 connections.
> >>>> --
> >>>> http://www.freelists.org/webpage/oracle-l
> >>>>
> >>>>
> >>>
> >>
> >>
> >>
> >> --
> >> Niall Litchfield
> >> Oracle DBA
> >> http://www.orawin.info
> >
> >
> >
> > --
> > Tanel Poder
> > http://blog.tanelpoder.com
> >
> >
>
>
-- Tanel Poder http://blog.tanelpoder.com -- http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 22 2009 - 09:19:17 CDT

Original text of this message