Reducing work done by the database is key to improving performance in Oracle-intensive applications. The Server Result Cache allows applications to virtually eliminate work done when processing repeated queries. For applications dealing with growing data sets, this ability can be very beneficial. The presentation will begin by focusing on SQL Query Result Cache and PL/SQL…

In this session, Blaine Carter will demonstrate how to REST enable your database using Oracle Rest Data Services (ORDS). He will demonstrate how to use SQL Developer to create and deploy REST services in ORDS. First Blaine will "Auto Rest" enable a table and a PL/SQL package.  Then he will secure the API and create…

The most interesting new feature Oracle 18c introduces to Developers are Polymorphic Table Functions and Qualified Expressions. Polymorphic Table Functions finally allow the Developer to define the structure of the returned records at runtime. Qualified Expressions allows defining array- or record constructors that assign all values in a single statement. What looks at first sight…

Learn how to easily run SQL, PL/SQL, and shell scripts on a large number of databases and hosts.  The open source program Method5 provides agentless remote execution, built entirely inside Oracle.  Remote execution lets us find, fix, and prevent problems that could never be solved one-database-at-a-time.  Querying and controlling all our databases starts with this…

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.

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!