Tunning SQL Oracle: 6ta Sesión

Cursores

Se puede reemplazar el open , close y fetch usando for

CREATE OR REPLACE FUNCTION SF_REFCURSOR
RETURN SYS_REFCURSOR IS
CV SYS_REFCURSOR;
V_SQL VARCHAR2(100);
BEGIN
V_SQL:=
'SELECT EMPLOYEE_LAST_NAME FROM EMPLOYEES';
OPEN CV FROM V_SQL;
END;

sqlplus/nolog
connect hr/hr
variable cv refcursor
execute :cv := sf_refcursor
print cv


Otro Ejempo:

DECLARE
v_correo employees.email%TYPE:='';
n_salario employees.salary%TYPE :=1000;
TYPE typEmp IS TABLE OF employees%ROWTYPE
   INDEX BY employees.email%TYPE;
l_empleado typEmp;
PROCEDURE cargaArreglo IS
FOR reg IN (SELECT * FROM employees) LOOP
  l_empleado(reg.email) := reg;
END LOOP;
END;
BEGIN
cargaArreglo; -- Deberia estar en un Package y ser invocado la primera vez
IF l_empleado(v_correo).salary > n_salario THEN
   DBM_OUTPUT.PUT_LINE(l_empleado(v_correo).last_name  ||'SUPERA EL MONTO');
ELSE
   DBMS_OUTPUT.PUT_LINE(l_empleado(v_correo).last_name||'NO SUPERA EL MONTO');
END IF;
END;



4. Afinamiento Pl/Sql


Modularizar el Codigo
Usar PLS_INTEGER que es más eficiente que usar INTEGER, xq usa aritmetica de CPU y no de Librerias.
Usar y recorrer las colecciones en vez de los cursores con BULK

También se usan las colecciones para manejar las excepciones de error BULK_EXCEPTIONS


5. Características del Motor

La Base de datos puede manejar políticas de acceso a tablas agregando predicados luego del WHERE.
Para esto se personalizan Contextos y evitamos modificar las aplicaciones a las que accede un usario para temas de seguridad.

CREATE CONTEXT

Para cargar la Política se usa DBMS_RLS.ADD POLICY

6. Profiling

Para Monitorieo de Packages. Se usa el Package DBMS_PROFILER
Buscar siempre en la vista USER_SOURCE para ver qué linea da el problema que sale en el profiling

Tunning SQL Oracle: 5ta Sesión

9. Particionamiento

ILM para segmentar la información de acuerdo al valor de la información.

Tipo de Particionamiento:
Range
Hash
List


10. Hizo el Insert a múltiples tablas con un solo select

11. Merge




PL/SQL Programming Concepts: Review

Erores predefinidos en el Package DBMS_STANDARD
- Ahi también se pueden encontrar los tipos de datos.



CREATE TABLE PERSONAS_ORIGEN AS
SELECT CUST_ID, CUST_LAST_NAME
FROM CUSTOMERS
WHERE CUST_CITY = 'Torrevieja'
AND CUST_YEAR_OF_BIRTH = '1977';

CREATE TABLE PERSONAS_DESTINO AS
SELECT *
FROM PERSONAS_ORIGEN

ALTER TABLE PERSONAS_DESTINO
ADD CONSTRAINT PK_PERSONAS
PRIMARY KEY (CUST_ID)

Agregamos un registro adicional a PERSONAS_ORIGEN... luego

INSERT INTO PERSONAS_DESTINO
SELECT * FROM PERSONAS_ORIGEN;

Devuelve Error

Vamos a configurar un log:

BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('PERSONAS_DESTINO');
END;

Ahora:

INSERT INTO PERSONAS_DESTINO
SELECT *
FROM PEROSNAS_ORIGEN
LOG ERRORS
REJECT LIMIT UNLIMITED


Trabajando con Colecciones

Nested Table

DECLARE
TYPE typTelefonos IS TABLE OF VARCHAR2(20);
vTelefonos typTelefonos;

BEGIN
vTelefonos :=typTelefonos('888888888','999999999','111111111')
--FOR i IN 1..vTelefonos.COUNT LOOP
FOR i IN vTelefonos.FIRST..vTelefonos.LAST LOOP
   DBMS_OUTPUT_LINE(vTelefonos(i));
END LOOP;
END;

No respeta el orden, no garantiza el ordenamiento
Lo almacena fuera del segmento


VARRAY

Size es definido
Siempre es denso
Garantiza el orden

Tunning SQL Oracle: 4ta Sesión

7. Vistas Materializadas: Sirven para las sumariazaciones del ROLAP

OLTP vs ROLAP

OLTP
Normalidad
3Fnormal, Maestro Detalle
Estructuras complejas
Demandan Joins
Para transacciones
Insert/Update/Delete
Debilidad: Análisis y Reporting

ROLAP (para Datamart)
Denormalizada
Estrella/Snowflake
Menos Joins
Sólo lectura/Consultas
Rinde para consultas
Representa Historia
Aprovechan features de la base de datos.
Para información a nivel agregado

Idea: El Gestor debe tener su módulo de reporting en otra instancia, no en la misma instancia relacional.

Crear "sumarios", donde ya tenga todos los datos precomputados, tantos como requiera para atender las solicitudes de información.

el OLTP, el ETL y el ROLAP pueden estar en la misma instancia, pero se recomienda tenerlos en distintas, también es común tener el ETL y el ROLAP en la misma instancia.

Con ENABLE QUERY REWRITE, Oracle reescribe la consulta y va a la vista materializada en vez de ir al las tablas ROLAP.

