There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):
where a_date_col_with_an_fbi = :a_date
and oracle_version >= '184.108.40.206';
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | 1 |
|* 1 | TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS | 1 |
|* 2 | INDEX RANGE SCAN | QUWF_DATE_FBI | (more...)
This blog entry is about investigating Oracle's DBMS_RESOURCE_MANAGER.CALIBRATE_IOSpoiler:
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...)
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 ‘*’ […]
So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.
So are joins always “good”?
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...)
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...)
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
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...)
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.
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
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 […]
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):
~~~~~~~~~~~~~~~~~~~ 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...)
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.
select c.table_name, c.column_name, c.data_type, c.num_nulls,
If you try to find out what is HCC and how it works you could start reading the documentation, then some books, blog posts and at the end you will have to put all together. In this post I’ll do exactly this. Put all together. Starting with the basic and going through the internals with […]
When you execute an SQL – why there is a difference in Consistent gets on the same set of data for same SQL. For any SELECT query, oracle need to prepare the consistent data in the buffer cache using undo records then forward the data the requesting session as of a specific SCN. Touching any block in buffer cache to prepare the blocks for consistent data is known as Consistent Reads.
In an (more...)
In the Oracle technical universe, it seems that the end of the calendar year is always eventful. First there’s OpenWorld: obviously significant for official announcements and insight into Oracle’s strategy. It’s also the week when many top engineers around the world meet up in San Francisco to catch up over (more...)
Recently I received an email with an simple content:
"It" is slow again :-)
The email explained a package to be slow.
Ok, I started with my "not enough information" reply and an email conversation began.
We are still not ready to identify the problem, but as there is no (more...)
A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post
includes links to video tutorials explaining what the tool is about.As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
It's just under a week to go before the doors open for the UKOUG Tech13 conference and the adjoining OakTable World UK 2013 sessions, so I thought I would write a very short blog post about what I will be doing there, where I'll be, and what I'm looking forward (more...)
Here are two interesting enhancements in Oracle database 12c PL/SQL. PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can (more...)
Today was speaking at HighLoad++ 2013 Moscow. I had two presentation covering deep internals of JVM. One about JIT compilation and other concerning pauseless garbage collection algorithms.
Slide decks are below (in Russian)