Conde Bond Post

Sólo existe un tipo de conocimiento, aquel que se transmite.

Oracle – Eines d’Administració: Obtenció d’Estadístiques


Obtenir estadístiques de taules, indexs i particions de la base de dades és sens dubte una tasca obligada quan hom parla de millorar el rendimient de les aplicacions. Tot seguit, us deixo un procedimient que faig servir sovint, i que hem integrat en algunes de les aplicacions PL/SQL per tal d’incorporar les estadístiques dels esquemes de base de dades en el seu funcionament normal.

Recordeu que per a obtenir estadístiques de qualsevol esquema de base de dades, necessitem accedir al paquet DBMS_STATS i a més tenir els següents rols atorgats a l’usuari:

GRANT ANALYZE ANY TO DBAPPSUPP ;
GRANT EXECUTE ON DBMS_STATS TO DBAPPSUPP ;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO DBAPPSUPP ;
GRANT CREATE JOB TO DBAPPSUPP ;
GRANT SELECT_CATALOG_ROLE to DBAPPSUPP;

El procediment admet diverses execucions, del que destaco que APP_UTL_CONSTANTS.PART_KEY és un valor que comparteixen totes les bases de dades que administro i que pot variar d’un entorn a un altre, però que sempre defineixo.

CREATE OR REPLACE PROCEDURE DBAPPSUPP.PRC_ANALIZA_ESQUEMA (
    pEntorno IN VARCHAR2, pParticion IN VARCHAR2, pParalelo IN VARCHAR2,
    pMetodo IN VARCHAR2, pEstimacion IN VARCHAR2, pResultado OUT NUMBER
) AUTHID CURRENT_USER AS
    l_paralelo         VARCHAR2(30);
    l_particion        VARCHAR2(30);
    l_inicio           NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
    l_metodo           VARCHAR2(200) DEFAULT 'FOR ALL COLUMNS SIZE AUTO';
    l_Estimacion       VARCHAR2(200) DEFAULT DBMS_STATS.AUTO_SAMPLE_SIZE;
    l_auxCountT        NUMBER(5):=0;
    l_auxCountP        NUMBER(5):=0;
    l_auxCount         NUMBER(5):=0;

BEGIN
    IF UPPER(pParalelo) = 'AUTO' THEN
       l_paralelo := DBMS_STATS.AUTO_DEGREE;
    ELSE
       l_paralelo := UPPER(pParalelo);
    END IF;
    IF pMetodo IS NOT NULL THEN
       l_metodo := UPPER(pMetodo);
    END IF;
    IF pEstimacion IS NOT NULL THEN
       l_Estimacion := UPPER(pEstimacion);
    END IF;
    SELECT COUNT(1) INTO l_auxCountP FROM ALL_TABLES WHERE OWNER  = UPPER (pEntorno) AND PARTITIONED='YES';
-- Tablas Particionadas
    IF l_auxCountP > 0 THEN
        FOR tp IN (SELECT DISTINCT OWNER, TABLE_NAME
                    FROM ALL_TABLES
                   WHERE OWNER = UPPER (pEntorno)
                     AND PARTITIONED='YES' ORDER BY 1,2
                 ) LOOP
                DBMS_OUTPUT.PUT_LINE('Procesando Particiones de la Tabla '||tp.OWNER||'.'||tp.TABLE_NAME);
                IF pParticion IS NULL THEN l_particion := APP_UTL_CONSTANTS.PART_KEY; ELSE l_particion:=pParticion; END IF;
                FOR p IN (SELECT PARTITION_NAME
                            FROM ALL_TAB_PARTITIONS
                           WHERE (TABLE_OWNER = tp.OWNER AND TABLE_NAME=tp.TABLE_NAME)
                             AND PARTITION_NAME LIKE '%'||l_particion||'%') LOOP
                  BEGIN
                    DBMS_STATS.GATHER_TABLE_STATS(ownname          => pEntorno,
                                                  tabname          => tp.TABLE_NAME,
                                                  partname         => p.PARTITION_NAME,
                                                  method_opt       => l_metodo,
                                                  estimate_percent => l_Estimacion,
                                                  degree           => l_paralelo ,
                                                  granularity      => 'PARTITION',
                                                  cascade          => TRUE

                    );

                    DBMS_OUTPUT.PUT_LINE('Procesando partición '||p.PARTITION_NAME);
                  EXCEPTION
                    WHEN OTHERS THEN
                       DBMS_OUTPUT.PUT_LINE('ERROR Obteniendo estadisticas de '||tp.OWNER||'.'||tp.TABLE_NAME);
                       DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,200));
                       NULL;
                       l_auxCount:=l_auxCount+1;
                  END;
                END LOOP;
        END LOOP;
    END IF;
-- Tablas no particionadas
    SELECT COUNT(1) INTO l_auxCountT FROM ALL_TABLES WHERE OWNER = UPPER (pEntorno) AND PARTITIONED != 'YES';
    IF l_auxCountT > 0 THEN
       DBMS_OUTPUT.PUT_LINE ('Encontradas   '||l_auxCountp||' Tablas sin particionar');
        FOR t IN (SELECT DISTINCT OWNER, TABLE_NAME
                    FROM ALL_TABLES
                   WHERE OWNER = UPPER (pEntorno)
                     AND PARTITIONED != 'YES' ORDER BY 1,2
                 ) LOOP
                BEGIN
                    DBMS_STATS.GATHER_TABLE_STATS(ownname          => pEntorno,
                                                  tabname          => t.TABLE_NAME,
                                                  method_opt       => l_metodo ,
                                                  estimate_percent => l_Estimacion ,
                                                  degree           => l_paralelo ,
                                                  granularity      => 'ALL',
                                                  cascade          => TRUE
                    );

                DBMS_OUTPUT.PUT_LINE('Procesando la Tabla '||t.OWNER||'.'||t.TABLE_NAME);
                EXCEPTION
                    WHEN OTHERS THEN
                       DBMS_OUTPUT.PUT_LINE('ERROR Obteniendo estadisticas de '||t.OWNER||'.'||t.TABLE_NAME);
                       DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,200));
                       NULL;
                       l_auxCount:=l_auxCount+1;
                END;
        END LOOP;
    END IF;
    IF (l_AuxCountT = 0 AND l_auxCountP = 0) THEN
        pResultado:= -666;
    ELSE
        pResultado:= l_auxCount;
    END IF;
    DBMS_OUTPUT.PUT_LINE(round( (DBMS_UTILITY.GET_TIME-l_inicio)/100, 2 ) ||' segundos...');
EXCEPTION
  WHEN OTHERS THEN
     pResultado:=SQLCode;
END;
/

Hi ha moments que disposem de molt poc temps per obtenir estadístiques en enotorns de gran volum, llavors és recomanat utilitzar l’obtenció d’estadístiques de la última partició i obtenir les generals en finestres de més baixa activitat.

Tant de bó us sigui útil.
Salutacions.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: