Re: Can resizing datafiles affect the shared pool?
- From: Balakrishna Y <krishna000@xxxxxxxxx>
- To: nkodner@xxxxxxxxx
- Date: Mon, 21 Sep 2009 11:02:58 +0530
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
>
>
>
Other related posts:
