OraFAQ Forum: Performance Tuning » Plan missing from cursor cache

Home » RDBMS Server » Performance Tuning » Plan missing from cursor cache (Oracle 12.1 ,OEL 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Plan missing from cursor cache [message #627205]

Sun, 09 November 2014 02:17

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Hello folks ,

I was doing a test an I was expecting to see two plans for the same SQL_ID , but i see only the recent . Any idea why ?

SQL> select /*+ id=1000 */ name from EMP where id=1000;

NAME
----------
someone



SQL> select sql_id, sql_text, hash_value, plan_hash_value
from v$sql
where sql_id='d27s4gw6xfp7s';  2    3

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PLAN_HASH_VALUE
---------- ---------------
d27s4gw6xfp7s
select /*+ id=1000 */ name from EMP where id=1000
 232215800      3956160932




SQL> CREATE INDEX IX_ID on EMP(id);

Index created.

SQL> select /*+ id=1000 */ name from EMP where id=1000;

NAME
----------
someone



SQL> select sql_id, sql_text, hash_value, plan_hash_value
from v$sql
where sql_id='d27s4gw6xfp7s';  2    3

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PLAN_HASH_VALUE
---------- ---------------
d27s4gw6xfp7s
select /*+ id=1000 */ name from EMP where id=1000
 232215800      2086888443

Report message to a moderator

Re: Plan missing from cursor cache [message #627206 is a reply to message #627205]

Sun, 09 November 2014 03:06

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

When you create an index on a table you invalidate all the dependent objects in the SGA and so the execution plans.
You will see that in INVALIDATIONS column of v$sql:

SQL> alter system flush shared_pool;

System altered.

SQL> create table emp_michel as select * from emp;

Table created.

SQL> select * from emp_michel where empno=7839;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

1 row selected.

SQL> select sql_id, invalidations, sql_text
  2  from v$sql
  3  where sql_text like 'select%emp_michel%'
  4    and sql_text not like '%v$sql%'
  5  /
SQL_ID        INVALIDATIONS
------------- -------------
SQL_TEXT
------------------------------------------------------------------------------------------------
dwjhj9zmtdwhp             0
select * from emp_michel where empno=7839

1 row selected.

SQL> create index i on emp_michel(empno);

Index created.

SQL> select * from emp_michel where empno=7839;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

1 row selected.

SQL> select sql_id, invalidations, sql_text
  2  from v$sql
  3  where sql_text like 'select%emp_michel%'
  4    and sql_text not like '%v$sql%'
  5  /
SQL_ID        INVALIDATIONS
------------- -------------
SQL_TEXT
------------------------------------------------------------------------------------------------
dwjhj9zmtdwhp             1
select * from emp_michel where empno=7839

1 row selected.

Report message to a moderator

Re: Plan missing from cursor cache [message #627207 is a reply to message #627206]

Sun, 09 November 2014 03:10

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Great . Thank you so much .

Report message to a moderator

Previous Topic:	Delete taking long due to index maintenance?
Next Topic:	PGA_AGREGATE_TARGET parameter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 22:48:15 CST 2025