Internals

tanelpoder's picture

Full scans, direct path reads and ORA-8103 error hacking session video here (plus iTunes podcast address!)

I have uploaded the latest hacking session video to blip.tv. I have edited it a little, I cut out the part where I spilled an entire Red Bull onto my desk, with some onto my laptop (some keys are still sticky:)

Also, I do upload all these sessins into iTunes – so you can subscribe to my podcast! That way you can download the videos into your computer, phone or iPad. I have deliberately used 1024×768 resolution so it would look awesome on iPad screen! (so hopefully your commute time gets a bit more fun now ;-)

 

Enjoy!

iTunes video-podcast:

 

 

tanelpoder's picture

Secret hacking session – full scans, direct path reads, object level checkpoints, ORA-8103s! (again)

I’m mentioning this again just in case you missed the announcement (because I posted it on the weekend):

 

There will be anotner free Secret hacking session – about full scans, direct path reads, object level checkpoints, ORA-8103s!

It will happen tomorrow, Tuesday 9th August, online!

 

Register here:

See you soon!

 

tanelpoder's picture

Training Schedule for 2011 and Public Appearances

Online Seminars
A lot of people have asked me about whether I’d be doing any more seminars in the future. And the answer is yes – at least this year (might be too busy running a company the next year ;-)
I have finally put together the schedule for my 2011 seminars. In addition to the Advanced Oracle Troubleshooting seminar I will also deliver my Advanced Oracle SQL Tuning and Oracle Partitioning and Parallel Execution for Performance seminars, which I have done only onsite in past.
So, check out the seminars page:
Also don’t forget the Expert Oracle Exadata virtual conference next week!
Public Appearances

Oracle OpenWorld 2. October
  • I will talk about Large-Scale Consolidation onto Oracle Exadata: Planning, Execution, and Validation
  • Session ID 09355
Maybe I’ll lurk around the UKOUG venue as well in december ;-)

mwidlake's picture

Index Organized Tables – the Basics.

IOT2 – Examples and proofs..>
IOT3 – Greatly reducing IO with IOTs….>
IOT4 – Boosting Buffer Cache Efficiency……>

I think Index Organized Tables(IOTs) are a much under-used and yet very useful feature of Oracle. Over the next few postings I’m going to cover some aspect of Index Organised Tables, both good and not-so-good. I am going to cover some benefits of IOTs that I think many people are unaware of. In this first post I am just going to run through the basics of IOTs.

The idea behind an IOT is simple. You hold all the data for the table in the ordered structure of an index. Why would you want to do that? Let us consider a very common requirement, accessing a row in a “large” table via a known, unique key.

Traditionally you have a heap table holding the data you want to access and a standard index to support access to that table. See the first diagram below. The 4-layer triangle represents the index, with a root block, two levels of branch blocks and then the leaf blocks at the “bottom”. The blue rectangle represents the table with the squares being individual rows. Of course, in a large table there would be thousands or millions of “squares”, this is just a simple diagram to show the idea.

When you issue a SQL statement to select the row via the indexed column(s) then oracle will read the root block (1), find the relevent block in the first level of branch blocks (2), then the relevant block in the second level of branch blocks (3) and finally (as far as the index is concerned) the relevant Leaf Block for the unique key. The leaf block holds the indexed column(s) and also the rowid. The rowid is the fastest way to look up a record, it states the file, block and row offset for the row. This allows oracle to go straight to the block and get the row. That is read number (5).
The number of branch blocks {and thus the number of blocks that need to be read to find a row} will vary depending on how much data is indexed, the number and size of the columns in the index, how efficiently the space has been used in the blocks and one or two other factors. In my experience most indexes for tables with thousands or millions of rows have one, two or three levels of branch blocks.

The second diagram shows a representation of the Index Organized Table. The table has in effect disappeared as a distinct object and the information has been moved into the leaf blocks of the index {part of me feels Index Organized Tables should really be called Table Organized Indexes or Table Containing Indexes as that would better indicate what is physically done}:

So with the IOT oracle reads the root block (1), the two branch level blocks (2 and 3) and finally the leaf block (4). The leaf block does not hold the rowid but rather the rest of the columns for the table {this can be changed, a more advanced feature allows you to store some or all the extra columns in an overflow segment}. Thus to access the same data, Oracle has to read only 4 blocks, not 5. Using an IOT saves one block read per unique lookup.

