Transcript Deadly Sins against Dtabase Performance

Oracle External Procedure Calls
Теодор Иванов
Софтуерен инженер
Как да се научим да използваме EPC
Oracle Documentation
http://www.oracle.com/technology/documentation/database10gr2.html
Oracle Course
Oracle Database 10g: Advanced PL/SQL
http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=
D17220GC10&p_org_id=1001&lang=US&source_call=
Помощ от приятел …
2
Моят подход ...
Oracle® Database SQL Reference 10g Release 1 (10.1),
Part No. B10759-01
PL/SQL User's Guide and Reference 10g Release 1 (10.1),
Part No. B10807-01
Oracle® Data Cartridge Developer's Guide
10g Release 1 (10.1), Part No. B10800-01
5. Implementing Data Cartridges in C, C++ and Java
3
Моят подход ...
Oracle® Database Application Developer's Guide –
Fundamentals 10g Release 1 (10.1), Part Number B10795-01
1 Programmatic Environments
Overview of Pro*C/C++
Overview of OCI and OCCI
Choosing a Programming Environment
8 Calling External Procedures
4
Моят подход ...
Oracle® Call Interface - Programmer's Guide
10g Release 1 (10.1), Part No. B10779-01
1. Introduction and Upgrading
2. OCI Programming Basics
3. Datatypes
18. OCI Datatype Mapping and Manipulation
Functions
19. OCI Cartridge Functions
5
Какво е 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
6
Кога се използва Oracle EPC ?
Липса на функционалност в Oracle RDBMS или
недостатачна ефективност на PL/SQL:
Многократни сметки с плаваща запетая и работа с
едномерни и многомерни масиви и указатели
Описание или пресмятания, свързани със сложни
информационни обекти – графи, матрици, и т.н.
Използване на вече готови библиотеки
Пълноценнo използване възможностите на
операционната система
7
Как работи Oracle EPC ?
listener.ora
SID_LIST_LISTENER =
OS
create or replace function a_plus_b(
$ORACLE_HOME/lib/sum.so
a in binary_integer,
tnsnames.ora
a_plus_b_c() { … }
(SID_DESC =
b in binary_integer
(PROGRAM = extproc)
) return binary_integer
using: OS .so load
EXTPROC_CONNECTION_DATA
=
is language C
PL/SQL (SID_NAME = EPC_SID)
calls: sum.so/a_plus_b_c()
(DESCRIPTION =
library sum_lib
(ORACLE_HOME = /oracle/10.1)
SQL
begin
name "a_plus_b_c"
(ADDRESS_LIST =
extproc agent
(ENVS = "EXTPROC_DLLS=ANY")
res := a_plus_b(
a, b );
SQL> select
$ORACLE_HOME/bin/extproc
(ADDRESS = (PROTOCOL = parameters(
IPC)(KEY =
EPC_IPC_KEY))
end;
)
a_plus_b( a, b )
a
int,
)
/
) from dual;
using: EPC_SID
b int,
(CONNECT_DATA =
executing: extproc agent
return int
(SID = EPC_SID)
);
LISTENER =
)
Oracle Listener process LISTENER
/
(DESCRIPTION_LIST = ) ...
using listener.ora
PL/SQL function
(DESCRIPTIONsum_lib
=
a_plus_b(…),
Oracle RDBMS
...
(SID_LIST =
using: EPC_IPC_KEY
sum_lib
looking for: EPC_SID
as '${ORACLE_HOME}/lib/sum.so';
/
OCI network libraries,
using tnsnames.ora
EXTPROC_CONNECTION_DATA
create =orEPC_IPC_KEY))
replace library
(ADDRESS = (PROTOCOL = IPC)(KEY
)
)
Library
sum_lib,
sum.so
8
Как работи Oracle EPC ? - 2
OS
Oracle RDBMS
$ORACLE_HOME/lib/sum.so
a_plus_b_c() { … }
using: OS .so load
calls: sum.so/a_plus_b_c()
PL/SQL
SQL
begin
res := a_plus_b( a, b );
SQL> select
end;
a_plus_b( a, b )
/
from dual;
extproc agent
$ORACLE_HOME/bin/extproc
using: EPC_SID
executing: extproc agent
PL/SQL function
a_plus_b(…), sum_lib
Library sum_lib,
sum.so
EXTPROC_CONNECTION_DATA
Oracle Listener process LISTENER
using listener.ora
using: EPC_IPC_KEY
looking for: EPC_SID
OCI network libraries,
using tnsnames.ora
9
Пример за EPC
С функция epc_demo_func_c() и Linux библиотека
epc_demo.so
C Тест на функцията epc_demo_func_c
Съдържание на tnsnames.ora
Съдържание на sqlnet.ora
Съдържание на listener.ora
Oracle библиотека epc_demo_lib
PL/SQL функция epc_demo_func()
Тест на PL/SQL функцията epc_demo_func()
makefile
10
Какво прави epc_demo_func()
Приема два параметара:
a - целочислено число
s - символен низ ( стринг )
Ако а е NULL, тогава epc_demo_func() връща 0
Ако а не е NULL, тогава разпечатва стойностите на
всички входящи за функцията параметри в стринг buff
и връща стойността на buff в s, а epc_demo_func()
връща стойността на a
11
PL/SQL библиотека и функция epc_demo_func.sql
create or replace library epc_demo_lib as '${ORACLE_HOME}/lib/epc_demo.so';
/
create or replace function epc_demo_func(
a in pls_integer,
s in out varchar2
) return pls_integer
is language C
library epc_demo_lib
name "epc_demo_func_c"
parameters(
a int,
a indicator,
s string,
s length by reference int,
s maxlen by reference int,
return int
);
/
exit
при wrap на библиотеката, не може
да се използва обращение към
shell environment variables
12
Съответствие на PL/SQL и C параметрите
PL/SQL
PARAMETERS
C
epc_demo_func(
parameters(
int
epc_demo_func_c(
a in pls_integer,
a int,
a indicator,
int
short
s in out varchar2
s string,
s length by reference int,
s maxlen by reference int,
char
int
int
return int
)
)
) return pls_integer
a,
a_ind,
*s,
*s_length,
*s_maxlen
13
С функция - epc_demo_func_c.c
#include <stdio.h>
#include <string.h>
#include "oci.h"
int epc_demo_func_c(
int
a,
short a_ind,
char *s,
int
*s_length,
int
*s_maxlen
)
{
// if a is NULL then return 0
if( a_ind == OCI_IND_NULL )
return( 0 );
продължава на следващият слайд ...
14
продължение от предишният слайд ...
// prepare the new return value for s
char buff[200];
sprintf(
buff,
"%s\na=%d, a_ind=%d, s='%s', s_length=%d, s_maxlen=%d",
"C func epc_demo_func_c()\nreceived the following input paramters:",
a,
a_ind,
s,
*s_length,
*s_maxlen
);
// copy the prepared value into y
strncpy( s, buff, *s_maxlen );
// set new s length
*s_length = strlen( s );
// return the value of a
return( a );
}
15
Тест на С функцията - epc_demo_func_c.c
#ifdef TEST
int main( int argc, char *argv[] )
{
char ss[200] = { "123" };
int aa = -7,
res = 0,
ss_len = strlen( ss ),
ss_maxl = sizeof( ss ) - 1;
printf( "\nC test: begins\n\n" );
printf( "C test main(): will call C epc_demo_func_c() with
parameters:\n" );
printf( "C test main():
not NULL a = %d\n", aa );
printf( "C test main():
and s = >>>%s<<<\n\n", ss );
res = epc_demo_func_c( aa, OCI_IND_NOTNULL, ss, &ss_len, &ss_maxl );
продължава на следващият слайд ...
16
продължение от предишният слайд ...
printf(
printf(
printf(
printf(
"C test main(): epc_demo_func_c() returns %d\n", res );
"C test main():
and resulted s is >>>%s<<<\n", ss );
"C test main():
and resulted s length is %d\n", ss_len );
"\nC test: ends\n\n" );
return( 0 );
}
#endif
17
Тест на PL/SQL функцията epc_demo_func_test.sql
set serveroutput on
declare
aa pls_integer
:= -7;
ss varchar2( 200 ) := '123';
res pls_integer
:= 0;
begin
dbms_output.enable;
dbms_output.put_line( chr( 10 ) || 'PL/SQL test: begins' || chr( 10 ) );
dbms_output.put_line( 'PL/SQL test: will call PL/SQL epc_demo_func() ' ||
|| 'with parameters' );
dbms_output.put_line( 'PL/SQL test:
not NULL a = ' || aa );
dbms_output.put_line( 'PL/SQL test:
s = >>>' || ss || '<<<' || chr( 10 ) );
res := epc_demo_func( aa, ss );
dbms_output.put_line( 'PL/SQL test: epc_demo_func() returns ' || res );
dbms_output.put_line( 'PL/SQL test:
and resulted s is >>>' || ss || '<<<' );
dbms_output.put_line( chr( 10 ) || 'PL/SQL test: ends' || chr( 10 ) );
end;
/
18
Редактиране на tnsnames.ora
Файлът се намира в $TNS_ADMIN
или в $ОRACLE_HOME/network/admin
EXTPROC_CONNECTION_DATA[.default_domain] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))
)
(CONNECT_DATA =
(SID = EPC_SID)
)
)
19
[.default_domain]
Трябва да съответствува на стойността на:
NAMES.DEFAULT_DOMAIN = semantec.bg
от файла sqlnet.ora
20
Редактиране на listener.ora
Спиране на лисънъра, редактиране на listener.ora и
стартиране на лисънъра
SID_LIST_LISTENER =
(SID_LIST =
...
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = EPC_SID)
(ORACLE_HOME = /oracle/10.1)
(ENVS = "EXTPROC_DLLS=ANY")
)
)
тези стойности трябва да
съответстват на маркираните
в зелено от tnsnames.ora
LISTENER =
(DESCRIPTION_LIST =
...
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))
)
)
21
Тест на редактираните *.ora файловете
[oracle@didcos42 ~]$ tnsping EXTPROC_CONNECTION_DATA.SEMANTEC.BG
TNS Ping Utility for Linux: Version 10.1.0.4.0 - Production on 24-OCT-2006 20:59:47
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle/10.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY =
EPC_IPC_KEY))) (CONNECT_DATA = (SID = EPC_SID)))
OK (10 msec)
[oracle@didcos42 ~]$ tnsping EXTPROC_CONNECTION_DATA
TNS Ping Utility for Linux: Version 10.1.0.4.0 - Production on 24-OCT-2006 20:59:51
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle/10.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY =
EPC_IPC_KEY))) (CONNECT_DATA = (SID = EPC_SID)))
OK (10 msec)
22
makefile
all: so test sql
so ${ORACLE_HOME}/lib/epc_demo.so: epc_demo_func_c.c
gcc -shared -o epc_demo.so epc_demo_func_c.c
cp epc_demo.so ${ORACLE_HOME}/lib
chmod 0755 ${ORACLE_HOME}/lib/epc_demo.so
ls -l ${ORACLE_HOME}/lib/epc_demo.so
test: epc_demo_func_c.c
gcc -o epc_demo_func_c epc_demo_func_c.c -D TEST
ls -l epc_demo_func_c
./epc_demo_func_c
sql: epc_demo_func.sql ${ORACLE_HOME}/lib/epc_demo.so
sqlplus -S di1/x @epc_demo_func.sql
sqlplus -S di1/x @epc_demo_func_test.sql
23
Файловете от примера за EPC
[oracle@didcos42 epc_demo]$ ls -l
total 16
-rw-r--r--
1 oracle users
449 Oct 23 19:58 epc_demo_func.sql
-rw-r--r--
1 oracle users
793 Oct 23 20:22 epc_demo_func_c.c
-rw-r--r--
1 oracle users
326 Oct 23 20:07 epc_demo_func_test.sql
-rw-r--r--
1 oracle users
456 Oct 23 20:57 makefile
[oracle@didcos42 epc_demo]$
24
[oracle@didcos42 epc_demo]$ make test
gcc -o epc_demo_func_c -I /oracle/10.1/rdbms/public -D TEST epc_demo_func_c.c
ls -l epc_demo_func_c
-rwxr-xr-x 1 oracle users 6175 Oct 25 18:59 epc_demo_func_c
./epc_demo_func_c
C test: begins
C test main(): will call C epc_demo_func_c() with parameters:
C test main():
not NULL a = -7
C test main():
and s = >>>123<<<
C test main(): epc_demo_func_c() returns -7
C test main():
and resulted s is >>>C func epc_demo_func_c()
received the following input paramters:
a=-7, a_ind=0, s='123', s_length=3, s_maxlen=199<<<
C test main():
and resulted s length is 113
C test: ends
[oracle@didcos42 epc_demo]$
25
[oracle@didcos42 epc_demo]$ make sql
sqlplus -S di1/x @epc_demo_func.sql
Library created.
Function created.
sqlplus -S di1/x @epc_demo_func_test.sql
PL/SQL test: begins
PL/SQL test: will call PL/SQL epc_demo_func() with parameters
PL/SQL test:
not NULL a = -7
PL/SQL test:
s = >>>123<<<
PL/SQL test: epc_demo_func() returns -7
PL/SQL test:
and resulted s is >>>C func epc_demo_func_c()
received the
following input paramters:
a=-7, a_ind=0, s='123', s_length=3, s_maxlen=200<<<
PL/SQL test: ends
PL/SQL procedure successfully completed.
[oracle@didcos42 epc_demo]$
26
Дебъгване на EPC
OS
Oracle RDBMS
[oracle@didcos42 ~]$ ps
UID
PID PPID C
oracle
9380
1 0
SQL*Plus 15776 15670 0
oracle
[oracle@didcos42
SQL>
select a_plus_b( 2,~]$
3 )
-ef |
STIME
15:32
15:33
from dual; <Enter>
…
grep extproc
TTY
TIME CMD
using: OS .so load
?
00:00:00 extprocEPC_SID (LOCAL=NO)
calls: sum.so/a_plus_b_c()
pts/1
00:00:00 grep extproc
extproc agent
$ORACLE_HOME/bin/extproc
SQL> !ps -ef | grep extproc <Enter>
…
oracle 9380 ... extprocEPC_SID (LOCAL=NO)
…
SQL> select function_for_debugging(…)
from dual; <Enter>
$ORACLE_HOME/lib/sum.so
a_plus_b_c() { … }
Debugger - gdb, ddd
(gdb) attach 9380 <Enter>
Attaching to process 9380
…
(gdb) break pextproc <Enter>
Breakpoint 1 at 0xb7d77782
(gdb) cont <Enter>
Continuing.
[Switching to Thread -1225242944 (LWP 9380)]
Breakpoint 1, 0xb7d77782 in pextproc () from /…/libagtsh.so
(gdb) break function_for_debugging <Enter>
Breakpoint 2 at 0xb6e9e564
(gdb) cont <Enter>
Continuing.
Breakpoint 2, 0xb6e9e564 in function_for_debugging ()
from /…/sum.so
(gdb) _
27
Допълнителна информация на EPC
Синтаксис на 'parameters'
Предизвикване на exception от EPC
Памет, указатели, стойности, променливи
Предаване на масиви като параметри
Изпълнение на DDL и DML от EPC
28
Синтаксис на 'parameters'
PARAMETERS (external_parameter[, external_parameter]...)
external_parameter stands for:
{ CONTEXT |
SELF [{TDO | property}] |
{parameter_name | RETURN} [property] [BY REFERENCE] [external_datatype]
}
property stands for:
{ INDICATOR [{STRUCT | TDO}] |
LENGTH |
DURATION |
MAXLEN |
CHARSETID |
-- OCI_ATTR_CHARSET_ID
CHARSETFORM
-- OCI_ATTR_CHARSET_FORM
}
29
Съответствие на типовете PL/SQL – C
PL/SQL data type
PARAMETERS ext.data type
CHAR
SHORT
INT
LONG
C data type
BOOLEAN,
BINARY_INTEGER,
PLS_INTEGER
[UNSIGNED]
[UNSIGNED]
[UNSIGNED]
[UNSIGNED]
[unsigned]
[unsigned]
[unsigned]
[unsigned]
char
short
int
long
CHAR, LONG,
VARCHAR2
STRING
OCISTRING
char *
OCIString *
BLOB, CLOB
OCILOBLOCATOR
OCILobLocator *
NUMBER
OCINUMBER
OCINumber *
DATE
OCIDATE
OCIDate *
VARRAY, TABLE
OCICOLL
OCITable *
CONTEXT
OCIExtProcContext *
30
Съответствие на типовете
PARAMETERS Property – C
Property
Allowed ext.data type
C data type
INDICATOR
SHORT
short
LENGTH,
MAXLEN
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
[unsigned] short
int
[unsigned] long
31
Предизвикване на exception от EPC
CREATE OR REPLACE PROCEDURE
plsTo_divide_proc (
dividend IN BINARY_INTEGER,
divisor IN BINARY_INTEGER,
result OUT FLOAT
)
AS LANGUAGE C
NAME "C_divide"
LIBRARY MathLib
WITH CONTEXT
PARAMETERS (
CONTEXT,
dividend INT,
divisor INT,
result FLOAT
);
/
32
void C_divide (
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result
)
{
/* Check for zero divisor. */
if( divisor == (int)0 ) {
/* Raise exception ZERO_DIVIDE, which is Oracle error 1476. */
if( OCIExtProcRaiseExcp( ctx, (int)1476 ) == OCIEXTPROC_SUCCESS )
return;
else
assert( 0 ); /* Incorrect parameters were passed. */
}
*result = (float)dividend / (float)divisor;
}
33
void C_divide (
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result
)
{
/* Check for zero divisor. */
if( divisor == (int)0 ) {
/* Raise exception 20100, which is user defined */
if( OCIExtProcRaiseExcpWithMsg( ctx, (int)20100, "divisor is zero", 0 )
== OCIEXTPROC_SUCCESS )
return;
else
assert( 0 ); /* Incorrect parameters were passed. */
}
*result = (float)dividend / (float)divisor;
}
34
Памет, указатели, стойности, променливи
Области на действие на променливите и използване
на стойностите им
Необходимост от OCIExtProcAllocCallMemory
Начин за заобикаляне необходимостта от
OCIExtProcAllocCallMemory
35
С функция - epc_demo_func_c()
int global_definition = 0;
int epc_demo_func_c(
...
)
{
char buf[200];
static char large_buf[10485760];
// 10 MBytes
int initialize_every_time = 8;
static int static_initialized_every_time = 10;
static int not_initialized;
...
}
36
Необходимост от
OCIExtProcAllocCallMemory
например във функция за обединяване на два стринга:
функцията приема като параметри два стринга,
който трябва да обединят и да върне резултът от
обединието
37
Начин за заобикаляне необходимостта от
OCIExtProcAllocCallMemory
вместо 'out' параметри да се използват 'in out'
параметри, чиито стойности да бъдат инициализирани
преди извикването на външна C функция:
declare
aa pls_integer
:= -7;
ss varchar2( 200 ) := '123';
res pls_integer
:= 0;
begin
...
res := epc_demo_func( aa, ss );
...
end;
/
38
Предаване на масиви като параметри PL/SQL
create or replace TYPE T_String_Array AS TABLE OF VARCHAR2(4000);
/
create or replace function func_with_varray(
pls_sql_table_of_strings in out T_String_Array,
) return pls_integer as
language C
name "c_func_with_varray"
library SOME_LIB
calling standard C
with context
parameters(
context,
pls_sql_table_of_strings OCICOLL,
return int
);
/
39
Предаване на масиви като параметри C
int c_func_with_varray(
OCIExtProcContext *ctx,
OCITable **pls_sql_table_of_strings,
)
{
// get the current number of elements into pls_sql_table_array
status = OCICollSize( ... );
// remove all current elements from pls_sql_table_array
status = OCICollTrim( ... );
// makes an OCI string from 'char *'
status = OCIStringAssignText( ... );
// append one more element to the pls_sql_table_array
status = OCICollAppend( ... );
}
40
Изпълнение на DDL и DML във EPC
int c_func_with_varray(
OCIExtProcContext *ctx,
...
)
{
OCIEnv
*envhp;
OCISvcCtx *svchp;
OCIError *errhp;
sword
err;
...
// get the environment handles for reuse the existing connection
err = OCIExtProcGetEnv( ctx, &envhp, &svchp, &errhp );
...
// executing all other kind of OCI calls
...
}
41
Q&A
42
Благодаря за Вашето внимание!
43