I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.
The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (more...)
This post is about tracing logical and physical reads in Oracle using systemtap. You will find a few examples illustrating the main mechanisms used by Oracle to do physical and logical I/O and you will learn how to build systemtap scripts to further explore and troubleshoot Oracle I/O.Introduction:
Performing input/output to and from the storage media, i.e. physical I/O, is one of the critical tasks underlying all database engines. When accessing data (more...)
There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy. A little of this emotion seems to […]
Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in 126.96.36.199 they did. For introduction to DPR, you can check my previous post, because […]
This post is about using SystemTap
for investigating and troubleshooting Oracle RDBMS. In particular you will learn how to probe Oracle
processes and their userspace
functions. These techniques aim to be useful
as well as fun
to learn for those keen into peeking under the hood
of the technology and improve
their effectiveness in troubleshooting and performance
is a very powerful technique that can be used to complement the available (more...)
In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think that this one, is one of them. Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit […]
When trying to understand why the optimiser might choose not to use an index to read rows from a table, I find the following diagram helpful.
The axis along the bottom represents the selectivity of the query (0% being no rows selected, 100% being all rows selected), and the axis up the side represents the cost of the chosen operation (more costly = more work to do).
When reading from a table without using an (more...)
Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 188.8.131.52 – results may vary on other versions.
A typical deadlock graph arising from an unindexed foreign key is as follows.
Resource Name process session holds waits process session holds waits
TM-0000508a-00000000 27 101 SX 28 12 SSX
TX-00090013-0000019b 28 12 X 27 101 S
This type of (more...)
(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.
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.
One of the important things (more...)
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 :
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 184.108.40.206 provides a new function APPROX_COUNT_DISTINCT implemented with a new-generation algorithm to address this issue by providing (more...)
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...)
Last week I’ve gotten a question on how storage indexes (SI) behave when the table for which the SI is holding data is changed. Based on logical reasoning, it can be two things: the SI is invalidated because the data it’s holding is changed, or the SI is updated to reflect the change. Think about this for yourself, and pick a choice. I would love to hear if you did choose the correct one.
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...)
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. […]
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 […]
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 […]