Use the innovative Dynamic Oracle Performance Analytics (DOPA) approach that relies on big data and advanced analytical techniques to analyze and improve Oracle Database performance. The approach discussed in this presentation is accessible and easy to use, but more importantly it represents a step-change paradigm shift away from traditional methods. Instead of relying on a few hand-picked, favorite metrics, or wading through multiple specialized tables of information such as those found in an automatic workload repository (AWR) report, you can draw on all available data, applying big data methods and analytical techniques to help you draw impactful, focused performance improvement conclusions.   The DOPA process targets the most relevant metrics for the problem interval which results in event focused and actionable intelligence on the performance issue, and insight into possible solutions.  This method improves on the typical/traditional approaches which monitor a standard set of hard coded metrics; but the traditional approaches may cause you to miss important system behavior or configuration that is relevant to the root cause of the performance problem.  The speaker will share his DOPA process and code.

One of the most common SQL Tuning problems is when the Oracle cost-based optimizer misestimates the expected number of rows that will be returned from an execution plan row source operation. This presentation delves into uncharted waters to help attendees understand strategies for dealing with cardinality misestimates.

Transform your ability to quickly respond to the challenge of ensuring high performing DB’s. The key to this transformation is to expertly use the advisor framework which underlies tools such as ADDM, SQL Tuning Advisor, Segment Tuning.  These tools provide actionable advice to the DBA seeking to solve performance problems, but getting at this information can be painstaking.  This presentation shows you many undocumented ways to proficiently use the underlying data to quickly get to tuning solutions.

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…

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…

  Developers often need their DBA’s to provide quicker identification and analysis on sub-optimal SQLs during load test cycles and peak production usage of the application. This becomes crucial, especially when the load test cycles are denser and there are relatively huge number of SQL statements in the entire application. Effective performance management and diagnosis…

AWR collects and persists thousands of performance metrics every hour; the problem is: the root causes of performance anomalies are difficult to detect and it is difficult to know which metrics/attributes are important for DBA’s to focus on to inform their root cause analysis and solutions. A full understanding of these thousands of metrics is impossible, thus many DBA’s (and even the off-the shelf tools) monitor only a standard set of well-known metrics. This “small model” approach may cause you to miss important system behavior or configuration that is relevant to the root cause of the performance problem. The presenters approach to this “small model” meta problem is to massively expand and dynamically extract only the relevant performance metrics. This paradigm shift is achieved by normalizing the data and looking across a wide array of AWR performance metrics gathered during the problem interval. Querying across the normalized data and using statistical analysis, this approach flags unusual trends. By targeting the right metrics at the right time, you “bundle” relevant results which results in event focused and actionable intelligence on the performance issue, and a richer insight into possible solutions.

ADDM* regularly runs behind the scenes and persists various performance tuning solutions that often go unnoticed by the DBA. Since DB performance problems can escalate quickly, the use of these persisted solutions can transform the DBA’s ability to skillfully devise a tuning action plan. To leverage these preexisting solutions, the presenter “reverse engineered” the advisor framework and developed some SQL queries that will help the DBA evaluate the solutions and extract the details and/or SQL needed to implement the solutions. This presentation takes a deep dive into the various ADDM tools, and details these custom queries against ADDM base data. Every DBA will leave this presentation with more confidence, new tools and approaches for solving Oracle database application performance problems. *ADDM (Automated Performance Diagnostics and Monitoring) is a key component in Oracle’s implementation of a self-managing database [part of the Diagnostics and Tuning pack license]. Within the persisted ADDM data, one will find solutions from automatic runs of the SQL Tuning Advisor , Segment Tuning Advisor, hourly ADDM runs, and more.

The time-based analysis of SQL performance issues is taught and lived actively throughout the database world. It proved its value many times, and is often a good way to solve problems. But with only little or no knowledge of the application behind, performance analysts have difficulties monitoring the timing behavior of software components in a reliable way. Especially within an Oracle execution plan, the measured execution times depend on too many factors and variables. Buffer Gets instead, are easy to count and a good "Unit Of Work" to hunt for.

The aim of this lecture is to show, how to analyze execution plans with very small knowledge about the application, and with on-board utilities of the Oracle Database. My way to identify big resource consumers is shown also, but the clear focus will stay on the analysis of the plan itself.

I will also talk about techniques, how to help misled Oracle features "back on track", always with the objective NOT to limit the dynamics in the Cost-Based Optimizer.

Original Broadcast: September 20, 2017 Be a hero with your DBA! Learn how to identify database key performance indicators, classify relevant workloads, and analyze AWR/STATSPACK reports to make meaningful recommendations for what is needed in your database system infrastructure to meet your performance service-level objectives. The methods discussed will apply equally to on-premises and cloud based infrastructure.…