This saving of block reads is probably the main feature that IOTs are known for, but there are others which I will cover in later posts. Two things I will mention now is that, firstly, the use of IOTs is potentially saving disc space. An index is in effect duplication of data held in the table. When you create an index no new information is created but space is used up holding some of the table information in a structure suitable for fast lookup. Secondly, the index and table have to be maintained whenever a change is made to the columns that are indexed. IOTs reduce this maintenance overhead as there is only one thing to maintain.

Now for some drawbacks.

  • The IOT has to be indexed on the primary key. There is no option to create an IOT based on other indexes. As such you have to either be accessing the table via the primary key to get the benefit – or you have to be a little cunning.
  • The index is going to be larger than it was and very often larger than the original table. This can slow down range scans or full scans of the index and a “full table scan” will now be a full index scan on this large object, so that can also negatively impact performance. However, if a range scan would then have resulted in access to the table to get extra columns, the IOT gives a similar benefit in reducing IO to that for single row lookups.
  • I just want to highlight that you now have no rowid for the rows.
  • Secondary indexes are supported but will potentially be less efficient due to this lack of rowid.

So, a brief summary is that Index Organised Tables effectively move the table data into the Primary Key index, reduce the number of block lookups needed to select one row, can save some disc space. But you can only organize the table via the Primary Key and it can make full or partial table scans and lookups via other indexes slower.

There are several more important benefits to IOTs {in my opinion} which I will come to over the next week or two.

tanelpoder's picture

RAC hack!

In other words – FREE STUFF!!!

Riyaj Shamsudeen does a free RAC hacking session on 12 July!

He will demonstrate how the LMS background process works, with the help of OS tracing tools like truss and DTrace.

Sign up here!

 

Update:

A video recording of the session can be found here: http://www.vimeo.com/26798681

 

 

tanelpoder's picture

What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

There was a very good question asked in Oracle-L list today, which has bothered me too in past.
The question was:
What is the purpose of a segment level checkpoint before DROP/TRUNCATE of a table?
 
In other words, why do we have to wait for the enq: RO – fast object reuse wait event (and in 11.2 the enq: CR – block range reuse ckpt wait) when dropping & truncating segments?
 
I’m not fully confident that I know all the real reasons behind this, but it could be related to the need to get rid of segment’s dirty buffers in buffer cache, before dropping the object.
 
Imagine this:
 
  • You have a large buffer cache and you drop table A without checkpointing the dirty buffers.  
  • Immediately after the drop succeeds (some buffers are still dirty in cache) some other segment (table B) reuses that space for itself and writes stuff into it.
  • A few seconds later, DBWR wakes up to find & write some dirty buffers to disk (anything it finds from its lists). As there are some old & dirty blocks of table A still in the cache, they get written to disk too, overwriting some of the new table B blocks!

  

So, this is one reason why you should checkpoint the blocks to disk before dropping (or truncating) a segment. Of course you might ask that why doesn’t DBWR just check whether the dirty buffer is part of an existing object or a dropped one when it walks through its dirty list? It could just discard the dirty buffers of dropped objects it finds. It would be doable – but I also think it would get quite complex. DBWR is a low level background proces, understanding the cache layer and dealing with physical datablocks in a file# = X block offset = Y. It doesn’t really know anything about the segments/objects which use these blocks. If it should start checking for logical existence of an object, it would have to start running code to access (a much higher level concept) data dictionary cache – and possibly query data dictionary tables via recursive calls, etc, so making it much more complicated.
 
So, this logic may just be matter of implementation, it’d be too complex to implement such selective discarding of dirty buffers, based on a higher-level concept of existence of a segment or object. Dropping and truncating tables so frequently, that these waits become a serious problem (consuming significant % of response time) indicate a design problem anyway. For example, former SQL server developers creating multiple temporary tables in Oracle – for breaking a complex query down into smaller parts, just like they had been doing it in SQL Server.
 
Anyway, here’s what I think about this – I’d love to hear other opinions, if you think otherwise!

tanelpoder's picture

Advanced RAC Training by Oracle RAC expert Riyaj Shamsudeen

If you’ve troubleshooted (or tuned) RAC then you probably already know Riyaj Shamsudeen and his Orainternals blog & website (links below).

