Oracle … as usual

Oracle by Laurent Leturgez

Database Load heatmap with AWR and Python

Recently, one of my customer asked me to design him a tool to quickly see the database load. He already did that for another Rdbms with a heatmap.

To do that, I used two distinct part in my work: Data Extraction, and Data Vizualisation.

Data Extraction from AWR

As he has licensed his databases with the Diagnostic pack , I decided to use AWR repository, and as I’m a bit lazy, I found a query from Marcin Przepiorowski (@pioro) and I modified it to pivot data.


col "00-01_ " for 90.99
col "01-02_ " for 90.99
col "02-03_ " for 90.99
col "03-04_ " for 90.99
col "04-05_ " for 90.99
col "05-06_ " for 90.99
col "06-07_ " for 90.99
col "07-08_ " for 90.99
col "08-09_ " for 90.99
col "09-10_ " for 90.99
col "10-11_ " for 90.99
col "11-12_ " for 90.99
col "12-13_ " for 90.99
col "13-14_ " for 90.99
col "14-15_ " for 90.99
col "15-16_ " for 90.99
col "16-17_ " for 90.99
col "17-18_ " for 90.99
col "18-19_ " for 90.99
col "19-20_ " for 90.99
col "20-21_ " for 90.99
col "21-22_ " for 90.99
col "22-23_ " for 90.99
col "23-24_ " for 90.99

WITH t AS
  (SELECT TO_CHAR(mtime,'YYYY/MM/DD') mtime,
    TO_CHAR(mtime,'HH24') d,
    LOAD AS value
  FROM
    (SELECT to_date(mtime,'YYYY-MM-DD HH24') mtime,
      ROUND(SUM(c1),2) AAS_WAIT,
      ROUND(SUM(c2),2) AAS_CPU,
      ROUND(SUM(cnt),2) AAS,
      ROUND(SUM(load),2) LOAD
    FROM
      (SELECT TO_CHAR(sample_time,'YYYY-MM-DD HH24') mtime,
        DECODE(session_state,'WAITING',COUNT(*),0)/360 c1,
        DECODE(session_state,'ON CPU',COUNT( *),0) /360 c2,
        COUNT(                               *)/360 cnt,
        COUNT(                               *)/360/cpu.core_nb load
      FROM dba_hist_active_sess_history,
        (SELECT value AS core_nb FROM v$osstat WHERE stat_name='NUM_CPU_CORES'
        ) cpu
      WHERE sample_time > sysdate - 30
      GROUP BY TO_CHAR(sample_time,'YYYY-MM-DD HH24'),
        session_state,
        cpu.core_nb
      )
    GROUP BY mtime
    )
  )
SELECT mtime,
  NVL("00-01_ ",0) "00-01_ ",
  NVL("01-02_ ",0) "01-02_ ",
  NVL("02-03_ ",0) "02-03_ ",
  NVL("03-04_ ",0) "03-04_ ",
  NVL("04-05_ ",0) "04-05_ ",
  NVL("05-06_ ",0) "05-06_ ",
  NVL("06-07_ ",0) "06-07_ ",
  NVL("07-08_ ",0) "07-08_ ",
  NVL("08-09_ ",0) "08-09_ ",
  NVL("09-10_ ",0) "09-10_ ",
  NVL("10-11_ ",0) "10-11_ ",
  NVL("11-12_ ",0) "11-12_ ",
  NVL("12-13_ ",0) "12-13_ ",
  NVL("13-14_ ",0) "13-14_ ",
  NVL("14-15_ ",0) "14-15_ ",
  NVL("15-16_ ",0) "15-16_ ",
  NVL("16-17_ ",0) "16-17_ ",
  NVL("17-18_ ",0) "17-18_ ",
  NVL("18-19_ ",0) "18-19_ ",
  NVL("19-20_ ",0) "19-20_ ",
  NVL("20-21_ ",0) "20-21_ ",
  NVL("21-22_ ",0) "21-22_ ",
  NVL("22-23_ ",0) "22-23_ ",
  NVL("23-24_ ",0) "23-24_ "
