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@xxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> > >> 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@xxxxxxxxx> > 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. > >>>> -- > >>>> //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