Deadly Sins against Dtabase Performance

Download Report

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