Administer and Tune Schema Object to Support Various Access Methods

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 16 SQL Tuning Overview

2. Lo que nos piden en este objetivo tan ambigüo es, probablemente, optimizar distintas sentencias SQL a través de diferentes métodos. En algunas situaciones nos puede interesar crear un índice que produzca mejores tiempos de acceso a una tabla, o a lo mejor debemos reescribir una sentencia SQL para evitar operaciones pesada como TABLE ACCESS FULL.

Hay un curso de Oracle específico de SQL Tuning en el que se explican los distintos tipos de acceso a los datos y operaciones del optimizador. El curso se llama “Oracle Database 11g: SQL Tuning Workshop” y uno de los cursos recomendados para poder presentarse al examen.

Este objetivo se complementa con estos otros, que debemos conocer para finalmente poder resolver el escenario que nos planteen con éxito:

· Use partitioned indexes
· Interpret execution plan
· Use SQL tuning tools and features
· Use SQL Tuning Advisor
· Use SQL Access Advisor
· Use SQL Performance Analyzer
· Use SQL Plan Management feature

Para este punto concreto voy a seguir la metodología y recomendaciones que hace Nilesh Kakkad en su libro “OCM: Oracle Database 10g Administrator Certified Master Exam Guide“. Aunque el libro te prepara para la versión 10g, la base teórica para la optimización de consultas SQL es la misma.

3. La primera recomendación es para aquellas situaciones en las que queremos seleccionar varias columnas “NOT NULL” de una tabla de hechos (Fact Table) con millones de registros. Si creamos un índice que incluya todos los campos que seleccionamos, entonces la sentencia no necesita acceder a la tabla ya que toda la información se encuentra en el índice. Veamos un ejemplo concreto.

-- Creamos un tabla para las pruebas
CREATE TABLE SH.TEST_SALES AS
SELECT
 P.PROD_ID, P.PROD_NAME, P.PROD_DESC, P.PROD_SUBCATEGORY, P.PROD_SUBCATEGORY_ID,
 P.PROD_SUBCATEGORY_DESC, P.PROD_CATEGORY, P.PROD_CATEGORY_ID, P.PROD_CATEGORY_DESC,
 P.PROD_WEIGHT_CLASS, P.PROD_UNIT_OF_MEASURE, P.PROD_PACK_SIZE, P.SUPPLIER_ID,
 P.PROD_STATUS, P.PROD_LIST_PRICE, P.PROD_MIN_PRICE, P.PROD_TOTAL, P.PROD_TOTAL_ID,
 P.PROD_SRC_ID, P.PROD_EFF_FROM, P.PROD_EFF_TO, P.PROD_VALID, S.CUST_ID, S.TIME_ID,
 S.CHANNEL_ID, S.PROMO_ID, S.QUANTITY_SOLD, S.AMOUNT_SOLD, S.UNIT_COST, S.UNIT_PRICE
FROM SH.PRODUCTS P, SH.SALES_TRANSACTIONS_EXT S
WHERE P.PROD_ID=S.PROD_ID;

-- Creamos un índice sobre la PK
CREATE INDEX SH.TEST_SALES_IDX1 ON SH.TEST_SALES (PROD_ID);

-- Pasamos estadísticas sobre la tabla
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','TEST_SALES', CASCADE=> TRUE);

-- Veamos el plan de ejecución de una consulta sencilla
--   · Hay que fijarse en que se consulta el índice (INDEX RANGE SCAN sobre TEST_SALES_IDX1)
--   · Para cada fila del índice se accede a la tabla (TABLE ACCESS BY INDEX ROWID)
SET AUTOTRACE TRACEONLY
SELECT PROD_ID, QUANTITY_SOLD, AMOUNT_SOLD
FROM SH.TEST_SALES
WHERE PROD_ID IN (30, 40);

-- Ahora vamos a "tunear" el objeto para mejorar el plan de ejecución
-- Creamos un índice que contenga las tres columnas de la consulta
CREATE INDEX SH.TEST_SALES_IDX2 ON SH.TEST_SALES(PROD_ID, QUANTITY_SOLD, AMOUNT_SOLD);

