创建含sysdate的函数index

1.模拟环境
创建表插入数据库

[oracle@node1 ~]$ sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 9 16:27:19 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table t_xifenfei(id number,intime date);

Table created.

SQL> DECLARE
  2  i NUMBER;
  3  BEGIN
  4    FOR i IN 1..1000 LOOP
  5      INSERT INTO t_xifenfei VALUES(i,SYSDATE-i);
  6      END LOOP;
  7      COMMIT;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
      1000


SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);

PL/SQL procedure successfully completed.

2.无index查询

SQL> set autot trace exp stat
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    10 |   120 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |    10 |   120 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL("INTIME",SYSDATE@!)>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> set autot off

这里只是做了一个简单的查询,因为这个nvl(intime,sysdate)的条件,无法使用正常的index,所以没有建立intime索引的测试。

3.尝试创建index

SQL>  create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging;
 create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging
                                                      *
ERROR at line 1:
ORA-01743: only pure functions can be indexed


SQL> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS).  SQL
//          expressions must not use SYSDATE, USER, USERENV(), or anything
//          else dependent on the session state.  NLS-dependent functions
//          are OK.
--因为含有sysdate创建函数index失败


SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE
  3  IS
  4  otime DATE;
  5  BEGIN 
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /

Function created.
--想采用自定义函数屏蔽掉sysdate在创建index时候的影响

SQL>  create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
 create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging
                                           *
ERROR at line 1:
ORA-30553: The function is not deterministic


SQL> !oerr ora 30553
30553, 00000, "The function is not deterministic" 
// *Cause:  The function on which the index is defined is not deterministic 
// *Action: If the function is deterministic, mark it DETERMINISTIC.  If it
//          is not deterministic (it depends on package state, database state,
//          current time, or anything other than the function inputs) then
//          do not create the index.  The values returned by a deterministic
//          function should not change even when the function is rewritten or
//          recompiled.
--因为函数缺少deterministic不能使用于index上

SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE deterministic
  3  IS
  4  otime DATE;
  5  BEGIN 
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /

Function created.

SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;

Index created.
--创建函数index成功

SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);

PL/SQL procedure successfully completed.

4.再次查询
确定已经使用函数index,达到在index中使用sysdate函数index的目的。

SQL> set autot on  exp stat
SQL> select * from t_xifenfei where f_xifenfei(intime)>=to_date('2011-12-31','yyyy-mm-dd');

Execution Plan
----------------------------------------------------------
Plan hash value: 2005404611

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |    10 |   200 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI    |    10 |   200 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IN_T_XIFENFEI |    10 |       |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CHF"."F_XIFENFEI"("INTIME")>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

5.总结说明
5.1)通过函数屏蔽函数index的时候,不能使用sysdate
5.2)在创建函数时,需要指定deterministic关键字

DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常BUG

1.数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2.查询DBA_DATA_FILES视图

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION 
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(MAXBYTES)/1024 
--------------- ------------------ 
EXAMPLE                   33554416     
OGG                        5242880    
SYSAUX                    33554416       
SYSTEM                    33554416        
TEMP                      33554416        
TS_INDEX_BASE             15728640     
TS_PUB_BASE               15728640        
UNDOTBS1                  33554416       
USERS                     33554416       

9 rows selected.

3.查询DBA_HIST_TBSPC_SPACE_USAGE视图

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;

NAME            TABLESPACE_MAXSIZE 
--------------- ------------------ 
EXAMPLE                    4194302    
OGG                         655360      
SYSAUX                     4194302         
SYSTEM                     4194302       
TEMP                       4194302         
TS_INDEX_BASE              1966080         
TS_PUB_BASE                1966080          
UNDOTBS1                   8388604         
USERS                      4194302        

9 rows selected.

观察者两个视图的运行结果,DBA_HIST_TBSPC_SPACE_USAGE视图收集到的统计大小和实际大小都存在一定的误差,但是UNDO表空间出入太明显(UNDOTBS1),特别是最大值和当前值,几乎是真实大小的两倍

4.排除原因
4.1)收集信息是否是最新

SQL> select MAX(rtime) FROM DBA_HIST_TBSPC_SPACE_USAGE;

MAX(RTIME)
-------------------------
01/09/2012 15:00:50

4.2)statistics_level是否被设置为basic

SQL> show parameter statistics_level;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

通过这两个查询证明,收集信息和statistics_level都是符合要求,那么为什么undo空间的空间信息还是正常的两倍呢?

5.怀疑bug,查询mos

6.查询11.2.0.3中DBA_HIST_TBSPC_SPACE_USAGE是否正常

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;


NAME            TABLESPACE_MAXSIZE 
--------------- ------------------ 
DRSYS_1                    4194302    
EXAMPLE                    4194302    
ODU                        8139262      
SYSAUX                     4194302      
SYSTEM                     4194302        
TEMP                       4194302       
TEST_OCP                   4194302         
UNDOTBS01                  3938560        
USERS                      4194302         

9 rows selected.

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024  3        
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION 
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(MAXBYTES)/1024 
--------------- ------------------ 
DRSYS_1                   33554416     
EXAMPLE                   33554416     
ODU                       65114096     
SYSAUX                    33554416       
SYSTEM                    33554416         
TEMP                      33554416      
TEST_OCP                  33554416         
UNDOTBS01                 31457280      
USERS                     33554416      

9 rows selected.

通过对比,发现基本误差不大,确定在该版本,bug7578292已经被修复