Re: Can resizing datafiles affect the shared pool?

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

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