On the Eleventh Day of 12.2, my DBA gave to me…

Utilities, PL/SQL, and More New Features

[hr width=”2px” color=”#2980b9″ style=”solid”][/hr]

December 22, 2016

Dynamic Debugger

Prior to 12.2, if you wanted to enable debugging, it had to run throughout the process; which was not possible on some of the data warehouse environments; because the process ran long and generated too many logs.

In 12.2, Oracle introduces the new feature Dynamic Debugging, on the running process. This provides the ability to identify the problem(s) in a long production process. It also allows the possibility, of investigating the problem from another session as well. In addition to inspecting the state of in-scope variables; it is now possible to examine the database state as the session being debugged views it, during an uncommitted transaction.

Detect Deprecated Functions in PL/SQL Block

This feature avoids using deprecated functions on a new version. Sometimes, developers will continue to use the existing PL/SQL code on newer database versions, to avoid re- programming. This leads to many performance issues, execution inefficiencies, and potentially wrong result sets. If pragma is used, it will display a warning message if any deprecated functions are used.
However, sometimes functionality causes existing code benefits to be implemented in incompatible ways. For example, the UTL_CALL_STACK package, which was new in Oracle Database 12c Release 1 (12.1), provided the functionality that earlier was provided by the FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE procedures in the DBMS_UTILITY package. In a similar way, this happens in custom developed PL/SQL code.

Improvements in Materialized Views (MVs)

Real-Time MVs

Starting with 12.2, there are several new features that are helpful with real-time data refresh, providing more accurate data. Query rewrite, works optimally with non-synchronized base tables and materialized view logs as well.

Statement-Level Refresh for MVs

Statement-Level Refresh for MVs, provides flexibility to materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities, that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.

Refresh Statistics History for MVs

Oracle provides an additional layer of information for Materialized views refresh statistics. We can manage collection and retention of historical refresh statistics for analytics and detailed reporting. By setting the refresh statistics history, we can track the performance of the MV refresh over time and trend execution time. It is also possible, to compare the current refresh execution time to diagnostic purposes; to determine if the refresh delay is caused by additional data or system performance. We can leverage the DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS procedure, to manage collection and retention of MV refresh statistics.

DBA View for Static and Dynamic SQL Reports

The view DBA_STAMENTS, helps know the use of SQL in a PL/SQL code; including the research of SQL in performance, including static and dynamic SQL. This view provides the use of static SQL for DML; like SELECT, INSERT, UPDATE and DELETE and dynamic SQL, like IMMEDIATE, EXECUTE, and OPEN CURSOR with the SQL_ID. In addition, the view, DBA_IDENTIFIERS, provides information on the static SQL identifiers such as table, column, materialized view, sequence, and others.


DBMS_PLSQL_CODE_COVERAGE Package, is a utility to collect detail information on PL/SQL block. A PL/SQL block, can have an entry and exit instruction, including exception and move point to another PL/SQL block. The primary usage, is to review and analyze the results of code testing; as well as incorporating it into code measurement tests, in a non-production environment. In the regression test and others, the information provided from the package will help with collecting data for PL/SQL unit in test run.

Other PL/SQL Improvements

• PL/SQL Pragma to mark an item as deprecated.
• Enhanced LISTAGG functionality and features.
• Approximation of Query Processing for percentile aggregation and other large volumes of data processing.

Enhancements for Exadata

ExaDirect SQL*Net Adapter

Enhancements to the SQL*Net transport, allow for low latency database access, by utilizing the RDMA over InfiniBand protocol. This new protocol, is only supported on Oracle Linux at the time of this post.

Upgrading the Incremental Transportable Scripts

In Oracle 12.2, migrating from a big-endian architecture to Exadata, has been improved with the use of cross-platform incremental backups. These types of migrations typically have long outage windows, because utilizing transportable tablespace migrations typically has the data in a read-only state; essentially making the database unusable by the application. Cross platform incremental backups, allow this window to be shortened to just the time needed to complete the migration to the metadata export and import.

Partitioning: Improving Support for XMLIndex

New XMLIndex partitioning support for the XMLType data type, has been added to allow for better parallel operations during large workloads to be offloaded, from the compute nodes to the storage cells.

XML Pushdown

If XML data is being stored in a SecureFiles LOB, certain WHERE clause conditions can cause query processing to be offloaded to the storage cells.

ZFS Analytics

With release 12.2, statistics for databases utilizing ZFS storage, over Direct NFS versions 4 and 4.1, can be reported to the storage appliance; based on database IDs and pluggable database IDs allowing for more….

[link_button link=”https://viscosityna.com/resources/dba-resources/twelve-days-12-2/day-11-utilities-plsql/” size=”small” color=”#1CC6DA” align=”left”]Get Full Version[/link_button]