Mòdul ASGBD

ASGBD - Monitorització i Optimització

Monitoratge del Sistema Gestor de Bases de Dades (SGBD)

Què és el monitoratge?

El monitoratge és el procés de controlar el funcionament del sistema gestor, detectant possibles errors o situacions que puguin afectar el rendiment. Ha de ser poc intrusiu, per tal de no interferir en el funcionament normal del sistema.

S'ha de tractar que les tasques de monitoratge i diagnòstic del sistema siguen el menys intrusives possible perquè no penalitzen el rendiment del sistema gestor, i relegar les tasques que requerisquen un consum de recursos mitjà o elevat per a realitzar-les en moments de baixa càrrega

🧰 Eines principals de monitoratge

Monitor de rendiment

Dades rellevants

📝 Registre d’errors (fitxers de logs)

Registra activitats com arrencades, aturades, errors, consultes lentes, etc.

📁 Exemples de fitxers de registre

📚 Diccionari de Dades (DD)

Conjunt de vistes com v$session, v$sqlarea, v$diag_info, etc. que permeten:

Exemple:

  SELECT * FROM v$session;
  SELECT * FROM v$sqlarea;
  SELECT * FROM v$diag_info;  -- Registre d'errors

🖥️ Monitoratge gràfic (SQL Developer, TOAD...)

⚠️ Les ferramentes gràfiques consumeixen més recursos que les consultes al DD
⚠️ Les ferramentes gràfiques requereixen més permisos que les consultes al DD

També es pot fer des de Herramientas → Controlar sesiones en SQL Developer.

Pràctica recomanada

Resum de bones pràctiques

Optimització

🎯 Objectiu

L’objectiu de l’optimització és millorar el rendiment del sistema, tot reduint els temps de resposta, l’ús de recursos i l’impacte en l’usuari final. No es tracta només de fer que les consultes funcionen, sinó que siguen ràpides i eficients.

Nivells on optimitzar