FROM t pivot( SUM(value) AS " " FOR d IN ('00' AS "00-01",'01' AS "01-02",'02' AS "02-03",'03' AS "03-04",'04' AS "04-05",'05' AS "05-06",'06' AS "06-07",'07' AS "07-08",
                                          '08' AS "08-09",'09' AS "09-10",'10' AS "10-11", '11' AS "11-12",'12' AS "12-13",'13' AS "13-14",'14' AS "14-15",'15' AS "15-16",
                                          '16' AS "16-17",'17' AS "17-18",'18' AS "18-19",'19' AS "19-20",'20' AS "20-21",'21' AS "21-22", '22' AS "22-23",'23' AS "23-24") 
            )
ORDER BY mtime

Nota: this query can be used to extract Database Load, Active Average Session (AAS), CPU part of AAS, and the Wait part of AAS. You just have to replace the value alias in the WITH Block.

Another point, I didn’t look at the plan, so I didn’t tune the statement … if you have some tricks to tune it … feel free to comment 😉

This query gives this kind of result:

MTIME      00-01_  01-02_  02-03_  03-04_  04-05_  05-06_  06-07_  07-08_  08-09_  09-10_  10-11_  11-12_  12-13_  13-14_  14-15_  15-16_  16-17_  17-18_  18-19_  19-20_  20-21_  21-22_  22-23_  23-24_
---------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
2016/11/23    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.03    0.03    0.00    0.04    0.02    0.00    0.00    0.04    0.01    0.01    0.01    0.04    0.01
2016/11/24    0.02    0.03    0.01    0.03    0.05    0.02    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/06    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.01    0.01    0.02    0.02    0.00    0.00    0.00    0.00
2016/12/07    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.01    0.00    0.00    0.04    0.03    0.01    0.02    0.01    0.02    0.01    0.01    0.06    0.03
2016/12/08    0.03    0.05    0.02    0.02    0.03    0.03    0.02    0.02    0.03    0.02    0.01    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/12    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.03    0.02    0.05    0.02
2016/12/13    0.03    0.01    0.01    0.01    0.02    0.03    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/14    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.02    0.02    0.02    0.02    0.18    0.03    0.02    0.01    0.01    0.00    0.00    0.00    0.00    0.00

When the result is greater than 1, your database is loaded, but if it’s greater or equal that 2 … it’s really overloaded. In both cases, you have to analyze why ?

The problem with this kind of results is it’s only numbers and you have to analyze the complete results to detect when the problem occurs, here comes the data vizualisation part 😉

Data Vizualisation with Python

On the Oracle database Area, a guy has made a lot for visualization of performance data, it’s Kyle Hailey (@dbavigil). And Visualize your data is a very important thing to quickly identify bottleneck. So if you’re interested by data vizualisation you can visit his blog, you will find a lot of resources to extract AWR data and to visualize them (and other stuff).

So I decided to use Python to vizualise database load data extracted previously. To do that, I used python 3.4.5 with a bunch of packages installed:

  • cx_Oracle to connect Oracle Database and execute queries on your Oracle Database
  • numpy for arrays manipulation
  • plotly to graph the heatmap

The python script is available by following this URL: https://app.box.com/s/irjzi64lbne6xhw3t57aixqiy95toxht. Then, you have to modify it to enter the connection string (line 33).

My customer asked me that my script generates an HTML file  to visualize the heatmap in a browser (One of the reason I used plotly).

This script generates the file in /var/tmp (but you can modify it at the end) and then open the browser locally (So don’t forget to export X11 display if you run it on a linux server).

Below you can see the heatmap (X Axis is the hour range, Y Axis represent a day analyzed, the most recent day is on the first line of the heatmap.).

heatmap_s

This heatmap will help my customer to quickly visualize his database load, I hope it will help you too ;).

