Oracle … as usual

Oracle by Laurent Leturgez

Active Average Session trending in Statspack

Active Average Session (AAS) is one of the first statistic you have to look when you analyze a performance issue.

In Oracle Standard Edition (or Enterprise Edition without Diagnostic pack), you cannot query V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views, nor execute ashrpt script.

If you have installed statspack and if you take snapshots, you can find Active Average Session between 2 snapshots.

AAS is calculated with the formula : DB Time / elapsed time. So, if you have recorded a db time of 300 seconds in 900 seconds (elapsed time between two snapshots), AAS is 0,33.

So it’s easy to calculate AAS between two snapshot, but the most important thing is to have AAS trending during a period.

To do that, I wrote a small PL/SQL function based on SYS_TIME_MODEL_DIF function available in STATSPACK package definition (in spcpkg.sql script).

create or replace function SYS_TIME_MODEL_DIF(i_name varchar2, bid number, eid number, db_ident number, inst_num number) RETURN number is

/* Returns the difference between statistics for the Time Model statistic
   name specified for the interval between the begin and end
   snapshot id's (bid, eid)

   In the case the data being queried is from a prior release
   which did not have the statistic requested, this function
   returns 0.
*/

beg_val_missing   boolean := false;
end_val_missing   boolean := false;
bval           number;
eval           number;

cursor SY (i_snap_id number) is
select value
  from stats$sys_time_model      stm
     , stats$time_model_statname tms
where stm.snap_id         = i_snap_id
   and stm.dbid            = db_ident
   and stm.instance_number = inst_num
   and stm.stat_id         = tms.stat_id
   and tms.stat_name       = i_name;

begin
   /* Get start value */
   open SY (bid); fetch SY into bval;
   if SY%notfound then
      beg_val_missing := true;
   end if; close SY;

   /* Get end value */
   open SY (eid); fetch SY into eval;
   if SY%notfound then
      end_val_missing := true;
   end if; close SY;

   if     beg_val_missing = true
      and end_val_missing = true      then

         /* this is likely a newer statitic which did not exist for this database version    */
        return 0;

   elsif     beg_val_missing = true
         and end_val_missing = false  then
         return -1;
   elsif     beg_val_missing = false
         and end_val_missing = true   then
         return -1;
   else

        /* Return difference */
        return eval - bval;

   end if;

end SYS_TIME_MODEL_DIF;
/

When this function has been created, you can call it to get Db time (or any value in the time model) between two snapshots (identified by their snapshot_id : bid (begin id) and eid (end id)) , for example :

