fritshoogland's picture

How long does a logical IO take?

This is a question that I played with for a long time. There have been statements on logical IO performance (“Logical IO is x times faster than Physical IO”), but nobody could answer the question what the actual logical IO time is. Of course you can see part of it in the system and session statistics (v$sysstat/v$sesstat), statistic name “session logical reads”. However, if you divide the number of logical reads by the total time a query took, the logical IO time is too high, because then it assumed all the time the query took was spend on doing logical IO, which obviously is not the case, because there is time spend on parsing, maybe physical IO, etc. Also, when doing that, you calculate an average. Averages are known to hide actual behaviour.

marco's picture

A Database Wordfile…

It is not often that something like the following happens on Google while searching for…

marco's picture

ORA-31145 – “Duplicate listener, %s, found in resource configuration”

Today I had an error on my test environment which surprised me…initially… While I was…

marco's picture

In Memory XML Performance (XVM)

I wouldn’t believe the bad XMLType performance statement given stated in Martin Preiss’ blog post,…

marco's picture

The info in OTHER_XML of view DBA_HIST_SQL_PLAN

I had some time to spend, killing time, and thought about something that was “on…

oakroot's picture

[Oracle] Researching internal latch implementation (ksl_get_shared_latch, kslfre, kslgetsl_w) and crashing PMON


Last week the DOAG 2014 conference took place in Nuremberg and it was a blast with a lot of useful presentations and especially great conversations and meet ups with Oracle friends. I had a nice talk about the Oracle latch implementation with a participant, who told me that his instance crashes every time, if he (manually) sets a shared latch in exclusive mode and tries to release it afterwards. It sounded really interesting as i have done this so many times without ever noticing such an issue. He also told me that this issue is reproducible at least on Oracle 11g R2 and 12c R1. I had no immediate answer or clue about the described issue and needed to research it furthermore.

fritshoogland's picture

Oracle database operating system memory allocation management for PGA – part 2: Oracle 11.2

This is the second part of a series of blogpost on Oracle database PGA usage. See the first part here. The first part described SGA and PGA usage, their distinction (SGA being static, PGA being variable), the problem (no limitation for PGA allocations outside of sort, hash and bitmap memory), a resolution for Oracle 12 (PGA_AGGREGATE_LIMIT), and some specifics about that (it doesn’t look like a very hard limit).

But this leaves out Oracle version 11.2. In reality, the vast majority of the database that I deal with at the time of writing is at version 11.2, and my guess is that this is not just the databases I deal with, but a general tendency. This could change in the coming time with the desupport of Oracle 11.2, however I suspect the installed base of Oracle version 12 to increase gradually and smoothly instead of in a big bang.

fritshoogland's picture

Physical IO on Linux

I posted a fair amount of stuff on how Oracle is generating IOs, and especially large IOs, meaning more than one Oracle block, so > 8KB. This is typically what is happening when the Oracle database is executing a row source which does a full segment scan. Let’s start off with a quiz: what you think Oracle is the maximum IO size the Oracle engine is capable of requesting of the Operating System (so the IO size as can be seen at the SCI (system call interface) layer? If you made up your answer, remember it, and read on!

The real intention of this blogpost is to describe what is going on in the Oracle database kernel, but also what is being done in the Linux kernel. Being a performance specialised Oracle DBA means you have to understand what the operating system does. I often see that it’s of the utmost importance to understand how an IO ends up as a request at the NAS or SAN head, so you understand what a storage admin is talking about.

fritshoogland's picture

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

fritshoogland's picture

Exadata storage indexes and DML

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.

First let’s do a step back and lay some groundwork first. The tests done in this blogpost are done on an actual Exadata (V2 hardware), with Oracle version (meaning bundle patch 6). The Exadata “cellos” (Cell O/S) version is on both the compute nodes and the storage nodes.

To prevent automated spam submissions leave this field empty.
Syndicate content