Of course, you can use the tool of your choice to visualize data (Tableau, Excel, Kibana etc.)

 

 

How Oracle 12.2 manage Editions.

Recently, I wrote a blog post about suspected new editions: Enteprise Core edition and Standard Core Edition.

This morning, I received a mention from twitter coming from Franck Pachot:

In GV$INSTANCE definition, we can see that edition is encoded in X$KSUXSINST view (in the column KSUXSEDITION). The corresponding code is this one:

  • 2 = PO = Personal Ed.
  • 4 = SE = Standard Ed.
  • 8 = EE = Enterprise Ed.
  • 16 = XE = eXpress Ed.
  • 32 = CS = Standard Core Ed.
  • 64 = CE = Enterprise Core Ed.
  • 128 = HP = Enterprise Ed. High Perf
  • 256 = XP = Enteprise Ed. Extreme Perf

How Oracle use these codes to determine Oracle Edition ?

When, you relink your Oracle kernel, you use ins_rdbms.mk makefile located in $ORACLE_HOME/rdbms/lib and you run a make command with specific targets.

For example, if you wan to link Core Enterprise Edition, you will run this (See my previous blog post):

$ make -f ins_rdbms.mk edition_coreenterprise ioracle
Deploying Oracle Database Core Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_cee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin

In fact, it uses a library which is copied as $ORACLE_HOME/lib/libvsn12.a and then this library is linked to Oracle kernel.

There are many libraries in the 12.2 kernel:

$ cd $ORACLE_HOME/lib
$ ls libvsn* -l
-rw-r--r-- 1 oracle oinstall 10774 Nov 28 17:47 libvsn12.a
-rw-r--r-- 1 oracle oinstall 10742 Oct  4 00:46 libvsn12.a.default
-rw-r--r-- 1 oracle oinstall 10774 Nov 28 17:40 libvsn12_backup.a.dbl
-rw-r--r-- 1 oracle oinstall 10790 Oct  4 00:46 libvsn12_cee.a.dbl
-rw-r--r-- 1 oracle oinstall 10790 Oct  4 00:46 libvsn12_cse.a.dbl
-rw-r--r-- 1 oracle oinstall 10774 Oct  4 00:46 libvsn12_hp.a.dbl
-rw-r--r-- 1 oracle oinstall 10766 Oct  4 00:46 libvsn12_std.a.dbl
-rw-r--r-- 1 oracle oinstall 10774 Oct  4 00:46 libvsn12_xp.a.dbl

After a closer look inside those libraries, I found they are containing two object files (.o)

$ ar -t libvsn12_hp.a.dbl
vsnhp.o
vsnfhp.o
$ ar -t libvsn12_cee.a.dbl
vsncee.o
vsnfcee.o
$ ar -t libvsn12_std.a.dbl
vsnstd.o
vsnfstd.o

etc...

After extracting these files from the library, and reading the ELF section, we can see:

  • the vsnXXX.o file contains the banner
  • the vsnfXXX.o file contains other things but I don’t know what yet.
$ ar -x libvsn12_cee.a.dbl
$ objdump -s vsncee.o

vsncee.o:     file format elf64-x86-64

Contents of section .comment:

.../...

Contents of section .rodata:
 0000 4f726163 6c652044 61746162 61736520  Oracle Database
 0010 31326320 456e7465 72707269 73652045  12c Enterprise E
 0020 64697469 6f6e202d 20436f72 65202564  dition - Core %d
 0030 2e25642e 25642e25 642e2564 20257300  .%d.%d.%d.%d %s.
 0040 00000000 40000000 2d203634 62697420  ....@...- 64bit
 0050 50726f64 75637469 6f6e0000 00000000  Production......

$ objdump -s vsnfcee.o

vsnfcee.o:     file format elf64-x86-64

Contents of section .comment:

.../...

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b840 00000048 89ec5dc3  f.UH...@...H..].
Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

