This presentation is about SQL statement parsing, what it is, how it affects database performance, and how to detect and correct parsing problems.  We begin by defining what parsing is, and then walking through it in detail.  This includes the initial checks (syntax, semantics, permissions), SQL_ID generation, and pointer creation from client process to private…

Oracle Database 12c is the first release to offer the capability to access a file in HDFS or HIVE format as an EXTERNAL table. Through live and recorded demonstrations, this session will explain how an Oracle DBA can leverage these new features to gather data in tabular format from these file formats while leveraging the…

This presentation discusses the developer tools (and effort) used to create Graph theoritic model of an Oracle equipment datawarehouse. A graph has nodes and edges and their ranking (based on # of adjecent nodes) was critical in the final decision making. The uniqueness of this approach is to take generic Relational tables and look at…

Oracle has lots of metrics, many can be confusing.  Lets walk through a few examples of some specific Oracle metrics and discuss what the mean.  Don’t have a statistics background, no problem.  I’ll try to put these examples into plain common English.  DBA’s need to use Oracle statistics properly to help make important decisions about…

The fact that triggers are written in PL/SQL creates a lot of bad publicity for this language because, for many IT professionals, database triggers and performance issues are synonymous. Sadly, there is a lot of truth in this statement. Very few features in the Oracle realm are misused as often as are triggers. The situation is so bad that some of the most outspoken Oracle gurus currently have database triggers included in their lists of things that should never have existed!

This presentation does not take such a radical stance. If you can make DBAs, developers, and architects actually communicate while designing software, the chances of using any feature properly are significantly better. There is nothing “evil” about triggers. They just have to be used where they can actually solve problems.

In this presentation, both table triggers and INSTEAD-OF triggers will be examined from a global system optimization point of view that includes, not only the aspect of functional correctness, but also the tradeoffs between multiple goals. For example, depending upon the available hardware, developers can select either CPU-intense or I/O-intense solutions.

Another key point will be the need to keep your knowledge up-to-date. For example, compound triggers should be used much more frequently for both functional reasons (resolving self-mutation) and performance reasons (bulk operations on tables and simulation of STATEMENT-level behavior on views) This presentation will focus on the most common performance problems related to different kinds of DML triggers and the proper ways of resolving them.

This talk will build out the full solution architecture block diagram showing the end-to-end stack of Oracle cloud security features for Exadata and how to use them to protect data in public, private, and hybrid clouds.  Topics will include ssh access, network firewalls and software defined networks (SDN) in Oracle Cloud Infrastructure, platform firewalls (iptables),…

Learn how to keep your system up and running continuously using Oracle built-in features and do it with relative ease. Oracle options will be shown, but we will go beyond the theory and show you how these are used on a critical production system. Hear the pros and cons of practical options that can be considered in 2018 including 12.2 and new 18c options. Architectures and implementations that will be shown are applicable to most installations, so everyone should benefit from the approaches presented..

Features such as FCF, FAN, ONS, Transaction Guard, Application Continuity, Global Data Services and Application VIP’s and their use will be covered. Many of these as used with 12c RAC and Data Guard provide a solution for Disaster Recovery database switchover and application failover that can be performed with virtually no downtime to the online user community.

Alternate options such as Active Data Guard, GoldenGate, disk mirroring and a RAC Stretch Cluster will be reviewed with the pros and cons of each. High availability solution. Approaches for performing online application upgrades using Edition Based Redefinition or GoldenGate will be presented with their advantages and disadvantages for home-built and purchased third party applications. Database physical design considerations that facilitate online application upgrades will be reviewed. Patching and upgrading all components of the system from servers to the operating system, middleware and database with the system online will be examined.

You will come away from this with a practical understanding of many of high availability features as they can be used in the industry today and the realization that you can implement complete HA solution on your own systems!

With the new Application Containers feature in Oracle Database 12c Release 2, Oracle has opened the mechanisms of the container database architecture for applications. Now in a container database application, containers can be defined and application databases can share the data model and common data. This simplifies the installation and the upgrade these applications. In this session the new feature and possible use cases are presented.

The methods used to track performance issues in SQL and PL/SQL vary based on the level of access within a system.  A developer in cooperation with a DBA, or already granted DBA privileges can has a wide variety of tools and deep visibility.  However, a user with nothing more than CREATE SESSION and SELECT privileges…

A number of polls taken during various PL/SQL-related presentations paints a very strange picture: one of the most critical FREE server-side performance tuning tools is almost totally unknown! If you ask basic questions such as “how much time is spent on routine A?” or “how often is function B called?” most developers would start to hand-code their own solution instead of utilizing the Oracle PL/SQL Hierarchical Profiler (HProf). This is not due to a dislike of the provided functionality, but because most developers are simply not aware of its existence! My presentation is an attempt to alter this trend and reintroduce HProf to a wider audience.

The PL/SQL Hierarchical Profiler became available in Oracle 11g Release 1 and replaced the old DBMS_PROFILER package. The goal was the same: to profile runtime behavior of PL/SQL code, i.e. to register and timestamp every operation (including SQL statements) that occurs during the monitoring window. However, the changes were startling:

Profiler output is now a file that can be generated in one environment and analyzed elsewhere. This makes production debugging significantly easier because performance-tuning specialists don’t need to touch PROD as long as they have access to the same code base.
In addition to loading profiler output to the database and running queries against it, the command line utility PLSHPROF creates HTML-based reports (human-readable!). These reports contain various data aggregations to speed up the review process.

This presentation covers a number of real use-cases when HProf significantly shortened response time to production performance problems. You will see how easy it is to figure out the actual source of the slowdown, namely:

Hundreds of thousands of calls to a pretty light user-defined function --> Check execution plans of corresponding queries
Sluggish SQL statement --> Don’t blame PL/SQL and start SQL tracing
Strange third-party calls in wrapped packages --> Collect hard evidence and start complaining

Overall, if you write PL/SQL, you must utilize the PL/SQL Hierarchical Profiler. Otherwise, you will be guessing at your code behavior instead of knowing it, which could lead to unpredictable performance. Unpredictable performance often means production calls at 3 am on Sunday. This presentation will help you sleep longer!