Recent Updates of OraLatencyMap and PyLatencyMap

Topic: This post introduces the latest changes to OraLatencyMap and PyLatencyMap, two custom tools for collecting and displaying Oracle wait event latency details using heatmaps.

OraLatencyMap is a SQL*Plus tool, with a core written in PL/SQL, aimed at studying Oracle random I/O by displaying the latency drill-down of the wait event 'db file sequential read' using heatmaps. The tool can also be used to collect and display event latency histograms for any other Oracle wait (more...)

Wait Event History Sampling, an Experiment in Oracle Performance Analysis

Topic: This post is about a performance analysis technique based on high frequency sampling of wait event history data in Oracle. Two scripts are provided for performing this type of analysis and two example cases are discussed applied to the study random read latency and read workload characterization.

Context: In the context of performance analysis, for example for a study of I/O response time, I want to analyze the flow of all wait events for (more...)

Clusterware 12c and Restricted Service Registration for RAC

Topic: This post is about exploring the mechanisms used by Oracle Clusterware 12.1.0.1 to restrict remote service registration, i.e. the 12c new feature "Restricting Service Registration for Oracle RAC Deployments"

Why is this useful? This improvement of 12c clusterware and listeners over the 11.2 version is useful mainly for security purposes, for example as a measure against TNS poisoning attacks (see also CVE-2012-1675), and it is particularly relevant for (more...)

How to Recover Files from a Dropped ASM Disk Group

Topic: This post describes a few tips and techniques on how to recover ASM files from a dropped disk group and an example of how to 'undrop' an ASM disk group

Context: This research stems from a recent case I have investigated where the data disk group of a test (more...)

UKOUG Tech13, Latency Sudies and Lost Writes

I am looking forward to participating again to the UKOUG annual conference. This year I will present together with my colleague Marcin Blaszczyk on two topics that are follow-up of articles in this blog.
The first talk is: Storage Latency for Oracle DBAs. A presentation about a performance tuning techniques for (more...)

Daylight Saving Time Change and AWR Data Mining

Topic: this article is about a tip for Oracle 11.2 and higher on how to handle DST change when querying directly the AWR repository. In addition a few comments on Perfsheet4, a tool to extract and plot AWR data in Excel.

Introduction: Statspack and more recently AWR reports, (more...)

Getting Started with PyLatencyMap: Latency Heat Maps for Oracle and DTrace

Topic: this is a getting-started article for PyLatencyMap, a performance tool for real-time investigation and troubleshooting of latency data. Contains examples relevant for the Oracle RDBMS and for DTrace.

Getting started

Download the latest version of PyLatencyMap (also available on GitHub). At the time of this writing PyLatencyMap (more...)

PyLatencyMap, a Performance Tool for Latency Data Visualization

Topic: this post is about PyLatencyMap, a performance-oriented tool I have written for command-line drill down of latency data, integrating various data sources (Oracle, DTrace, trace files) into a basic visualization engine.

Motivations: Understanding a performance problem is often about understanding where time is spent. Many of the systems I (more...)

Dtrace Explorations of Oracle Wait Events on Linux

DTrace is a great tool to measure and investigate latency for performance troubleshooting. DTrace is now coming to the Linux platform too and I would like to share a few tests I did with it. The following is meant to be a short technology exploration but hopefully it can also highlight (more...)

OraLatencyMap v1.1 and Testing with SLOB 2

Topic: OraLatencyMap v1.1 is an updated versions of a performance tool aimed at collecting and displaying Oracle wait event histogram data as latency heat maps. We will also briefly discuss an example of the usage of OraLatencyMap in the context of storage testing.

OraLatencyMap v1.1 is now available (more...)

Latency Heat Maps in SQL*plus with OraLatencyMap

Topic: This post is about the use of heat maps to investigate wait event latency in Oracle (and in particular I/O-related latency). This post also discusses a SQL*plus-based script/tool I have developed to help with this type of monitoring and performance drill-down (OraLatencyMap).

Context: Oracle (since version 11gR1) exposes (more...)

Testing Lost Writes in Oracle and with Data Guard

Topic: This post is about lost writes in Oracle and in particular on techniques to reproduce and investigate the effects of lost writes in a test environment and with Data Guard