If we compare these sections (text.unlikely and .eh_frame) between many Edition library files, we can see the following points:

  • Eh_frame section contains exception unwinding and source language information. They shouldn’t be very different:
$ objdump -s -j .eh_frame vsnfcee.o vsnfxp.o vsnfhp.o vsnfstd.o vsnfcse.o

vsnfcee.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfxp.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfhp.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfstd.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfcse.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

Indeed, these sections are identical.

  • Now let’s see the .text.unlikely section:
$ objdump -s -j text.unlikely vsnfcee.o vsnfxp.o vsnfhp.o vsnfstd.o vsnfcse.o

vsnfcee.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b840 00000048 89ec5dc3  f.UH...@...H..].

vsnfxp.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b800 01000048 89ec5dc3  f.UH.......H..].

vsnfhp.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b880 00000048 89ec5dc3  f.UH.......H..].

vsnfstd.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b804 00000048 89ec5dc3  f.UH.......H..].

vsnfcse.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b820 00000048 89ec5dc3  f.UH... ...H..].

At the first look, they seem identical … but they are not, the section highlighted in red is different, and as my platform is linux and so little-endian platform, we have to read the highlighted blocks like this:

  • vsnfstd.o : 0x00 04 = 4
  • vsnfcse.o 0x00 20 = 32
  • vsnfcee.o : 0x00 40 = 64
  • vsnfhp.o : 0x00 80 = 128
  • vsnfxp.o : 0x01 00 = 256

So Oracle have just some libraries that encore the edition, and this code seems to enable some option at the runtime depending on the value included in the library (which is much more secure than having a list of enabled options embedded in a library).

Another thing to mention, in my instance, no trace of Enterprise Edition nor Express and Personal Edition, even if they are coded in GV$INSTANCE view’s code.

UPDATE: It seems that Stefan Koehler (@OracleSK) has the same conclusion but using another method:

Oracle 12.2, new release … new editions or just a cloudy feature ?

Recently I have created an Oracle 12.2 database in the Oracle Cloud (Extreme Performance). I was able to test some of the new features.

And usually, when I test a new release of Oracle, I have a look into the ins_rdbms.mk file to see if there are some new options to link the Oracle kernel, and in this release I found some interesting stuff.

As I said before, my instance was in the Extreme Performance, it was normal that, when connected, I got this banner:

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

And when I had a look to the V$INSTANCE view, there’s a column EDITION. This column is already here in 12.1 and is documented.

If you have a closer look to the documentation (12.1), you can see different editions:

  • CORE EE: CORE Enterprise Edition
  • CORE SE: CORE Standard Edition
  • EE: Enterprise Edition
  • PO: Personal Edition
  • SE: Standard Edition
  • XE: Express Edition

Ok for EE, PO, SE and XE … they are well known edition, but what about those “Core” Editions (Standard and Enterprise).

Note: on 1st December, 12.2 documentation mention only Core EE, EE, PO and XE (Might be a doc bug) 

I had a look to both (12.1 and 12.2) ins_rdbms.mk files (located in $ORACLE_HOME/lib folder). For On premises installations (11.2 & 12.1), no trace of these new editions, but they are available on Oracle Cloud Platform

  • Oracle 12.1.0.2
$ grep -i edi /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
edition_corestandard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Standard Edition"
edition_coreenterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Enterprise Edition"
edition_standard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Standard Edition"
edition_enterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition"
edition_highperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition High Performance"
edition_extremeperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition Extreme Performance"

  • Oracle 12.2.0.1
$ grep -i edi /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk
edition_corestandard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Standard Edition"
edition_coreenterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Enterprise Edition"
edition_standard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Standard Edition"
edition_enterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition"
edition_highperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition High Performance"
edition_extremeperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition Extreme Performance"

ohoohhh ;), let’s try to activate those rules and relink the kernel

$ make -f ins_rdbms.mk edition_coreenterprise ioracle
Deploying Oracle Database Core Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_cee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin

 - Linking Oracle