Particionament

  • S’evita processar tota una taula (sols es processa la partició)
  • Permet guardar en una sola taula més dades que en un disc
  • Les dades poden ser accedides en paral·lel
  • Facilita operacions com p.e. el purgat de dades

    • Creació d'índex

    Optimització de consultes

    Pràctiques recomanades per a índex

    Pràctiques recomanades per a consultes

    🛑 Consultes ineficients

    Exemples a evitar:

    Versions millorades

      -- Evitar funcions sobre columnes
  WHERE data >= TO_DATE('01/01/2023', 'DD/MM/YYYY')
  
  -- Substituir subconsulta per JOIN
  SELECT a.nom, c.nom_curs
  FROM alumnes a
  JOIN cursos c ON a.curs_id = c.id;

    Altres consells d’optimització

    Resum de bones pràctiques

    Optimització en Oracle

    📈 L'optimitzador

    Oracle utilitza un component anomenat optimitzador per determinar el millor pla d’execució per a una consulta SQL. L’objectiu és minimitzar el temps d’execució i l’ús de recursos.

    📊 Estadístiques

    ORACLE utilitza un optimitzador CBO (basat en costos calculats amb estadístiques)

    L’optimitzador CBO utilitza estadístiques sobre les taules, columnes i índexs per decidir l’estratègia d’execució.

    Com generar estadístiques

        EXEC DBMS_STATS.GATHER_TABLE_STATS('USUARI', 'TAULA');
    -- Actualitzar estadístiques
    EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'USUARI', tabname=>'ALUMNES');
    --
    execute dbms_stats.gather_table_stats(‘esquema’.’taula’);
    execute dbms_stats.gather_schema_stats(‘esquema’);

    ⚠️ Per tant, és molt importat crear tasques programades que realitzen esta actualització fora d'hores de càrrega de treball

    També es poden automatitzar mitjançant tasques de manteniment o recollida automàtica de l’Oracle Scheduler.

    🧪 EXPLAIN PLAN

    Aquesta ordre permet visualitzar el pla que Oracle seguirà per executar una consulta. Mostra si s’utilitzen índexs, escaneigs complets, joins, etc. Ajuda a detectar consultes lentes o mal optimitzades.

    Exemple d’ús

      EXPLAIN PLAN FOR
  SELECT * FROM alumnes WHERE edat > 18;
  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    Resultats comuns:

    Factors que afecten el pla d’execució

    ⚠️ Errors habituals d’optimització

    Altres eines d’optimització

    Recomanacions generals

    Exemple de consulta optimitzada

    Consulta original (lenta):

      SELECT * FROM alumnes WHERE TO_CHAR(data_naixement, 'YYYY') = '2005';

    Versió optimitzada:

      SELECT * FROM alumnes
  WHERE data_naixement BETWEEN TO_DATE('01/01/2005', 'DD/MM/YYYY')
                          AND TO_DATE('31/12/2005', 'DD/MM/YYYY');

    💬 Conclusió

    L’optimització en Oracle es basa en la col·laboració entre el desenvolupador i l’optimitzador del sistema. Mantenir estadístiques actualitzades, evitar males pràctiques i revisar plans d’execució són claus per un rendiment òptim.

    Eines d'optimització en Oracle

    🎯 Objectiu

    Aquestes eines permeten analitzar el rendiment de consultes SQL, sessions i càrregues del sistema, ajudant a diagnosticar colls d’ampolla i a optimitzar els accessos a dades.

    1. EXPLAIN PLAN

    Mostra el pla d’execució previst per Oracle per a una consulta. Ajuda a detectar si s’utilitzen índexs o si es fa un escaneig complet de taula. 

      EXPLAIN PLAN FOR
  SELECT * FROM alumnes WHERE edat > 18;
  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    Sortides habituals:

    2. DBMS_XPLAN

    Paquet que permet mostrar el pla d’execució amb més claredat, incloent cost, línies estimades, filtre aplicat, etc.

    Exemple:

      SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    Per veure el pla real d’una consulta ja executada:

      SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

    3. AUTOTRACE

    Mostra automàticament el pla d’execució i estadístiques després d’executar una consulta.

    Activar AUTOTRACE a SQL*Plus:

      SET AUTOTRACE ON;
  SELECT * FROM alumnes WHERE edat > 18;

    Resultat: mostra el cost, nombre de lectures, línies retornades i accés utilitzat.

    4. AWR (Automatic Workload Repository)

    Recull mètriques de rendiment cada hora. Permet fer comparatives, generar informes i detectar canvis de rendiment.

    📝 Informes AWR:

      @$ORACLE_HOME/rdbms/admin/awrrpt.sql

    5. ASH (Active Session History)

    Mostra l’activitat de les sessions que han estat actives en els darrers minuts. Ideal per veure quines consultes estan causant càrrega.

    🔍 Exemple de consulta ASH:

      SELECT sql_id, session_id, wait_class, event, sample_time
  FROM v$active_session_history
  WHERE sample_time > SYSDATE - 1/24;

    Requereix: llicència Oracle Enterprise Edition + Pack Diagnòstic

    Altres eines útils

    Bones pràctiques

    Conclusió

    Les eines d’optimització d’Oracle et permeten monitorar i millorar el comportament del teu sistema, evitant colls d’ampolla i millorant l’experiència d’usuari.

    Optimització dels objectes de la bbdd

    Fragmentació de Taules en Oracle

    🔍 Què és la fragmentació de taules?

    La fragmentació fa referència a l'espai de memòria mal aprofitat dins d'una taula (o índex) a la base de dades. Pot provocar que certes operacions, com escaneigs complets de taula o accés via índex, siguin menys eficients.

    Causes comunes de fragmentació en Oracle

    Conseqüències de la fragmentació

    🧰 Com detectar la fragmentació

    ANALYZE TABLE nom_taula COMPUTE STATISTICS;
    
  SELECT table_name, blocks, empty_blocks, avg_space
  FROM dba_tables
  WHERE table_name = 'NOM_TAULA';

    També pots consultar les vistes DBA_SEGMENTS i DBA_EXTENTS.

    🛠️ Solucions per reduir o eliminar la fragmentació

    Bones pràctiques

    📊 Índexs

    Els índexs permeten accedir més ràpidament a dades. Cal usar-los correctament:

    🔧 Crear un índex

      CREATE INDEX idx_nom ON alumnes(nom);

      create index nom_ind on nom_taula (camp1, camp2,..);
  alter index nom_ind rebuild;

    Quan es crea una taula amb una clau primaria o unique, ORACLE crea un índex automàticament

    Tipus índex

        create [bitmap | unique] index nom_ind on nom_taula (camp1, camp2,..);
    alter index nom_ind rebuild;

    Com explorar els índex (en DD)

        select index_name , index_type ,table_name , tablespace_name , secondary 
    from all_indexes where table_name = 'TAULA_A_CONSULTAR';

    Particionament

    create table ....( ) partition by range(nomcamp) (....);

    En que consisteix el particionament?

    El particionament consisteix a dividir lògica i físicament una taula gran en parts més petites anomenades particions, de manera que el sistema pugui gestionar-les de forma més eficient.

    Per a què serveix?

    Per exemple imaginem una taula de factures, on tenim el detall de la nostra facturació al llarg de 6 anys, 2017, 2018… 2022, si volguérem fer:

    SELECT SUM(total_fac) FROM facturacio WHERE any = 2019;

    En aquest exemple s'hauria de recórrer tota la taula (imaginem que parlem de 30 milions de registres en total, és molt no?), per aquest motiu un criteri possible per a particionar la taula seria per l'any de la data de la factura

    Tipus de particionament

    1. Particionament per rang (RANGE)

    Divideix segons un valor dins d’un rang (ex: dates).

    CREATE TABLE vendes (
    id NUMBER,
    data_venda DATE,
    import NUMERIC
)
PARTITION BY RANGE (data_venda) (
    PARTITION p2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
    PARTITION p2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION p2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

    2. Particionament per llista (LIST)

    Divideix segons valors específics.

    CREATE TABLE clients (
    id NUMBER,
    nom VARCHAR2(50),
    regió VARCHAR2(20)
)
PARTITION BY LIST (regió) (
    PARTITION p_nord VALUES ('NORD'),
    PARTITION p_sud VALUES ('SUD'),
    PARTITION p_est VALUES ('EST'),
    PARTITION p_oest VALUES ('OEST')
);

    Com saber quina estratègia escollir?

    Depèn de:

    Altres operacions útils

    -- Afegir partició
ALTER TABLE vendes ADD PARTITION p2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'));

-- Eliminar partició
ALTER TABLE vendes DROP PARTITION p2020;

-- Consultar només una partició
SELECT * FROM vendes 
WHERE data_venda >= TO_DATE('2021-01-01', 'YYYY-MM-DD');

    Consultar si una taula està particionada

    SELECT table_name, partitioning_type
FROM user_part_tables;

    Optimització de consultes

    consultesOracle activa un optimitzador de consultes automàticament i reescriu les consultes si ho estima necessari. Realitza les següents operacions

    Pla d'execució

      Oracle guarda en el DD , les estadístiques de les taules
  En la Vista ==> user_tables

    Usant les estadístiques (DD) , el monitor de rendiment i el registre d’errors, proposa un pla d’execució, que determina com es pot resoldre una consulta de la forma més eficient. Una vegada executades les consultes, els plans d’execució s’emmagatzemen en la cau de consultes

    Cada vegada que executem una sentència una de les coses que fa Oracle és crear un pla d'execució de la sentència. (SELECT, UPDATE, INSERT o DELETE). Un pla d'execució defineix la forma en què Oracle cerca o grava les dades. Decideix, per exemple, si usarà o no els índexs en una sentència SELECT

    DELETE PLAN_TABLE;
  EXPLAIN PLAN FOR SELECT * FROM T_PEDIDOS WHERE CODPEDIDO = 5;
  select * from plan_table;

    El Pla d'execució ¡¡ Usa estadístiques de taules !!

    Un usuari necessita tindre permís per revisar els plans d’execució de les consultes

      grant select_catalog_role to nom_usu;
  grant select any dictionary to nom_usu;

    El SQL Developer permet consultar el pla d’execució de forma gràfica, polsant F10 sobre la consulta abans de llançar-la

    El SQL*Plus permet consultar el pla d’execució de les consultes, executant la següent instrucció set autotrace traceonly explain

    SQL Tunning Advisor

    Un usuari necessita tindre permís per executar el SQL tuning advisor

    En una SQL, abans d’executar, pulsa ctrl + F12

    I després, run SQL amb alt + F11

    Monitor d'operacions

    Es consulta des del paquet DBMS_SQL_MONITOR, 
  - procediment report_sql_monitor
  - vista V$SQL_MONITOR

    Operacions particulars d'ORACLE

    d’OracleInsercions massives : SQL Loader
  Insert Append: INSERT /*+ APPEND */ INTO NOM_taula VALUES (...);
  Nologging alter table t1 nologging;
  Truncate table truncate table t1 ;
  Intercanvi de particions
  Vistes materialitzades. Guarden consulta i dades. Solen guardar càlculs massius
  Merge Combina la inserció i la modificació en una sola instrucció
  Hints
    Els hints s'incorporen a una sentència DML en forma de comentari i han d'anar just darrere del comando principal. Per exemple, si es tractara d'una sentència SELECT el format seria el següent:
  SELECT /*+ COMANDO-HINT */ ...

  S’ha d’anar amb compte amb estes operacions, donat que redueixen la seguretat i la possibilitat de recuperació davant d’operacions no desitjades !!