Oracle IO wait events: db file sequential read

(the details are investigated and specific to Oracle’s database implementation on Linux x86_64)

Exadata IO: This event is not used with Exadata storage, ‘cell single block physical read’ is used instead.
Parameters:
p1: file#
p2: block#
p3: blocks

Despite p3 listing the number of blocks, I haven’t seen a db file sequential read event that read more than one block ever. Of course this could change in a newer release.

Implementation:
One of the important things (more...)

Capturing long-running SQL in Statspack

Oracle 11.2.0.1.0

I have been investigating “enq: TM – contention” wait events, which are almost certainly due to un-indexed foreign keys. One such extreme example which showed up in a Statspack report is as follows :

                                                             Avg          (more...)

Complément : Analyse des performances sur du long terme

Scaling up Cardinality Estimates in 12.1.0.2

Topic: Counting the number of distinct values (NDV) for a table column has important applications in the database domain, ranging from query optimization to optimizing reports for large data warehouses. However the legacy SQL method of using SELECT COUNT (DISTINCT <COL>) can be very slow. This is a well known problem and Oracle 12.1.0.2 provides a new function APPROX_COUNT_DISTINCT implemented with a new-generation algorithm to address this issue by providing (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

B-tree and nulls

Today while I was scrolling my “WordPress reader” page I saw a post which reminds me to something I had fun before /and find it useful in many situations/: So if we have an index based on null column followed by not null column we can use it to filter the rows with null value. […]

FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?

June 8, 2014 It has been nearly two years since I last wrote a review of an Oracle Database related book, although I have recently written reviews of two Microsoft Exchange Server 2013 books and a handful of security cameras in the last two years.  My copy of the second edition of the “Troubleshooting Oracle […]

Simora: Alpha Testers Confirmed

It's been a while since I provided any public updates regarding Simora, our Oracle workload simulation product. It's finally time to unveil the status of Simora and our steps moving forwards. We have been working extensively on the Simora engine and infrastructure over the  last several months, with a view to transforming it into a […]

Flame Graphs for Oracle

Topic: This post is a hands-on introduction to using on-CPU Flame Graphs for investigating Oracle workloads. This technique is about collecting and analyzing sampled stack trace data to analyze and troubleshoot Oracle processes at the OS level (in particular applied to Linux).

Motivations: The techniques and tools described here can be used for performance investigations to complement wait-event based information gathered from the Oracle engine, such as information available with ASH and sql monitoring. They (more...)

A Closer Look at CALIBRATE_IO

Topic: This blog entry is about investigating Oracle's DBMS_RESOURCE_MANAGER.CALIBRATE_IO

Spoiler: If you have reached this article in search of a tool for quantitative analysis of storage performance and in particular for measuring random read I/O in Oracle, I'd rather advise you to use tools that allow generating test workloads in a controlled manner, in a way that can be understood and measured and in particular with latency details together with IOPS measurements. For example (more...)

Solaris 11 – IPS Local Repository

A fast and straight approach for creating local IPS repository. CREATING THE REPOSITORY The steps are basic: 1.Download the repository; 2.Concatenate the iso files; 3.Mount the iso; 4.Create the zfs dataset; 5.Create the repository; 6.Copy the repository; 7.Build the search index; 8.Set the repository; 9.Unmount the iso; Code example: Note – pkg set-publisher: -G ‘*’ […]

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)

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...)

Which SQL Server instance is consuming all my CPU/memory?

For anyone running Windows 2008 (or above), you can simply add the “Command Line” column to the Task Manager view. From there, the instance name will follow the “-s” startup option, for example: C:\…\Binn\sqlservr.exe” –sPREPROD If you’re on Windows 2000/2003 then it’s not quite as straight forward. You can either get the Process ID from

Read More...

IT Stockholm Syndrome

Shrugging

Random thoughts on a Friday afternoon…

We’ve all got problems. More to the point, every IT department or team has problems of some kind. It’s why we hire consultants, buy products, start long and arduous journeys into the great unknown depths of root cause analysis, and so on.

What fascinates me is the level at which we come to identify with our problems. When I’ve gone into an environment to deliver recommendations, the conversation usually (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...)

Direct Path Reads and Cell Offloading

Most people are relating direct path reads with an algorithm which is just controlling the way our read is performed. But actually in Exadata environment this is the algorithm which is balancing the load between the Compute and the Storage nodes. Something really important. As usual, the algorithm is not perfect and for some situations […]

Averages

Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.

Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):

Workload Comparison
~~~~~~~~~~~~~~~~~~~                   1st Per Sec          2nd Per Sec      %Diff              1st Per Txn          2nd Per Txn      %Diff
                                  ---------------      ---------------     ------          ---------------      ---------------     ------
                    DB time:                  0.6                  0.6        1.6                      0.0                  0.0      -50.0
                   CPU time:                  0.3                  0. (more...)

How to use index to get NULL values

I have learned something new today and this blog post will be (primary) reminder to myself. I am sure that most Oracle DBA’s or Developers are familiar with this trick - so please skip this post if you are one of them :)


My test table will have 1000000 rows with 1000 NULL values for “OBJECT_NAME” column.

Tests are performed on 11gR1 version.

Table/column info:

select c.table_name, c.column_name, c.data_type, c.num_nulls,
t. (more...)