1、clob/blob
创建一个lob列时,存储在行中的只是一个指针,当我们请求得到lob数据时,lob指针将通过lobindex找到数据的存储位置,然后访问lobsegment获取数据。
表空间
lob字段与表放在不同的表空间,主要与管理和性能有关。一方面,为lob数据单独使用一个表空间有利于备份和恢复以及空间管理。另一方面,默认情况下lob不在缓存区中进行缓存,对于每个lob访问,不论是读还是写,都会带来一个IO,所以将这些对象分离到他们自己的磁盘上就很有意义。另外需要说明,logindex和logsegment总会在同一个表空间中。
in row 子句
这控制了lob数据是否总与表分开存储。如果设置了enable storage in row(默认设置) ,小lob(最多4000字节)就会像varchar2一样存储在表本身中,只有当lob超过了4000字节时,才会移出到lobsegment中。如果lob字段数据小于4000字节,建议采用行内存储,即默认设置。
JEL@JEL >create table t
2 (id int primary key,
3 in_row clob,
4 out_row clob)
5 lob (in_row) store as (enable storage in row)
6 lob (out_row) store as (disable storage in row);
Table created.
JEL@JEL >insert into t
2 select rownum,
3 owner||' '||object_name||' '||object_type||' '||status,
4 owner||' '||object_name||' '||object_type||' '||status
5 from all_objects;
9345 rows created.
JEL@JEL >alter session set SQL_TRACE=TRUE;
Session altered.
JEL@JEL >declare
2 l_cnt number;
3 l_data varchar2(4000);
4 begin
5 select count(*) into l_cnt from t;
6 for i in 1 .. l_cnt
7 loop
8 select in_row into l_data from t where id=i;
9 select out_row into l_data from t where id=i;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
[oracle@jelephant ~]$ tkprof /u01/app/oracle/admin/JEL/udump/jel_ora_4540.trc /u01/app/oracle/admin/JEL/udump/jel_ora_4540.txt
跟踪文件如下:
********************************************************************************
SELECT IN_ROW
FROM
T WHERE ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9345 0.03 0.16 0 0 0 0
Fetch 9345 0.08 0.19 0 28035 0 9345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18691 0.11 0.36 0 28035 0 9345
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
9345 TABLE ACCESS BY INDEX ROWID T (cr=28035 pr=0 pw=0 time=199495 us)
9345 INDEX UNIQUE SCAN SYS_C002903 (cr=18690 pr=0 pw=0 time=96206 us)(object id 10067)
********************************************************************************
SELECT OUT_ROW
FROM
T WHERE ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9345 0.07 0.16 0 0 0 0
Fetch 9345 1.08 0.57 9345 56072 0 9345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18691 1.15 0.73 9345 56072 0 9345
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
9345 TABLE ACCESS BY INDEX ROWID T (cr=28035 pr=0 pw=0 time=208311 us)
9345 INDEX UNIQUE SCAN SYS_C002903 (cr=18690 pr=0 pw=0 time=102863 us)(object id 10067)
********************************************************************************
获取in_row列显然快的多,而且所占的资源也远远少于获取out_row列。这种行内行外存储设置不仅会影响读,还会影响修改。
chunk子句
lob存储在块(chunk)中,指向lob数据的索引会指向各个数据块。块(chunk)是逻辑上连续的一直数据库块(block),这也是lob的最小分配单元,但通常数据块的最小分配单元是数据块库块。chunk大小必须是block的整数倍。
注意两点,第一,一个chunk只能由一个lob值使用,每个lob值会占用至少一个chunk。如果一个表有100行,每行有一个包含7kb数据的lob。oracle就分配100个chunk,如果将chunk大小设置成32kb,就会分配100*32kb的空间,从而产生大量空间浪费。第二,让每个lob值相应的chunk数减至最少,lobindex用于指向各个块,块越多,索引就越大。
pctversion子句
用于控制lob的读一致性。lobsegment并不使用undo来记录其修改,而是直接在lobsegment本身中维护信息的版本,而lobindex会像其他段一样上传undo。修改一个lob时,oracle会分配一个新chunk,并且仍保留原来的chunk。如果回滚事务,对lobindex所做的修改会回滚,索引将再次指向原来的chunk。
如果不用undo段来存储回滚lob所需的信息,而且lob支持读一致性,那么怎么避免ORA-1555:snapshot too old错误呢?这正是pctversion起作用的地方
如果处理lob是遇到了ORA-22924错误,解决方案是执行如下命令:
alter table tabname modify lob (lobname)(pctversion n)
cache子句
默认nocache
cache reads 允许缓存从磁盘读取得lob数据,但是lob数据的写操作则直接写至磁盘。cache则允许读和写都能缓存lob数据,建议对小lob进行缓存。
alter table tabname modify lob (lobname)(cache)
alter table tabname modify lob (lobname)(nocache)
2、bfile
它只是操作系统上一个文件的指针,它用于为这些操作系统文件提供只读访问。
JEL@JEL >create table t_file (id int primary key,os_file bfile);
Table created.
JEL@JEL >create or replace directory my_dir as '/home/oracle/';
Directory created.
JEL@JEL >insert into t_file values (1,bfilename('MY_DIR','db.txt'));
1 row created.
JEL@JEL >commit;
Commit complete.
JEL@JEL >select dbms_lob.getlength(os_file) from t_file;
DBMS_LOB.GETLENGTH(OS_FILE)
---------------------------
951
文件大小为951kb,注意存储是要求MY_DIR必须为大写。
读取文件:
JEL@JEL >grant read,write on directory my_dir to public;
Grant succeeded.
JEL@JEL > SET SERVEROUTPUT ON
脚本如下:
declare
fhandle utl_file.file_type;
f_buffer varchar2(4000);
begin
fhandle := utl_file.fopen('MY_DIR','db.txt','r');
loop
begin
utl_file.get_line(fhandle,f_buffer);
dbms_output.put_line(f_buffer);
exception
when no_data_found then
exit;
end;
end loop;
utl_file.fclose(fhandle);
end;
创建一个lob列时,存储在行中的只是一个指针,当我们请求得到lob数据时,lob指针将通过lobindex找到数据的存储位置,然后访问lobsegment获取数据。
表空间
lob字段与表放在不同的表空间,主要与管理和性能有关。一方面,为lob数据单独使用一个表空间有利于备份和恢复以及空间管理。另一方面,默认情况下lob不在缓存区中进行缓存,对于每个lob访问,不论是读还是写,都会带来一个IO,所以将这些对象分离到他们自己的磁盘上就很有意义。另外需要说明,logindex和logsegment总会在同一个表空间中。
in row 子句
这控制了lob数据是否总与表分开存储。如果设置了enable storage in row(默认设置) ,小lob(最多4000字节)就会像varchar2一样存储在表本身中,只有当lob超过了4000字节时,才会移出到lobsegment中。如果lob字段数据小于4000字节,建议采用行内存储,即默认设置。
JEL@JEL >create table t
2 (id int primary key,
3 in_row clob,
4 out_row clob)
5 lob (in_row) store as (enable storage in row)
6 lob (out_row) store as (disable storage in row);
Table created.
JEL@JEL >insert into t
2 select rownum,
3 owner||' '||object_name||' '||object_type||' '||status,
4 owner||' '||object_name||' '||object_type||' '||status
5 from all_objects;
9345 rows created.
JEL@JEL >alter session set SQL_TRACE=TRUE;
Session altered.
JEL@JEL >declare
2 l_cnt number;
3 l_data varchar2(4000);
4 begin
5 select count(*) into l_cnt from t;
6 for i in 1 .. l_cnt
7 loop
8 select in_row into l_data from t where id=i;
9 select out_row into l_data from t where id=i;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
[oracle@jelephant ~]$ tkprof /u01/app/oracle/admin/JEL/udump/jel_ora_4540.trc /u01/app/oracle/admin/JEL/udump/jel_ora_4540.txt
跟踪文件如下:
********************************************************************************
SELECT IN_ROW
FROM
T WHERE ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9345 0.03 0.16 0 0 0 0
Fetch 9345 0.08 0.19 0 28035 0 9345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18691 0.11 0.36 0 28035 0 9345
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
9345 TABLE ACCESS BY INDEX ROWID T (cr=28035 pr=0 pw=0 time=199495 us)
9345 INDEX UNIQUE SCAN SYS_C002903 (cr=18690 pr=0 pw=0 time=96206 us)(object id 10067)
********************************************************************************
SELECT OUT_ROW
FROM
T WHERE ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9345 0.07 0.16 0 0 0 0
Fetch 9345 1.08 0.57 9345 56072 0 9345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18691 1.15 0.73 9345 56072 0 9345
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
9345 TABLE ACCESS BY INDEX ROWID T (cr=28035 pr=0 pw=0 time=208311 us)
9345 INDEX UNIQUE SCAN SYS_C002903 (cr=18690 pr=0 pw=0 time=102863 us)(object id 10067)
********************************************************************************
获取in_row列显然快的多,而且所占的资源也远远少于获取out_row列。这种行内行外存储设置不仅会影响读,还会影响修改。
chunk子句
lob存储在块(chunk)中,指向lob数据的索引会指向各个数据块。块(chunk)是逻辑上连续的一直数据库块(block),这也是lob的最小分配单元,但通常数据块的最小分配单元是数据块库块。chunk大小必须是block的整数倍。
注意两点,第一,一个chunk只能由一个lob值使用,每个lob值会占用至少一个chunk。如果一个表有100行,每行有一个包含7kb数据的lob。oracle就分配100个chunk,如果将chunk大小设置成32kb,就会分配100*32kb的空间,从而产生大量空间浪费。第二,让每个lob值相应的chunk数减至最少,lobindex用于指向各个块,块越多,索引就越大。
pctversion子句
用于控制lob的读一致性。lobsegment并不使用undo来记录其修改,而是直接在lobsegment本身中维护信息的版本,而lobindex会像其他段一样上传undo。修改一个lob时,oracle会分配一个新chunk,并且仍保留原来的chunk。如果回滚事务,对lobindex所做的修改会回滚,索引将再次指向原来的chunk。
如果不用undo段来存储回滚lob所需的信息,而且lob支持读一致性,那么怎么避免ORA-1555:snapshot too old错误呢?这正是pctversion起作用的地方
如果处理lob是遇到了ORA-22924错误,解决方案是执行如下命令:
alter table tabname modify lob (lobname)(pctversion n)
cache子句
默认nocache
cache reads 允许缓存从磁盘读取得lob数据,但是lob数据的写操作则直接写至磁盘。cache则允许读和写都能缓存lob数据,建议对小lob进行缓存。
alter table tabname modify lob (lobname)(cache)
alter table tabname modify lob (lobname)(nocache)
2、bfile
它只是操作系统上一个文件的指针,它用于为这些操作系统文件提供只读访问。
JEL@JEL >create table t_file (id int primary key,os_file bfile);
Table created.
JEL@JEL >create or replace directory my_dir as '/home/oracle/';
Directory created.
JEL@JEL >insert into t_file values (1,bfilename('MY_DIR','db.txt'));
1 row created.
JEL@JEL >commit;
Commit complete.
JEL@JEL >select dbms_lob.getlength(os_file) from t_file;
DBMS_LOB.GETLENGTH(OS_FILE)
---------------------------
951
文件大小为951kb,注意存储是要求MY_DIR必须为大写。
读取文件:
JEL@JEL >grant read,write on directory my_dir to public;
Grant succeeded.
JEL@JEL > SET SERVEROUTPUT ON
脚本如下:
declare
fhandle utl_file.file_type;
f_buffer varchar2(4000);
begin
fhandle := utl_file.fopen('MY_DIR','db.txt','r');
loop
begin
utl_file.get_line(fhandle,f_buffer);
dbms_output.put_line(f_buffer);
exception
when no_data_found then
exit;
end;
end loop;
utl_file.fclose(fhandle);
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1063972/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29337971/viewspace-1063972/