jump to navigation

Chained Rows, Migrated Rows, Row Pieces – The Basics October 11, 2022

Posted by mwidlake in Architecture.
Tags: , ,
add a comment

For #JoelKallmanDay I wanted to go back to basics and remind people what Chained & Migrated rows are, and something about row pieces.

A simple overview of block structure

First, let’s review how an oracle block is structured. All tables and indexes are made of blocks and they are (by default) all the same size in a given database and the size is stated when you create the database. Blocks are usually 8K (the default), less often 16K and rarely can also be 2K, 4K, or 32K in size. If you want to use a block size other than 8K or 16K, please have a very good, proven reason why before doing so. You can create tablespaces with a block size different to the one stated at database creation, but again you should only do this if you have a very good, proven reason. And I mean a VERY good reason for this one!

(If you have gone the whole CDB/PDB route then PDBs have the same block size as the CDB. You *can* plug in PDBs with different block sizes but check out Mike Deitrich’s post on this first).

At the “top” of the block is the block header, which includes information and access control structures for the rows in the block. Unless you are using Direct Insert, rows are actually inserted from the bottom of the block” upwards, until there is only PCTFREE space available. This is the free space left to cope with row updates and defaults to 10%. PCTFREE is hardly changed these days, which is a shame as it can be very useful to change it for some tables – but that is a different topic.

Why would you use 16K rather than the default of 8K? it is usually used for Data Warehouse database or other database holding long rows. You will generally waste less space within the blocks and avoid row chaining (see later) by having larger blocks, but if you have short rows and an OLTP type database (where you read lots of individual rows) then 16K will be more wasteful of your buffer cache in the SGA and make less efficient use of that memory.

Updating a row, when there is space for it in the block

In the above, row 103 is updated, making the row longer. All the rows are packed together so to do this Oracle can do one of two things. In (1) there is enough space at the “top” of the block to hold the whole row, so it is simply moved there and the pointers in the header updated. If there is not enough space in the “top” of the block but there is within the whole block (2), then Oracle moves Row 3 to the “top” of the block and shuffles the rest.

When a Migrated row is created

However, if you update a row and there is not enough frees space in the block, what does Oracle do? It Migrates the row. It leaves the row header in the original location but that simply holds a pointer (a ROWID) to the new location of the row – which could be in a block a long way away from the original row, even in a different file of the tablespace. All the columns for the row are moved to this new location. As you are changing two blocks there will be even more REDO generated than if the updated row was contained in the PCTFREE area.

Why does Oracle leave the header in place and simply point to the new row location? Well, if the ROWID for the row changes (as it would if the block it is in changes) then if there any indexes referencing that column, those index entries would need to be changed. Also any internal-to-oracle use of ROWIDs would need to be managed. That is extra work for the database to do and it will take more time. Usually people want updates to happen as quickly as possible.

The impact of this is that if you select that row via eg an index, Oracle will find the row header and have to do a second single block read to get the actual row. That’s more (possibly physical) IO and two blocks in the buffer cache not one. So it is slower. Lot s and lots of row migration can slow down eg index ranges scans considerably.

The impact on full table scans of a simple Migrated row is pretty much zero. Oracle is scanning the full table, the actual row will be found in due course no matter where it is in the table (or partition). The header is simply thrown away.

(Note, if you have enabled ROW MOVEMENT for the table then the whole row may be moved and the relevant indexes/internal rowid references updated – but that’s an advanced topic).

Traditional Row Chaining

Row chaining is where a row is to wide to fit into a single block. In this case Oracle has no choice but to create several ROW PIECES. In reality every row consists of a header and ROW PIECE but there is only one and it is the ROW PIECE that is migrated with migrate rows. If the row is larger than the block then the row header and the first piece is put in the first available block. If the row is being created large then the next piece(s) are usually put in the following blocks.

Of course, the row could be absolutely massive and chained over many, many blocks. This is generally an indication that the table design and the physical implementation of the database has not been thought about enough and you should either learn enough to sort it out yourself or find someone who can help you with that, it could well help make your database run more efficiently. I’m retired, don’t come and ask me.

You can easily see that if you are reading this single row, for example via an index lookup, then Oracle is going to have to read as many blocks as the row spans. That’s more IO. This won’t happen if all the columns you want are in the first row piece though – Oracle will stop working through the row pieces once it has all the columns it needs.

This sort of row chaining where the row pieces are in contiguous blocks is not much of an issue during FTS as Oracle will stitch the pieces together in memory.

Of course, if you have updated the row to make it too large to fit in a block, your row may not only be chained but migrated, and the row pieces may not be contiguous, especially if you updated the row over several statements/period of time.

What HAVE you done to this poor row

There can be even worse issues that come with tables with over 255 columns, but that would be for another blog.


Birthday Cake Bake October 6, 2022

Posted by mwidlake in Baking, humour, off-topic.
Tags: , ,