SQL> select SYS_TIME_MODEL_DIF('DB time',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBTIME',791,792,1386309985,1)
-------------------------------------------------
 10245630292

SQL> select SYS_TIME_MODEL_DIF('DB CPU',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBCPU',791,792,1386309985,1)
------------------------------------------------
 3698356113

SQL> select SYS_TIME_MODEL_DIF('parse time elapsed',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('PARSETIMEELAPSED',791,792,1386309985,1)
-----------------------------------------------------------
 211981805

Note that time is given in microseconds.

Next step, I wrote an anonymous PL/SQL script that executes this function accross all the snapshots taken between two snap_id and prints DB Time, DB CPU, and AAS in CSV format (you can adapt this script to adapt your output format):

declare
 -- Constantes
 usec_to_sec number := 1000000;
 day_to_sec number := 86400;
 db_id number := &dbid
 --
 dbtim number;
 dbcpu number;
 cursor SN_tim(sn_id number) is select snap_time from stats$snapshot where snap_id=sn_id;
 cursor snaps(sn1 number, sn2 number) is select snap_id from stats$snapshot where snap_id between sn1 and sn2 order by snap_id;
 b_time date;
 e_time date;
 bid number;
 eid number;
 cur_nf number;
begin
dbms_output.put_line('BEGIN_TIME;END_TIME;DBTIME;DBCPU;AAS');
open snaps(&start,&end);
loop
fetch snaps into bid;
exit when snaps%notfound;
 cur_nf := 0;
 fetch snaps into eid;

 open SN_tim(bid); fetch SN_tim into b_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 open SN_tim(eid); fetch SN_tim into e_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 dbtim := SYS_TIME_MODEL_DIF('DB time',bid,eid,db_id,1);
 dbcpu := SYS_TIME_MODEL_DIF('DB CPU',bid,eid,db_id,1);
 if ((dbtim>0 or dbcpu>0) and cur_nf = 0) then
 dbms_output.put_line(b_time||';'||e_time||';'||round(dbtim/usec_to_sec,2) ||';'||round(dbcpu/usec_to_sec,2) ||';'|| round((dbtim/usec_to_sec) / ((e_time-b_time)*day_to_sec),2) );
 else
 dbms_output.put_line(b_time||';'||e_time||';N/A;N/A' );
 end if;
end loop;
end;

Then you can graph it in Excel or Tableau (for example) and you will get the AAS trend:

aas

Keep in touch with My Oracle Support updates

This post is related to My Oracle Support (MOS) and one of the features every DBA has to configure is the Hot Topic email.

This feature will send you an email every day ou week (depending on how you configure it) with all the new stuff published or updated on MOS (Knowledge base article, bugs, alerts …).

Of course, you can filter on the products you want to subscribe and select the number of items you want to receive by category.

To configure this fonction, log in to MOS with your account and click on the “settings” tab, ,the select the “Hot Topic Email” link on the left … see in the screenshot below (green box):

hot_topics

Then, you will see the “hot topics” part and here you can configure it:

  • On the first part (1), you can configure the frequency of the mail and its format. You can also configure the content you want to include in this mail: favorites and Service requests
  • Next, on the second part (2), which is for me the most important part, you will select the products you want to subscribe and what kind of information you want to receive (knowledge articles, alerts, bugs etc.)
  • Finally, on the third part (3), you will choose the number of items the mail will includes. In the previous screenshot, I will receive 25 bugs (for all the products I selected), 25 knowledge articles etc. (Usually the bug and knowledge articles are the most important parts… yes, there are a lot of bugs updated every day ;) )

As a result, I receive this kind of email every day:

Alerts

Alert Product Area Last Updated
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00

 

Bugs

Bug Product Area Bug ID Last Updated
ORACLE PROCESS RUNNING OUT OF OS KERNEL I/O RESOURCES EVEN AFTER APPLY 6343215 Oracle Database – Enterprise Edition 7523755 Sat, 27 Jun 2015 13:10 GMT+01:00
MESSAGES IN AQ$_ALERT_QT_E ARE NOT BEING PURGED IN A RAC CLUSTER Oracle Database – Enterprise Edition 19904389 Sat, 27 Jun 2015 13:10 GMT+01:00
ORA-01792 OCCURED WHEN USING RIGHT OUTER JOIN Oracle Database – Enterprise Edition 9256994 Sat, 27 Jun 2015 12:55 GMT+01:00
ORA-600 [KLAPRS_11]/[KLAPRS_12] BEING HIT DURING IMPDP Oracle Database – Enterprise Edition 6944948 Sat, 27 Jun 2015 12:54 GMT+01:00
ORA-00600 [KDTIGETROW-2] AFTER UPGRADE TO 11.2.0.3 BUNDLE 22 Oracle Database – Enterprise Edition 17343797 Sat, 27 Jun 2015 12:52 GMT+01:00
MERGE SQL WITH BOTH AN INSERT AND UPDATE AND LOGGING ENABLED CAN CAUSE INT. ERR Oracle Database – Enterprise Edition 17397545 Sat, 27 Jun 2015 12:52 GMT+01:00
WRONG PRECISION RETURNED FOR QUERY AFTER DB UPGRADE TO 12.1.0.2 Oracle Database – Enterprise Edition 21241579 Sat, 27 Jun 2015 12:49 GMT+01:00
NUMA MESSAGES IN THE ALERT LOG AFTER 11.2.0.4 UPGRADE Oracle Database – Enterprise Edition 17475024 Sat, 27 Jun 2015 12:49 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] USING SQL TUNING ADVISOR Oracle Database – Enterprise Edition 17401718 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] WHEN AUTOMATIC SQL TUNING ADVISOR EXECUTED Oracle Database – Enterprise Edition 16491690 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-00600 [QKSVCREPLACEVC0] Oracle Database – Enterprise Edition 13959984 Sat, 27 Jun 2015 12:45 GMT+01:00
INSTANCE CRASHES WITH ORA-600 [KJBRWRDONE:SC2] ON LMS Oracle Database – Enterprise Edition 17027916 Sat, 27 Jun 2015 12:35 GMT+01:00
WITH 11.2.0.4, THE CAPTURE MECHANISM STARTS UP TOO MANY PARALLEL QUERY SERVERS Oracle Database – Enterprise Edition 19587324 Sat, 27 Jun 2015 12:28 GMT+01:00
WAIT DEPENDENCY ON APPLY INSERT AFTER UPGRADE TO 11.2.0.4 Oracle Database – Enterprise Edition 19442102 Sat, 27 Jun 2015 12:28 GMT+01:00
FAILED TO RAISE ORA-1 FOR PK UPDATE WHEN CONSTRAINT=IMMEDIATE Oracle Database – Enterprise Edition 19440386 Sat, 27 Jun 2015 12:28 GMT+01:00
INTEGRATED REPLICAT INVALIDATES DEPENDENT PACKAGES RESULTING IN AN ORA-4068 Oracle Database – Enterprise Edition 19277336 Sat, 27 Jun 2015 12:28 GMT+01:00
PGA MEMORY LEAK ON APPLY SERVER ON KOH-KGHU CALL – KNGLXRCOL Oracle Database – Enterprise Edition 18973548 Sat, 27 Jun 2015 12:28 GMT+01:00
LOGMINER DDL TRACKING NOT SCALABLE, SESSIONS BLOCK EACHOTHER DURING DDL APPLY Oracle Database – Enterprise Edition 16674686 Sat, 27 Jun 2015 12:28 GMT+01:00
MALFORMED KTU-KRVMISC TRANSACTION FINALIZATION MARKER Oracle Database – Enterprise Edition 14705949 Sat, 27 Jun 2015 12:28 GMT+01:00
ORA 7445 [__INTEL_SSSE3_REP_MEMCPY] WITH CLUSTER TABLE CREATION Oracle Database – Enterprise Edition 21041573 Sat, 27 Jun 2015 12:27 GMT+01:00
ORA-600 [17285] RUNNING DIFFERENT APPLICATION PACKAGES Oracle Database – Enterprise Edition 19475971 Sat, 27 Jun 2015 12:09 GMT+01:00
WRONG RESULTS USING FUNCTION BASED INDEX WITH LOWER FUNCTION Oracle Database – Enterprise Edition 18550648 Sat, 27 Jun 2015 12:06 GMT+01:00
PGA MEMORY SPIKE WHEN CONSISTENTLY RUNNING A JAVA STORED PROCEDURE Oracle Database – Enterprise Edition 20806625 Sat, 27 Jun 2015 12:06 GMT+01:00
LOADJAVA FAILS WITH ORA-12154 WHEN USING NAME-VALUE PAIR IN CONNECTION STRING. Oracle Database – Enterprise Edition 21072270 Sat, 27 Jun 2015 12:05 GMT+01:00
ORA-600 [KSSADD: NULL PARENT] DURING HIGH DATABASE ACTIVITY Oracle Database – Enterprise Edition 16590736 Sat, 27 Jun 2015 11:50 GMT+01:00

 

Knowledge Articles

