Oracle Database 11g Express Edition Quick Tour

by Przemyslaw Piotrowski

The new Express Edition reflects essential updates to the Oracle Database code base since 10.2.0.1, and thus contains an avalanche of value for developers and DBAs.

Published September 2011

In this article you will learn about top new features introduced in Oracle Database 11g Express Edition (XE). This latest version of Oracle's free database offering packages the essential updates to Enterprise Edition from 10.2.0.1 and through 11.2.0.2. (That tells a lot about the magnitude of changes: four release cycles of 10g and four of 11g.) From relatively simple improvements like the PIVOT operator, deferred segment creation, and virtual columns to the groundbreaking adaptive cursor sharing, database resident connection pooling, and edition-based redefinition features, this new release sets a new standard for "express" RDBMSs.

With respect to growing needs for storage, Oracle Database 11g XE now offers a full 11GB for user data alone, which is almost threefold increase since previous 4GB limit in Oracle Database 10g XE. Other hardware restrictions remain the same with CPU usage capped at one physical core and memory at 1GB. Oracle Database 11g XE is available immediately for Windows and Linux platforms.

Below find a quick tour of the Oracle Database 11g Release 2 functionality that is now made available gratis via Express Edition.

Install and Upgrade

Upgrading to this new version of XE is very simple compared to traditional methods like Database Upgrade Assistant (DBUA) or manual upgrade: The entire process comprises getting a dump from your existing database, uninstalling the previous release, installing the new one, and importing the dump. (Note: Oracle Application Express applications go through a separate path and are excluded from the full dump; the provided gen_inst.sql script takes care of that.)

 11g-xe-quicktour-f1

Figure 1 Installation screen

The installation procedure allows for either regular deployments or silent installs so the process can be easily scripted for mass deployment. For more information, refer to Oracle Database Express Edition Installation Guide, which explains the exact steps required to perform the upgrade.

11g-xe-quicktour-f2

Figure 2 New Web interface

Application Development

Oracle Application Express (APEX) remains one of the most actively developed Oracle Database features, with two important milestones reached since the 2.1 release that was included with Oracle Database 10g XE. First, Oracle APEX 3.0 brought PDF printing, Access migration, page caching, and a number of builder enhancements. Then, release 4.0, which is bundled with 11g XE in the form of Oracle APEX 4.0.2, introduced Websheets - a unique technology for effortless content management where users take control of both content and structure of exposed data. Other improvements include native dynamic actions, a plug-ins framework, team features for streamlining the application development process, RESTful Web Services, a J2EE APEX listener, and a revamped application builder. (The most current release at the time of this writing, version 4.1, delivers improved error handling, ability to use ROWIDs instead of primary keys in forms, spreadsheet upload capabilities, and even more refinements to the builder.)

Check out the Oracle APEX change logs 2.2, 3.0, 3.2, 4.0 and 4.1 to learn more details about these releases.

11g-xe-quicktour-f3

Figure 3 Oracle APEX homepage

With the numerous contributions Oracle has made to the open-source PHP driver (OCI8), the module now offers improved connectivity and compliance with OCI drivers, including support for fast application notification. PHP developers can also benefit from native support for server-side connection pooling which delivers increased performance for Web traffic. The Oracle Learning Library and 2 Day + PHP Developer’s Guide are excellent starting points for finding out more on using PHP with this new release.

Oracle’s Java database drivers were upgraded to JDBC 4.0 standard, which offers auto-loading, improved connection handling, support for ROWID, XML and ANYTYPE data types. Other enhancements include SQL annotations and improved exception handling.

.NET developers will definitely appreciate the ability to set up ODP.NET through configuration files and performance improvements in LOB retrieval and context caching.

SQL

SQL is one of the areas where Oracle Database 11g, and thus by extension XE, really shines. Many of the new features that stand out were implemented in response to user feedback over the last couple of years. Others are stepping ahead and bring impressive new capabilities to your development toolbox.

Previously adding a new Not Null column presented a big challenge, especially for huge tables with billion of rows. Such operations locked up the whole table for the duration of the ALTER statement, usually requiring some complex workarounds. No longer the case with Oracle Database 11g Release 2, which works around this problem gracefully by taking a radical new approach to handling how mandatory columns are added. Because the new column has a constant default for all rows, and cannot be null, it is stored in the data dictionary only and the change happens momentarily.

Virtual columns are named expressions that are visible to users as regular table columns. They do not consume disk space and are a great way to supplement base columns with additional information extracted from them. Virtual columns bridge the gap between tables and views by exposing the ability to present extra information in multiple different ways.

Many times deploying an index comes with a risk of picking up the wrong execution plan and is usually unpredictable in advance. Oracle Database 11g Release 2 presents a whole new way of mitigating that risk with the introduction of invisible indexes. Visibility allows for silent deployment of indexes in the privacy of a session with OPTIMIZER_USE_INVISIBLE_INDEXES parameter set to TRUE, which could also be set at system level so that either all invisible indexes are picked up by optimizer or remain ignored. A visibility keyword needs to be provided during creation of an index (CREATE INDEX … INVISIBLE) or afterward (ALTER INDEX … INVISIBLE). By default, the server is configured to ignore them unless specified otherwise for the session.

The highly anticipated SQL string aggregation function LISTAGG made it into the official release as well. Elegant, simple syntax LISTAGG(...) WITHIN GROUP (ORDER BY …) is not only easy to use but also outperforms all alternatives known to date in terms of speed. And that’s not the only new aggregate; another one is NTH_VALUE, which lets you pick n-th value of a window, filling the gap between FIRST_VALUE and LAST_VALUE functions. Also, we have a new possibility for LAG and LEAD functions to ignore null values.

11g-xe-quicktour-f4

Figure 4 Example LISTAGG usage

Transposing columns, even if possible in previous Oracle versions, is now easier than ever with the dedicated SQL operatorsPIVOT and UNPIVOT. Exchanging rows for columns to obtain cross-tabular format is now achievable with a single, explicit clause within a select statement, and comes with out-of-the-box UNPIVOT functionality so that transposed results could be rotated backward. A superb feature is PIVOT’s ability to operate on arbitrary number of columns, using XML as an output: (PIVOT XML (SUM(...) FOR … IN. (ANY)).

Recursive subquery factoring is one of Oracle’s new features for increasing compliance with ANSI SQL standard. The WITH clause, starting with 11g, can now take recursive form, meaning that it can refer to itself from inside the WITH block. Naturally, we are not saying goodbye to CONNECT BY just yet, but from now on the new WITH clause is the preferred method of working with hierarchical data.

Every DBA’s favorite tool (SQL*Plus, of course) has silently received numerous improvements as well. SHOW command can now reveal session’s EDITION and also SPFILE contents with SPPARAMETERS clause. Furthermore, new debugging mechanisms allow for capturing of all SQL, PL/SQL and SQL*Plus errors into error log table with SET ON command. Finally, we can now display contents of BLOBs directly on the command line.

PL/SQL

Fine grained dependency tracking, a remarkable new approach to handling object invalidation inside the database, is one of the most attractive features for developers. Previously, all operations on parent objects would invalidate child ones, even when the change did not affect them in any way. Now, the only invalidated objects are those that are directly impacted by the change. PL/SQL coders will definitely consider this single feature worth the upgrade.

Native PL/SQL compilation has finally won its independence from third-party C compilers and now, all it takes to have PL/SQL code native-compiled is to set PLSQL_CODE_TYPE to NATIVE at either session, object, or instance levels. No external compiler is required - rather interpreted PL/SQL code is transparently turned into compiled M-Code. Compiled code can run an order of magnitude faster than interpreted code and offers an amazing performance boost absolutely free.

Triggers drew much attention in the release, with the most notable change in the form of compound triggers that allow for sharing global state for the lifetime of the statement that fired them. This prevents against mutating table exceptions and also supplements cross-editioning. Other improvements include the ability to specify order for triggers of the same type through the FOLLOWS clause and a method for creating them as DISABLED.

Smaller enhancements include a new, high-performance PL/SQL data type ((SIMPLE_INTEGER), the highly demanded CONTINUE statement for loops, ability to assign sequence’s value to variable directly, and support for dynamic SQL statements larger than 32KB.

Manageability

Over the last few years Oracle has added many new database features for minimizing downtime and addressed almost every area impacting application availability. However, one particular maintenance task was strictly dependent on having to bring the application down. This gap is now filled with the introduction of edition-based redefinition (EBR), a revolutionary new technology for eliminating application upgrade downtime - completely. It's great news that this feature is present in XE.

The approach taken was to add a whole new dimension to the data dictionary itself: an edition. Editions are revisions of database object definitions, that are hierarchical and configurable at either database, session, service or OS levels. During the upgrade you simply switch the edition user is going to see upon reconnect.

11g-xe-quicktour-f5

Figure 5 Working with edition-based redefinition

Since tables themselves are not editionable (it would require a copy of data), new object types are introduced to assist with evolving tables: editioning views and cross edition triggers. They are used to control object visibility and pump data back and forth between coexisting editions. For further information about EBR consult the Oracle Database Advanced Application Developer's Guide.

Improvements to memory management began in 10g and are pushed even further in 11g. To further minimize administrative efforts, SGA and PGA are now being managed as one with Automatic Memory Management (AMM), which means no more fine-tuning SGA_TARGET and PGA_AGGREGATE_TARGET to meet desired OLTP or batch needs - Oracle will now adjust memory allocation under a simple setting of MEMORY_TARGET, and scale as needed. There is also something new to parameter files management: PFILEs can now be created from memory. DBAs will surely appreciate this little gem.

The core security engine is also represented in the improvements list. The most noteworthy change brings case-sensitivity to passwords, so caps-lock can no longer be ignored during database log on. This and few other authentication improvements like setting maximum number of failed attempts, the ability to conceal the version banner, and prevention against Denial of Service (DoS) attack are managed with a new family of system parameters with SEC_ prefix.

Database applications with hundreds of empty tables created during installation can now benefit from another interesting functionality for reducing storage consumption (deferred segment creation), enabled by default for all new tables. When a table is created with segments deferred (CREATE TABLE … SEGMENT CREATION DEFERRED,) no storage is allocated until the first insert. Such behavior could yield large space savings when tables are populated optionally, together with individual application modules, but the whole package is shipped at once. You can disable this functionality by setting DEFERRED_SEGMENT_CREATION to FALSE at instance level or explicitly while creating a table (CREATE TABLE … SEGMENT CREATION IMMEDIATE).

Gathering statistics is now managed through automatic maintenance tasks infrastructure. Autotasks, as they are commonly referred as to, are now the foundation for recurrent system upkeep tasks and can be monitored through DBA_AUTOTASK% views.

This release is packed with a ton of smaller features that make the DBA’s life easier, such as the ability to make tables read-only (ALTER TABLE … READ ONLY), Data Pump compatibility with legacy export parameter files, and RMAN support for variables in script.

New initialization parameters includeDDL_LOCK_TIMEOUT for controlling how long DDL statements would wait on DML locks, ENABLE_DDL_LOGGINGfor reporting all DDL modifications to alert log, COMMIT_WAIT for configuring redo flush behavior, and DB_ULTRA_SAFE for setting I/O protection levels, among many others.

Finally, you will be much surprised to discover that the alert log comes in XML format and trace files are now being written to $ORACLE_BASE/diag destination specified with DIAGNOSTIC_DEST parameter. Locations of trace and log files are now being governed by the ADR Structure.

Performance

Oracle Database 11g Release 2 brings a whole new connection type to the already available shared and dedicated modes. Created specifically with Web traffic in mind, database resident connection pooling (DRCP) uses a combination of dedicated server and connection broker to handle short, transient sessions coming from Web applications. According to various statistics, DRCP can be even 10x faster than shared servers and 20x faster than dedicated ones. It works around the problem of exhausting server resources through a set of timeouts, pool limits, and session settings so that only a few dedicated processes are launched to handle connections coming into the pool.

DRCP setup with default configuration takes only two simple steps: starting the pool and altering TNS entry by setting (SERVER=POOLED) in place of (SERVER=DEDICATED) or (SERVER=SHARED). With such setup, all new connections to database will only last for a short lifespan and will be recycled automatically by Oracle server.

11g-xe-quicktour-f6

Figure 6 Enabling pooled server and verifying pool connectivity

Previously, when a SQL statement was first parsed with a provided bind variable, the database kept its execution plan for the lifetime. And although bind peeking worked incredibly well for evenly distributed values, it was not quite effective at handling skewed data. Adaptive Cursor Sharing (ACS) introduced in Oracle Database 11g Release 2, is designed specifically to address this problem by keeping several plans for the same statement with different binds. This allows you to pick execution plans based on the actual bind values. ACS is enabled by default and can’t be disabled, remaining completely transparent to end user and developer.

There’s also a piece of Oracle Database engine that has been rewritten from scratch. Enter SecureFiles, Oracle’s new implementation of Large Objects (LOBs) with reinvented characteristics in terms of buffered I/O access, ASSM dependency, dynamic chunk size, reduced contention, and superior performance. SecureFiles are defined individually with LOB clause (LOB(...) STORE AS SECUREFILE) or at instance level for all new LOBs by setting DB_SECUREFILE parameter to ALWAYS. When dealing with media content inside the database, SecureFiles should be your natural choice from now on.

Good old Statspack plays a key role in watching over performance statistics, having received numerous enhancements from release to release. In 11g Release 2 it can automatically capture snapshots, comes with extensive reporting capabilities, and allows one-stop insight into performance of your instance. For a full manual, refer to the SPDOC.TXT file located under ORACLE_HOME/rdbms/admin directory of your XE installation.

Eventually, performance tuning experts will be thrilled to discover that the data dictionary comes with high-resolution, microsecond statistics in views related to wait events, such as V$SESSION, V$SESSION_WAIT and V$SESSION_EVENT.

Even More

Hopefully this article whet your appetite enough to start learning more about the new release. Increased storage limits, development enhancements and improvements to manageability will definitely pay-off once you get to know them better. I’ve been involved with Express Edition since the first beta came out in 2005, and saw it deployed nearly everywhere - from developer laptops and virtual machine farms to ISVs demos and application bundles. With the new release, it’s sure to become even more ubiquitous.


Przemyslaw Piotrowski is a senior database administrator working with maximum availability architectures and agile development environments. Having extensive experience in administration, development and design, he keeps a firm grip on Oracle Database and its supporting technologies.