-- Volvemos a revisar el plan de ejecución de la consulta anterior
--   · Nos hemos desprendido de la operación "TABLE ACCESS BY INDEX ROWID"
--   · Tenemos un mejor plan de ejecución ya que evitamos acceso innecesarios a la tabla
SELECT PROD_ID, QUANTITY_SOLD, AMOUNT_SOLD
FROM SH.TEST_SALES
WHERE PROD_ID IN (30, 40);

4. Los operadores “NOT IN” y “<>” suelen provocar que no se utilicen los índices que tengamos. Existe un “workaround” para forzar su utilización. Este procedimiento consiste en crear una tabla de “consulta” mucho más pequeña para la clausula que tenemos y así evitar los accesos pesados a la tabla principal.

-- Creamos un índice sobre la columna PROD_CATEGORY
CREATE INDEX SH.TEST_SALES_IDX3 ON SH.TEST_SALES (PROD_CATEGORY);

-- Lanzamos un par de consultas para comprobar que no se utiliza el índice TEST_SALES_IDX3
SELECT * FROM SH.TEST_SALES
WHERE PROD_CATEGORY NOT IN ('Software/Other', 'Electronics');

-- Esta consulta utiliza el operador "<>"
SELECT * FROM SH.TEST_SALES
WHERE PROD_CATEGORY <> 'Software/Other';

-- Creamos una tabla de referencia sobre la columna PROD_CATEGORY
CREATE TABLE SH.REF_PROD_CATEGORY
AS SELECT DISTINCT PROD_CATEGORY FROM SH.TEST_SALES;

-- Pasamos estadísticas a la tabla
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','REF_PROD_CATEGORY');

-- Reescribimos las consultas anteriores
-- En la primera de ellas transformamos el operador "NOT IN"
SELECT * FROM SH.TEST_SALES
WHERE PROD_CATEGORY IN (
  SELECT PROD_CATEGORY FROM SH.REF_PROD_CATEGORY
  WHERE PROD_CATEGORY NOT IN ('Software/Other', 'Electronics'));

-- En la segunda transformamos el operador "<>"
SELECT * FROM SH.TEST_SALES
WHERE PROD_CATEGORY IN (
  SELECT PROD_CATEGORY FROM SH.REF_PROD_CATEGORY
  WHERE PROD_CATEGORY <> 'Software/Other');

5. Veamos la importancia de las fechas dentro de las consultas.

-- Creamos un índice sobre una columna DATE
CREATE INDEX SH.TEST_SALES_IDX4 ON SH.TEST_SALES (PROD_EFF_FROM);

-- Comprobamos que el acceso por índice es correcto
SELECT * FROM SH.TEST_SALES WHERE PROD_EFF_FROM = SYSDATE;

-- Veamos qué sucede cuando pasamos un tipo de datos TIMESTAMP
--   · El plan de ejecución realiza esta vez una operación TABLE ACCESS FULL
SELECT * FROM SH.TEST_SALES WHERE PROD_EFF_FROM = SYSTIMESTAMP;

DROP TABLE SH.TEST_SALES;
DROP TABLE SH.REF_PROD_CATEGORY;

6. Otra recomendación general es que si queremos acceder por índice sobre campos de texto y al mismo tiempo utilizar el operador LIKE, debemos tener cuidado de donde ponemos los ‘%’. Veamos con un ejemplo práctico el problema de los índices sobre cadenas de texto.

-- Creamos un índice sobre el campo PROD_NAME
CREATE INDEX SH.TEST_SALES_IDX5 ON SH.TEST_SALES(PROD_NAME);

-- Lanzamos una consulta donde provocamos el correcto uso del índice
SELECT * FROM SH.TEST_SALES WHERE PROD_NAME LIKE '1.44MB%';

-- Sin embargo si incluimos el símbolo '%' a ambos lados se realiza un TABLE ACCESS FULL
SELECT * FROM SH.TEST_SALES WHERE PROD_NAME LIKE '%1.44MB%';

