Tag: oracle
- Written by: ilmarkerm
- Category: Blog entry
- Published: May 17, 2012
Oracle Database has had the possibility to run Java code inside the database for a long time. It’s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn’t successful in implementing it under 11.2.0.2).
First, load the Java source into database. The code below shows:
- How to return simple datatype (int) from Java function – makeConnection
- How to return Oracle CLOB datatype from Java – makeConnectionClob
- How to execute SQL from Java, in the same calling session
Note that method main is just added for testing from command line.
Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.
Download the source: java_source.java and PL/SQL wrapper.sql.
When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );
- Written by: ilmarkerm
- Category: Blog entry
- Published: May 10, 2012
When instance fails in Oracle RAC, the services that were using this instance as a preferred instance are automatically relocated to instances marked as available for this service. But after the failed instance recovers and starts up again, the relocated services are not moved back and need manual srvctl relocate service command from administrator to move them back.
Here is a little Bash script to automate this process. Oracle Clusterware (Grid Infrastructure) can execute user callout scripts on FAN events, like INSTANCE up/down. Place this script under $GRID_HOME/racg/usrco/ and set the execute bits on the file. Then clusterware will execute that script for all FAN events, but the script will start processing only for instance up event.
Why is it needed? We just switched over to 4-node RAC consisting of many different applications, almost each of them connecting to its own schema. We created each application its own service restricting it to 1 (or max 2) nodes (1 or 2 nodes as preferred, all other nodes listed as available). After the first rolling patching, I noticed that the connection count and load on each node was very unbalanced, vast majority of the connections were connected to node1 and the last patched node had almost none and it did not get better over a few hours. This was because most of the services ended up on node1 and I had to manually look over each service and relocate it back where it belongs. This script attempts to automate this process.
Tested on Oracle Linux 5.8 with Oracle Grid Infrastructure 11.2.0.3 and Oracle Database 11.2.0.2 and 11.2.0.3.
- Written by: ilmarkerm
- Category: Blog entry
- Published: August 28, 2010
My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.
Oracle offers this solution for Enterprise Edition, but its an extra cost option – Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in its simplest form, using user profile password verification function and database logon trigger.
Update: Thanks to Job Miller for pointing out that password authentication does not require licensing ASO option anymore, but requires licensing Oracle Internet Directory: Link to 10.2 Oracle® Database Licensing Information – 2 Options and Packs
Basically, the procedure works as follows:
- Create a new profile for AD-authenticated users with password verification function.
- Assign this new profile to a database user.
- When the database user changes password, the password verification function will try to connect to AD with the user specified password, if AD authentication is successful, then the password is correct and can be changed and stored by Oracle.
- Every time the user connects to the database, after logon trigger will query AD for the user status: does the user still exist, is the user disabled or expired, when did the user last change password. If the user status is changed in AD, the trigger will lock or expire the database user accordingly.
The code
My solution consists of a PL/SQL package, that I create under my database administration account, named DBAUSER. The package will be called from password verification function and the logon trigger. The DBAUSER account needs network ACLs to permit it to connect to the AD server (11g+!) and the following system privileges:
GRANT create job TO dbauser;
GRANT alter user TO dbauser;
GRANT select ON sys.user$ TO dbauser;
Now the package:
NB! Look into the package body script! There are configuration parameters on top, that you MUST look over and change!
Now, create the password verification function under SYS schema, that will only call out the created package:
CREATE OR REPLACE FUNCTION sys.ad_profile_verify_function
(username varchar2, password varchar2, old_password varchar2)
RETURN boolean IS
BEGIN
RETURN DBAUSER.PROFILE_AD_AUTH.VERIFY_PASSWORD(username, password);
END;
/
Next, create a new profile setting the password verification function and also would be good to set parameter PASSWORD_LIFE_TIME to the same value AD has for password expiration. An example profile:
CREATE PROFILE ad_user LIMIT
SESSIONS_PER_USER 2
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION ad_profile_verify_function;
And finally the logon trigger, that will fire the user verification function for all users having the ad_user profile. Modify it for your own needs before executing. The following procedure also needs SELECT privilege on SYS.DBA_USERS granted to SYSTEM.
GRANT execute ON dbauser.profile_ad_auth TO system;
CREATE OR REPLACE TRIGGER system.AD_USER_LOGON AFTER LOGON ON DATABASE
DECLARE
i NUMBER;
BEGIN
IF user NOT IN ('SYS','SYSTEM','APPUSER1','APPUSER2') and sys_context('USERENV','AUTHENTICATION_METHOD') = 'PASSWORD' THEN
-- Check if user belongs to a specific profile
SELECT count(*) INTO i
FROM sys.dba_users
WHERE username = user AND profile = 'AD_USER';
-- Execute verification procedure
IF i > 0 THEN
dbauser.profile_ad_auth.verify_user(user);
END IF;
END IF;
END;
/
To create a user that will be authenticated from AD, first create a regular user with a temporary password, then issue ALTER USER to set the profile. This is needed because if profile is set in CREATE USER command, then the initial password will also be validated with the verification function.
CREATE USER ilmar IDENTIFIED BY test123;
ALTER USER ilmar PROFILE ad_user PASSWORD EXPIRE;
NB! 10g passwords are not case sensitive, so subsequent logins will be allowed with passwords using the wrong case. In 11g, be sure to enable initialization parameter SEC_CASE_SENSITIVE_LOGON, to benefit from case sensitive passwords.
I have not yet used it in production, so all feedback is welcome! I’ve tested the code on 10.2 EE and 11.2 EE, should work on standard edition also.
Update: Take a look at Oracle-L mailing list thread about the same subject: Click here.
Update: SQL Developer supports changing password when OCI/Thick driver is used. To make it easier for users, set g_expired_password parameter to not null value. In this case users do not need to remember their previous password and could use a known preset password.
Update: Due to differences in 11gR2 and 10g dbms_scheduler, I added ” and sys_context(‘USERENV’,’AUTHENTICATION_METHOD’) = ‘PASSWORD'” to logon trigger. So that the trigger is only executed when user is authenticated with password, not session created by scheduler.
- Written by: ilmarkerm
- Category: Blog entry
- Published: October 27, 2009
I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX.
It’s a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database 🙂
Oracle XE comes with a pre-configured XDB HTTP listener for APEX.
I don’t need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is no need to use PHP or some other scripting engine outside the database.
Here are the steps I used to configure Oracle XE XDB HTTP listener with additional path (DAD) to my custom procedure.
Make HTTP listener to listen on all network interfaces, not only localhost:
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
Oracle XE has pre-defined DAD for APEX – /apex/
The following code will create a new DAD /mobileapp/, associate it with schema MOBILEAPP and authorize it to run without asking the user for a password.
BEGIN
DBMS_EPG.create_dad (
dad_name => 'mobileapp',
path => '/mobileapp/*');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'default-page',
attr_value => 'home');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'database-username',
attr_value => 'MOBILEAPP');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'nls-language',
attr_value => 'american_america.al32utf8');
DBMS_EPG.authorize_dad (
dad_name => 'mobileapp',
user => 'MOBILEAPP');
END;
/
I used the following DAD attributes:
| default-page | specifies the default procedure name, that will be used when the procedure name is not specified in a HTTP URL |
|---|---|
| database-username | associated database schema name |
| nls-language | NLS client value, for a web application the important part is the charset. al32utf8 means the result page is in UTF-8. |
| DBMS_EPG.authorize_dad | This will disable the HTTP basic authentication |
There are other interesting DAD attributes and the current XDB HTTP configuration and APEX DAD configuration can be views using this SQL:
SQL> SELECT DBMS_XDB.CFG_GET().getClobVal() FROM DUAL;
...
APEX
PL/SQL
APEX
ANONYMOUS
apex
wwv_flow_file_objects$
docs
wwv_flow_file_mgr.process_download
american_america.al32utf8
wwv_flow_epg_include_modules.authorize
...
Now all MOBILEAPP PL/SQL procedures and packages can be accessed over HTTP and the HTTP URL has the following form:
http://oraclexeserver:8080/mobileapp/procedurename
or
http://oraclexeserver:8080/mobileapp/packagename.procedurename
So, a simple hello world procedure using PL/SQL Web Toolkit could look like this:
CREATE OR REPLACE PROCEDURE mobileapp.home IS
BEGIN
HTP.htmlopen;
HTP.headopen;
HTP.title('Page title');
HTP.headclose;
HTP.bodyopen;
HTP.print('Hello world');
HTP.bodyclose;
HTP.htmlclose;
END;
/
HTTP URL for this procedure will be http://oraclexeserver:8080/mobileapp/home or just http://oraclexeserver:8080/mobileapp/ (note the default-page DAD attribute).
The proceudres like HTP.htmlopen will just generate HTML tags.
Passing HTTP GET parameters is very easy and they will be set as a procedure parameters with the same name. Parameter data type is VARCHAR2 or if HTTP request has more than one parameter with the same name, then TABLE OF VARCHAR2 data type is used.
CREATE OR REPLACE PROCEDURE mobileapp.paramtest(p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL) IS
BEGIN
IF p1 IS NULL THEN
HTP.p('Hello world');
ELSE
HTP.p('Hello p1='||p1||' p2='||p2);
END IF;
END;
/
This procedure can be called with HTTP URL:
http://oraclexeserver:8080/mobileapp/paramtest?p1=value1&p2=value2
Both parameters are optional (procedure parameters have DEFAULT value set).
If the procedure raises an exception or suitable procedure name with correct argument list cannot be found, then HTTP error code 404 (page not found) is the result.
- Written by: ilmarkerm
- Category: Blog entry
- Published: August 4, 2009
I got a task some time ago, to copy a-lot-of-millions of rows from Oracle to MySQL. As fast as possible.
In the last year I have used Oracle Heterogeneous Services (HSODBC) a lot to select data from different non-Oracle databases, but I had yet to try to unload data from Oracle with best performance.
My first thought was, of course, that the process needs to run in parallel. Here I’ll first take a look at the overall HSODBC performance and then look how can I make this process run in parallel.
In this test I use Oracle Database 11.1.0.7 as source, Oracle Heterogeneous Services 10.2.0.4 with MyODBC 5.1 driver, MySQL 5.1 destination database. In my setup, I have all these three components running on separate servers.
SQL> CREATE TABLE hsperftest AS SELECT * FROM all_objects UNION ALL SELECT * FROM all_objects UNION ALL SELECT * FROM all_objects; SQL> DESC hsperftest Name Null? Type ------------------------------------------- -------- --------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) ID NOT NULL NUMBER(10)
And I create the same table in MySQL.
CREATE TABLE `hsperftest` ( `OWNER` varchar(30) DEFAULT NULL, `OBJECT_NAME` varchar(30) DEFAULT NULL, `SUBOBJECT_NAME` varchar(30) DEFAULT NULL, `OBJECT_ID` int(11) DEFAULT NULL, `DATA_OBJECT_ID` int(11) DEFAULT NULL, `OBJECT_TYPE` varchar(19) DEFAULT NULL, `CREATED` datetime DEFAULT NULL, `LAST_DDL_TIME` datetime DEFAULT NULL, `TIMESTAMP` varchar(19) DEFAULT NULL, `STATUS` varchar(7) DEFAULT NULL, `TEMPORARY` varchar(1) DEFAULT NULL, `GENERATED` varchar(1) DEFAULT NULL, `SECONDARY` varchar(1) DEFAULT NULL, `NAMESPACE` int(11) DEFAULT NULL, `EDITION_NAME` varchar(30) DEFAULT NULL, `ID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Set up HSODBC to connect to the MySQL database and create a database link on Oracle side. When setting up HSODBC, do not enable logging/tracing (HS_FDS_TRACE_LEVEL = off)! Tracing will serialize all database calls to the remote database.
I tested this with HSODBC running on Windows Vista and Linux (using unixODBC). The results were similar.
Test that MySQL table is accessible for Oracle:
SQL> DESC "hsperftest"@hsdb Name Null? Type ----------------------------------------------------- -------- ----------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER(10) DATA_OBJECT_ID NUMBER(10) OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER(10) EDITION_NAME VARCHAR2(30) ID NOT NULL NUMBER(10)
Before continuing, check that all MySQL data types are mapped correctly to Oracle. And make sure, that none of the columns are using deprecated LONG datatype.
First of all… the following SQL will not work:
SQL> insert into "hsperftest"@hsdb select * from hsperftest;
insert into "hsperftest"@hsdb select * from hsperftest
*
ERROR at line 1:
ORA-02025: all tables in the SQL statement must be at the remote database
Some PL/SQL is needed to first select from Oracle cursor and then bind the values to the remote database INSERT statement. The easiest way is:
BEGIN
FOR rec IN (SELECT * FROM HSPERFTEST)
LOOP
INSERT INTO "hsperftest"@hsdb
VALUES rec;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:14:28.38
When running this anonymous block I noticed, that the load on Oracle database and MySQL database was very light, but the HSODBC CPU usage was at the maximum. So HSODBC seems to be the bottleneck. What is it doing? With this kind of CPU usage its not going to scale much in parallel. If its performing some mappings between Oracle SQL and MySQL SQL, then can I remove this bottleneck by using DBMS_HS_PASSTHROUGH, that sends the query unmodified to the remote database?
DECLARE
p_c BINARY_INTEGER;
nr INTEGER;
BEGIN
p_c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsdb;
DBMS_HS_PASSTHROUGH.PARSE@hsdb (
p_c,
'insert into hsperftest (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,
TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,ID)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
);
FOR rec IN (SELECT * FROM HSPERFTEST)
LOOP
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 1, rec.owner);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 2, rec.object_name);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 3, rec.SUBOBJECT_NAME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 4, rec.OBJECT_ID);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 5, rec.DATA_OBJECT_ID);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 6, rec.OBJECT_TYPE);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 7, rec.CREATED);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 8, rec.LAST_DDL_TIME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 9, rec.TIMESTAMP);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 10, rec.STATUS);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 11, rec.TEMPORARY);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 12, rec.GENERATED);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 13, rec.SECONDARY);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 14, rec.NAMESPACE);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 15, rec.EDITION_NAME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 16, rec.ID);
nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@hsdb (p_c);
END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsdb (p_c);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:07:37.77
That is a huge difference in execution time! And with DBMS_HS_PASSTHROUGH, the HSODBC process CPU usage is much less, making it more scalable running in parallel.
Now, how to run it in parallel?
Since the destination database is non-Oracle, the database built-in parallel execution cannot be used, so “DIY parallelism” is needed: split/partition the input data with some kind of criteria and start a new process for each partition.
In the real project I used each records CREATED_DATE value for partitioning, because underlying table was already partitioned by that column, but its also possible to use table ROWID values for partitioning. A good example of it is on book Expert Oracle database architecture By Thomas Kyte, chapter 14, Parallel Execution, Do-It-Yourself Parallelism.
Here I’m going to split the table just into 3 pieces to make it simple. To get the maximum IO performance from Oracle, Tom Kytes example needs to be followed. But I’ll make my example just simple.
Table hsperftest_job will keep the metadata for every parallel slave.
CREATE TABLE hsperftest_job
(
job_no NUMBER NOT NULL,
low_id ROWID NOT NULL,
hi_id ROWID NOT NULL,
max_in NUMBER NOT NULL,
start_date DATE,
end_date DATE,
rows_processed NUMBER
);
CREATE SEQUENCE seq_hsperftest_job;
DECLARE
p_min rowid;
p_max rowid;
p_row1 rowid;
p_row2 rowid;
p_count number;
BEGIN
SELECT MIN(rowid), MAX(rowid), COUNT(*) INTO p_min, p_max, p_count
FROM hsperftest;
SELECT MAX(a) INTO p_row1 FROM (
SELECT rowid a FROM hsperftest WHERE rownum < CEIL(p_count/3)
);
SELECT MAX(a) INTO p_row2 FROM (
SELECT rowid a, rownum rn FROM hsperftest WHERE rowid > p_row1
) WHERE rn < CEIL(p_count/3);
INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_min, p_row1, 0);
INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_row1, p_row2, 0);
INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_row2, p_max, 1);
COMMIT;
END;
/
SELECT j.job_no, COUNT (1)
FROM hsperftest_job j, hsperftest h
WHERE h.ROWID BETWEEN j.low_id AND j.hi_id
GROUP BY j.job_no;
JOB_NO COUNT(1)
---------- ----------
1 59313
2 59314
3 59317
Looks like my very simple ROWID hack is good enough for this test.
Now I'll create a procedure that will be executed in parallel.
CREATE OR REPLACE PROCEDURE hsperftest_proc(p_job_no hsperftest_job.job_no%TYPE) IS
p_min ROWID;
p_max ROWID;
p_max_in NUMBER;
p_c BINARY_INTEGER;
nr INTEGER;
p_count NUMBER:= 0;
BEGIN
-- Read the job metadata
SELECT low_id, hi_id, max_in INTO p_min, p_max, p_max_in
FROM hsperftest_job
WHERE job_no = p_job_no;
UPDATE hsperftest_job SET start_date = sysdate WHERE job_no = p_job_no;
COMMIT;
-- Do the work
p_c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsdb;
DBMS_HS_PASSTHROUGH.PARSE@hsdb (
p_c,
'insert into hsperftest (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,
TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,ID)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
);
FOR rec IN (SELECT * FROM HSPERFTEST WHERE rowid BETWEEN p_min AND p_max AND (p_max_in = 1 OR (p_max_in = 0 AND rowid < p_max)))
LOOP
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 1, rec.owner);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 2, rec.object_name);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 3, rec.SUBOBJECT_NAME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 4, rec.OBJECT_ID);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 5, rec.DATA_OBJECT_ID);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 6, rec.OBJECT_TYPE);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 7, rec.CREATED);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 8, rec.LAST_DDL_TIME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 9, rec.TIMESTAMP);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 10, rec.STATUS);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 11, rec.TEMPORARY);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 12, rec.GENERATED);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 13, rec.SECONDARY);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 14, rec.NAMESPACE);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 15, rec.EDITION_NAME);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 16, rec.ID);
nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@hsdb (p_c);
p_count:= p_count + 1;
END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsdb (p_c);
COMMIT;
-- Write the job end time
UPDATE hsperftest_job SET end_date = sysdate, rows_processed = p_count WHERE job_no = p_job_no;
COMMIT;
END;
/
And finally I'll execute the jobs by putting them on the job queue.
DECLARE
x PLS_INTEGER;
BEGIN
FOR rec IN (SELECT job_no FROM hsperftest_job)
LOOP
DBMS_JOB.submit (job => x, what => 'hsperftest_proc(' || rec.job_no || ');');
END LOOP;
COMMIT;
END;
/
SQL> SELECT job, total_time, broken, what FROM user_jobs;
JOB TOTAL_TIME B WHAT
---------- ---------- - -----------------
10967 95 N hsperftest_proc(1);
10968 95 N hsperftest_proc(2);
10969 95 N hsperftest_proc(3);
When the jobs are running, monitor the HSODBC processes CPU and memory usage. If there are too much parallel slaves running, then they may become a bottleneck.
If HSODBC memory usage becomes a problem, then partition the source data into smaller chunks.
When everything is done, look at the results.
SQL> SELECT SUM(rows_processed) total_rows, (MAX(end_date)-MIN(start_date))*24*60 runtime_min FROM hsperftest_job;
TOTAL_ROWS RUNTIME_MIN
---------- -----------
177942 3.58333333
3.58 min = 3 min 34.8 sec. Nice 🙂