A week or so ago Mrs W and I were watching a TV program called “The Great British Bake Off” – I believe it is also shown in many other countries or they have their own version? Anyway, Mrs W looked at me and said “you better be baking me a cake for my birthday! I want a hedgehog!!!” She was joking, Mrs W knows that cake baking is not one of my skills and the last one I did resulted in something more like a rubber biscuit than a soft, moist sponge cake.

However, I am up for a challenge. How hard could it be? Well…

First, boys and girls, bake your cake!

I knew we had most of the ingredients for baking a basic Victoria Sponge and I picked up the extra bits I thought I might need to do the decoration when I went shopping, so I was good to go. I lined my tins with baking paper, which took almost as much time as making the actual cakes, and I made the sponge mixture. The reason I chose a Victoria sponge is it is almost (but not impossible) to mess up – and it is about the only cake I have cooked before, other than a fruit cake (which I did not have time or inclination to do). The big issue with a Victoria sponge is whether it rises as you cook it.

When I saw it growing in the oven I was very relieved, the last cake I made for Mrs W utterly failed to do that!

I baked two cakes, I wanted one slightly wider than the other and the narrower one higher. This was to make it easier to carve the shape I had in mind. This did mean the cakes cooked at slightly different rates and although I did not open the door until I felt the first one would be ready, I mis-judged and got a little sinkage on both. I could live with that, I knew when I shaped the cakes I would have cut-offs I could fill those dips with.

Carve the general shape.

Now I had to carve the cake into the general shape of a hedgehog. I mixed up some chocolate glaze icing (icing sugar, cocoa powder, much less water than you think) and used that to glue the two cakes together and then stick the chunks I carved for the head together. It’s messy but once I had moved the finished shape onto a clean cake board, it looked good to me and my confidence rose like a fountain of joy. Tell me that does not look like the shape of a hedgehog? (One without feet, admittedly…)

Now, just decorate it!

I had a supply of piping bags and Mrs W’s extensive set of piping nozzles. I’ve even done a bit of icing piping in the past when helping Mrs W or my mum decorate cakes. I’m not bad at it, I say with pride and no modesty at all.

I now mixed up a load of glaze icing and, as I am (well, was) a zoologist, I knew I needed a light brown for the head & shoulders and darker brown for the body. I made both. But because I am (well, was) a zoologist and not in any way a cake baker, I made glaze icing. The clue is in the name. Glaze icing is intend to flow smooth and give a glazed, shiny finish. Any of you who know anything at all about decorating cakes is now shouting at the screen “you need Royal icing for piping you idiot!”. I looked at this gloop, I knew it was NOT what had been in the piping bags I had used in the past. This stuff I had was liquid. I mixed it thicker. It was thicker liquid. This was as likely to pipe into firms shapes as honey is!

A minute of google revealed my mistake. A look at the clock and it told me I had about 30 minutes to get this cake done. I did not have time to whip up egg whites, incorporate the sugar and cocoa, get it wrong at least once and re-make it to provide the royal icing I needed and do the actual decoration. I’ll just see what I could do with the very thick glaze icing I had…

As you can see, it did not go well! I put on some of the glaze icing – and it slid down the cake and puddled on the board. And it dripped everywhere. And the thicker stuff? as I tried to spread it, the icing stuck to the cake and then pulled it apart as I tried to spread the thick blobs wider. The picture on the left is actually after I had scraped off about half a pint of icing from the board and wiped it down. That fountain of joy had splashed down to a become a swamp of despair.

OK, I had Emergency Flakes I had bought. I’ve always regarded the use of flakes or chocolate fingers to make a hedgehog cake a bit of a cheat. I now realised I love cheating.

I cut up some flakes and stuck them on the cake out of desperation.

It was at this point Mrs W came into the kitchen to see what all the clattering & swearing was about. She took one look at the flesh-running zombie hedgehog I had created and laughed in a way I thought was dangerous for her underwear. “What… IS it?” It’s a hedgehog cake. “It’s not!” It’s your birthday, I’m making you a hedgehog cake. She tried to not laugh anymore but it was impossible for her. Oh well…

She took a photo. I knew why. There is a section in the follow-up program to The Great British Bake Off where people send in both their baking masterpieces… and their disasters.

I told her to leave it with me, I had a cunning plan. Sadly I did not have time to get to the shops and buy a cake, but maybe if I just really *believed* I could do this, it might work!

Back from the Brink?

I had lots of flakes. I had about 15 minutes until I would need to take the actual birthday meal I was cooking in the oven (at the same time as creating a zombie horror hedgehog cake) out. I cut and broke up the flakes, slapped on more glaze as glue where needed, picked off bits of cake and gloop, and I got the cake covered.

OK, it was less awful, but I was now out of flakes.

Well, I did what I could to tidy up the glaze icing. I used some final bits of Flake to fill in gaps and re-set some bits. I scraped off the (now fairly set) puddles and wiped off the debris and washed down the board as best I could. And I made some eyes and nostrils out of black fondant.

I have to confess, the final cake (finished as the oven beeped at me to tell me to take the main course out) looked OK – at least to me. What do you think?

By the way (and apologies to John Beresniewicz who had already asked this 2 days ago and was shocked I did let you know the name the hedgehog) – He’s called Harry.