Home » RDBMS Server » Performance Tuning » Plan missing from cursor cache (Oracle 12.1 ,OEL 6)
Plan missing from cursor cache [message #627205] Sun, 09 November 2014 02:17 Go to next message
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
Re: Plan missing from cursor cache [message #627206 is a reply to message #627205] Sun, 09 November 2014 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.


Re: Plan missing from cursor cache [message #627207 is a reply to message #627206] Sun, 09 November 2014 03:10 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Great . Thank you so much .
Previous Topic: Delete taking long due to index maintenance?
Next Topic: PGA_AGREGATE_TARGET parameter
Goto Forum:
  


Current Time: Thu Mar 28 19:41:24 CDT 2024