Oracle Database Enterprise vs Standard Edition 2

Oracle database enterprise

The Oracle Database Enterprise software is known in several variants. In practice, two of these are really relevant for production on-premise use. The more comprehensive of these two is called Enterprise Edition and, as the name suggests, is aimed at meeting the most extensive requirements. The other variant is called Standard Edition 2 and is the successor of the old Standard Edition (One). It is intended for those who do not need the additional features of Enterprise Edition and also do not need to use servers with more than two CPU sockets.

Oracle has published details of the distinguishing characteristics for each database version on its website under the heading “Database Licensing Information User Manual”. That summary, however, is not very pleasant to read.

In this article we will try to point out what we think are important differences between the editions and when the choice for one or the other is the most obvious.

Without revealing everything, we would like to state that Standard Edition 2 is the ideal choice for those who are attached to their data, but for whom databases themselves are not the hub of the IT services to be provided. Think of the situation that the database must above all be a good storage/reference medium. For those who would like something extra and are looking for the possibility to extract added value from the database itself (which DBA.nl can help with of course!), there is the Enterprise Edition.

Standard Edition 2 (SE2)

This is the basic edition of Oracle Database but it really isn’t a little boy. For situations where you need a reliable and powerful database for protection and 24×7 availability of your data, SE2 is an excellent choice. In practice, you will not encounter any limits to data growth with this edition, and the basic functionality is already very extensive.

Features of SE2

SE2 is a modern system, ideally suited as a database backend for your business-critical applications.

The well-known Oracle-specific features such as Multimedia (with Locator), Oracle Text, APEX, In-database Java and XMLDB are all present and there are no restrictions on the number of users or on the allocation of memory/disk resources. It goes without saying that support is available for the most modern SQL standards and that the database core in the latest version 12.2 has again been improved in terms of security, reliability, diagnostics and speed.

SE2 manageability is fine; RMAN, DataPump, sql*loader are for data backup and transport and sql*plus, statspack, and the ADR diagnostics give the DBA the necessary tools for management, debugging and performance analysis.

SE2 imposes no limits on the size of the data and thus, like EE, can scale as best. Fun fact: The maximum size of a single Oracle database is approximately 8 Exabytes (8 million Terabytes) and each tablespace can be up to 128 Terabytes in size.

Oracle offers the Real Application Clusters option for SE2 at no extra cost, which allows you to have two servers simultaneously offer the same database when using shared storage to increase availability. However, there is the limiting condition that the maximum use of CPU power per server in that case is half of what is allowed for a non-RAC execution (combined, the maximum CPU power remains the same).

Limits

In recent years, the number of cores per CPU has increased dramatically. Four years ago, a 6-core server processor was still common; now 10-core processors are commonplace and 24-core Intel processors are exotic but available.

This trend has made it possible to take full advantage of Oracle’s Standard Edition database being purchased per CPU (rather than per core) allowing the use of increasingly powerful hardware without increasing licensing and support costs. In addition, it is now less common to use servers with more than a few sockets because multi-core is much more efficient than multi-CPU. The latter in turn means that Standard Edition could be used on more server models.

Oracle has of course also recognized this, but fortunately still continues to use socket licensing for Standard Edition 2. However, they want to keep the goals for Standard Edition (“normal” workloads) in mind and two restrictions are imposed. First, no more than two CPU sockets may be used per physical server and, in the case of RAC, only one per server. In addition, each database is limited to using 16 CPU threads (for example, 8 cores with Hyperthreading or 16 cores without HT). In the case of a RAC database, this is 8 threads per RAC instance. This will still be more than sufficient for regular to even fairly large workloads, and moreover, the thread limitation only applies per database. If you run multiple databases on a server, you can still take advantage of all available CPU capacity, even if it exceeds 16 threads (well). This still makes server consolidation with SE2 possible and attractive!

Furthermore, there are no limitations to SE2 that you should be aware of. It is true that Enterprise Edition offers more features compared to SE2. More on that now.

Enterprise Edition (EE)

The full power of Oracle Database is reflected in the Enterprise Edition. Not only does this have more built-in management gadgets compared to SE2, but it is also possible to add even more specialist functionality (albeit at an additional cost).

At the SQL level, the basic functionality in itself is not very different from SE2. However, there are some extensions that are useful for some applications and that are used. In such cases, your application vendor will require Enterprise Edition. For example, think of Spatial or Partitioning. Apart from these functional differences, the distinction between EE and SE2 is mainly a matter of handles that (database) administrators are given to better protect the data, make it more available and improve the database performance.

Better performances

Compared to SE2, EE has additional result caches, dynamic query optimization and parallelization as well as multiple simultaneous data streams for RMAN backup and database export. Depending on the application, the strength of the server and the composition of the data, this can yield a smaller or larger performance gain and also speed up the backup.

Also in EE it is possible to use bitmap indexes that can be very effective in speeding up queries on data with limited variation (Y/N columns for example). We do see bitmap indexes being used by some applications. A bitmap index determines per value in which rows it occurs and uses minimal storage space (which is also quick to read).

The following example shows how a bitmap index indicates that the province of South Holland is populated in rows number 2, 4 and 5.

So there is a one next to the row in which it occurs and a zero where it is not. The database only needs to read the “value” 01011 from the index line associated with ZH to know which rows to retrieve. A traditional index should store the much longer ROWIDs for each value something like this:

That is clearly more reading work and can add up when a value appears in many rows.

Another EE optimization is the ability to use data compression in tables. With the Basic Table Compression standard present in EE, certain actions (data loads, Create Table as Select statements, table moves) can make use of highly optimized data storage in the database. Since database performance is usually limited by the speed of the storage medium, reducing the amount of data to be read and written is usually the best way to improve performance. Contrary to what you might expect, however, it is not real compression like “zip” what Oracle does. Instead, it works primarily through data deduplication and reordering for maximum block filling when processing bulk submitted data. Hence, the compression is only active during the execution of certain operations; the table data itself is not stored as zipped.

In EE it is possible to deploy additional management functions as part of the optional Tuning Pack. This allows a DBA to more accurately determine where any delay occurs and thus more effectively make suggestions for improvement. The Tuning Pack is used in combination with the Diagnostics Pack, which also provides more real-time information about the “health” of the database system itself.

Higher availability, better protection

With EE, you can use Oracle Data Guard to provision fallback databases that are always up-to-date. A Data Guard standby database is basically a continuously running backup and restore of a primary production database. In the event of damage to a production database, a standby database can quickly take over without loss of data. It is a common feature of EE for setting up fall-back environments and is extremely reliable in our experience.

Also “under water” there are useful additions in EE in the field of data protection. For example, in the EE implementation of RMAN it is possible to recover damaged data files without having to restore and update them in their entirety. This saves time and it is also possible to do this without downtime of the affected tablespace. This is called Block Media Recovery. Such damage can, for example, occur due to disk defects or disk controller problems and unfortunately sometimes occur in daily practice. For those with the luxury of a Read-only query standby database, this recovery can even be done completely automatically by the database itself.

Another data recovery trick is Flashback database. Standard Edition 2 already offers the possibility to check with “Flashback query” what certain data looked like earlier today. Undo data is then used to consult older table contents and you can look back to the oldest available undo information in the UNDO tablespace. However, it pretty much ends there; data recovery will have to be done manually. However, with the Enterprise Edition it is also possible to “un”-drop tables and even roll back the entire database without restore. This can be very valuable in case a serious data entry error has been accidentally made that needs to be fixed!

Finally, another availability-related feature: online index rebuild. In an SE2 database, any rebuilding of indexes will result in these indexes being temporarily unavailable and therefore the indexed data itself may not be changed during the rebuild time. You will at least experience inconvenience and probably real downtime in practice. In EE, it is possible to perform index rebuilds without downtime because Oracle then keeps track of changes that occur and processes them in the new index at the end of the rebuild. With large indexes, this can significantly reduce downtime.

Even better security

Enterprise Edition gives the ability to more closely monitor what is going on in your database. This applies not only to performance-related information, but also to “security”.

SE already provides good facilities for performing database auditing. The database can record (without a user being able to prevent this) which table queries/changes are made by “who”. It should be noted, however, that the database cannot always see who an end user is because an application often does not inform the database of this. Anyway, the database can in any case record what it does know and it may then be possible to check in the application with which end user this corresponds.

On top of that, with Fine-grained auditing, EE offers the possibility to record audit data when specific columns are consulted or changed. You can use this to indicate for specific (sensitive) information whether the database must record that it has been viewed. In SE, this can only be done at the table level, which can cause a lot of false reports because it cannot distinguish between sensitive and non-sensitive information that can be in the same table. In addition, fine-grained auditing can be configured to capture audit information when consulted at certain (suspicious) times.

Based on the audit information, it is fairly easy to generate overviews of notable activities that can provide a picture of “suspicious transactions” with the right parameters. Of course the, hopefully much more common, reverse scenario is also interesting: database auditing can help establish that certain data has not been accessed by unauthorized persons.

An EE feature somewhat similar to Fine-grained auditing is Virtual Private Database. VPD is somewhat the “enforcement” counterpart of the “recording” auditing. VPD works at the row level and is intended to prevent certain information from being accessed at all, even if the user had sufficient rights at the table level. VPD is a smart idea in which the access rights of a user are limited by means of adjustable policies to only the data that this user is allowed to view. It is a bit like using views but is more flexible and robust. VPD is included with EE at no extra cost.

Suppose, for example, that a branch manager wants to request all turnover from the products sold by his branch. The problem is that there is only one table that contains everything and that it contains all sales from all locations together. You could then build such a query function especially for this location by means of views and multiple schemes, but such constructions become vulnerable over time, mistakes are made in rights assignment and views can be problematic because they are not always “updateable”. VPD is able to indicate with a policy that even with a “select *” the branch manager only gets back the rows that meet certain criteria, such as the fact that they belong to his branch. Other rows are simply invisible. An effective protection mechanism that can make complex view/function constructs unnecessary.

Finally, the pinnacle of data security in Oracle Database: Database Vault. This is an additional licensed option on top of EE specifically designed to meet stringent regulatory requirements such as Sarbanes-Oxley and US HIPAA. It would go too far to list here what is possible with this; In any case, DV goes far enough in terms of possibilities to exclude that even so-called superusers/administrators such as DBAs and system administrators can still see your data!

We hope that the above information provides a sufficiently clear insight into the extras that EE offers over SE2 and whether they justify the necessary additional costs in your opinion. We believe that there is at least the right Oracle edition for every environment!