Transcript Deadly Sins against Dtabase Performance

Особенности при работа с
LOB-ве и EPC
Теодор Иванов
Софтуерен инженер
Април 2007, Пловдив
Какво е LOB ?
съкращение от Large Object - тип данни, предназначен
за съхранение на информационни обекти с размер до
4GB
Един LOB instance се състои от locator и value locator-а е указател към физическото място, където
се съхранява value-то
Според мястото на съхранение на value-то LOB-вете
се делят на Persistent и Temporary
2
Типове LOB в Oracle
BLOB - Binary Large Object, за съхранение на големи
двоични обекти, като изображения, видео или аудио
информация
CLOB - Character Large Object, за съхранение на
голям обем символна информация, използва
NLS_CHARACTERSET ( database character set )
NCLOB - National Character Set Large Object, за
съхранение на голям обем символна информация
използвайки NLS_NCHAR_CHARACTERSET ( National
Character Set )
BFILE - позволява директно четене на файлове извън
базата данни. Oт BFILE може само да се чете
3
Какво е Oracle EPC ?
Начин да се използват функции от външни за
Oracle RDBMS библиотеки ( .lib, .so, .dll )
Oracle RDBMS
C interface
C procedures
or functions
- SQL
- PL/SQL
- Java
Java interface
Java
procedures
or functions
4
Запис на файл в BLOB
Oracle RDBMS
OS
file system
Temporary Tablespace
file_to_get.bin
BLOB value
PL/SQL
BLOB locator
BLOB instance
OCI libraries
Memory
>file_to_get.bin<
'ABC'
begin
some_blob := to_blob( '616263' );
c_get_file_to_blob( fn, some_blob );
OCIExtProcContext, BLOB locator, fn
...
external procedure call
buffer
c_get_file( ... ){ ... }
5
Пример за използване на LOB през EPC
С функции c_get_file(), c_put_file() и Linux
библиотека lob_epc.so
Oracle библиотека lob_epc_lib
BLOB PL/SQL функции c_get_file_to_blob() и
c_put_file_from_blob()
CLOB PL/SQL функции c_get_file_to_clob() и
c_put_file_from_clob()
Тест на PL/SQL четене на данни от файл и запис в
друг файл, през BLOB
Тест на PL/SQL четене на данни от файл и запис в
друг файл, през CLOB
makefile
6
PL/SQL библиотека lob_epc_lib
create or replace
library lob_epc_lib as
'${ORACLE_HOME}/lib/lob_epc.so';
/
7
Какво прави c_get_file_to_?lob()
Приема четири параметара:
pin_path - името на файла, който да се прочете
pio_?lob - инициализиран LOB, където да се
сложи съдържанието на файла
errMsg - връща текст на грешка
errCode - връща код на грешка
Ако четенето на файла е усшешно,
c_get_file_to_?lob() връща 0
8
PL/SQL c_get_file_to_?lob()
create or replace function c_get_file_to_blob(
pin_path in
varchar2,
pio_blob in out nocopy blob
errMsg
in out
varchar2,
errCode
in out
binary_integer
) return binary_integer as
language C
library LOB_EPC_LIB
name "c_get_file"
parameters(
context,
pin_path
string,
pio_blob
ociloblocator,
pio_blob
indicator by reference,
errMsg
string,
errCode
int,
return
int
);
/
9
Съответствие на PL/SQL и C параметрите
PL/SQL
c_get_file_to_blob(
PARAMETERS
C
parameters(
int c_get_file(
context,
OCIExtProcContext
*ctx,
pin_path
in varchar2, pin_path string,
char
pio_blob
in out
pio_blob ociloblocator,
nocopy blob, pio_blob
indicator by reference,
OCILobLocator** pio_blob,
errMsg
in out varchar2, errMsg
short
string,
char
errCode in out
binary_integer
errCode
int,
int
) return binary_integer
return
int
)
*pin_path,
*pio_blob_ind,
*errMsg,
*errCode
10
С функция - c_get_file()
int c_get_file(
OCIExtProcContext *ctx,
char
*pin_path,
OCILobLocator
**pio_blob,
short
*pio_blob_ind,
char
*errMsg,
int
*errCode
)
{
if( ctx == 0 ||
pio_blob == 0 || *pio_blob == 0 || pio_blob_ind == 0 ||
errMsg == 0
|| errCode == 0 ) {
LogError( "null input pointer", "c_get_file() " );
return( -1 );
}
...
11
...
// get the OCI environment
status = OCIExtProcGetEnv(
(OCIExtProcContext *) ctx,
(OCIEnv **) &envhp,
(OCISvcCtx **) &svchp,
(OCIError **) &errhp
);
if( status != OCI_SUCCESS ) {
*errCode = status;
strcpy( errMsg, "Failed to get the OCI connections
environment" );
LogError( errMsg, "c_get_file() " );
return( -2 );
}
...
12
...
// check whether pio_blob is initialized
if( *pio_blob_ind == OCI_IND_NULL ) {
*errCode = -3;
strcpy( errMsg, "The input blob is NULL" );
LogError( errMsg, "c_get_file() " );
return( *errCode );
}
// check whether the lob is initialized
status = OCILobLocatorIsInit (
envhp,
// OCIEnv *envhp,
errhp,
// OCIError *errhp,
pio_blob,
// CONST OCILobLocator *locp,
&boolval
// boolean *is_initialized
);
...
13
...
if( status != OCI_SUCCESS ) {
strcpy( log_msg, "Failed to check whether the lob is
initialized" );
return( -1 );
}
if( boolval == FALSE )
return( -2 ); // lob is not initialized
// get the lob data size
status = OCILobGetLength (
svchp,
errhp,
pio_blob,
size
);
//
//
//
//
OCISvcCtx *svchp,
OCIError *errhp,
OCILobLocator *locp,
ub4 *lenp
if( status != OCI_SUCCESS ) {
strcpy( log_msg, "Failed to get size of lob" );
return( -3 );
}
...
14
...
// truncate the lob
status = OCILobTrim (
svchp,
errhp,
pio_blob,
0
);
//
//
//
//
OCISvcCtx *svchp,
OCIError *errhp,
OCILobLocator *locp,
ub4 newlen
if( status != OCI_SUCCESS ) {
strcpy( log_msg, "Failed to truncate the lob" );
return( -3 );
}
...
// open the file
...
// read a part of the file and fulfill the buffer
//
#define
LOB_BUFF_SIZE
( (ub4)( 1*1024*1024 ) )
//
char
lob_buff[LOB_BUFF_SIZE];
...
// 1 MBytes
15
...
// write the buffer data into the LOB
wrote_num = to_write_num;
status = OCILobWrite (
svchp,
// OCISvcCtx *svchp,
errhp,
// OCIError *errhp,
pio_blob,
// OCILobLocator *locp,
&wrote_num,
// ub4 *amtp,
write_from,
// ub4 offset,
lob_buff,
// dvoid *bufp,
to_write_num,
// ub4 buflen,
OCI_ONE_PIECE, // ub1 piece,
NULL,
// dvoid *ctxp,
NULL,
// OCICallbackLobWrite (cbfp)
0,
// ub2 csid, The character set ID of the data in the buffer
// 0 -> client's NLS_LANG or NLS_CHAR value
SQLCS_IMPLICIT
// ub1 csfrm, The character set ID of the destination LOB
// SQLCS_IMPLICIT -> database character set
// SQLCS_NCHAR -> NCHAR character set ID
);
...
16
Как да вземен character set ID?
{
ub2 char_set_id;
char_set_id = OCINlsCharSetNameToId (
envhp,
// dvoid *hndl,
"WE8ISO8859P1" // CONST oratext *name
)
...
}
17
...
if( status != OCI_SUCCESS ) {
sprintf( log_msg, "Failed to write %u bytes in the lob",
to_write_num );
return( -2 );
}
if( wrote_num != to_write_num ) {
sprintf( log_msg, "Wrote only %u from all %u bytes in the
lob", wrote_num, to_write_num );
return( -3 );
}
...
// close the file
...
return( 0 );
}
18
Какво прави c_put_file_from_?lob()
Приема четири параметара:
pin_path - името на файла, където да се запише
съдържанието на ?LOB-а
pio_?lob - инициализиран ?LOB, от където да се
вземе съдържанието на файла
errMsg - връща текст на грешка
errCode - връща код на грешка
Ако записа на файла е успешен,
c_put_file_from_?lob() връща 0
19
PL/SQL c_put_file_to_?lob()
create or replace function c_put_file_to_blob(
pin_path in
varchar2,
pio_blob in out nocopy blob
errMsg
in out
varchar2,
errCode
in out
binary_integer
) return binary_integer as
is language C
library LOB_EPC_LIB
name "c_put_file"
parameters(
context,
pin_path
string,
pio_blob
ociloblocator,
pio_blob
indicator by reference,
errMsg
string,
errCode
int,
return
int
);
/
20
С функция - c_put_file()
int c_put_file(
OCIExtProcContext *ctx,
char
*pin_path,
OCILobLocator
**pio_blob,
short
*pio_blob_ind,
char
*errMsg,
int
*errCode
)
{
if( ctx == 0 ||
pio_blob == 0 || *pio_blob == 0 || pio_blob_ind == 0 ||
errMsg == 0
|| errCode == 0 ) {
LogError( "null input pointer", "c_put_file() " );
return( -1 );
}
...
// get the OCI environment
...
// check whether pio_blob is initialized and get its size
...
21
...
// open the file for write
...
// read path of the blob data and put it into buffer
//
#define LOB_BUFF_SIZE
( (ub4)( 1*1024*1024 ) )
//
char
lob_buff[LOB_BUFF_SIZE];
read_num = to_read_num;
status = OCILobRead (
svchp,
errhp,
pio_blob,
&read_num,
read_from,
lob_buff,
to_read_num,
NULL,
NULL,
0,
SQLCS_IMPLICIT
);
...
//
//
//
//
//
//
//
//
//
//
//
// 1 MBytes
OCISvcCtx *svchp,
OCIError *errhp,
OCILobLocator *locp,
ub4 *amtp,
ub4 offset,
dvoid *bufp,
ub4 bufl,
dvoid *ctxp,
OCICallbackLobRead (cbfp)
ub2 csid, The character set ID of the data in the buffer
ub1 csfrm, The character set ID of the source LOB
22
...
if( status != OCI_SUCCESS || status == OCI_NEED_DATA ) {
sprintf( log_msg, "Unable to get the lob data" );
return( -3 );
}
...
// write the buffer data into the opened file
...
// close the file
...
return( 0 );
}
23
Тест на комплекта BLOB функции
set serveroutput on
set verify off
declare
pin_path
varchar2( 1000 );
pio_blob
blob;
errmsg
varchar2( 1000 ) := lpad( '', 1000, ' ' );
errcode
binary_integer;
v_return
binary_integer;
begin
dbms_output.enable;
pin_path := '&1';
pio_blob := to_blob( 'abcdef' );
errmsg := '';
errcode := 5;
dbms_output.put_line( 'PL/SQL blob test: will read a blob from file ' ||
pin_path || '...' );
dbms_output.put_line( 'PL/SQL blob test: c_get_file_to_blob() ...' );
v_return := c_get_file_to_blob( pin_path, pio_blob, errmsg, errcode );
продължава на следващият слайд ...
24
продължение от предишният слайд ...
dbms_output.put_line( 'PL/SQL blob test: c_get_file_to_blob() returns ' || v_return );
dbms_output.put_line( 'PL/SQL blob test: got ' || dbms_lob.getlength( pio_blob ) ||
' bytes into blob' );
dbms_output.put_line( 'PL/SQL blob test: blob content in hexadecimal numbers' );
dbms_output.put_line( '[ 0..15] = ' || rawtohex( dbms_lob.substr( pio_blob, 16, 1)) );
dbms_output.put_line( '[16..31] = ' || rawtohex( dbms_lob.substr( pio_blob, 16, 17)) );
dbms_output.put_line( '[32..47] = ' || rawtohex( dbms_lob.substr( pio_blob, 16, 33)) );
pin_path := '&2';
errmsg := '';
errcode := 5;
dbms_output.put_line( 'PL/SQL blob test: will write the blob to file ' ||
pin_path || '...' );
dbms_output.put_line( 'PL/SQL blob test: c_put_file_from_blob() ...' );
v_return := c_put_file_from_blob( pin_path, pio_blob, errmsg, errcode );
dbms_output.put_line( 'PL/SQL blob test: c_put_file_from_blob() returns ' ||
v_return );
end;
/
25
Файловете от примера
[oracle@didel4 lob_epc]$ ls -l
total 68
-rw-r--r-- 1 oracle dba 2851
-rw-r--r-- 1 oracle dba 1075
-rw-r--r-- 1 oracle dba 1754
-rw-r--r-- 1 oracle dba 1723
-rw-r--r-- 1 oracle dba 3073
-rw-r--r-- 1 oracle dba 1076
-rw-r--r-- 1 oracle dba 2568
-rw-r--r-- 1 oracle dba
860
-rw-r--r-- 1 oracle dba
323
-rw-r--r-- 1 oracle dba 1250
-rw-r--r-- 1 oracle dba 3636
-rw-r--r-- 1 oracle dba
763
-rw-r--r-- 1 oracle dba 3843
[oracle@didel4 lob_epc]$
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
Apr
18
18
18
18
18
18
18
18
18
18
18
18
18
21:08
21:08
21:19
21:24
21:08
21:08
21:08
21:08
21:08
21:33
21:08
21:08
21:08
file_get.c
file_get.h
file_gp_blob_test.sql
file_gp_clob_test.sql
file_put.c
file_put.h
lob_epc.sql
logging.c
logging.h
makefile
oci_lobs.c
oci_lobs.h
oci_lobs_fixed_iso8859p1.c
26
makefile
OS_SOURCES=oci_lobs.c file_get.c file_put.c logging.c
LOB_EPC_SO=${ORACLE_HOME}/lib/lob_epc.so
LOB_EPC_LOG=/tmp/lob_epc.log
all: so func blob clob
so $(LOB_EPC_SO): $(SO_SOURCES)
gcc -shared -o lob_epc.so -I ${ORACLE_HOME}/rdbms/public $(SO_SOURCES)
cp lob_epc.so $(LOB_EPC_SO)
chmod 0755 $(LOB_EPC_SO)
ls -l $(LOB_EPC_SO)
func: lob_epc.sql $(LOB_EPC_SO)
sqlplus -S di1/x @lob_epc.sql
продължава на следващият слайд ...
27
продължение от предишният слайд ...
blob: file_gp_blob_test.sql $(LOB_EPC_SO)
rm -rf /tmp/file_in.bin /tmp/file_out.bin $(LOB_EPC_LOG)
cp $(LOB_EPC_SO) /tmp/file_in.bin
ls -l /tmp/file_in.bin
od -t x1 /tmp/file_in.bin | head -3
sqlplus -S di1/x @file_gp_blob_test.sql /tmp/file_in.bin \
/tmp/file_out.bin
diff /tmp/file_in.bin /tmp/file_out.bin
cat $(LOB_EPC_LOG)
clob: file_gp_clob_test.sql $(LOB_EPC_SO)
rm -rf /tmp/file_in.txt /tmp/file_out.txt $(LOB_EPC_LOG)
cp makefile /tmp/file_in.txt
ls -l /tmp/file_in.txt
od -c /tmp/file_in.txt | head -3
sqlplus -S di1/x @file_gp_clob_test.sql /tmp/file_in.txt \
/tmp/file_out.txt
diff /tmp/file_in.txt /tmp/file_out.txt
cat $(LOB_EPC_LOG)
28
make so
[oracle@didcos42 epc_demo]$ make so
gcc -shared -o lob_epc.so -I /oracle/10.2/rdbms/public oci_lobs.c file_get.c
file_put.c logging.c
cp lob_epc.so /oracle/10.2/lib/lob_epc.so
chmod 0755 /oracle/10.2/lib/lob_epc.so
ls -l /oracle/10.2/lib/lob_epc.so
-rwxr-xr-x 1 oracle dba 12365 Apr 19 10:27 /oracle/10.2/lib/lob_epc.so
[oracle@didcos42 epc_demo]$
29
make func
[oracle@didcos42 epc_demo]$ make func
sqlplus -S di1/x @lob_epc.sql
Library created.
create function c_get_file_to_blob ...
Function created.
create function c_get_file_to_clob ...
Function created.
create function c_put_file_from_blob ...
Function created.
create function c_put_file_from_clob ...
Function created.
[oracle@didcos42 epc_demo]$
30
make blob
[oracle@didcos42 epc_demo]$ make blob
rm -rf /tmp/file_in.bin /tmp/file_out.bin /tmp/lob_epc.log
cp /oracle/10.2/lib/lob_epc.so /tmp/file_in.bin
ls -l /tmp/file_in.bin
-rwxr-xr-x 1 oracle dba 12365 Apr 19 10:35 /tmp/file_in.bin
od -t x1 /tmp/file_in.bin | head -3
0000000 7f 45 4c 46 01 01 01 00 00 00 00 00 00 00 00 00
0000020 03 00 03 00 01 00 00 00 30 0e 00 00 34 00 00 00
0000040 3c 23 00 00 00 00 00 00 34 00 20 00 04 00 28 00
sqlplus -S di1/x @file_gp_blob_test.sql /tmp/file_in.bin /tmp/file_out.bin
PL/SQL blob test: will read a blob from file /tmp/file_in.bin...
PL/SQL blob test: c_get_file_to_blob() ...
PL/SQL blob test: c_get_file_to_blob() returns 0
PL/SQL blob test: got 12365 bytes into blob
PL/SQL blob test: blob content in hexadecimal numbers
[ 0..15] = 7F454C46010101000000000000000000
[16..31] = 0300030001000000300E000034000000
[32..47] = 3C230000000000003400200004002800
продължава на следващият слайд ...
31
продължение
PL/SQL blob
PL/SQL blob
PL/SQL blob
от предишният слайд ...
test: will write the blob to file /tmp/file_out.bin...
test: c_put_file_from_blob() ...
test: c_put_file_from_blob() returns 0
PL/SQL procedure successfully completed.
No errors.
diff /tmp/file_in.bin /tmp/file_out.bin
cat /tmp/lob_epc.log
[Thu Apr 19 10:35:11 2007] [info] c_get_file() will get /tmp/file_in.bin
[Thu Apr 19 10:35:11 2007] [info] write_lob() successfully wrote 12365
bytes to pos 1 of the lob
[Thu Apr 19 10:35:11 2007] [info] c_get_file() successfully got file
/tmp/file_in.bin with size 12365 and putted into lob
[Thu Apr 19 10:35:11 2007] [info] c_put_file() will create file
/tmp/file_out.bin
[Thu Apr 19 10:35:11 2007] [info] c_put_file() the input lob is valid and
its size is 12365
[Thu Apr 19 10:35:11 2007] [info] read_lob() successfully read 12365
bytes from pos 1 of the lob
[Thu Apr 19 10:35:11 2007] [info] c_put_file() created file
/tmp/file_out.bin 12365
32
[oracle@didcos42 epc_demo]$
make clob
[oracle@didcos42 epc_demo]$ make clob
rm -rf /tmp/file_in.txt /tmp/file_out.txt /tmp/lob_epc.log
cp makefile /tmp/file_in.txt
ls -l /tmp/file_in.txt
-rw-r--r-- 1 oracle dba 1241 Apr 19 15:01 /tmp/file_in.txt
od -c /tmp/file_in.txt | head -3
0000000
O
S
_
S
O
U
R
C
E
S
=
o
c
i
_
l
0000020
o
b
s
.
c
f
i
l
e
_
g
e
t
.
c
0000040
f
i
l
e
_
p
u
t
.
c
l
o
g
g
sqlplus -S di1/x @file_gp_clob_test.sql /tmp/file_in.txt /tmp/file_out.txt
PL/SQL clob test: will read a clob from file /tmp/file_in.txt...
PL/SQL clob test: c_get_file_to_clob() ...
PL/SQL clob test: c_get_file_to_clob() returns 0
PL/SQL clob test: got 1241 characters into clob
PL/SQL clob test: clob content
[ 0..15] = SO_SOURCES=oci_l
[16..31] = obs.c file_get.c
[32..47] = file_put.c logg
продължава на следващият слайд ...
33
продължение
PL/SQL clob
PL/SQL clob
PL/SQL blob
от предишният слайд ...
test: will write the clob to file /tmp/file_out.txt...
test: c_put_file_from_clob() ...
test: c_put_file_from_clob() returns 0
PL/SQL procedure successfully completed.
No errors.
diff /tmp/file_in.txt /tmp/file_out.txt
cat /tmp/lob_epc.log
[Thu Apr 19 15:04:26 2007] [info] c_get_file() will get /tmp/file_in.txt
[Thu Apr 19 15:04:26 2007] [info] write_lob() successfully wrote 1241
bytes to pos 1 of the lob
[Thu Apr 19 15:04:26 2007] [info] c_get_file() successfully got file
/tmp/file_in.txt with size 1241 and putted into lob
[Thu Apr 19 15:04:26 2007] [info] c_put_file() will create file
/tmp/file_out.txt
[Thu Apr 19 15:04:26 2007] [info] c_put_file() the input lob is valid and
its size is 1241
[Thu Apr 19 15:04:26 2007] [info] read_lob() successfully read 1241 bytes
from pos 1 of the lob
[Thu Apr 19 15:04:26 2007] [info] c_put_file() created file
/tmp/file_out.txt 1241
34
[oracle@didcos42 epc_demo]$
Въпроси и отговори ? !
35
Благодаря за Вашето внимание!
36