Knowledge Article Product Area Last Updated
Does Weblogic Server support SAML2.0 Single Logout Protocol? Oracle WebLogic Server Sat, 27 Jun 2015 02:52 GMT+01:00
How to use the DBMS_SQLPA (SQL Performance Analyzer) API to test database upgrade using CONVERT SQLSET Oracle Database – Enterprise Edition Sat, 27 Jun 2015 02:37 GMT+01:00
12c ACFS Configuration on ASM Flex Architecture (White Paper) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:37 GMT+01:00
Best Practices and Recommendations for RAC databases using very large SGA (e.g. 100 GB) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:24 GMT+01:00
Receive “There are Issues with the Configuration of The Static Files…” After Upgrading to APEX 5.0 Oracle Application Express (formerly HTML DB) Oracle REST Data Services Oracle HTTP Server Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:19 GMT+01:00
Cluster Health Monitor (CHM) FAQ Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:16 GMT+01:00
ODA: “oakcli show repo” Does Not Show Any Repository After Restart Oracle Database Appliance Software Oracle Database Appliance X3-2 Fri, 26 Jun 2015 22:34 GMT+01:00
NTP leap second event causing Oracle Clusterware node reboot Oracle Database – Enterprise Edition Fri, 26 Jun 2015 22:29 GMT+01:00
ODA (Oracle Database Appliance): Leap Second adjustment impact Oracle Database Appliance Oracle Database Appliance Software Fri, 26 Jun 2015 22:01 GMT+01:00
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00
Exadata 12.1.2.1.2 release and patch (20748218) Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:40 GMT+01:00
11gR2 Grid Infrastructure Does not Use ulimit Setting Appropriately Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:33 GMT+01:00
Exadata Database Machine and Exadata Storage Server Supported Versions Oracle Exadata Hardware Exadata Database Machine X2-2 Hardware Oracle Platinum Services Oracle Exadata Storage Server Software Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:32 GMT+01:00
Disable SSLv3 And Enable TLSv2 / TLSv1.1/ TLSv1.2 Oracle WebLogic Server Fri, 26 Jun 2015 18:30 GMT+01:00
ORA-7445 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 17:13 GMT+01:00
ORA-600 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:58 GMT+01:00
hp-ux: Database Instance Startup is Slow in 11gR2 as DBW0 Has High Wait “Disk file operations I/O” Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:49 GMT+01:00
ODA (Oracle Database Appliance): Unable to Delete VM Oracle Database Appliance Software Fri, 26 Jun 2015 16:49 GMT+01:00
WLS Admin Server Deadlock Detected Waiting To Acquire Lock Java.util.concurrent.CopyOnWriteArrayList Oracle WebLogic Server Fri, 26 Jun 2015 16:43 GMT+01:00
[WLS-10.3.6] -JAXB Marshaller Returns The Class Name Instead Of The Objects In The List Oracle WebLogic Server Fri, 26 Jun 2015 16:14 GMT+01:00
Oracle Advance Security Licence Components Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:11 GMT+01:00
Steps to Implement Address Windowing Extensions (AWE) / VLM on 32-bit Windows Platforms Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:50 GMT+01:00
DBMS_AUDIT_MGMT does not release the space occupied by LOB segment Oracle Database – Standard Edition Fri, 26 Jun 2015 15:47 GMT+01:00
WLS 10.3.6.0 Wtc-l10n.jar Missing Oracle WebLogic Server Fri, 26 Jun 2015 15:26 GMT+01:00
Egypt cancels DST in 2015 – Impact on Oracle RDBMS Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:21 GMT+01:00

 

 

Oracle 12c Application Continuity and its resources usage

During a recent Oracle Meetup in Paris, I met Martin Bach and Bertrand Drouvot. Martin was here to present on Application Continuity, and we had an interesting discussion about this technology. (Bertrand had recently published an interesting post about Application Continuity in a Dataguard configuration … here).

In the post, I will explain quickly how it works based on an sample code I wrote few months ago. I will explained too application continuity resources allocation, where they are consumed, and how much is the resource overhead.

12c Application Continuity … What is it and how does it work?

Application Continuity is a technology that will improve application availability. Before Oracle 12c, Transparent Application Failover (TAF) or Fast Connection Failover (FCF) coud help you to improve application availability without downtime, but developers usually have to implement a TAF or FCF aware code. And usually, it’s wasn’t the case for many reasons (testing and UAT platform which weren’t RAC platform, specifications weren’t written in this aim etc.).

In 12c, Oracle releases application continuity. This technology will do the same thing with more simplicity and has been developed to handle planned and unplanned outages.

Application Continuity works on Oracle 12c database and with application that use:

  • Oracle Weblogic Server
  • Oracle UCP (Unified Connection Pool)
  • Third party JAVA application server which used the PooledConnection Interface
  • JDBC Thin-driver

The lab …

To test application continuity, I wrote a simple test case based on :

  • a 12c (12.1.0.2) RAC Cluster with a container database (CDB), a Pluggable Database (PDB), and a Database Schema (LAURENT) with on 2 tables and a “spy” trigger.
  • a small Java Code sample.

On the database side, I created a TEST table add fill it with 500000 rows, and another one (SPY_TABLE) which will track the id of the TEST table that have been modified :


SQL> create table test as select rownum id, dbms_random.string('U',20) v from dual connect by level <= 500000;

Table created.

SQL> create table spy_table(d date, inst_name varchar2(16), id number);

Table created.

Row updates tracking is made by a trigger on update on the TEST table:


SQL> create or replace trigger spy_test
 2  before update on test for each row
 3  declare
 4    pragma autonomous_transaction;
 5  begin
 6    insert into spy_table values(sysdate,sys_context('USERENV', 'INSTANCE_NAME'), :old.id);
 7  commit;
 8  end;
 9 /

Trigger created.

To make the test, we have to create a RAC service for our database which have these features:

  • FAILOVER_TYPE has to be set on TRANSACTION to enable Application Continuity
  • COMMIT_FAILOVER has to be set to TRUE (Even if it’s a parameter related to Transaction Guard, you have to set it)
  • REPLAY_INITIATION_TIMEOUT, FAILOVER_RETRIES, FAILOVER_DELAY should be reviewed to configure the delays for the replay (see this link for more information)
[oracle@rac1 JAVA]$ srvctl add service -d cdb -s app -preferred cdb1 -available cdb2 -pdb pdb -j SHORT -B SERVICE_TIME -z 30 -w 10 -commit_outcome TRUE -e TRANSACTION -replay_init_time 1800 -retention 86400 -notification TRUE
[oracle@rac1 JAVA]$ srvctl start service -s app -d cdb

[oracle@rac1 JAVA]$ srvctl status service -d cdb
Service app is running on instance(s) cdb1

[oracle@rac1 JAVA]$ /u01/app/12.1.0/grid/bin/crsctl stat res ora.cdb.app.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb.app.svc
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------

Note: I voluntarily configured the service with a preferred node to be sure It will be always the same I will use for my tests (related to resources). But I recommend you to configure it with all instances as “preferred”.

The Java source code is available at this link.