7. Tener muchos índices sobre una tabla perjudica el rendimiento ya que cada vez que se realiza DML sobre la tabla hay que realizar modificaciones en los índices que tenga. Podemos monitorizar su uso durante un tiempo, y eliminar aquellos que no se utilicen. Además, en la versión 11g tenemos la posibilidad de tener índices invisibles, que son índices que se actualizan pero no se usan. Esto da agilidad a la hora de comprobar el rendimiento de una aplicación CON y SIN índices.

-- Activamos la monitorización de los índices
ALTER INDEX SH.TEST_SALES_IDX1 MONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX2 MONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX3 MONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX4 MONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX5 MONITORING USAGE;

-- La vista V$OBJECT_USAGE contiene la información acerca de su uso
-- Nos conectamos primero al esquema SH (también podemos usar el objeto SYS.OBJECT_USAGE)
CONN SH/sh
SELECT * FROM V$OBJECT_USAGE;

-- Generamos una consulta de prueba
SELECT COUNT(PROD_ID) FROM SH.TEST_SALES;

-- Volvemos a consultar el uso de los índices
-- Vemos que el índice TEST_SALES_IDX1 se ha usado (USED = YES)
SELECT * FROM V$OBJECT_USAGE;

-- Desactivamos la monitorización de los índices
ALTER INDEX SH.TEST_SALES_IDX1 NOMONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX2 NOMONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX3 NOMONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX4 NOMONITORING USAGE;
ALTER INDEX SH.TEST_SALES_IDX5 NOMONITORING USAGE;

-- Otra opción que tenemos es hacer invisible un índice y ver si el rendimiento es el mismo
-- Hacemos invisibles dos índices de la tabla TEST_SALES
ALTER INDEX SH.TEST_SALES_IDX1 INVISIBLE;
ALTER INDEX SH.TEST_SALES_IDX2 INVISIBLE;

-- La siguiente consulta realiza un TABLE FULL ACCESS con la penalización I/O que conlleva
SELECT PROD_ID FROM SH.TEST_SALES;

-- Volvemos a dar visibilidad a los índices
ALTER INDEX SH.TEST_SALES_IDX1 VISIBLE;
ALTER INDEX SH.TEST_SALES_IDX2 VISIBLE;

8. Es interesante revisar el concepto de Index-Organized Table o IOT. Es un tipo de tabla que se almacena en una estructura de tipo Árbol-B, es decir, como un índice común. Tiene las siguientes ventajas:

· Acceso arbitrario más rápido ya que con una operación de INDEX SCAN obtienes toda la información que necesitas
· Los accesos por rango sobre la Primary Key son más rápidos porque los eregistros están agrupados por PK
· Reducción de almacenamiento ya que la PK no está duplicada

Las tablas IOT son muy útiles es un par de escenarios. Uno de ellos son las tablas de asociaciones, que son las tablas que surgen de las relaciones de muchos a muchos. El otro escenarios son tablas donde la ubicación física de los datos es importante, pero el orden de inserción no es predecible.

-- El primer ejemplo es de una tabla de asociación donde se guardan los préstamos de libros de una biblióteca
--   · Una persona puede tener varios libros en préstamos
--   · Un libro puede ser sacado en préstamos por varias personas
-- El almacenamiento en una IOT es recomendable para este tipo de estructuras
CREATE TABLE BOOKLOANS (
  PERSON_ID NUMBER,
  BOOK_ID NUMBER,
  CONSTRAINT BOOKLOAN_PK PRIMARY KEY (PERSON_ID, BOOK_ID))
ORGANIZATION INDEX;

-- El segundo ejemplo, propuesto por Tom Kyte, es de una tabla donde no conocemos el orden de llegada de los datos
--   · Un usuario guarda documentos en la BD a lo largo del tiempo
--   · Nos interesa poder consultar con rápidez los documentos que tiene dicho usuario
CREATE TABLE USERDOCS (
  USERNAME VARCHAR2(30),
  DOCUMENT_NAME VARCHAR2(30),
  CONSTRAINT USERDOCS_PK PRIMARY KEY (USERNAME, DOCUMENT_NAME))
ORGANIZATION INDEX;

-- Limpiamos el entorno
DROP TABLE BOOKLOANS;
DROP TABLE USERDOCS;