Re: Can resizing datafiles affect the shared pool?

1. You current ASM extent pointer array is let say 1MB in shared pool (this is the area which maps logical ASM datafile addresses to physical ASM disk offsets)
2. Now you extend datafiles much larger - therefore you will have more ASM extent pointers, thus you need to increase ASM extent pointer array size
3. Oracle allocates relatively large chunks of memory from shared pool for that - potentially flushing out more cursors than regular small chunk allocations would do

As I said, this would apply to databases using ASM and is meant for illustrating one (special) case where datafile extension *may *indirectly cause trouble.

Do not take this as "ASM datafile extension *will *always cause trouble"!!!

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


On Tue, Sep 22, 2009 at 10:19 PM, Tanel Poder <tanel_at_poderc.com> wrote:



> 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

>

>



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

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