Re: Can resizing datafiles affect the shared pool?
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx
- Date: Tue, 22 Sep 2009 17:47:17 +0800
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
Other related posts:
