CHAPTER

10

Utilities and Scheduler New Features

In Oracle Database 12c Release 2, the utilities and built-in stored procedures that you probably use every day have been enhanced not only to integrate and support database new features but also to expand the scalability and ease of use of the utilities themselves. In this chapter I’ll cover three of the most significant enhancements to those utilities: parallel-enabled Data Pump metadata management, external table access driver types, and enabling scheduler jobs to be even more lightweight than in Oracle Database 12c Release 1!

Oracle Data Pump Parallel Metadata Management

Oracle Data Pump has evolved over the last several releases of Oracle Database into the most robust and powerful logical data backup and recovery tool. You can export and import database objects at the table, schema, tablespace, and (of course) database level. There was still one significant bottleneck in Data Pump performance that is now addressed in Oracle Database 12c Release 2: parallel import/export of Data Pump metadata.

To ensure that your Data Pump operations are scalable, you want to leverage multiple CPUs and I/O channels if those resources aren’t being fully utilized by other user jobs. In Oracle Database 12c Release 1 and previous releases, you can run both the Data Pump import (impdp) and export (expdp) commands with the PARALLEL parameter, as in this example:

This example uses four parallel servers by specifying PARALLEL=4 along with the %U wildcard in the dump file specification to more quickly create the four Data Pump export dump files. However, there is a potential bottleneck even when you want to leverage parallelism in Data Pump operations. Mixed in with those four dump files is the database metadata. If you wanted to extract just the metadata from those dump files in releases previous to Oracle Database 12c Release 2, you would run something like this:

In a database with thousands of database objects and users, the metadata export file can be quite large—potentially larger than the export of the database itself! Therefore, having an option to use multiple CPUs and I/O channels to extract metadata from a dump file is very appealing. The PARALLEL parameter for both Data Pump import and export now applies to the metadata as well.

To give you even more control over the degree of parallelism that you will use for metadata export, you can add the MAX_METADATA_PARALLEL parameter to your Data Pump jobs.

NOTE

You can only use the MAX_METADATA_PARALLEL parameter for the expdp command to set the maximum degree of parallelism for export of metadata to a Data Pump dump file.

The previous expdp example would look like this with the MAX_METADATA_PARALLEL parameter added:

NOTE

In a RAC environment, set PARALLEL=1 or MAX_METADATA_PARALLEL=1 to keep all Data Pump processes on a single node in the cluster.

Notice that I specified a different number of parallel server processes for the metadata part of the export so as not to take too many resources away from the rest of the export process; conversely, I can set MAX_METADATA_PARALLEL to a value larger than PARALLEL if I want to prioritize creating the metadata in the Data Pump file.

External Table Access Driver Improvements

When you create external tables in the database, one of the key parameters in the definition of the external table is the ORGANIZATION EXTERNAL clause. That clause identifies the type of external table, where it’s located, and how to process it. Here’s the CUSTOMERS_EXT table definition from Chapter 9, to which I’ll make changes to demonstrate the enhancements for Hadoop and the ORACLE_DATAPUMP driver:

When using Oracle Loader for Hadoop (OLH), you use files that are exported and imported by the ORACLE_DATAPUMP access driver. The problem with OLH and ORACLE_DATAPUMP is that the files written by the ORACLE_DATAPUMP driver have a header block that has to be updated when the export is done, but since HDFS is a write-once file system, that header block cannot be updated. To solve this issue and avoid multiple passes over the same data, you still use the ORACLE_DATAPUMP access driver but add the HADOOP_TRAILERS parameter to the ACCESS PARAMETERS clause, as in the following example. Here is the external table created with the ORACLE_DATAPUMP driver, based on the existing CUSTOMERS_EXT table:

Syntactically, you won’t have to change your CREATE TABLE statement other than the addition of the new access parameter, but under the covers, the amount of I/O that needs to be done in a Hadoop environment when accessing an HDFS file system is dramatically reduced. The Hadoop-friendly external table shows up in the file system much like any other external table:

In-Memory Scheduler Jobs

As early as Oracle Database 11g Release 2, there were several enhancements to the Oracle job scheduler to make it more scalable and reduce its footprint. One of these enhancements was to offer lightweight jobs, which had several advantages such as reduced create and drop times, smaller disk footprints, and RAC load balancing. Lightweight scheduler jobs made it much easier to set up a new scheduler job and run it much more frequently than a traditional scheduler job due to database overhead, especially in terms of job startup and shutdown resources. However, you still had to store the jobs in the data dictionary along with the accompanying I/O requirements.

In Oracle Database 12c Release 2, these limitations are addressed by memory-resident scheduler jobs. You can use in-memory jobs to enhance performance even beyond the performance gains from using lightweight jobs. In-memory jobs occupy a bit more memory but can almost eliminate I/O when jobs start and stop, which translates into significantly reduced elapsed time during the job startup and shutdown phases. There are two types of in-memory jobs: in-memory runtime and in-memory full.

In-Memory Runtime Jobs

In-memory runtime jobs are based on lightweight jobs, so they are persistent; you can run them multiple times on a schedule with a repeat interval. In releases previous to Oracle Database 12c Release 2, you would create a job template and submit a lightweight job like this:

The job type for in-memory runtime jobs is IN_MEMORY_RUNTIME and in the job class DEFAULT_IN_MEMORY_JOB_CLASS, which does no job logging—therefore, you won’t see job completion or status information in views like DBA_SCHEDULER_JOB_RUN_DETAILS. To instead submit the DUAL_CHECK program as an in-memory runtime job that starts now and runs every 15 seconds, you’d create the job like this:

In-Memory Full Jobs

In-memory full jobs are even more lightweight since they only exist in memory, unlike in-memory runtime jobs, which are based on lightweight jobs. The primary use case for in-memory full jobs is to run them once and exit. The job type for in-memory full jobs is IN_MEMORY_FULL. Because in-memory full jobs only exist in memory, they must have a program associated with them, can’t have a repeat interval, and aren’t saved on disk. Using my DUAL_CHECK example again, here is how to create that job:

Since you can’t specify either START_DATE or REPEAT_INTERVAL, the job is going to run right now and only once.

Summary

There is definitely a theme apparent when looking at most of the changes in Oracle Database 12c Release 2. Although there are many new features, the vast majority of the changes are enhancements to existing features to make the database more scalable, more interoperable, and less resource-intensive.

For the Oracle Data Pump utility, you can get your database metadata into and out of the dump file in parallel, which is especially useful if the database metadata is all you need. External tables can now interoperate more easily with Hadoop data stores and reduce I/O requirements significantly. Finally, even Oracle scheduler jobs are leveraging in-memory technologies with two new types of scheduler jobs that reduce your disk, I/O, and CPU overhead and make it even easier than before to create either one-time jobs or scheduled lightweight jobs.

In the next chapter, I’ll talk about several new features that can make life easier for developers, DBAs, and report writers alike, such as the new built-in database functions.