昨天遇到一些程序的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.