By Franck Pachot

.
Look at the Oracle Download page: Standard Edition is there in 12.1.0.2 – more than one year after the release of the patchset for Enterprise Edition. It’s a new name – SE2 in short – and new rules. Let’s have a look at it.

We waited for it after those rumors from July, and as expected we have been notified by from Ann Sjökvist, the Standard Edition lady, on her blog www.sejustloveit.com. And the news are good, as promised by Dominic Giles who has to face so many questions currently…

Installation

So we have a new binary distribution, whith only one possible choice for the edition: Standard Edition Two:

CaptureSE2-001

Everything else has nothing special.

Rules

We’re waiting for documentation updates at oracle.com but basically SE2 is limited to 2 sockets, which means that:

  • If you have SE on server with 2 sockets, then you can install SE2 without additional cost
  • If you have SE on a cluster (RAC) with 2 nodes having 1 socket each, then you an install SE2 without additional cost
  • If you have SE One then you have to pay additional fee to go to 12.1.0.2 with SE2
  • If you have SE on a cluster (RAC) with 4 nodes having 1 socket each, then you have to remove 2 nodes before going to 12.1.0.2
  • If you have SE on a cluster (RAC) with 2 nodes having 2 socket each, then… you have to change hardware before going to 12.1.0.2, remove one socket (physically), or virtualize with OVM

Of course, you can also choose to go to Enterprise Edition and/or in the Oracle Cloud Services…

Apply latest PSU

In the uncompressed p20831110_121020_Linux-x86-64.zip folder I install the JUL2015 PSU
The installation binaries is different, but the PSU is the same as Enterprise Edition


[oracle@VM115 20831110]$ ../../OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.5
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
 
Oracle Home       : /u01/app/oracle/product/12102SE
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12102SE/oraInst.loc
OPatch version    : 12.1.0.1.5
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12102SE/cfgtoollogs/opatch/opatch2015-09-01_21-13-49PM_1.log
 
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   19769480  20299023  20831110
 
Do you want to proceed? [y|n]

Create Database

At that point you can upgrade a 12.1.0.1 database or create a new one.

I run DBCA in advanced mode. You remember that non-CDB is deprecated? Then choose container database. Of course you can’t choose the number of PDBs to create here. You are in Standard Edition: it’s single tenant. Only one PDB.

CaptureSE2-002

The ‘database options’ step has been skipped. You don’t choose. You install a CDB with all options allowed in SE.

CaptureSE2-003

and here is my database:


$ sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 1 21:06:23 2015
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
 
SQL>

datapatch

If you have read Mike Dietrich post on dbca and datapatch, you know that you have to run datapatch after DBCA or DBUA. 


$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Tue Sep  1 21:21:00 2015
Copyright (c) 2015, Oracle.  All rights reserved.
 
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_5954_2015_09_01_21_21_00/sqlpatch_invocation.log
 
Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
 
Current state of SQL patches:
Bundle series PSU:
  ID 4 in the binary registry and not installed in any PDB
 
Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    Nothing to roll back
    The following patches will be applied:
      20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
 
Installing patches...
Patch installation complete.  Total patches installed: 2
 
Validating logfiles...
Patch 20831110 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_SE2_CDBROOT_2015Sep01_21_21_48.log (no errors)
Patch 20831110 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_SE2_PDBSEED_2015Sep01_21_21_55.log (no errors)
SQL Patching tool complete on Tue Sep  1 21:22:01 2015

Oh. I forgot that my pluggable database is closed…


SQL> alter pluggable database pdb open;
Warning: PDB altered with errors.

then running datapatch again:


Installing patches...
Patch installation complete.  Total patches installed: 1
 
