2.建立测试例子:
SCOTT@book> create table t as select * from all_objects where rownum @ desc_proc sys dbms_lob substr
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
----- ------------ ----------- -------- ------------- --------- --------- ----------
SYS DBMS_LOB SUBSTR 1 RAW OUT N
2 LOB_LOC BLOB IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
1 VARCHAR2 OUT N
2 LOB_LOC CLOB IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
1 RAW OUT N
2 FILE_LOC BFILE IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
12 rows selected.
--//dbms_lob.substr函数参数与substr不一样,第2个参数AMOUNT表示取字符串的数量,第3个参数OFFSET表示字符串的偏移量.
--//注意最后一列DEFAULTED表明可以不输入第2,3参数(Y),不知道缺省怎么值.可以根据下面的执行推断offset缺省值=1.
--//注意返回数据类型支持raw,varchar2.
3.测试:
SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from t where dbms_lob.substr(object_name)='DEPT';
no rows selected
SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7fybpwpmd0svt, child number 0
-------------------------------------
select * from t where dbms_lob.substr(object_name)='DEPT'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.02 | 1298 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 0 |00:00:00.02 | 1298 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"))='DEPT')
--//测试多次,最后稳定在1298逻辑读.
SCOTT@book> select * from t where dbms_lob.substr(object_name,30.0,1.0)='ICOL$';
SCOTT@book> @ pr
==============================
OWNER : SYS
OBJECT_NAME : ICOL$
SUBOBJECT_NAME :
OBJECT_ID : 20
DATA_OBJECT_ID : 2
OBJECT_TYPE : TABLE
CREATED : 2013-08-24 11:37:35
LAST_DDL_TIME : 2013-08-24 11:47:37
TIMESTAMP : 2013-08-24:11:37:35
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 1
EDITION_NAME :
PL/SQL procedure successfully completed.
SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID baj2g6ratkpgq, child number 0
-------------------------------------
select * from t where dbms_lob.substr(object_name,30.0,1.0)='ICOL$'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.02 | 1299 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 1 |00:00:00.02 | 1299 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"),30,1)='ICOL$')
--//如果查询有返回值,逻辑读增加1个.
SCOTT@book> with a as (select /*+ materialize */ dbms_lob.substr(object_name) xx,t.* from t ) select * from a where a.xx='DEPT';
no rows selected
SCOTT@book> create index if_t_object_name on t(DBMS_LOB.SUBSTR(OBJECT_NAME)) ;
Index created.
--//ok,索引可以建立!!
SCOTT@book> select * from t where dbms_lob.substr(object_name)='ICOL$';
SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fxs2sk8mvtp5s, child number 0
-------------------------------------
select * from t where dbms_lob.substr(object_name)='ICOL$'
Plan hash value: 1757252843
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 75 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IF_T_OBJECT_NAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00016$"='ICOL$')
--//很好,可以很好地使用我建立的函数索引.
6.继续分析看看为什么前面的逻辑读是4.
SCOTT@book> drop index IF_T_OBJECT_NAME;
Index dropped.
--//开启跟踪:
SCOTT@book> alter system set "_trace_pin_time"=1 scope=spfile;
System altered.
--//重启数据库略.
SCOTT@book> @ t
TRACEFILE
-------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10291.trc
--//执行如下多次.
select t.*,dbms_lob.substr(object_name) from t;
select t.*,dbms_lob.substr(object_name) from t;
select t.*,dbms_lob.substr(object_name) from t;
--//0x40ce80 = set dba 1,52864 = alter system dump datafile 1 block 52864 = 4247168
--//0x40ce84 = set dba 1,52868 = alter system dump datafile 1 block 52868 = 4247172
SCOTT@book> SELECT * FROM sys.idl_ub1$ WHERE ROWID BETWEEN 'AAAADhAABAAAM6AAAA' AND 'AAAADhAABAAAM6EAMg';
OBJ# PART VERSION PIECE# LENGTH P
---------- ---------- ---------- ---------- ---------- -
57638 1 0 0 38743 F
SCOTT@book> SELECT * FROM sys.idl_ub1$ WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 52864 and 52868;
OBJ# PART VERSION PIECE# LENGTH P
---------- ---------- ---------- ---------- ---------- -
57638 1 0 0 38743 F
SYS@book> @ desc sys.idl_ub1$
Name Null? Type
------------------------------- -------- ----------------------------
1 OBJ# NOT NULL NUMBER
2 PART NOT NULL NUMBER
3 VERSION NUMBER
4 PIECE# NOT NULL NUMBER
5 LENGTH NOT NULL NUMBER
6 PIECE NOT NULL LONG RAW
--//确实仅仅有1个对象,PIECE类型为long raw占用许多空间,38743/8000 = 4.8,占5块。
SCOTT@book> create index if_t_object_name on t(DBMS_LOB.SUBSTR(OBJECT_NAME)) ;
Index created.
select * from t where dbms_lob.substr(object_name)='ICOL$';
select * from t where dbms_lob.substr(object_name)='ICOL$';
select * from t where dbms_lob.substr(object_name)='ICOL$';
--//执行多次。
SCOTT@book> select * from t where dbms_lob.substr(object_name)='ICOL$';
...
$ egrep "dba 0x1000" 20231128a.txt
pin qeilwhrp: qeilbk dba 0x1000423:1 time 4238232606
pin kdswh05: kdsgrp dba 0x100041b:1 time 4238232706
pin kdiwh16: kdifxs dba 0x1000423:1 time 4238233321
--//0x1000423 应该访问的索引数据块.
$ egrep "dba " 20231128a.txt
pin qeilwhrp: qeilbk dba 0x1000423:1 time 4238232606
pin kdswh05: kdsgrp dba 0x100041b:1 time 4238232706
pin kdiwh16: kdifxs dba 0x1000423:1 time 4238233321
--//完全看不到dba 0x40ce8X的访问。
7.收尾:
SCOTT@book> alter system reset "_trace_pin_time";
System altered.
--//重启略。
> @ ashtable d DBMS_LOB.SUBSTR 1=1 &day
@ tpt/dashtop sql_id,module "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%DBMS_LOB.SUBSTR%'))" sysdate-1 sysdate