Recientemente me han planteado un ejercicio mental que consistía en hacer un pequeño script en PL/SQL que balanceara la carga de usuarios de una base de datos Oracle entre los ‘tablespaces temporales’ que hay en el sistema.

Para afrontar este problema se pueden tomar varios caminos. Un camino posible sería hacer una repartición simple de usuarios entre el número de tablespaces, por ejemplo, si tengo 3 tablespaces temporales y 30 usuarios a cada tablespace temporal (TST) le corresponden 10 usuarios. Esta repartición puede parecer correcta, pero ¿qué pasaría si los tamaños de los TST fueran 300MiB, 500KiB, 10KiB?, o bien, ¿y si el espacio libre en cada TST es de 200KiB (en el de 300MiB), 500KiB (en el de 500KiB) y 0KiB en el último?. Creo que según este  último planteamiento está claro que la repartición más correcta sería usando el espacio libre de cada TST. No obstante en la cuestión de los balanceos de carga, sea cual sea su naturaleza, lo que hay que sopesar es qué tipo de ‘carga’ se quiere repartir, porque se podría dar el caso de que podemos considerar que tenemos TST infinitos pero repartidos por distintos dispositivos de bloques, en ese caso la repartición sería en función del tráfico I/O de los buses a los que están conectados los dispositivos o bien si son unidades de red pues en función del tráfico de la red.

Para este problema yo usaré el balanceo en función del espacio libre de casa TST.

Algoritmo usado

‘patente en trámite’

El algoritmo que me he inventado es muy simple y lo explicaré mediante un ejemplo.

Supongamos que disponemos de 4 TST con los siguientes espacios libres ordenados desde TST1 a TST4: 10MiB, 2MiB, 1MiB, 0MiB. La repartición de usuarios la hago asignando a un único usuario el tamaño menor, en este caso 1MiB, como dispongo de 13MiB, yo podría repartir de forma exacta 13MiB a 13 usuarios, repartidos así: 10U, 2U, 1U. Pero no tenemos por qué tener 13 usuarios, pueden ser más o menos. La asignación es muy simple:

• Obtengo la lista de usuarios que tienen sesión abierta, por ejemplo, 20 usuarios.
• Recorro la lista de usuarios uno a uno.
• La elección del tablespace es: ¿Qué tablespace tiene más U? (en este caso TST1: 10U)
• Asigno ese usuario al TST1, y le resto 1, quedando TST1 con 9U.
• Voy asignando de esa forma usuarios.

Básicamente es recorrer la lista TST e ir asignando usuarios al que más U tiene en ese momento. Lo bueno de todo es que cuando todos llegan a cero, la cuenta sigue pero en el conjunto de los números enteros negativos con lo que este algoritmo simple sigue teniendo validez.

Código

Enlace al repo: https://github.com/manuel-alcocer/oracle-procs/tree/master/temporary-tablespace-loadbalancer

ser serveroutput on;

create or replace package loadBalancer
as
    TYPE TipoRegTS is record
    (
        nombre          dba_tablespaces.tablespace_name%type,
        bloquesLibres   number,
        numUsuarios     number
    );
    TYPE TipoTablaTS is table of TipoRegTS
        index by binary_integer;

    TablaTS TipoTablaTS;

    procedure BalanceoCargaTemp;
end loadBalancer;
/

create or replace package body loadBalancer
as
    procedure RellenarBloques
    is
        cursor c_tsbloques is
            select tablespace_name, blocks_free
            from v$TEMP_SPACE_HEADER
            where blocks_free > 0
            and tablespace_name in ( select tablespace_name from dba_tablespaces
                                        where lower(status) = 'online'
                                        and lower(contents) = 'temporary')
            order by blocks_free desc;
        v_indice binary_integer := 0;
    begin
        TablaTS.delete;
        for v_tsbloque in c_tsbloques loop
            TablaTS(v_indice).nombre := v_tsbloque.tablespace_name;
            TablaTS(v_indice).bloquesLibres := v_tsbloque.blocks_free;
            v_indice := v_indice + 1;
        end loop;
    end RellenarBloques;

    procedure CalcularUsuariosporTS
    is
        v_minbloques    number;
        v_numUsuarios   number;
        v_indice        binary_integer;
    begin
        v_minbloques := TablaTS(TablaTS.last).bloquesLibres;
        v_indice := TablaTS.first;
        while v_indice is not null loop
            v_numUsuarios := floor(TablaTS(v_indice).bloquesLibres / v_minbloques);
            TablaTS(v_indice).numUsuarios := v_numUsuarios;
            v_indice := TablaTS.next(v_indice);
        end loop;
    end CalcularUsuariosporTS;

    procedure MostrarReparto
    is
        v_indice    binary_integer;
    begin
        v_indice := TablaTS.first;
        while v_indice is not null loop
            dbms_output.put_line(TablaTS(v_indice).nombre || ' - ' || 
                TablaTS(v_indice).bloquesLibres || ' - ' || 
                TablaTS(v_indice).numUsuarios );
            v_indice := TablaTS.next(v_indice);
        end loop;
    end MostrarReparto;

    function elegirTs
    return binary_integer
    is
        v_indice        binary_integer := TablaTS.first;
        v_indicemax     binary_integer := TablaTS.first;
    begin
        while v_indice is not null loop
            if TablaTS(v_indicemax).numUsuarios < TablaTS(v_indice).numUsuarios then
                v_indicemax := v_indice;
            end if;
            v_indice := TablaTS.next(v_indice);
        end loop;
        TablaTS(v_indicemax).numUsuarios := TablaTS(v_indicemax).numUsuarios - 1;
        return v_indicemax;
    end elegirTs;

    procedure AsignarUsuarios
    is
        cursor c_usuarios is
            select username from dba_users
            where lower(account_status) = 'open';
        v_indice binary_integer;
    begin
        v_indice := elegirTs;
        for v_usuario in c_usuarios loop
            -- Descomentar la línea siguiente para ver cómo progresa el reaparto
            -- MostrarReparto;
            execute immediate 'alter user ' || v_usuario.username || ' temporary tablespace ' || TablaTS(v_indice).nombre;
            v_indice := elegirTs;
        end loop;
    end AsignarUsuarios;

    procedure BalanceoCargaTemp
    is
    begin
        RellenarBloques;
        CalcularUsuariosporTS;
        AsignarUsuarios;
    end;
end loadBalancer;
/