[20120229]無效sql語句與shared pool的問題.txt

昨天遇到一些程式的bug，因為查詢要顯示1年的資訊，因為2011年沒有2月29號，導致查詢出錯。由此想到另外的問題，如果查詢存在這些語句，會保留在共享池嗎？自己做了一個測試：

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from emp where hiredate=to_date('2012-02-30','yyyy-mm-dd');

select * from emp where hiredate=to_date('2012-02-30','yyyy-mm-dd')

                                         *

ERROR at line 1:

ORA-01839: date not valid for month specified

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  fv0w0c06d6jsa, child number 0

-------------------------------------

select * from emp where hiredate=to_date('2012-02-30','yyyy-mm-dd')

Plan hash value: 3956160932

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |     3 (100)|

|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     3   (0)|

--------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("HIREDATE"=TO_DATE('2012-02-30','yyyy-mm-dd'))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.

SQL> column aa format a140

SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where sql_id='fv0w0c06d6jsa';

EXECUTIONS SQL_ID        AA

---------- ------------- ---------------------------------------------------------------------

         1 fv0w0c06d6jsa select * from emp where hiredate=to_date('2012-02-30','yyyy-mm-dd')

--居然算執行了。

SQL> column aa format a76

SQL> select sql_id,substr(sql_text,1,76) aa,users_opening,  open_versions,users_executing from v$sql  where sql_id ='fv0w0c06d6jsa';

SQL_ID        AA                                                                           USERS_OPENING OPEN_VERSIONS USERS_EXECUTING

------------- ---------------------------------------------------------------------------- ------------- ------------- ---------------

fv0w0c06d6jsa select * from emp where hiredate=to_date('2012-02-30','yyyy-mm-dd')                      0             0               0

--僅僅USERS_OPENING=0, OPEN_VERSIONS=0.