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 184.108.40.206) 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...)
GV$ views of the 'metrics family
' and how they can help in tuning + links to scripts I use.
Oracle has plenty of instrumentation, statistics counters and wait event data are the bread and butter of Oracle monitoring and tuning.
However counters are typically incremented during the life of the instances/sessions, so delta values
are often needed to make sense of data for analysis. AWR reports are a way to do that for instance-wide data.
A class of GV$ views that can be of help for faster/ 'online' monitoring are the V$ views that I call of (more...)
This is part 2/2 of a performance investigation. In the previous part we have seen how the sudden increase of single block read latency would harm performance of the production database and how this would appear consistently when taking backups. Drill down questions:
what causes the high latency for single block reads during backup? Why do the sequential IO of the backup causes random reads to slow down? Are disks saturating? Is it an Oracle issue or more a storage issue? Finally, what can be done about it?Oracle event histogram:
Oracle instrumentation of wait events provides additional information (more...)