There are various ways to look at performance. You can look at user experience, generate load similar to what application usage produces or you can do more basic things like query performance. What will you be looking at? Resource consumption and throughput are the usual suspects.
I'll look at a simple example in this blog post. I'll change database parameters and look at throughput of various actions which are regularly performed on databases. This takes away the complexity of distributed systems. I used a single Python script for this which can be downloaded here.
Test setup
System used
I did not test with parallel load or a true production like or application load. Just plain operations. I did this to determine effects on low level of certain changes. I conducted the test on RHEL 7.6 within a VirtualBox environment. The environment was assigned 16Gb of RAM and 6 (of 12 available) cores. I've used an 12R2 database for this test.
What does a database do?
Actually this question is difficult to answer when looking at an Oracle database. It can do a lot. It can send e-mails, provide webservices, store data, process data, do complex calculations (including machine learning). For this example I'm looking at some basic features and a slightly more advanced one (which is already deprecated in 12c);
- Provide a client with a connection
- Create and remove database objects (table, sequence, trigger)
- Select, insert, delete, commit data
- Host PL/SQL procedures using an HTTP interface using the PL/SQL gateway
Which settings would be interesting to change? There are some settings which often provide better performance but if set incorrectly, can cause detrimental effects. The below settings are also often considered 'scary' since they alter basic database behavior. These are (among others of course, relevant Oracle support notes are also mentioned):
- HugePages / Transparent HugePages
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1). HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
Enabling HugePages and disabling transparent HugePages is described here. - FILESYSTEMIO_OPTIONS
Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1). ORA-1578 ORA-353 ORA-19599 Corrupt blocks with zeros when filesystemio_options=SETALL on ext4 file system using Linux (Doc ID 1487957.1)
Setting the FILESYSTEMIO_OPTIONS parameter is described here. - PL/SQL native compilation
Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1). How To Convert the Entire DB From INTERPRETED Mode to NATIVE or Vice Versa for PL/SQL (Doc ID 1340441.1)
Some worries about these settings
HugePages
The main worry about using HugePages is that AMM (automatic memory management) cannot be used anymore. Thus you are required to manually determine, set and monitor the SGA and PGA size. This luckily is relatively easy and can be done using Advisors in the Enterprise Manager or by using queries described at: Tuning SGA_TARGET Using V$SGA_TARGET_ADVICE (Doc ID 1323708.1) and for the PGA here. Within the SGA, memory can still automatically be managed by using ASMM (see: ASMM versus AMM and LINUX x86-64 Hugepages Support (Doc ID 1134002.1)). Disabling transparent hugepages is recommended on Oracle support in an Alert even. Setting HugePages requires changing some things on your OS. This can be challenging if OS and DBA are managed by different people, departments or organizations even (think Cloud).
FILESYSTEMIO_OPTIONS
A lot has been written about the different settings for this option. Important things to know are that if you run on a very old OS with an outdated kernel, database block corruption can occur (see above mentioned Note). Also I've heard doubts about if asynchronous IO can be trusted just as much as synchronous IO since the database might expect data to be written to disk which actually isn't yet. These doubts are unfounded. Tom Kyte indicates in the following here that even with asynchronous IO, the database still waits for a callback from the filesystem. Only requests are provided more than 1 at a time to the filesystem. This is faster then using the overhead of multiple database writer processes to achieve the same.
PL/SQL native compilation
PL/SQL is stored in a compiled state in the database. Thus processing PL/SQL becomes faster since interpretation is not needed at the moment of execution. This causes PL/SQL to be executed faster. Drawback is that PL/SQL debugging does not work anymore.
How can you use Python to connect to the database?
Python, being a very popular (the most popular according to some reports) programming language around of course can easily do all kinds of interesting things with the Oracle database and has been able to do so for quite a while. You can use the cx_Oracle Python module (available through pip) to provide an easy way to do various things with the Oracle database. It does require an Oracle Client which needs to be available in the path. I recommend this one here. If you want to try it out yourself, you can get the free Oracle XE database. For Python IDE you can use whatever you like, for example PyCharm, Spyder or Jupyter (do mind that indentation has meaning in Python and IDE support for this helps).
Using cx_Oracle is relatively easy.
import cx_Oracle
db_hostname = 'localhost'
db_port = '1521'
db_sid = 'XE'
db_username = 'TESTUSER'
db_password = 'TESTUSER'
dbstring = db_username + '/' + db_password + '@' + db_hostname + ':' + db_port + '/' + db_sid
con = cx_Oracle.connect(dbstring)
cur = con.cursor()
cur.execute('select to_char(systimestamp) from dual')
res=cur.fetchone()
con.close()
This creates a connection, performs a selection and returns the result to the Python procedure.
An alternative is to use the service instead of the SID.
dbstring = cx_Oracle.makedsn(db_hostname, db_port, service_name=db_sid)
con = cx_Oracle.connect(user=db_username, password=db_password, dsn=dbstring)
How can you use Python to create and call procedures
This is also relatively easy. See for example:
def runtest_prep(cur):
statement = """
BEGIN
execute immediate('CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE');
EXECUTE IMMEDIATE ('CREATE TABLE test_tab(
test_id NUMBER,
kolom VARCHAR2(50) NOT NULL,
PRIMARY KEY(test_id))');
execute immediate('CREATE TRIGGER test_trg
BEFORE INSERT OR UPDATE ON test_tab
FOR EACH ROW
BEGIN
:NEW.test_id := test_seq.NextVal;
END;');
END;"""
cur.execute(statement)
Here I create a sequence, table, trigger with as input a cursor.
For returning values, for functions and procedures which have IN OUT or OUT parameters, you have to declare a variable of the correct type first. See for example:
def getdadporturl(cur):
statement="""
create or replace procedure tmp_proc(p_path out varchar2,p_port out varchar2) as
l_paths DBMS_EPG.varchar2_table;
l_dadname user_epg_dad_authorization.dad_name%TYPE;
BEGIN
select dad_name into l_dadname from user_epg_dad_authorization where rownum=1;
SELECT to_char(DBMS_XDB.GETHTTPPORT) into p_port FROM DUAL;
DBMS_EPG.get_all_dad_mappings (
dad_name => l_dadname,
paths => l_paths);
FOR i IN 1 .. l_paths.count LOOP
p_path := replace(l_paths(i),'/*','');
END LOOP;
END;
"""
cur.execute(statement)
l_port = cur.var(cx_Oracle.STRING)
l_path = cur.var(cx_Oracle.STRING)
cur.callproc('tmp_proc', [l_path, l_port])
return l_port.getvalue(), l_path.getvalue()
I chose STRING because it is easy to use.
Create and test the PL/SQL gateway
Accessing your environment
Make sure your database listener is listening on the specific interface you are connecting to. For example:
Also make sure your firewall is not blocking your ports. Use below for development only. In a more managed environment, you'll want to specifically open a single port.
sudo systemctl stop firewalld
sudo systemctl disable firewalld
Else you might get errors like:
Or something similar from a Python script.
In order to easily start the database when your server starts (container + pluggable) you can look here.
Configuring the embedded PL/SQL gateway
The following here describes how you can enable the Embedded PL/SQL Gateway. This can be done with a few commands. Do mind that in 12c you have pluggable databases.
(as system user, my database was called ORCLPDB)
ALTER SESSION SET CONTAINER = ORCLPDB
ALTER PLUGGABLE DATABASE OPEN READ WRITE
Check if the HTTP port for XDB is already set:
SELECT DBMS_XDB.gethttpport FROM dual;
If it is not enabled, enable it:
EXEC DBMS_XDB.sethttpport(8080);
This will register itself with a listener and make configured Database Access Descriptors (DADs) available on that port. Below the DAD,
In order to create a DAD the following can be used:
BEGIN
DBMS_EPG.create_dad (
dad_name => 'my_epg_dad',
path => '/my_epg_dad/*');
END;
Next create a user
CREATE USER testuser IDENTIFIED BY Welcome01;
GRANT CONNECT,RESOURCE,DBA TO testuser;
Authorize your user to use the DAD
BEGIN
DBMS_EPG.authorize_dad (
dad_name => 'my_epg_dad',
user => 'TESTUSER');
END;
Make debugging easier
exec dbms_epg.set_dad_attribute('my_epg_dad', 'error-style', 'DebugStyle');
Set the username
BEGIN
DBMS_EPG.set_dad_attribute (
dad_name => 'my_epg_dad',
attr_name => 'database-username',
attr_value => 'TESTUSER');
Python, being a very popular (the most popular according to some reports) programming language around of course can easily do all kinds of interesting things with the Oracle database and has been able to do so for quite a while. You can use the cx_Oracle Python module (available through pip) to provide an easy way to do various things with the Oracle database. It does require an Oracle Client which needs to be available in the path. I recommend this one here. If you want to try it out yourself, you can get the free Oracle XE database. For Python IDE you can use whatever you like, for example PyCharm, Spyder or Jupyter (do mind that indentation has meaning in Python and IDE support for this helps).
Using cx_Oracle is relatively easy.
import cx_Oracle
db_hostname = 'localhost'
db_port = '1521'
db_sid = 'XE'
db_username = 'TESTUSER'
db_password = 'TESTUSER'
dbstring = db_username + '/' + db_password + '@' + db_hostname + ':' + db_port + '/' + db_sid
con = cx_Oracle.connect(dbstring)
cur = con.cursor()
cur.execute('select to_char(systimestamp) from dual')
res=cur.fetchone()
con.close()
This creates a connection, performs a selection and returns the result to the Python procedure.
An alternative is to use the service instead of the SID.
dbstring = cx_Oracle.makedsn(db_hostname, db_port, service_name=db_sid)
con = cx_Oracle.connect(user=db_username, password=db_password, dsn=dbstring)
How can you use Python to create and call procedures
This is also relatively easy. See for example:
def runtest_prep(cur):
statement = """
BEGIN
execute immediate('CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE');
EXECUTE IMMEDIATE ('CREATE TABLE test_tab(
test_id NUMBER,
kolom VARCHAR2(50) NOT NULL,
PRIMARY KEY(test_id))');
execute immediate('CREATE TRIGGER test_trg
BEFORE INSERT OR UPDATE ON test_tab
FOR EACH ROW
BEGIN
:NEW.test_id := test_seq.NextVal;
END;');
END;"""
cur.execute(statement)
Here I create a sequence, table, trigger with as input a cursor.
For returning values, for functions and procedures which have IN OUT or OUT parameters, you have to declare a variable of the correct type first. See for example:
def getdadporturl(cur):
statement="""
create or replace procedure tmp_proc(p_path out varchar2,p_port out varchar2) as
l_paths DBMS_EPG.varchar2_table;
l_dadname user_epg_dad_authorization.dad_name%TYPE;
BEGIN
select dad_name into l_dadname from user_epg_dad_authorization where rownum=1;
SELECT to_char(DBMS_XDB.GETHTTPPORT) into p_port FROM DUAL;
DBMS_EPG.get_all_dad_mappings (
dad_name => l_dadname,
paths => l_paths);
FOR i IN 1 .. l_paths.count LOOP
p_path := replace(l_paths(i),'/*','');
END LOOP;
END;
"""
cur.execute(statement)
l_port = cur.var(cx_Oracle.STRING)
l_path = cur.var(cx_Oracle.STRING)
cur.callproc('tmp_proc', [l_path, l_port])
return l_port.getvalue(), l_path.getvalue()
I chose STRING because it is easy to use.
Create and test the PL/SQL gateway
Accessing your environment
Make sure your database listener is listening on the specific interface you are connecting to. For example:
Also make sure your firewall is not blocking your ports. Use below for development only. In a more managed environment, you'll want to specifically open a single port.
sudo systemctl stop firewalld
sudo systemctl disable firewalld
Else you might get errors like:
Or something similar from a Python script.
In order to easily start the database when your server starts (container + pluggable) you can look here.
The following here describes how you can enable the Embedded PL/SQL Gateway. This can be done with a few commands. Do mind that in 12c you have pluggable databases.
(as system user, my database was called ORCLPDB)
ALTER SESSION SET CONTAINER = ORCLPDB
ALTER PLUGGABLE DATABASE OPEN READ WRITE
SELECT DBMS_XDB.gethttpport FROM dual;
If it is not enabled, enable it:
EXEC DBMS_XDB.sethttpport(8080);
This will register itself with a listener and make configured Database Access Descriptors (DADs) available on that port. Below the DAD,
In order to create a DAD the following can be used:
BEGIN
DBMS_EPG.create_dad (
dad_name => 'my_epg_dad',
path => '/my_epg_dad/*');
END;
Next create a user
CREATE USER testuser IDENTIFIED BY Welcome01;
GRANT CONNECT,RESOURCE,DBA TO testuser;
Authorize your user to use the DAD
BEGIN
DBMS_EPG.authorize_dad (
dad_name => 'my_epg_dad',
user => 'TESTUSER');
END;
Make debugging easier
exec dbms_epg.set_dad_attribute('my_epg_dad', 'error-style', 'DebugStyle');
Set the username
BEGIN
DBMS_EPG.set_dad_attribute (
dad_name => 'my_epg_dad',
attr_name => 'database-username',
attr_value => 'TESTUSER');
END;
Next enable the user anonymous to allow unauthenticated access to the service. Yes, you are right, we are unlocking user ANONYMOUS on the container database instead of the pluggable database. The status of the ANONYMOUS user in the pluggable database is irrelevant for the gateway. See here.
ALTER SESSION SET CONTAINER=CDB$ROOT
ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER anonymous IDENTIFIED BY Welcome01 CONTAINER=ALL
Next add allow-repository-anonymous-access to the XDB configuration.
SET SERVEROUTPUT ON
DECLARE
l_configxml XMLTYPE;
l_value VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
l_configxml := DBMS_XDB.cfg_get();
IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
-- Add missing element.
SELECT insertChildXML
(
l_configxml,
'/xdbconfig/sysconfig/protocolconfig/httpconfig',
'allow-repository-anonymous-access',
XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
l_value ||
'</allow-repository-anonymous-access>'),
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_configxml
FROM dual;
DBMS_OUTPUT.put_line('Element inserted.');
ELSE
-- Update existing element.
SELECT updateXML
(
DBMS_XDB.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
l_value,
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_configxml
FROM dual;
DBMS_OUTPUT.put_line('Element updated.');
END IF;
DBMS_XDB.cfg_update(l_configxml);
DBMS_XDB.cfg_refresh;
END;
/
Now you can access whatever TESTUSER is allowed access to by going to http://db_host:8080/my_epg_dad/username.procedure?parameter=value
Should you have issues, the following script helps: rdbms/admin/epgstat.sql. Also the following displays the XDB configuration.
DECLARE
l_configxml XMLTYPE;
l_value VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
l_configxml := DBMS_XDB.cfg_get();
dbms_output.put_line(l_configxml.getCLOBVal());
END;
TESTUSER is allowed to create procedures in this example so first I create a procedure to determine the configured DAD and endpoint. Next I create a procedure which can be accessed by an unauthenticated user anonymous (grant execute on ... to public), call this procedure a number of times and remove it again.
Also note that the SHARED_SERVERS and DISPATCHERS database parameters are important for the performance of this gateway. The performance can easily be improved by increasing SHARED_SERVERS to 10 and DISPATCHERS to (pro=tcp)(dis=5).
Performance results
Before any changes
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4525 ms
function [runtestcon] finished in 4712 ms
function [runtestcon] finished in 4584 ms
Average 4607 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12362 ms
function [runtestindb] finished in 12581 ms
function [runtestindb] finished in 12512 ms
Average 12485 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3204 ms
function [runtestcreateremoveobjects] finished in 3145 ms
function [runtestcreateremoveobjects] finished in 3189 ms
Average 3180 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3318 ms
function [runtestinsdel] finished in 3232 ms
function [runtestinsdel] finished in 3303 ms
Average 3285 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 6258 ms
function [runtestinsdelcommit] finished in 4592 ms
function [runtestinsdelcommit] finished in 4468 ms
Average 5106 ms
Testing DAD EPG
function [urltest] finished in 51584 ms
function [urltest] finished in 51707 ms
function [urltest] finished in 51711 ms
Average 51667 ms
Transparent HugePages
For disabling transparent hugepages, I've followed the instructions here.
There did not seem to be any clear change in performance. Perhaps a slight improvement in some areas.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4459 ms
function [runtestcon] finished in 4472 ms
function [runtestcon] finished in 4115 ms
Average 4349 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12396 ms
function [runtestindb] finished in 12575 ms
function [runtestindb] finished in 9895 ms
Average 11622 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3158 ms
function [runtestcreateremoveobjects] finished in 3713 ms
function [runtestcreateremoveobjects] finished in 3215 ms
Average 3362 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3210 ms
function [runtestinsdel] finished in 3295 ms
function [runtestinsdel] finished in 3332 ms
Average 3279 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 4427 ms
function [runtestinsdelcommit] finished in 5208 ms
function [runtestinsdelcommit] finished in 4955 ms
Average 4863 ms
Testing DAD EPG
function [urltest] finished in 51649 ms
function [urltest] finished in 51731 ms
function [urltest] finished in 51720 ms
Average 51700 ms
HugePages
Also note that the SHARED_SERVERS and DISPATCHERS database parameters are important for the performance of this gateway. The performance can easily be improved by increasing SHARED_SERVERS to 10 and DISPATCHERS to (pro=tcp)(dis=5).
Performance results
Before any changes
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4525 ms
function [runtestcon] finished in 4712 ms
function [runtestcon] finished in 4584 ms
Average 4607 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12362 ms
function [runtestindb] finished in 12581 ms
function [runtestindb] finished in 12512 ms
Average 12485 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3204 ms
function [runtestcreateremoveobjects] finished in 3145 ms
function [runtestcreateremoveobjects] finished in 3189 ms
Average 3180 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3318 ms
function [runtestinsdel] finished in 3232 ms
function [runtestinsdel] finished in 3303 ms
Average 3285 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 6258 ms
function [runtestinsdelcommit] finished in 4592 ms
function [runtestinsdelcommit] finished in 4468 ms
Average 5106 ms
Testing DAD EPG
function [urltest] finished in 51584 ms
function [urltest] finished in 51707 ms
function [urltest] finished in 51711 ms
Average 51667 ms
Transparent HugePages
For disabling transparent hugepages, I've followed the instructions here.
There did not seem to be any clear change in performance. Perhaps a slight improvement in some areas.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4459 ms
function [runtestcon] finished in 4472 ms
function [runtestcon] finished in 4115 ms
Average 4349 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12396 ms
function [runtestindb] finished in 12575 ms
function [runtestindb] finished in 9895 ms
Average 11622 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3158 ms
function [runtestcreateremoveobjects] finished in 3713 ms
function [runtestcreateremoveobjects] finished in 3215 ms
Average 3362 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3210 ms
function [runtestinsdel] finished in 3295 ms
function [runtestinsdel] finished in 3332 ms
Average 3279 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 4427 ms
function [runtestinsdelcommit] finished in 5208 ms
function [runtestinsdelcommit] finished in 4955 ms
Average 4863 ms
Testing DAD EPG
function [urltest] finished in 51649 ms
function [urltest] finished in 51731 ms
function [urltest] finished in 51720 ms
Average 51700 ms
HugePages
For me the recommended setting from the script at Doc ID 401749.1 was: vm.nr_hugepages = 1187
I've updated Doc ID 401749.1 with vm.nr_hugepages = 1189
Added two lines to /etc/security/limits.conf (1187*2048)
* soft memlock 2435072
* hard memlock 2435072
After a reboot I made sure HugePages were enabled:
[oracle@rhel ~]$ cat /proc/meminfo | grep Huge
AnonHugePages: 0 kB
HugePages_Total: 1189
HugePages_Free: 12
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
To be sure the database was only using the HugePages, I did:
ALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;
If the HugePages cannot be allocated, the database will not start with this setting.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4880 ms
function [runtestcon] finished in 5307 ms
function [runtestcon] finished in 4551 ms
Average 4912 ms
Testing create cursor, query, return result
function [runtestindb] finished in 11790 ms
function [runtestindb] finished in 13198 ms
function [runtestindb] finished in 11889 ms
Average 12292 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3099 ms
function [runtestcreateremoveobjects] finished in 3089 ms
function [runtestcreateremoveobjects] finished in 3101 ms
Average 3096 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3087 ms
function [runtestinsdel] finished in 3123 ms
function [runtestinsdel] finished in 3293 ms
Average 3168 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 5324 ms
function [runtestinsdelcommit] finished in 6059 ms
function [runtestinsdelcommit] finished in 4784 ms
Average 5389 ms
FILESYSTEMIO_OPTIONS
The default setting for this parameter was NONE on my installation. I changed it to SETALL.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4323 ms
function [runtestcon] finished in 4240 ms
function [runtestcon] finished in 4192 ms
Average 4252 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12303 ms
function [runtestindb] finished in 10631 ms
function [runtestindb] finished in 9692 ms
Average 10875 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3096 ms
function [runtestcreateremoveobjects] finished in 3165 ms
function [runtestcreateremoveobjects] finished in 3183 ms
Average 3148 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3049 ms
function [runtestinsdel] finished in 3059 ms
function [runtestinsdel] finished in 3064 ms
Average 3057 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 4266 ms
function [runtestinsdelcommit] finished in 4339 ms
function [runtestinsdelcommit] finished in 4346 ms
Average 4317 ms
Testing DAD EPG
function [urltest] finished in 51887 ms
function [urltest] finished in 52329 ms
function [urltest] finished in 51834 ms
Average 52017 ms
This setting caused improvement in almost all areas! Except the DAD which remained the same.
PL/SQL native compilation
I've updated Doc ID 401749.1 with vm.nr_hugepages = 1189
Added two lines to /etc/security/limits.conf (1187*2048)
* soft memlock 2435072
* hard memlock 2435072
[oracle@rhel ~]$ cat /proc/meminfo | grep Huge
AnonHugePages: 0 kB
HugePages_Total: 1189
HugePages_Free: 12
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
ALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;
If the HugePages cannot be allocated, the database will not start with this setting.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4880 ms
function [runtestcon] finished in 5307 ms
function [runtestcon] finished in 4551 ms
Average 4912 ms
Testing create cursor, query, return result
function [runtestindb] finished in 11790 ms
function [runtestindb] finished in 13198 ms
function [runtestindb] finished in 11889 ms
Average 12292 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3099 ms
function [runtestcreateremoveobjects] finished in 3089 ms
function [runtestcreateremoveobjects] finished in 3101 ms
Average 3096 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3087 ms
function [runtestinsdel] finished in 3123 ms
function [runtestinsdel] finished in 3293 ms
Average 3168 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 5324 ms
function [runtestinsdelcommit] finished in 6059 ms
function [runtestinsdelcommit] finished in 4784 ms
Average 5389 ms
Testing DAD EPG
function [urltest] finished in 51735 ms
function [urltest] finished in 51690 ms
function [urltest] finished in 51791 ms
Average 51739 ms
The default setting for this parameter was NONE on my installation. I changed it to SETALL.
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4323 ms
function [runtestcon] finished in 4240 ms
function [runtestcon] finished in 4192 ms
Average 4252 ms
Testing create cursor, query, return result
function [runtestindb] finished in 12303 ms
function [runtestindb] finished in 10631 ms
function [runtestindb] finished in 9692 ms
Average 10875 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3096 ms
function [runtestcreateremoveobjects] finished in 3165 ms
function [runtestcreateremoveobjects] finished in 3183 ms
Average 3148 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3049 ms
function [runtestinsdel] finished in 3059 ms
function [runtestinsdel] finished in 3064 ms
Average 3057 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 4266 ms
function [runtestinsdelcommit] finished in 4339 ms
function [runtestinsdelcommit] finished in 4346 ms
Average 4317 ms
Testing DAD EPG
function [urltest] finished in 51887 ms
function [urltest] finished in 52329 ms
function [urltest] finished in 51834 ms
Average 52017 ms
This setting caused improvement in almost all areas! Except the DAD which remained the same.
PL/SQL native compilation
I did the following:
ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE SCOPE=both;
Startup the database in upgrade mode (STARTUP UPGRADE)
Run the following
@$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql TRUE
Followed by
@$ORACLE_HOME/rdbms/admin/utlrp.sql
alter session set container = orclpdb
Run the following
@$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql TRUE
Followed by
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Checking can be done with:
select name, type, plsql_code_type from user_plsql_object_settings
Testing create connection, create cursor, query, close connection
function [runtestcon] finished in 4511 ms
function [runtestcon] finished in 4417 ms
function [runtestcon] finished in 4308 ms
Average 4413 ms
Testing create cursor, query, return result
function [runtestindb] finished in 11973 ms
function [runtestindb] finished in 12444 ms
function [runtestindb] finished in 12446 ms
Average 12287 ms
Testing creating and removing objects
function [runtestcreateremoveobjects] finished in 3285 ms
function [runtestcreateremoveobjects] finished in 3381 ms
function [runtestcreateremoveobjects] finished in 3296 ms
Average 3320 ms
Inserting data, commit, deleting data, commit
function [runtestinsdel] finished in 3126 ms
function [runtestinsdel] finished in 3129 ms
function [runtestinsdel] finished in 3141 ms
Average 3132 ms
Inserting single row, commit, delete single row, commit
function [runtestinsdelcommit] finished in 4248 ms
function [runtestinsdelcommit] finished in 4291 ms
function [runtestinsdelcommit] finished in 4328 ms
Average 4289 ms
Testing DAD EPG
function [urltest] finished in 51650 ms
function [urltest] finished in 51693 ms
function [urltest] finished in 51671 ms
Average 51671 ms
Conclusions
Some of the differences were marginal but the setting FILESYSTEMIO_OPTIONS caused improvement in almost all areas. The settings seemed to have little effect on the responsetimes from the DAD. Probably there most of the time the actions performed were not limited by PL/SQL processing time, disk IO or memory. Combining the settings does not give a much better performance. Query performance and actions involving a lot of data improved with all (and any one of) these settings.
Of course you can do many other load tests, also parallel and do other things to the database like space management, statistics, indexes, SQL tuning, etc. Most of these require effort. These settings are also sometimes not straightforward. Native compilation could require a long period of downtime. HugePages require settings on Linux level. FILESYSTEMIO_OPTIONS only requires a restart.
Generally speaking, from the suggested settings, I would go just for FILESYSTEMIO_OPTIONS = SETALL for most systems if performance becomes an issue. Default for my system the setting was set to NONE. The setting is easy to do and will improve most of the database actions in probably most environments.
Python is a great language to implement performancetests in against an Oracle database. It does require some manual scripting. This is just a quick example. The database challenges (specifically with the PL/SQL gateway and the CDB/PDB structure of the multitenant database) were far greater than the effort required to write the test.
No comments:
Post a Comment