ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120229]无效sql语句与shared pool的问题.txt

[20120229]无效sql语句与shared pool的问题.txt

原创 Linux操作系统 作者:lfree 时间:2012-03-01 09:15:02 0 删除 编辑
昨天遇到一些程序的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.






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-717416/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2286
  • 访问量
    6026284