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...)
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
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...)
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...)
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 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...)
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
Oracle (since version 11gR1) exposes (more...)
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 GuardMotivations:
Imagine this scenario: a production system has two standbys to protect against disaster and to load balance read-only load (with (more...)
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...)
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...)
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...)
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...)
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 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...)
a discussion on the usage of Kerberos authentication in Oracle and of the usage of proxy users together with Kerberos authenticated accountsIntroduction: 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...)
Discussion and implementation of a method for finding hash collisions for sql_id and SQL signature in Oracle.Introduction:
MD5 hashing is used by Oracle to compute sql_id and SQL signature (see also a previous blog post
). Those hash values are normally used as if they were a unique representation of a given SQL statement. However collisions (2 different statements with the same hash value) can happen or, as it is the case of this post, will happen!The underlying math (
a short discussion on birthday attack
). Let's say you walk into a room an meet 30 people: (more...)
A discussion on how Oracle computes SQL signature using MD5 hashing and on how SQL text is normalized before computing SQL signatures.
Introduction and warm-up:
SQL statements that are cached in the library cache are associated to a hash value, which is visible in various forms across a few V$ views. In 11gR2 for example: V$DB_OBJECT_CACHE.FULL_HASH_VALUE, V$SQL.SQL_ID, V$SQL.HASH_VALUE.
From the work of Tanel
we learn that the full hash is an md5 hash in hexadecimal, sql_id is a base-32 representation of the trailing 8 bytes of the same md5 hash and finally the (more...)
Discussion on how to create sql_patch with force_match=true in Oracle 11g and related topics.
SQL patches have recently saved the day for me in a production issue where a given SQL had suddenly changed execution plan causing IO overload (a full scan was done instead of index-based read for a high-load statement). 11g allows for a quick fix in such situations (as in, stop the fire and buy time to find a more stable solution): a set of hints can be added to a given query via the use of SQL Patch.
The official Oracle documentation has not many details (more...)
does not exist (at least up to 220.127.116.11) however it could be handy! The idea is combine the detailed information from event histograms and the ease of use of metric views. Here below an example of how this can be implemented with a sample script and a reference to the code of the script.Example:
Measure latency for single block reads for an OLTP database, as exposed by Oracle wait event interface as 'db file sequential read'. This is used to investigate a case of performance degradation where storage behaviour is involved. Step 1:
collect GV$EVENT_HISTOGRAM (more...)