Tuesday, October 19, 2010

Oracle: A safe way to invalid execution plan related with sepcific objects

SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
---------- ------------- -----------
1 3 1

SQL> COMMENT ON TABLE EMPLOYEES IS 'THIS IS TEST EMPLOYEE';

Comment created.

SQL> select /*+ testsql*/ count(*) from employees where employee_id=1;


COUNT(*)
----------
0

SQL> SQL>
SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
--- ------- ------------- -----------
1 4 1

SQL> select /*+ testsql*/ count(*) from employees where employee_id=1;

COUNT(*)
----------
0

SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
---------- ------------- -----------
2 4 2