rm -f /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/12.2.0/dbhome_1/bin/orald  -o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/12.2.0/dbhome_1/lib/ -L/u01/app/oracle/product/12.2.0/dbhome_1/lib/stubs/   
-Wl,-E /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.2.0/dbhome_1/lib/nautab.o 
/u01/app/oracle/product/12.2.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/12.2.0/dbhome_1/lib/naect.o /u01/app/oracle/product/12.2.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/config.o  
-ldmext -lserver12 -lodm12 -lofs -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12  -lvsn12 -lcommon12 
-lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12  -lrt -lplp12 
-ldmext -lserver12 -lclient12  -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi`
 `if [ -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -ljavavm12 -lserver12  -lwwg  `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags` 
   -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12  -lcore12
 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12
 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12   -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12
 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` 
-L/u01/app/oracle/product/12.2.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -locr12 -locrb12 -locrutl12 -lhasgen12
 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12  -lgeneric12 -lorazip -loraz -llzopro5 -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls12 -lnls12  -lcore12 
-lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12  -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 
-lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12  -laio -lons  -lfthread12   `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.2.0/dbhome_1/lib -lm   
 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.2.0/dbhome_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && 
test -r /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
test ! -f /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle || (\
           mv -f /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracleO &&\
           chmod 600 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracleO )
mv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

That seems to work, now let’s start the instance and let’s connect:

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 28 16:41:28 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition - Core 12.2.0.1.0 - 64bit Production

New banner … but are there any new features ?

If we compare EE Core edition and EE Extreme perf, it seems there are some difference in the field of enabled options:

  • EE Core Edition (12.2)
SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ------------------------------
ASM Proxy Instance             FALSE *
Active Data Guard              FALSE
Adaptive Execution Plans       FALSE
Advanced Analytics             FALSE
Automatic Storage Management   FALSE *
Cache Fusion Lock Accelerator  FALSE
Change Data Capture            FALSE
DICOM                          FALSE
Data Mining                    FALSE
Exadata Discovery              FALSE
Global Data Services           FALSE
I/O Server                     FALSE * 
Management Database            FALSE *
OLAP                           FALSE
Oracle Data Guard              FALSE
Oracle Database Vault          FALSE *
Oracle Label Security          FALSE *
Partitioning                   FALSE
Real Application Clusters      FALSE *
Real Application Security      FALSE
Real Application Testing       FALSE
Spatial                        FALSE
Unified Auditing               FALSE *
  • EE Extreme Perf Edition and High Perf Edition (12.2)
SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ------------------------------
ASM Proxy Instance             FALSE
Automatic Storage Management   FALSE
I/O Server                     FALSE
Management Database            FALSE
Oracle Database Vault          FALSE
Oracle Label Security          FALSE
Real Application Clusters      FALSE
Unified Auditing               FALSE

And if we have a look to “traditional” EE that we can choose from the Cloud Interface … (12.2)

SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
ASM Proxy Instance             FALSE
Automatic Storage Management   FALSE
I/O Server                     FALSE
Management Database            FALSE
Oracle Database Vault          FALSE
Oracle Label Security          FALSE
Real Application Clusters      FALSE
Unified Auditing               FALSE

OK, so Enterprise Core Edition and Enterprise Edition look differents in terms of available options. And like Franck Pachot pointed it out, the Core EE is like a traditional EE but with all paid options disabled and other EE features disabled (DataGuard for example).

 

On the field of licensing, these Core editions seem to be unavailable. If you buy Oracle License, on-premises or in the cloud, you only have the choice between SE, EE and EE High Perf and EE Extreme perf (in the cloud for HP and XP), no Core Editions.

If we go back to ins_rdbms.mk file, it’s possible to link these editions only in Oracle Cloud Service for 12.1 and 12.2, but Oracle Enterprise Edition 12.2 is impossible to link (because of missing libraries), but if Core Edition is a core based license model, Core Edition and Actual Enterprise Edition are licensed per core not per socket, no difference on this field:

$ make -f ins_rdbms.mk edition_enterprise ioracle
Deploying Oracle Database Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_ee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
cp: cannot stat `/u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_ee.a.dbl': No such file or directory
make: *** [edition_enterprise] Error 1

Oracle Standard Edition and Standard Core Edition are both able to be linked.

As a conclusion, the “Core” Edition could be :

  • A new licensing model for Cloud and On Premises platform and will be released later. As a consequence, a new licensing model will appear for Standard Edition, not per Socket, but per Core like EE (the Socket model would be abandoned ?)
  • A new licensing model for Cloud platform … only
  • A bug ?

The future will tell us 😉

 

DOAG 2016 … a great conference !

From 15th till 18th November, I was at the DOAG Conference in Nurnberg and it was a very great conference and a great opportunity for the Oracle Community.

Last year, I decided to attend (and to present) at different conferences in Europe and these conferences represent a good opportunity to develop your network (mainly technical for me), to learn a lot about technical stuff from worldwide Oracle experts, and to share your experiences.

More, you will share very good moments with people you certainly know by social networking (twitter, blogs etc.).

Let me explain how it was during DOAG and why I enjoyed it.

I arrived at my hotel the day before the conference, I didn’t know Nurnberg and as the conference center was not located in the town center, I feared to be alone in this area and to meet people exclusively during the conference days. But here comes the Oracle community …

Once installed in my room, I went to twitter to view the last news and I saw a tweet from Jan Karremans (http://www.jk-consult.nl/) who asked who was in his hotel (Yipiie !!! it’s mine too). So we plan to meet up in the hotel’s lobby in the evening. During this time, we found other people (Martin Widlake @MDWidlake, Neil Chandler @ChandlerDBA, Kamil Stawiarski @ora600pl etc.) that will enjoy to have a beer (or more 😉 ) in a pub just before the Oracle ACE dinner.

So we had a great time with those guys and I have a new opportunity to meet great people at the dinner which was organized by Oracle for its fellow advocates 😉

Day 1 : great conferences and a German Organization for a very nice event. After the conferences, comes the speaker dinner. With different guys, we had a beer (or two 🙂 ) before the dinner but once arrived at the restaurant, this one was full and we cannot join the speakers that came earlier. We were about 20 people and we decided to find a restaurant for our “unspeaker restaurant” event. Kamil found a great place and during this time, I had the pleasure to sit near Martin Widlake and Neil Chandler who made the demonstration of their “soooo” british sense of humor .. we had a lot of fun !! 😉

The last two days were more studious for me, I attended lots of sessions presented by so interesting and famous guys (Kerry Osborne: @KerryOracleGuy, Stefan Koehler: @OracleSK, Mauro Pagano: @Mautro, Franck Pachot: @FranckPachot, Ludovico Caldara: @ludodba etc.). The most interesting one was given by Toon Kooperlars and Bryn Llewellyn about the ThickDB concept. I’m specialized in infrastructure and this session was much oriented to development, it can be surprising but that’s the power of the community and the power to attend this kind of conference … you can discover new fields and develop your technical culture (even if I have some skills in database development 🙂 ).

Last day, I presented my session (about SIMD instructions) and I was very honored to receive some congratulations from Stefan Koehler who is one of the most interesting guy in the Oracle’s internal field (and member of the prestigious “Oak Table” network).

Thanks DOAG and thanks to all the speakers … it was a blast 😉

OTN Appreciation Day: SQL Patch

Feature: SQL Patch.

Why : Because, it can help you to use some hints on a non editable query (for example, in a packaged application). In order to stabilize performance of this kind of application, it’s very useful.

Why not: Because sometimes, having many execution plans for a SQL Statement is better than one.

If you don’t understand why such a short post, or why this post .. please read this: https://oracle-base.com/blog/2016/09/28/otn-appreciation-day/
Many thanks to Tim Hall (@oraclebase) for the Idea 😉