Redolog Contention associated to Redo Sizing and Number ... and Numbers in Oracle

Last post about this was a little philosophical, so gets back to the numbers, let's put a few initial conditions to make numbers easily read.

We'll have a simple, 10G insert in a non indexed table (to simplify) , a db_block of 8192 (most common), and .....

500 datafiles on the database these seems to be disconnected, but as you'll see it's deeply interconnected with the redo log processing and therefore with the transactions performance of the database.

Condiciones:

As everything goes thru Undo .. then we duplicate our 10G, and later we'll talk of archived logs , let's keep that there by the moment.

Let's put the numbers and talk a little about them.

Why there's a IO difference ? , every redo switch (when we move from one redo to the following one) triggers a few actions

one of them  : a process to write a block on every datafile to record that "we are there", of course this should wait til the changes contained on the datafiles are effectively written to the datafile, 

these waits won't add immediately as processes are asynchronous (everybody continues doing it's stuff as much as possible) but with few redo groups, eventually you have all of them "active" trying to catch up between the headers and the datafile writes, Oracle have a process CKPT dedicated to this coordination, 

anyway

point 1:

 the more datafiles, the more headers to write, the smaller the redologs the more write to the headers of the datafiles

(I'm not adding these writes to the undo because I don't know if they go there and we'll go crazy on a recursive chase, wtiting, undoing the changes to mark, etc)

point 2:

the less groups you have the more possibility you've to reach the "1 Current, the rest Active, no Inactive" status where all your redologs are waiting for the headers and datafiles to catch up , at this point the database freezes in small bursts.

point 3:  before you think in putting all the database in a big file and forget about all this extra header IOs carnival, remember that RMAN backups  (and recovery) paralelize on datafiles, and recovery of a 32gb datafile is a little faster than recovery of 1TB datafile, so as everything in life, it's a balance.

Note 1  why don't we see more complains about all of this ? 

the answer is "controllers cache", we can have all this inefficiency at the database level because there's a SAN, or controller Cache hiding the IO disaster from our eyes.

We've at least 32G (or more)  cushion, ready to catch the extra IOs, giving superfast response time and distributing the IO so we won't note the issue.

Note 2 why should we worry about ? if the SAN masks all of this 

 Because SAN(or controller) caches are not infinite as we have more information eventually we'll write more than the size of a cache in few seconds, and then suddenly people will notice a catastrophic performance issue that they can't explain as "everything was ok a second ago".

In the next post, I'll talk a little about what happens next

 (ArchiveLogs , Tapes and Dataguard)

Redologs

 RedoLogs

if an Oracle database is in archivelog mode, (with just one destination) no dataguard enabled.

if you add a register,

in a table without indexes, (as a minimum) this will happen:

one write for the table block,

one write for the undo block of the table,

one write for the redolog entry of the inserted block,

one write for the redolog entry of the undo block

two writes for the archivelog entries of the redolog blocks

  6 IO operations

if you add a register, in a table with one index, and this insert won't trigger an index page split (or more), but it's affected, let's suppose a primary key

(as a minimum) this will happen:

one write for the table block,

one write for the index block

one write for the undo block of the table block

one write for the undo block of the index block

one write for the redolog entry of the table inserted block,

one write for the redolog entry of the index block

one write for the redolog entry of the undo record for the table block

one write for the redolog entry of the undo record for the index block

eight writes for the archivelog entries of the redolog blocks

 16 IO operations just by adding an index

As you can suspect now, more complex tables, with multiple indexes and triggers can easily increment the quantity of IOs that a simple record insert can generate and consider the update or delete, essentially all that we normally do in a database.

The important thing here is to visualize the central role that the redologs have in the database performance tuning

Uso y seccionamiento del Buffer Keep, el Buffer Recycle

Oracle cuenta con una serie de buffers especializados, para diferentes tareas, muchas veces como DBA's
nos vemos restringidos en cuanto al uso de la memoria, dado esto se debe tomar el camino de administrarla
de la mejor manera.

Debemos dividir la informacion  en 3 secciones distintas (bueno sugerencia, hay muchas formas).

a) Informacion de uso constante y volumen pequeño ..... la ideal para permanecer en el Keep.

b) Informacion de uso frecuente, pero de volumen grande (cortesia de la mezcla de historicos y transaccional y de la falta de una politica de historicos establecida en la empresa).

c) Informacion de uso infrecuente y de volumen grande (tienden a vaciar el buffer cache) estos generan grandes variaciones en los tiempos de respuesta ..... ideal para el Recycle


Aqui se deben hacer varias salvedades, la primera pregunta es que es "grande", como todas las cosas, eso depende del volumen de la base de datos y la relacion con respecto a la base de datos.

No es lo mismo la relacion de una cache de 2GB, contra una base de datos de 20GB,
que 2GB contra una base de datos de 200GB.

La idea es intentar que las estructuras mas utilizadas por el transaccional se encuentren en el Keep, ojala a un 100% de hit ratio. Eso garantiza que los usuarios tienen lo que necesitan aunque se corran otros procesos.

Claro esto no siempre es posible, depende en gran medida del diseño de la base de datos, asi que el primer paso es identificar las estructuras mas utilizadas y su peso a nivel de accesos a la base de datos.

El siguiente paso es identificar las estructuras menos usadas, pero grandes, de uso infrecuente pero que ocurren, estas debemos tratar de asociarlas al buffer recycle, este no necesita tener un gran tamaño, es evidente que no va a haber una cache capaz de mantenerla y adicionalmente no vale la pena.

Este proceso se debe llevar a cabo en una forma iterativa y de monitoreo, para verificar que las desiciones tomadas son efectivarmente adecuadas.

Efecto Particionamiento sobre Tablas

Queria llamar la atencion sobre un fenomeno asociado al particionamiento de tablas.

 Es conocido que el particionar mejora los accesos asociados a la llave de particionamiento, para la tabla
o indice sobre la que se realiza el proceso.

Sin embargo adicional a esto se da un efecto secundario que es la mejora del sistema como un todo, al seccionar el uso de recursos de la cache de Oracle y del disco, el sistema como un todo se beneficia,
pues el uso menor de buffers, por ejemplo, permite que otras estructuras vengan a ocupar el lugar de la
tabla o indice particionado.

Asimismo los recorridos de disco de las estructuras completas se ven recortados
liberando la cola de solicitudes para otras estructuras.