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 > >