Re: Can resizing datafiles affect the shared pool?
Date: Tue, 22 Sep 2009 15:41:40 +0530
Message-ID: <e53436b70909220311i1eae2a7dx4b36afa6fbdea8d7_at_mail.gmail.com>
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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 22 2009 - 05:11:40 CDT