Motivations: Imagine this scenario: a production system has two standbys to protect against disaster and to load balance read-only load (with (more...)

AWR Analytics and Oracle Performance Visualization with PerfSheet4

Topic: This post describes PerfSheet4, a tool for performance analysis aimed at streamlining access and visualization of Oracle's AWR data. The tool is aimed at DBAs and Oracle performance analysts. PerfSheet4 is a spin off of previous original work by Tanel Poder, rewritten and integrated with additional functionality for AWR analysis and with important changes to the user interface.

Context: There is much information in the counters and metrics of Oracle's AWR that can be of substantial help for troubleshooting and for capacity planning. Besides the standard AWR report, time-based analysis is often very useful. However this type of access is (more...)

Active Data Guard and UKOUG 2012

I am looking forward to participating again to the UKOUG annual conference (and also to attend the Sunday's OakTable event). This is for me a great opportunity to meet and discuss with many passionate Oracle experts who regularly attend the conference and also to get up-to-date with the latest technology and news from the Oracle community.
My colleague Marcin and I have just finished preparing our presentation on the subject of Active Data Guard. I have enjoyed the work even though, as it is often the case, this has taken quite some effort from both of us to reach the level of details (more...)

On Command-Line DBA

Topic: A review of the command-line monitoring scripts that I currently use for Oracle.

Command-line is still very useful for Oracle database administration, troubleshooting and performance monitoring. One of the first thins that I do when I want to work with a given Oracle database is to connect as a privileged user with Sql*plus and get information on the active sessions with a script. I will run the script a few times to get an idea of what's happening 'right now' and get a possible starting point for more systematic tuning/troubleshooting if needed.
I liken this approach to examining a few pictures from a remote (more...)

How to Turn Off Adaptive Cursor Sharing, Cardinality Feedback and Serial Direct Read

Scope: New features and improvements on the SQL (cost-based) execution engine are great. However sometimes the need comes to turn some of those new features off. Maybe it's because of a bug that has appeared or simply because we just want consistency, especially after an upgrade. This is often the case when upgrading an application that has been tuned already with heavy usage of hints for critical SQL.The discussion of pros and cons of using hints is a very interesting topic but outside the scope of this entry.

When this is relevant: using a full set of hints (an (more...)

Recursive Subquery Factoring, Oracle SQL and Physics

Introduction: this post  is about the use of recursive subquery factoring in Oracle to find numerical solutions to the equations of classical mechanics, for demonstration purposes. This technique will be introduced using 4 examples or growing complexity. In particular by the end of the post we will be able to compute the length of the (sideral) year using the laws of physics and SQL!

Recursive subquery factoring (aka recursive common table expressions) is a new feature of Oracle 11gR2 that has already been used for businness and for play: see this post on solving sudokus with Oracle and this (more...)

Listener.ora and Oraagent in RAC 11gR2

Topic: An investigation of a few details of the implementation of listeners in 11gR2, including the configuration of listener.ora in RAC and the role of the cluster process 'oraagent'.

11gR2 comes with several important changes and improvements to the clusterware in general and in particular the way listeners are managed. While the listener process is still the 'good old' process  tnslsnr (Linux and Unix), it is now started from the grid home (as opposed to database oracle home). Moreover listeners are divided in two classes: node listeners and scan listeners, although they use the same binary for (more...)

Purging Cursors From the Library Cache Using Full_hash_value

Introduction: Purging cursors from the library cache is a useful technique to keep handy for troubleshooting. Oracle has introduced a procedure call to do that in version 11 with backports to 10g. Besides Oracle documentation, this has been covered by several blogs already (including Kerry Osborne, Harald van Breederode, Martin Widlake, Martin Bach), Oracle support (note 457309.1 for example) and the actual package file in $ORACLE_HOME/rdbms/admin/dbmspool.sql

Most of the examples and discussions in the links above utilize with the following syntax:

SQL> exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,'c’)


What's new in 11.2:

A new (overloaded) procedure (more...)

Kerberos Authentication and Proxy Users

Topic: a discussion on the usage of Kerberos authentication in Oracle and of the usage of proxy users together with Kerberos authenticated accounts

Introduction: Kerberos authentication allows to connect to Oracle without specifying the username/password credentials. The authentication is done externally. Kerberos has a widespread usais in use already in large environments so is a good candidate (for example for windows domain accounts or for an afs file system in Linux).
Proxy authentication allows connect to the DB to a target user via another DB user (the proxy user). For example we can authorize a user with a development account to connect (more...)