No convienen en ambientes OLTP porque las ventajas se ven en el Rewrite al realizar consultas, escenario ideal del ROLAP.

CREATE MATERIALIZED VIEW MV_RESUMEN
ENABLE QUERY REWRITE AS
SELECT P.PROD_SUBCATEGORY,
               T.WEEK_ENDING_DAY,
               SUM(S.AMOUNT_SOLD) AS AMOUNT_SOLD
FROM SALES S, PRODUCTS P, TIMES T
WHERE S.PROD_ID = S.PROD_ID
    AND S.TIME_ID = T.TIME_ID
GROUP BY P.PROD_SUBCATEGORY, T.WEEK_ENDING_DAY


8. Dimensiones

CREATE DIMENSION PRODUCTS_DIM
   LEVEL product IS (PRODUCTS.PROD_ID)
   LEVEL subcategory IS (PRODUCTS.PROD_SUBCATEGORY)
   LEVEL category IS (PRODUCTS.PROD_CATEGORY)
HIERARCHY PROD_ROLLUP
(
PRODUCT CHILD OF
   SUBCATEGORY CHILD OF
   CATEGORY
)
ATTRIBUTE product DETERMINES products.prod_name
ATTRIBUTE product DETERMINES products.prod_desc
ATTRIBUTE subcategory DETERMINES
products.prod_subcategory_desc
ATTRIBUTE category DETERMINES products.prod_category_desc


Para actualizar una vista materializada con refresh _mode Demand, se usa el package
BEGIN
DBMS_MVIEW.REFRESH('MV_VENTA_ANUAL')
END

Se usa SELECT * FROM USER_MVIEWS para ver el diccionario de datos de las vistas materializadas que tenemos


Las Vistas Materializadas se crearon para reescrituras de los selects y se administra el modo de actualización.



9. particionamiento

cuando la tabla es muy grande, se puede reemplazar un DELETE TABLE por un ALTER TABLE TRUNCATE PARTITION

Tunning SQL Oracle: 3ra Sesión

EXPLAIN PLAN
No Ejecuta la sentencia SQL, sino que extrae el plan de ejecución de un query.

EXPLAIN PLAN FOR
SELECT *
FROM CUSTOMERS
WHERE CUST_ID= 100;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY());

- Esto es similar a presionar F6 en Oracle SQL Developer
- En este caso Dysplay retorna una colección (Nested Table)

EXPLAIN PLAN FOR
SELECT e.last_name, e.first_name,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id

Esto solo es para ver cómo serían los planes de ejecución, pero cómo podríamos ver el plan de ejecución de una sentencia del SharedPool? Busquemos su ID:

SELECT  SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%MINISTERIO%';

Como vemos, estamos usando el comentario puesto anteriormente en el Query para identificar el SQL.

El resultado serán dos registros, donde el primero correspone al sql que buscamos. Su ID es "ds2hz95tuOw5j"

Luego:

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ds2hz95tuOw5j'));


* En SQL Plus existen las siguientes funciones:

SET AUTOTRACE ON
SELECT SYSDATE FROM DUAL
/
Muestra el Plan de Ejecución y ejecuta la sentencia

SET AUTOTRACE TRACEONLY
SELECT SYSDATE FROM DUAL
/
Muestra el Plan de Ejecución y Estadísticas, pero no ejecuta la sentencia

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT SYSDATE FROM DUAL
/
Muestra solo el Plan de Ejecución

5. Estadísticas

SELECT owner, job_name, enabled
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOBS'
/


Nota:

Vistas de Performance (Info de la Instancia)
V$SQL_ARGA, V$FILE_STATS

Vistas de Diccionario
DBA_
USER_
ALL_

Ideas:
- El Proceso de Asistencia y de Planilla podría estar en packages,
- El Proceso de Generación Contable podría estar en packages,

Sampling
Minimizar recursos para recolectar estadísticas.
Histogramas
Información Valiosa para la distribución es máximo y mínimoSELECT table_name, column_name, num_distinct, num_buckets, last_analysed
FROM DBA_TAB_COL_STATISTICS
WHERE OWNER = 'HR'
AND HISTOGRAM = 'FREQUENCY'
/

SELECT *
FROM DBA_HISTOGRAMS
WHERE TABLE_NAME='JOB_HISTORY' AND COLUMN_NAME='DEPARTMENT_ID'
/

• Histogramas de Alto Baloanceado (Heigh-Balanced)
• Histogramas de Frecuencia

* Se puede bloquear con estadísticas y bloquear sin estadísticas: Dinamic Sampling

6. Indices

Es mejor eliminar índices que no se usan.

El desarrollador puede saber los índices q se usan con el Explain plan

B*-tree

• Normal
• Function: create index funcindex on clientes (upper(cust_last_name));

    Composite Indexes

    Skip Scanning

Bitmap Index

Solo para reporting

Para columnas de baja cardinalidad: genero, estado civil, ubicación geográfica (no más de 8% del total de registros)

SELECT CUST_ID, CUST_GENDER, CUST_MARITAL_STATUS, CUST_INCOME,_LEVEL FROM CLIENTES SAMPLE(0.05)

SELECT *

FROM CUSTOMERS

WHERE CUST_GENDER = 'M'

AND CUST_INCOME_LEVEL IN('F:110,00 - 129,999', 'E:90,000 - 109,999')

CREATE BITMAP INDEX CUST_SALES_BJI
ON SALES(C.CUST_CITY)
FROM SALES S, CUSTOMERS C
WHERE C.CUST_ID=S.CUST_ID;


SHOW PARAMETER QUERY;
Debe presentar true