Análisis de Métodos de Acceso y Estadísticas Tabulares
La selección del camino de acceso que sigue el optimizador de costos depende directamente de la distribución física de los datos y de las estadísticas recopiladas. Para ilustrar este comportamiento, se establece un entorno de prueba con una tabla derivada del diccionario de datos, ordenada originalmente por un identificador numérico.
SQL> CREATE TABLE inventario_base AS SELECT * FROM all_objects ORDER BY object_id;
Table created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_PRUEBAS', 'INVENTARIO_BASE');
PL/SQL procedure successfully completed.
Tras la recolección, los indicadores clave a monitorear incluyen el número de bloques que ocupa la estructura, la cardinalidad de las columnas filtradas y la densidad de valores únicos. Consultando los diccionarios de datos:
SELECT blocks, num_rows, table_name FROM dba_tables WHERE table_name = 'INVENTARIO_BASE';
-- Resultado aproximado: 1260 bloques ocupados
SELECT column_name, num_distinct, num_nulls FROM dba_tab_columns
WHERE owner = 'SCHEMA_PRUEBAS' AND table_name = 'INVENTARIO_BASE' AND column_name = 'OBJECT_ID';
-- Resultado aproximado: 86300 valores distintos
Comparativa: Escaneo Completo vs. Ruta Indexada
Al solicitar un registro específico sin estructuras de búsqueda, el motor debe recorrer físicamente cada segmento asignado. La traza de ejecución evidencia un recorrido secuencial:
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM inventario_base WHERE object_id = 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| INVENTARIO_BASE| 1 | 98 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1244 consistent gets
0 physical reads
La métrica consistent gets refleja lecturas lógicas desde el buffer cache. Un valor superior a 1200 indica que el motor evaluó prácticamente todos los bloques. Al implementar un B-Tree sobre la columna objetivo:
SQL> CREATE INDEX idx_inv_base_id ON inventario_base(object_id);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_PRUEBAS', 'INVENTARIO_BASE');
La nueva ejecución aprovecha la estructura de navegación:
SQL> SELECT * FROM inventario_base WHERE object_id = 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INVENTARIO_BASE| 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_INV_BASE_ID| 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 consistent gets
0 physical reads
La reducción de 1244 a 4 accesos lógicos confirma la eficiencia del índice para filtros de alta selectividad.
Influencia del Factor de Agrupamiento y la Ordenación Física
El rendimiento de un índice no depende únicamente de su existencia, sino de la correlación entre el orden lógico de las entradas indexadas y el orden físico en los bloques de la tabla. Este parámetro se conoce como Cluster Factor. Se crea una segunda tabla donde los registros están distribuidos aleatoriamente respetco al identificador:
SQL> CREATE TABLE inventario_caos AS SELECT * FROM all_objects ORDER BY object_name;
Table created.
SQL> CREATE INDEX idx_inv_caos_id ON inventario_caos(object_id);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_PRUEBAS', 'INVENTARIO_CAOS');
Al consultar el diccionraio de índices, se observa que el factor de agrupamiento de esta estructura supera las 46000 unidades, cercano al total de bloques de la tabla. Esto indica una distribución fragmentada. Al solicitar un rango amplio:
SQL> SELECT * FROM inventario_caos WHERE object_id BETWEEN 1 AND 8000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7879 | 754K| 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| INVENTARIO_CAOS| 7879 | 754K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1762 consistent gets
El optimizador descarta el índice y opta por un escaneo completo. Forzar el uso de la ruta indexada mediante hints degrada notablemente el rendimiento:
SQL> SELECT /*+ INDEX(inventario_caos idx_inv_caos_id) */ * FROM inventario_caos WHERE object_id BETWEEN 1 AND 8000;
Statistics
----------------------------------------------------------
5750 consistent gets
Cada entrada del índice apunta a un bloque distinto de la tabla, generando saltos aleatorios que multiplican el costo I/O. No obstante, para búsquedas puntuales (condición de igualdad), el índice mantiene su utilidad incluso con un factor de agrupameinto elevado, limitando las lecturas a 4 consistent gets.
Control del Buffer de Lectura: Arraysize y Fetch Size
El parámetro arraysize en entornos de línea de comandos (o fetch size en aplicaciones) determina cuántas filas se extraen por cada llamada de red hacia el motor. Un tamaño reducido provoca que un mismo bloque sea consultado repetidamente para recuperar lotes pequeños, incrementando artificialmente las estadísticas de consistent gets y el tráfico de red.
En entornos Java, esta configuración se gestiona directamente sobre el objeto Statement o ResultSet. A continuación, se presenta una implementación moderna que ajusta el tamaño del cursor antes de la ejecución:
package db.optimizacion;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class GestorCursorJDBC {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String DB_URL = "jdbc:oracle:thin:@host_db:1521:service_orcl";
private static final String USER = "app_user";
private static final String PASS = "secure_pass";
private static final int TAMANO_LOTE = 100;
public void ejecutarConsultaConBuffer() {
String query = "SELECT owner, object_name FROM all_objects WHERE rownum < 500 ORDER BY object_type";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement stmt = conn.prepareStatement(query)) {
conn.setAutoCommit(false);
// Configurar el buffer de recuperación antes de ejecutar
stmt.setFetchSize(TAMANO_LOTE);
System.out.println("Tamaño de recuperación configurado: " + stmt.getFetchSize());
try (ResultSet rs = stmt.executeQuery()) {
int contador = 0;
while (rs.next()) {
System.out.printf("[%d] %s - %s%n", ++contador, rs.getString(1), rs.getString(2));
}
System.out.println("Procesamiento finalizado. Filas leídas: " + contador);
}
} catch (SQLException ex) {
ex.printStackTrace();
System.err.println("Error durante la operación de base de datos: " + ex.getMessage());
}
}
public static void main(String[] args) {
try {
Class.forName(GestorCursorJDBC.DRIVER);
} catch (ClassNotFoundException e) {
System.err.println("Controlador JDBC no encontrado: " + e.getMessage());
}
new GestorCursorJDBC().ejecutarConsultaConBuffer();
}
}