Anyway, since I started delivering my Advanced Oracle Troubleshooting classes some years ago, many people asked whether I would do a similar class for RAC. I had deliberately left out the RAC-specific stuff from my troubleshooting material, because it’s a very wide and complex topic and I feel like before trying to master RAC troubleshooting, you should master troubleshooting of regular single instance databases anyway. I realized that I didn’t have the time to build (and maintain) yet another set of trainig material, especially on so complex topic as RAC performance & troubleshooting. 

So, having seen Riyaj’s impressive work and his presentations at various conferences, I asked whether he would be interested in building a RAC troubleshooting class, going from fundamentals to advanced topics – and he said yes. By now we are that far that I’m happy to announce the first Advanced RAC online seminars by Riyaj Shamsudeen (split across two weeks of online sessions, 4-hours per day, in end of august and september).

We initially called the seminar “Advanced RAC Troubleshooting” but then realized, that there are some closely related non-troubleshooting topics to be covered, like fundamental concepts, internals and also how to configure RAC for performance (so that you wouldn’t have to troubleshoot performance later :-)

We’ll use the same infrastructure and seminar philosophy as I do in my own online seminars, it’s just that this is Riyaj’s material and he will deliver it too.

You can read more about the seminar content, dates and sign up at the seminars page:

Seminars:

Riyaj’s blog:

Riyaj’s website (articles, slides etc):

 

Let the RAC hacking begin! ;-)

 

tanelpoder's picture

Tech Reviewer, Tech Reviewer! ;-)

I just noticed that Jonathan Lewis has announced that he’s writing a new Oracle (fundamental) internals book, due to be out in November.

So, I’m happy to add to Jonathan’s announcement, that I’m the tech reviewer of that book!

After all the hard work on the Exadata book, I didn’t want to hear about working on any book again (even if it’s just tech reviewing work), but as this is Jonathan’s book, about exactly these topics I love and focus on, I had no choice but to make an exception and become a reviewer ;-)

I’ve already reviewed a couple of chapters and this book is going to be awesome!

 

tanelpoder's picture

Reminder and Public Appearances 2011

First, a reminder – my Advanced Oracle Troubleshooting v2.0 online seminar starts next week already. Last chance to sign up, I can accept registrations until Sunday :-)

I won’t do another AOT seminar before Oct (or Nov) this year. More details and sign-up here:

I have rescheduled my Advanced SQL Tuning and Partitioning & Parallel Execution for Performance seminars too. I will do them in September/October. Unfortunately I’m too busy right now to do them before the summer.

Public Appearances:

  • I will be speaking at the UKOUG Exadata Special Event in London on 18th April
  • I have submitted a few papers for Oracle OpenWorld in San Francisco as well (end of Sep/beginning of Oct), all about Exadata. Let’s see how it goes, but I’ll be there anyway, which means that I’ll probably show up at the Oracle Closed World event too!

And that’s all the travel I will do this year…

Virtual Conferences:

I’ll soon announce the 2nd EsSN virtual conference too ;-)

Free online stuff:

Perhaps in a month or so I will do another hacking session (I’ll plan 2 hours this time, 1 hour isn’t nearly enough for going deep). The topic will probably be about low-level details of SQL plan execution internals… stay tuned!

tanelpoder's picture

Oracle Troubleshooting TV Show: Season 1, Episode 01 ;-)

Ok, it’s official – the first and only Oracle Troubleshooting TV show is live now!

The first show is almost 2 hours about the ORA-4031 errors and shared pool hacking. It’s a recording of the US/EMEA timezone online hacking session I did some days ago.

There are a couple of things to note:

  1. The text still isn’t as sharp as in the original recording, but it’s much better than in my previous upload attempts and is decently readable. I’ll try some more variations with my next shows so I hope the text quality will get better! Or maybe I should just switch to GUI tools or powerpoint slides? ;-)
  2. You probably should view this video in full screen (otherwise the text will be tiny and unreadable)
  3. There’s advertising in the beginning (and maybe end) of this show! I’ll see how much money I’ll make out of this – maybe these shows start contributing towards the awesome beer selection I’ll have in my fridge some day (right now I have none). Viewing a 30-sec advert is small price to pay for 2 hours of kick-ass shared pool hacking content !!!
  4. You can download the scripts and tools used in the demos from http://tech.e2sn.com/oracle-scripts-and-tools/
  5. Make sure you check out my online Oracle troubleshooting seminars too (this April and May already)

View the embedded video below or go to my official Oracle Troubleshooting TV show channel:

http://tanelpoder.blip.tv

Enjoy!

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