Re: Can resizing datafiles affect the shared pool?

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

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