Validating logfiles...
Patch 20831110 apply (pdb PDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_SE2_PDB_2015Sep01_21_28_11.log (no errors)
SQL Patching tool complete on Tue Sep  1 21:28:17 2015

Thread limitation

I’m on a VM with only 4 cores here. Let’s run 20 session all in CPU anyway, by running 20 times the following:


(
TWO_TASK=//vm115/PDB sqlplus sys/oracle as sysdba <<END
alter session set plsql_optimize_level=0;
exec loop null; end loop;
END
)&

Here is the top screen while running:


top - 21:58:17 up  1:09,  3 users,  load average: 4.34, 4.45, 2.82
Tasks: 199 total,   5 running, 194 sleeping,   0 stopped,   0 zombie
%Cpu(s): 99.7 us,  0.1 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem:   1018372 total,  1008288 used,    10084 free,      568 buffers
KiB Swap:  1257468 total,    97252 used,  1160216 free.   103572 cached Mem
 
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 8145 oracle    20   0 1032592   8000   5700 R  21.9  0.8   2:14.05 oracle_8145_se2
 8118 oracle    20   0 1032596   8004   5704 S  20.6  0.8   2:20.59 oracle_8118_se2
 8148 oracle    20   0 1032592   8016   5720 S  20.6  0.8   2:13.43 oracle_8148_se2
 8103 oracle    20   0 1032596   7996   5688 S  20.3  0.8   2:30.29 oracle_8103_se2
 8112 oracle    20   0 1032596   8168   5868 S  20.3  0.8   2:23.69 oracle_8112_se2
 8130 oracle    20   0 1032596   8008   5704 S  20.3  0.8   2:16.14 oracle_8130_se2
 8133 oracle    20   0 1032592   8020   5720 S  20.3  0.8   2:14.40 oracle_8133_se2
 8151 oracle    20   0 1032596   8008   5708 S  20.3  0.8   2:13.41 oracle_8151_se2
 8154 oracle    20   0 1032592   8032   5732 S  20.3  0.8   2:11.76 oracle_8154_se2
 8136 oracle    20   0 1032592   8004   5704 S  19.9  0.8   2:14.70 oracle_8136_se2
 8139 oracle    20   0 1032596   8008   5704 S  19.9  0.8   2:14.92 oracle_8139_se2
 8106 oracle    20   0 1032592   7980   5680 R  19.6  0.8   2:28.44 oracle_8106_se2
 8121 oracle    20   0 1032600   7976   5668 S  19.6  0.8   2:19.45 oracle_8121_se2
 8142 oracle    20   0 1032596   8016   5716 R  19.6  0.8   2:13.60 oracle_8142_se2
 8157 oracle    20   0 1032596   8000   5700 R  19.6  0.8   2:12.65 oracle_8157_se2
 8160 oracle    20   0 1032592   8488   6188 S  19.6  0.8   2:12.62 oracle_8160_se2
 8115 oracle    20   0 1032596   7980   5676 S  19.3  0.8   2:21.39 oracle_8115_se2
 8127 oracle    20   0 1032596   7984   5684 S  19.3  0.8   2:18.06 oracle_8127_se2
 8109 oracle    20   0 1032596   8164   5860 S  18.9  0.8   2:26.49 oracle_8109_se2
 8124 oracle    20   0 1032596   8008   5708 S  18.9  0.8   2:17.93 oracle_8124_se2
 5792 oracle    20   0 1041576 109364  99516 S   0.3 10.7   0:07.97 ora_cjq0_se2

Look at the load average: it’s 4. It seems that the database itself has limited the number of processes willing to run in CPU.
That looks like Resource manager instance caging.

We usually think that we need to have a resource manager plan and set explicitely the cpu_count for that, but that’s not the case here:


SQL> show spparameter cpu_count
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        cpu_count                     integer
 
SQL> show spparameter resource
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        resource_manager_plan         string

Anyway, in Standard Edition, the resource manager is not usable:


SQL> alter system set resource_manager_plan=DEFAULT;
alter system set resource_manager_plan=DEFAULT
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00439: feature not enabled: Database resource manager

However when I check Statspack report for my 20 sessions run:


Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          3 01-Sep-15 21:47:37       22       1.4
  End Snap:         11 01-Sep-15 21:54:05       24       2.5
   Elapsed:       6.47 (mins) Av Act Sess:      19.5
   DB time:     126.27 (mins)      DB CPU:      24.10 (mins)
...
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):               19.5              315.7        0.48       25.00
       DB CPU(s):                3.7               60.3        0.09        4.77
...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
resmgr:cpu quantum                              14,987       6,114    408   99.7
CPU time                                                        12            .2
db file sequential read                            862           6      7     .1
log file sync                                       18           1     57     .0
Disk file operations I/O                           204           0      2     .0
          -------------------------------------------------------------

I have on average 3.7 sessions in CPU (roughly the load average at OS level), a total of 19.5 average active session (my 20 sessions looping in CPU). And 6,114/(6.47*60)=15.7 sessions waiting on ‘resmgr:cpu quantum’

What happens then? Like in XE (where limit is 1 thread), the resource manager is used internally. I’ve not enough cpu here to show it, but the limit for Standard Edition is 16 foreground session active in CPU at maximum. All that will be documented soon. Big thanks to Dominic Giles for sharing with us.

Remark: that 15.7 has nothing to do with the thread limit of 16 – it’s just my 20 sessions minus my 4 cpu. We can see the limit of 16 only when we have more than 16 cores

update: Of course, this 16 threads per database is 8 threads per server when in RAC (remember that you can’t have more than two nodes).

Pricing, features and support

The price and features is the same as the Standard Edition (pricelist, features) that we know.

update: One thing change if you are in NUP. The minimum that was 5 NUP, is now 10 NUP and is per server.

The 12.1.0.1 is supported until August 2016 (MOS Doc ID 2027072.1) but don’t wait: 12.1.0.2 has new features even for Standard Edition (more on that soon), and lot of bugs fixed.

Post Views: 1,937
Thumbnail [60x60]
by
Oracle Team