Procedures and functions are:

  • pressAnyKeyToContinue: it’s a private method that make a pause during the program. This pause can be useful to attach a JConsole or VisualVM to track JVM memory and CPU.
  • getStatValue: this function returns the value of an oracle statistics for the current session.
  • doTx: this is the most important function in this program. It will perform the transaction and it’s in this one you will find the most important thing when we code Java with Application Continuity: the callback. This callback is delimited by two calls: beginRequest and endRequest. In this callback, you will put all of the operation you want to protect against failures. If a planned or unplanned outage occurs during the callback, the transaction will be rolled back and then replayed. Please note that in the callback area, you have to:
    • call the prepareStatement function to assign the SQL statement, otherwise you will throw a SQLRecoverableException during the connection failover.
    • disable autoCommit for the connection.
    • put a commit statement to say where the transaction ends.
    private void doTx(Connection c, int numValue) throws SQLException {
        String updsql = "UPDATE test " +
                        "SET v=UPPER(v) " +
                        "WHERE id=?";
        PreparedStatement pstmt = null;

        /* Beginning of the callback
         * AutoCommit has to be disabled in the callback part.
         */
        ((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
        pstmt=c.prepareStatement(updsql);
        c.setAutoCommit(false);
        for (int i=0;i<numValue;i++) {
            pstmt.setInt(1,i);
            pstmt.executeUpdate();
        }
        c.commit();
        // End of the Callback.
        ((oracle.jdbc.replay.ReplayableConnection)c).endRequest();
        pstmt.close();
    }

  • In the main function, the most important thing is to use the new OracleDataSourceImpl class instead of OracleDataSource.

Then, I compile the code and run it with the number of updates I want to execute in my transaction.

First run

During the run, I execute a “kill -9″ against the PMON process of the instance where my java program is connected to:

[oracle@rac1 JAVA]$ ./launchJava.sh SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions

<<<< KILLED THE PMON PROCESS HERE >>>>

Instance Name = cdb2
CPU used by this session = 38643
session pga memory max = 4140696

We can see that, even with an instance failure which was running a transaction (of 50000 updates), the failover occurs without any error message.

The Spy table shows the results above:

SQL> select inst_name,count(*) from spy_table group by inst_name;

INST_NAME	   COUNT(*)
---------------- ----------
cdb2		      50000
cdb1		       1270

This proves that the transaction has been rolled-back and completely replayed after the connection failover.

Mutable values

When a transaction becomes replayable, a problem comes with what we call a mutable value. If you are interested by the definition you can read this on wikipedia: http://en.wikipedia.org/wiki/Immutable_object. But I prefer to explain this in a different way.

Imagine we do a close test to the previous one, but instead of updating data, we insert lines into a table and in this table, we have a timestamp column which takes the value of systimestamp oracle function when we insert a line. Now an outage occurs, the connection if failed over and the transaction is rolled back and replayed on the other node. If we don’t say to Oracle that this value is immutable, the value will be inserted at the timestamp recorded on the second node and not the original one and this could cause some problems in your data integrity.

This problem occurs with sysdate, systimestamp, sequence values and another function that depends on your process id (and other parameters) : SYS_GUID.

If you want to make your variables immutables, you need to have the KEEP grant:


SQL> show user
USER is "SYS"
SQL> grant keep date time to laurent;

Grant succeeded.

SQL> grant keep sysguid to laurent;

Grant succeeded.

SQL> connect laurent/laurent@rac-scan:1521/app
Connected.

SQL> grant keep sequence on seq1 to laurent;

Grant succeeded.

No pain, no gain … what about resources consumption?

Application continuity is a good thing because, with less code modification your java app will become completely fault tolerant. As the documentation mentions, there’s an overhead on CPU and memory … then, let’s check this !

To test this, I used the same environment than previously but I didn’t kill any process, I just ran it with and without callback definition.

Then I measured different things:

  • CPU used by the database session (statistic: CPU used by this session)
  • Memory consumed by the database session (statistic: session pga memory max)
  • CPU used by the java program
  • Memory consumed by the java program (Java heap memory usage)

CPU and memory consumed by the java program has been measured with this good old Jconsole.

To get the java heap memory usage, I set up the program with a heap size of 512Mb as it was the good value to avoid garbage collection during execution.

Here are the results:

  • With callback defined

[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest' SimpleAC.java
((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
((oracle.jdbc.replay.ReplayableConnection)c).endRequest();

[oracle@rac1 JAVA]$ ./compile.sh

[oracle@rac1 JAVA]$ ./launchJava.sh -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 34859
session pga memory max = 3961144

load1_avecCallback

Note the heap memory usage high water mark is 110Mb and the average CPU is 0,4%

  • With no callback defined
[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest' SimpleAC.java
//((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
//((oracle.jdbc.replay.ReplayableConnection)c).endRequest();

[oracle@rac1 JAVA]$ ./compile.sh

[oracle@rac1 JAVA]$ ./launchJava.sh -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 35138
session pga memory max = 3747576

load1_sansCallback

The Heap usage high water mark is 91Mb and the average CPU is 0,2%.

To sum up, we can tell that there is a memory overhead on the jdbc layer (for my example which was based on a transaction of 50000 updates). The CPU overhead announced by Oracle seems to be trivial. On the database side, no overhead has been found on CPU nor on the memory.

If you search more information about Application Continuity, you can read this paper by Oracle : http://www.oracle.com/technetwork/database/database-cloud/private/application-continuity-wp-12c-1966213.pdf

Oracle 12c, VMWare Fusion and the perl binary’s segmentation fault

Recently I have installed a new Virtual Machine in VM Ware Fusion 7 Pro under Oracle Enterprise Linux 6.6 for running Oracle 12c Grid Infrastructure and database (12.1.0.2). Mac OS X is a 10.9.5 (Mavericks). As usual for my tests environment, I deploy the oracle-rdbms-server-12cR1-preinstall rpm package to be sure everything will be correctly configured on my system. During the installation process, there was a first error during link edition of rman (“error invoking target irman ioracle of makefile /u01/app/oracle/product/12.1.0/grid/rdbms/lib/ins_rdbms.mk”). This problem can be fixed by copying the libjavavm12.a library located in $ORACLE_HOME/javavm/jdk/jdk7/lib to $ORACLE_HOME/lib directory, and then retry the link edition by clicking on “Retry” button. So, if you don’t use Grid Infrastructure here or if you install only the database kernel … you won’t have any problem. Maybe, you will encountered the problems detailed above if you create a mutitenant (or single tenant) database (because it needs to run catcon.pl script … but we will see why later). In my case, I was deploying Grid Infrastructure and at the end of the installation process, you have to run root.sh script to configure the CRS stack. At this step, the root.sh script fails with an error on  roothas.pl script and a cute “Segmentation fault (core dumped)” error:


Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
/u01/app/oracle/product/12.1.0/grid/crs/config/rootconfig.sh: line 131: 4562 Segmentation fault (core dumped) $ROOTSCRIPT $ROOTSCRIPT_ARGS
The command '/u01/app/oracle/product/12.1.0/grid/perl/bin/perl -I/u01/app/oracle/product/12.1.0/grid/perl/lib -I/u01/app/oracle/product/12.1.0/grid/crs/install /u01/app/oracle/product/12.1.0/grid/crs/install/roothas.pl ' execution failed

After analyzing the error, I get the cause of my problem which is located on the perl binary delivered with Oracle. If I run a simple “perl -v”, I got the error.


[oracle@oel6 bin]$ pwd
/u01/app/oracle/product/12.1.0/grid/perl/bin
[oracle@oel6 bin]$ ./perl -v
Segmentation fault (core dumped)

If we go deeper, gdb shows us an error located on PerlIO functions and specifically on PerlIO_default_layers function:


[oracle@oel6 bin]$ gdb perl
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-75.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /u01/app/oracle/product/12.1.0/grid/perl/bin/perl...(no debugging symbols found)...done.
(gdb) r
Starting program: /u01/app/oracle/product/12.1.0/grid/perl/bin/perl
warning: no loadable sections found in added symbol-file system-supplied DSO at 0x7ffff7ffa000
[Thread debugging using libthread_db enabled]

Program received signal SIGSEGV, Segmentation fault.
0x0000000000531399 in PerlIO_default_layers ()
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.149.el6_6.7.x86_64 libgcc-4.4.7-11.el6.x86_64 nss-softokn-freebl-3.14.3-22.el6_6.x86_64
(gdb) bt
#0 0x0000000000531399 in PerlIO_default_layers ()
#1 0x000000000053070e in PerlIO_resolve_layers ()
#2 0x000000000053050f in PerlIO_openn ()
#3 0x0000000000530206 in PerlIO_fdopen ()
#4 0x0000000000530198 in PerlIO_stdstreams ()
#5 0x0000000000530150 in Perl_PerlIO_stdin ()
#6 0x0000000000435f09 in S_parse_body ()
#7 0x00000000004343bb in perl_parse ()
#8 0x000000000041cf13 in main ()

IMHO, it’s a specific problem on WMWare fusion because on a VM hosted on Virtualbox there’s no problem, and more :

  • If I copy the working perl binary from the virtualbox VM to the VMWare VM … execution failed on VMWare
  • If I copy the failing perl binary from the VMWare VM to the virtualbox VM … it works fine

At the time I write this post, I have asked to a friend of mine if he can reproduced the problem on a VM hosted on an ESX server, I will update this post as soon as I will get his results. I made a lot of different tests (Downgrading VMWare fusion to Fusion 6 Pro, Installing OEL 6.4, OEL 7.0, Redhat 6.5 and 7, creating my vmdk’s on another disk, changing scsi driver etc … always the same problem). I read on many blogs, people have faced the same problem and have symlinked the failing perl to the system perl binary which runs fine. Doing this can produce different issues because of the INC directory which is not the same, you can encountered some errors due to version compatibility between modules in $OH/perl/lib directory and the perl binary. Indeed Oracle 12cR1 uses a perl v5.14.1 and system perl binary is 5.10 on OEL/RHEL 6, and 5.16 on OEL/RHEL7. I workarounded the problem by recreating the perl binary from sources, but you need to be careful of the $OH/perl/lib directory, because it contains all the perl modules needed by Oracle. See above, the different steps to compile perl binary and replace it in $ORACLE_HOME:


export ORACLE_HOME=/u01/app/oracle/product/12.1.0/grid
cd ~
rm -rf perl
mkdir perl
cd perl/
curl -O http://www.cpan.org/src/5.0/perl-5.14.1.tar.gz
tar -xvzf perl-5.14.1.tar.gz
cd perl-5.14.1

cd $ORACLE_HOME
mv perl/ perl.OLD
mkdir perl
cd /home/oracle/perl/perl-5.14.1
./Configure -des -Dprefix=$ORACLE_HOME/perl -Doptimize=-O3 -Dusethreads -Duseithreads -Duserelocatableinc && make clean && make && make install
cd $ORACLE_HOME/perl
rm -rf lib/ man/
cp -r ../perl.OLD/lib/ .
cp -r ../perl.OLD/man/ .

Once these steps are done and you have a running perl binary, you can safely run your root.sh script, create multitenant databases, and all the stuff that needs $ORACLE_HOME/bin/perl.

SIMD Extensions in and out Oracle 12.1.0.2

First of all, I would like to thank Tanel Pöder from Enkitec Accenture for its review of this post and some precious information he gave me.

—-

Recently I posted a link on twitter which explains basics of SIMD Programming (https://www.kernel.org/pub/linux/kernel/people/geoff/cell/ps3-linux-docs/CellProgrammingTutorial/BasicsOfSIMDProgramming.html), and I had a reply which asked me if it was Oracle 12c style, and the answer is … yes and no.

What is a SIMD extension?

A SIMD Extension is a CPU instruction that computes many data in only one instruction (Single Instruction Multiple Data). Imagine, you have 2 arrays of 4 integers, and you want to compute a sum of those 2 arrays. A classical way will be to loop on each value and add them one by one and to get the result in another array. This operation will produce 4 operations.

Now Imagine, your arrays are now located in a vector of 4 integers, those 2 vectors are in fact specific registers and with only one CPU instruction, you will add those 2 vectors by producing only one vector. You reduce CPU instructions by 4 … for the same result.

If it’s not clear, don’t go away … I have written small C sample code to demonstrate this.

A bit of history

SIMD extensions are not quite recent. They have been created in 1970 with vector programming.

In 1996, SIMD extensions have been widely deployed with MMX extensions (which are SIMD extensions), then Alvitec systems with motorola processors and IBM Power systems have developed more powerful instructions. Then Intel reveals its new SSE extensions in 1999 that have been improved by other extension SSE2, SSE3, SSSE3, SSE4 and now AVX, AVX2 and AVX512 extensions.

So Oracle is not using a specific extension but those which are available on your platform, because all CPUs are not offering the same extensions. For example, modern processors have AVX extensions, but most recent extension (AVX-512) are only available in Xeon Phi Knights Landing and Xeon Skylake microarchitectures (broadwell successors).

Data Structures

SIMD extensions are based on data structures or vectors.

A vector is an array data structure (don’t be confused with an array datatype) which have a fixed length and which is, in fact, a succession of scalars of one type.

For example, if you have a vector of 64 bits (8 bytes), you can put in it 2 integers because an integer has a 4 bytes size (in x86-64 arch), 8 chars (1 bytes) but only one double (8 bytes long).

Those data structures are located is CPU registers dedicated for those SIMD instructions.

Let’s take an example, you want to process the sum of two vectors in a processor which uses only MMX instructions (old one ;) ) have 8 registers (MM0 through MM7). Each register holds 64 bits.

First vector content is 1,2 and second one is 1,2. First vector is copied from memory to MM0 register and the second in MM1, and then the CPU launch the SIMD instruction that will produce in MM0 the sum on MM1 and MM0, and then MM0 is copied in memory as a result.

Now imagine, your vector doesn’t hold 64 bits but 128, 256, 512 or 1024 … you will put in it more data and those data will be computed with only one operation …

It’s one of the key of SIMD evolution, MMX uses 64 bits registers (MM0 to MM7), SSE (1/2/3 and 4) uses 128 bits registers (XMM), AVX (1/2) uses 256 bits registers (YMM), and AVX-512 uses 512 bits registers (ZMM).

For Intel processors, vector datatypes are __m64, __mm128, __mm256, and __mm512 (each vector will contain floating point value aka float), you have the equivalent for double precision values (__mm128d, __mm256d, __mm512d) and for other types : int, short, char (__mm128i, __mm256i, __mm512i).

Note: Note that all those types are automatically aligned on a 8, 16, 32 or 64 bytes boundaries.

Now computing data

Now you know how will be computed your data, you can perform operation on it. You can add, multiply your vectors, perform bit shifting etc.

You have the choice to do “classical” operations, or you can use Intel’s intrinsics which are functions which computes a specific operation (basic mathematics, bit shifting, comparisons etc.). All of Intel’s Intrinsics are available at this URL: https://software.intel.com/sites/landingpage/IntrinsicsGuide/. On this page you can also see performance information of each function on different processors.

 Examples

For all examples above, I used C langage.

Compiling “SIMD aware” programs (with GCC)

If you want to compile SIMD aware program, you have to include “immintrin.h” header file which is available with GCC. This header will test which extension you have, and you have used for you compilation. (Just find this file and open it). Depending on your CPU and compilation, it will include another header file:

  • mmintrin.h for MMX instructions and datatypes:
  • xmmintrin.h for SSE
  • emmintrin.h for SSE2
  • pmmintrin.h for SSE3
  • tmmintrin.h for SSSE3
  • smmintrin.h for SSE4.1 and SSE4.2
  • avxintrin.h for AVX

When you compile your program, some extensions are not included by default. Indeed if your CPU supports AVX extensions, if you don’t give the correct option to the compiler, AVX won’t be used.

Main options are:

  • O3: this option enable vectorization loops optimization.
  • msse4.1: this option enable SSE4.1 extension
  • msse4.2: this option enable SSE4.2 extension
  • mavx: this option enable AVX extension
  • mavx2: this option enable AVX2 extension

Other options are available here: https://gcc.gnu.org/onlinedocs/gcc-4.4.7/gcc/i386-and-x86_002d64-Options.html

To demonstrate this, I used a small program:


#include <stdio.h>
#include <stdlib.h>
#include <immintrin.h>

void print_extensions () {
#ifdef __MMX__
printf("MMX ... OK\n");
#else
printf("MMX ... KO\n");
#endif

#ifdef __SSE__
printf("SSE ... OK\n");
#else
printf("SSE ... KO\n");
#endif

#ifdef __SSE2__
printf("SSE2 ... OK\n");
#else
printf("SSE2 ... KO\n");
#endif

#ifdef __SSE3__
printf("SSE3 ... OK\n");
#else
printf("SSE3 ... KO\n");
#endif

#ifdef __SSSE3__
printf("SSSE3 ... OK\n");
#else
printf("SSSE3 ... KO\n");
#endif

#if defined (__SSE4_2__) || defined (__SSE4_1__)
printf("SSE4_1/2 ... OK\n");
#else
printf("SSE4_1/2 ... KO\n");
#endif

#if defined (__AES__) || defined (__PCLMUL__)
printf("AES/PCLMUL ... OK\n");
#else
printf("AES/PCLMUL ... KO\n");
#endif

#ifdef __AVX__
printf("AVX ... OK\n");
#else
printf("AVX ... KO\n");
#endif
}

int main(int argc, char** argv) {
print_extensions();
return 0;
}

If you run it with only O3 optimization, you will get this result:


macbook-laurent:simd $ sysctl -a | egrep 'cpu.*features'
machdep.cpu.features: FPU VME DE PSE TSC MSR PAE MCE CX8 APIC SEP MTRR PGE MCA CMOV PAT PSE36 CLFSH DS ACPI MMX FXSR SSE SSE2 SS HTT TM PBE SSE3 PCLMULQDQ DTES64 MON DSCPL VMX SMX EST TM2 SSSE3 FMA CX16 TPR PDCM SSE4.1 SSE4.2 x2APIC MOVBE POPCNT AES PCID XSAVE OSXSAVE SEGLIM64 TSCTMR AVX1.0 RDRAND F16C
machdep.cpu.leaf7_features: SMEP ENFSTRG RDWRFSGS TSC_THREAD_OFFSET BMI1 HLE AVX2 BMI2 INVPCID RTM
machdep.cpu.extfeatures: SYSCALL 1GBPAGE EM64T LAHF RDTSCP TSCI

macbook-laurent:simd $ cc -O3 -o simd_ext simd_ext.c
macbook-laurent:simd $ ./simd_ext
MMX ... OK
SSE ... OK
SSE2 ... OK
SSE3 ... OK
SSSE3 ... OK
SSE4_1/2 ... KO
AES/PCLMUL ... KO
AVX ... KO

If you run with correct options, your program can use AVX or SSE4 extensions:

macbook-laurent:simd $ cc -O3 -msse4.2 -o simd_ext simd_ext.c
macbook-laurent:simd $ ./simd_ext
MMX ... OK
SSE ... OK
SSE2 ... OK
SSE3 ... OK
SSSE3 ... OK
SSE4_1/2 ... OK
AES/PCLMUL ... KO
AVX ... KO
macbook-laurent:simd $ cc -O3 -mavx -o simd_ext simd_ext.c
macbook-laurent:simd $ ./simd_ext
MMX ... OK
SSE ... OK
SSE2 ... OK
SSE3 ... OK
SSSE3 ... OK
SSE4_1/2 ... OK
AES/PCLMUL ... KO
AVX ... OK

Note that if you enable AVX extension, SSE4 extensions are enabled by default.

Example of SSE2 usage in a basic operation (sum)

The C code above will show you how to perform a sum of two arrays of 16 integers each without using Intel intrinsics:


void func2_sse() {
int a[16] = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16};
int b[16] = {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1};
__m128i* aptr;
__m128i* bptr;
int i;
int loopcnt=0;
printf("sizeof(__m128i)=%lu\n",sizeof(__m128i));
printf("sizeof(a)=%lu\n",sizeof(a));

// Above, we cast integer arrays to vectors of integers

aptr=(__m128i*)a;
bptr=(__m128i*)b;

// and now we compute the sum
for (i=0;i<sizeof(a)/sizeof(__m128i);i++) {
loopcnt++;
bptr[i]=aptr[i]+bptr[i];
}

int* c=(int*)bptr;

printf("loopcount = %d\nresult= ",loopcnt);
for (i=0;i<16;i++) {
printf("%d ",c[i]);
}
printf("\n");
}

and the result, my sum has been computed in only 4 loops:


SSE
--------------------
sizeof(__m128i)=16
sizeof(a)=64
loopcount = 4
result= 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Same example with AVX extension:


void func2_avx() {
 int a[16] = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16};
 int b[16] = {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1};
 __m256i* aptr;
 __m256i* bptr;
 int i;
 int loopcnt=0;
 printf("sizeof(__m256i)=%lu\n",sizeof(__m256i));
 printf("sizeof(a)=%lu\n",sizeof(a));
 aptr=(__m256i*)a;
 bptr=(__m256i*)b;

 for (i=0;i<sizeof(a)/sizeof(__m256i);i++) {
 loopcnt++;
 bptr[i]=aptr[i]+bptr[i];
 }

 int* c=(int*)bptr;

 printf("loopcount = %d\nresult= ",loopcnt);
 for (i=0;i<16;i++) {
 printf("%d ",c[i]);
 }
 printf("\n");
}

and the result, my sum has been computed in only 2 loops:


AVX
--------------------
sizeof(__m256i)=32
sizeof(a)=64
loopcount = 2
result= 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

 Now, let’s compare two data sets with SIMD extension

Next code sample concerns a vector where we want to search the value 10. To do that, we use a comparison function and a function to build a 256bits (AVX) vector full of the value we search. The comparison function works with 32bits packets (useful to compare integers) and returns 0xFFFFFFFF if both values are equal, 0x0 otherwise. As it’s an AVX function, our initial vector composed by 16 values is processed in only 2 CPU cycles.

void func2_compare_32bitsPack() {
    int a[16] = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16};
    __m256i* aptr;
    __m256i b;
    int i;
    int loopcnt=0;
    aptr=(__m256i*)a;
    // b is a vector full off int(32bits) equal to 10 (the value we search)
    b=_mm256_set1_epi32(10);

    for (i=0;i<sizeof(a)/sizeof(__m256i);i++) {
        loopcnt++;
        // comparison intrinsic function: packed by 32 bits(specific for int: if equal set 0xFFFFFFFF, 0x0 otherwise)
        aptr[i]=_mm256_cmpeq_epi32(aptr[i],b);
    }

    // print results
    int* c=(int*)aptr;

    printf("loopcount = %d\nresult= ",loopcnt);
    for (i=0;i<16;i++) {
        printf("0x%x   ",c[i]);
    }
    printf("\n");
}

And the result:


macbook-laurent:simd $ ./simd
Comparison
loopcount = 2
result= 0x0 0x0 0x0 0x0 0x0 0x0 0x0 0x0 0x0 0xffffffff 0x0 0x0 0x0 0x0 0x0 0x0

It becomes easy to identify that the value 10 is located at the index 10 in our initial array.

Ok, and how SIMD extensions are used in Oracle 12c In Memory ?

If you have read my last post on how to activate SSE4 extensions on VirtualBox guests (http://laurent-leturgez.com/2015/04/14/enable-simd-sse4-extension-in-oracle-virtualbox/) , and Tanel Pöder’s post (https://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/), you have noticed that Oracle can run IM with only SSE2 extension (default), but if your CPUs have SSE4, or AVX extensions, Oracle will use some specific libraries that uses SSE4 (libshpksse4212.so) and AVX (libshpkavx12.so).

If we have a look at functions in those libraries, we will see that every function starts with “kdzk”

[oracle@oel64-112 ~]$ readelf -a /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.a | grep FUNC
 6: 0000000000000030 256 FUNC LOCAL DEFAULT 3 kdzk_overload_opc_name
 23: 0000000000000130 80 FUNC LOCAL DEFAULT 3 kdzk_flag_name
 26: 0000000000000180 112 FUNC LOCAL DEFAULT 3 kdzk_enc_name
 31: 00000000000001f0 320 FUNC LOCAL DEFAULT 3 kdzk_datawidth_name
 64: 0000000000002b70 544 FUNC LOCAL DEFAULT 3 kdzk_eq_dict_1bit
 65: 0000000000002d90 544 FUNC LOCAL DEFAULT 3 kdzk_lt_dict_1bit
 66: 0000000000002fb0 544 FUNC LOCAL DEFAULT 3 kdzk_gt_dict_1bit
 67: 00000000000031d0 592 FUNC LOCAL DEFAULT 3 kdzk_le_dict_1bit
 68: 0000000000003420 592 FUNC LOCAL DEFAULT 3 kdzk_ge_dict_1bit
 69: 0000000000003670 544 FUNC LOCAL DEFAULT 3 kdzk_ne_dict_1bit
 70: 0000000000003890 224 FUNC LOCAL DEFAULT 3 kdzk_gt_lt_dict_1bit
 71: 0000000000003970 576 FUNC LOCAL DEFAULT 3 kdzk_gt_le_dict_1bit
 72: 0000000000003bb0 576 FUNC LOCAL DEFAULT 3 kdzk_ge_lt_dict_1bit
 73: 0000000000003df0 992 FUNC LOCAL DEFAULT 3 kdzk_ge_le_dict_1bit
 74: 00000000000041d0 512 FUNC LOCAL DEFAULT 3 kdzk_eq_dict_1bit_null
 75: 00000000000043d0 192 FUNC LOCAL DEFAULT 3 kdzk_lt_dict_1bit_null
 76: 0000000000004490 512 FUNC LOCAL DEFAULT 3 kdzk_gt_dict_1bit_null
 77: 0000000000004690 512 FUNC LOCAL DEFAULT 3 kdzk_le_dict_1bit_null
 78: 0000000000004890 464 FUNC LOCAL DEFAULT 3 kdzk_ge_dict_1bit_null
 79: 0000000000004a60 512 FUNC LOCAL DEFAULT 3 kdzk_ne_dict_1bit_null
 80: 0000000000004c60 192 FUNC LOCAL DEFAULT 3 kdzk_gt_lt_dict_1bit_null
 81: 0000000000004d20 528 FUNC LOCAL DEFAULT 3 kdzk_gt_le_dict_1bit_null
 82: 0000000000004f30 192 FUNC LOCAL DEFAULT 3 kdzk_ge_lt_dict_1bit_null
 83: 0000000000004ff0 528 FUNC LOCAL DEFAULT 3 kdzk_ge_le_dict_1bit_null
 84: 0000000000005200 848 FUNC LOCAL DEFAULT 3 kdzk_eq_dict_2bit_selecti
 85: 0000000000005550 960 FUNC LOCAL DEFAULT 3 kdzk_eq_dict_2bit
 89: 0000000000005910 848 FUNC LOCAL DEFAULT 3 kdzk_lt_dict_2bit_selecti
 90: 0000000000005c60 1056 FUNC LOCAL DEFAULT 3 kdzk_lt_dict_2bit
 91: 0000000000006080 848 FUNC LOCAL DEFAULT 3 kdzk_gt_dict_2bit_selecti
 92: 00000000000063d0 1008 FUNC LOCAL DEFAULT 3 kdzk_gt_dict_2bit
 93: 00000000000067c0 848 FUNC LOCAL DEFAULT 3 kdzk_le_dict_2bit_selecti
 94: 0000000000006b10 1024 FUNC LOCAL DEFAULT 3 kdzk_le_dict_2bit
 95: 0000000000006f10 848 FUNC LOCAL DEFAULT 3 kdzk_ge_dict_2bit_selecti
 96: 0000000000007260 1056 FUNC LOCAL DEFAULT 3 kdzk_ge_dict_2bit
 97: 0000000000007680 848 FUNC LOCAL DEFAULT 3 kdzk_ne_dict_2bit_selecti
 98: 00000000000079d0 960 FUNC LOCAL DEFAULT 3 kdzk_ne_dict_2bit
 99: 0000000000007d90 928 FUNC LOCAL DEFAULT 3 kdzk_gt_lt_dict_2bit_sele
 100: 0000000000008130 1328 FUNC LOCAL DEFAULT 3 kdzk_gt_lt_dict_2bit
 101: 0000000000008660 928 FUNC LOCAL DEFAULT 3 kdzk_gt_le_dict_2bit_sele
 102: 0000000000008a00 1296 FUNC LOCAL DEFAULT 3 kdzk_gt_le_dict_2bit
 103: 0000000000008f10 928 FUNC LOCAL DEFAULT 3 kdzk_ge_lt_dict_2bit_sele
 104: 00000000000092b0 1328 FUNC LOCAL DEFAULT 3 kdzk_ge_lt_dict_2bit</pre>

kdzk is the Oracle component that manages compression:


SQL> oradebug doc components

.../...

Components in library ADVCMP:
--------------------------
 ADVCMP_MAIN Archive Compression (kdz)
 ADVCMP_COMP Archive Compression: Compression (kdzc, kdzh, kdza)
 ADVCMP_DECOMP Archive Compression: Decompression (kdzd, kdzs)
 ADVCMP_DECOMP_HPK Archive Compression: HPK (kdzk)
 ADVCMP_DECOMP_PCODE Archive Compression: Pcode (kdp)

An interesting thing to see is that, even you use an Oracle Kernel without any SSE4 nor AVX extension active (so your process doesn’t use libshpksse4212.so nor libshpkavx12.so library), you use kdz functions when you query and filter a table which is managed in Memory.

In a session I run the statements above:


SQL> select segment_name,BYTES,BYTES_NOT_POPULATED from v$im_segments

SEGMENT_NAME         BYTES         BYTES_NOT_POPULATED
-------------------- ------------- -------------------
S                         37748736                   0

SQL> select spid from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));

SPID
------------------------
3619

SQL> select count(*) from s where amount_sold>1700;

Just before launching the command, I attach my process and run gdb to catch every call to kdz functions:


[oracle@oel64-112 ~]$ pmap -x 3619 | egrep 'sse|avx'

[oracle@oel64-112 ~]$ gdb -pid 3619
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-64.el6_5.2)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
.../...
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libnque12.so
0x000000362ea0e740 in __read_nocancel () from /lib64/libpthread.so.0
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.132.el6_5.4.x86_64 libaio-0.3.107-10.el6.x86_64 numactl-2.0.7-8.el6.x86_64
(gdb) rbreak ^kdz

.../...

(gdb) commands
Type commands for breakpoint(s) 1-2165, one per line.
End with a line saying just "end".
>continue
>end

If you study the output, you will see that a lot of functions are called, and in the list, you will find some interesting functions: kdzdcol_get_minval, kdzdcol_get_maxval, kdzk_build_vector etc. Oracle clearly uses vectors to process IM compression units.

In my opinion, it’s normal to use functions related to compression because the kernel manipulates “Compression Units”, and it should integrates SIMD functions in its libraries.

A last curiosity with Oracle 12c (12.1.0.2)

Ok now you had a look to your installation, your machine is “AVX enabled”, and Oracle processes uses the AVX compatible library (libshpkavx212.so), everything is OK and you think you will use all this stuff.

But if you use objdump on this library, and you search for AVX registers, you won’t find anything:


[oracle@oel64-112 ~]$ grep -i ymm objdump_out.1 | wc -l
0

Tanel Pöder gave me the answer !!! Oracle database code is compiled to be compatible with Redhat/Oracle Linux 5, so it must be compatible with kernel 2.6.18. But linux scheduler can work with YMM registers from version 2.6.30 onwards.

You can use new instructions without the kernel knowing about us, but you can’t use registers that are not yet supported by the kernel.

I think next version of Oracle will improve this, maybe in 12.2.

To conclude, there is not Oracle 12c style for SIMD instructions. Oracle has developed functions that uses SIMD instructions, for Intel CPUs, they uses SSE, SSE2, SSE3, SSE4 or AVX depending on the CPU architecture, on IBM AIX these libraries use VMX extension (SIMD instruction on Power) etc.

Sources:

http://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/

http://en.wikipedia.org/wiki/Data_structure_alignment

http://en.wikipedia.org/wiki/Advanced_Vector_Extensions

http://en.wikipedia.org/wiki/Streaming_SIMD_Extensions

http://en.wikipedia.org/wiki/SIMD

https://software.intel.com/sites/landingpage/IntrinsicsGuide/

https://www.kernel.org/pub/linux/kernel/people/geoff/cell/ps3-linux-docs/CellProgrammingTutorial/BasicsOfSIMDProgramming.html

http://laurent-leturgez.com/2015/04/14/enable-simd-sse4-extension-in-oracle-virtualbox/

Follow

Get every new post delivered to your Inbox.

Join 286 other followers