Procesamiento de consultas de Cloud SQL

Las consultas se procesan con celdas en el clúster de Oracle Big Data Service.

Acerca de la exploración inteligente para orígenes de Big Data

Las tablas externas de Oracle no tienen índices tradicionales. Las consultas de estas tablas externas normalmente necesitan una exploración de tabla total. El agente de procesamiento de Oracle Cloud SQL en los nodos de datos del cluster de Hadoop amplía las capacidades de exploración inteligente (como las descargas de predicado de filtro) a las tablas externas de Oracle. Smart Scan se ha utilizado durante algún tiempo en Oracle Exadata Database Machine para realizar un filtrado de columnas y predicados en la capa de almacenamiento antes de que los resultados de la consulta se envíen de nuevo a la capa de base de datos. En Cloud SQL, Smart Scan es un filtrado final que se realiza localmente en el servidor de Hadoop para garantizar que solo los elementos solicitados se envían a Cloud SQL Query Server. Las celdas de Cloud SQL que se ejecutan en los nodos de datos de Hadoop pueden realizar exploraciones inteligentes con diferentes formatos de datos en HDFS, como texto CSV, Avro y Parquet.

Esta implementación de Smart Scan aprovecha la potencia de procesamiento en paralelo masiva del cluster de Hadoop para filtrar los datos en su origen. Puede desechar de forma preventiva una parte enorme de datos irrelevantes, hasta un 99 % del total. Esto tiene varias ventajas:

  • Reduce considerablemente el movimiento de datos y el tráfico de red entre el cluster y la base de datos.

  • Devuelve conjuntos de resultados mucho más pequeños al servidor de Oracle Database.

  • Agrega datos cuando es posible, ya que aprovecha la escalabilidad y el procesamiento de clusters.

Los resultados de la consulta se devuelven considerablemente más rápido. Es el resultado directo del tráfico reducido en la red y la carga reducida en Oracle Database.

Acerca de los índices de almacenamiento

Para los datos almacenados en HDFS, Oracle Cloud SQL mantiene los índices de almacenamiento automáticamente, que es transparente para Oracle Database. Los índices de almacenamiento contienen el resumen de la distribución de datos en un disco duro para los datos almacenados en HDFS. Los índices de almacenamiento reducen el costo de las operaciones de E/S y el costo de la CPU relacionados con la conversión de datos de archivos planos a bloques de Oracle Database. Puede pensar en un índice de almacenamiento como un índice negativo. Indica a Smart Scan que los datos no están en un bloque de datos, lo que permite a Smart Scan omitir la lectura de ese bloque. Esto puede provocar una reducción de E/S significativa.

Los índices de almacenamiento solo se pueden utilizar para tablas externas que estén basadas en HDFS y se hayan creado con los controladores ORACLE_HDFS o ORACLE_HIVE. Los índices de almacenamiento no se pueden utilizar para las tablas externas que utilizan StorageHandlers, como Apache HBase y Oracle NoSQL.

Un índice de almacenamiento es una recopilación de índices de región en memoria, y cada índice de región almacena resúmenes para hasta 32 columnas. Hay un índice de región para cada división. El contenido almacenado en un índice de región es independiente de los otros índices de región. Esto los hace altamente escalables y evita la contención de bloqueos internos.

Los índices de almacenamiento mantienen los valores mínimo y máximo de las columnas de una región para cada índice de región. Los valores mínimos y máximos se utilizan para eliminar la E/S innecesaria, que también se conoce como filtrado de E/S. Los bytes de E/S de gránulo XT de celda guardados por la estadística de índices de almacenamiento, disponible en la vista V$SYSSTAT, muestra el número de bytes de E/S guardados mediante los índices de almacenamiento.

Las consultas que utilizan las siguientes comparaciones mejoran con los índices de almacenamiento:

  • Igualdad (=)

  • Inequality (<, != o >)

  • Menor o igual que (<=)

  • Mayor o igual que (>=)

  • ES NULO

  • NO ES NULO

Los índices de almacenamiento se crean automáticamente después de que el servicio Oracle Cloud SQL reciba una consulta con un predicado de comparación mayor que el valor máximo o menor que el valor mínimo para la columna en una región.

Nota

  • La eficacia de los índices de almacenamiento se puede mejorar ordenando las filas de una tabla en función de las columnas que aparecen con frecuencia en la cláusula de consulta WHERE.

  • Los índices de almacenamiento funcionan con cualquier tipo de dato no lingüístico y con tipos de dato lingüísticos similares a un índice no lingüístico.

Eliminación de E/S de disco con índices de almacenamiento

En la siguiente figura se muestran una tabla e índices de región. Los valores de la columna B en la tabla oscilan entre 1 y 8. Un índice de región almacena el mínimo de 1 y el máximo de 5. El otro índice de región almacena el mínimo de 3 y el máximo de 8.

Tabla que muestra cuatro columnas, de la A a la D. La columna B tiene seis filas de datos con los valores: 1,3,5,5,8,3. Para las filas 1-3, se muestra el texto: Min B = 1 y Max B = 5. Para las filas 4-6, se muestra el texto: Min B = 3 y Max B = 8. Para las filas 4-6 se muestra el texto: I/O eliminated by using storage index (E/S eliminada mediante el índice de almacenamiento).
Para una consulta como la siguiente, solo coincide el primer juego de filas. Se elimina la E/S de disco porque el mínimo y el máximo del segundo juego de filas no coinciden con la cláusula WHERE de la consulta.
SELECT * 
FROM TABLE 
WHERE B < 2;

Mejora del rendimiento de la unión con índices de almacenamiento

El uso de índices de almacenamiento permite que las uniones de tablas omitan operaciones de E/S innecesarias. Por ejemplo, la siguiente consulta realizaría una operación de E/S y aplicaría un filtro Bloom solo al primer bloque de la tabla de hechos. Los filtros Bloom son la clave para mejorar el rendimiento de la unión. En el ejemplo, un predicado está en la tabla de dimensiones, no en la tabla de hechos. El filtro Bloom se crea según dim.name=Hard drive, y este filtro se aplica después a la tabla de hechos. Por lo tanto, aunque el filtro esté en la tabla de dimensiones, podrá filtrar los datos en su origen (Hadoop) según los resultados de la consulta de dimensión. Esto también permite que se apliquen las optimizaciones, como los índices de almacenamiento.

SELECT count(*) 
FROM fact, dimension dim  
WHERE fact.m=dim.m and dim.product="Hard drive";
Esta imagen tiene dos tablas. La primera tabla tiene dos columnas, Dimension y M, con los valores: Fan: 1, Hard drive: 3, LED: 5 y Power cord: 8. Se aplica a esta tabla un filtro bloom con mínimo/máximo para M y da como resultado otra tabla denominada Fact. La tabla Fact tiene seis filas. La columna M de la tabla Fact tiene seis filas de datos con los siguientes valores: 1,3,5,5,8,3. Para las tres primeras filas, se muestra el texto: Realizar E/S y aplicar filtro bloom. Para las filas 4-6 se muestra el texto: Omitir E/S debido al índice de almacenamiento.

Los índices de almacenamiento eliminan completamente la E/S del segundo bloque de la tabla de hechos porque su rango mínimo/máximo (5,8) no está presente en el filtro Bloom.

Acerca del descenso de predicados

Muchos sistemas de Big Data soportan algún nivel de descarga de predicado, ya sea a través del propio tipo de archivo (por ejemplo, Apache Parquet) o a través de la partición de Hive y las API de StorageHandler. Oracle Cloud SQL aprovecha estas capacidades de descarga mediante la transferencia de predicados de Oracle Database a sistemas de soporte. Por ejemplo, el descenso de predicados activa los siguientes comportamientos automáticos:

  • Las consultas de las tablas particionadas de Hive se depuran según los predicados de filtro de las columnas de partición.

  • Las consultas de archivos de Apache Parquet y Apache ORC reducen la E/S mediante la prueba de predicados en las estructuras internas de tipo índice incluidas en estos formatos de archivo.

    Nota

    Los archivos de Parquet deben crearse con Hive o Spark. Los archivos de Parquet creados con Apache Impala no tienen las estadísticas necesarias para que el descenso de predicados se aplique.
  • Las consultas de Oracle NoSQL Database o Apache HBase utilizan predicados de SARGable para impulsar las subexploraciones de los datos en el almacén de datos remoto.

Tipos de datos necesarios para activar el descenso de predicados

El descenso de predicados necesita que estén presentes determinadas asignaciones entre los tipos de datos de Hive y los tipos de datos de Oracle. Estas asignaciones se describen en la tabla siguiente.

Tipo de dato de Hive Asignado al tipo de dato de Oracle

CHAR(m)

CHAR(n), VARCHAR2(n) donde n es >= m

VARCHAR(m)

CHAR(n), VARCHAR2(n) donde n es >= m

Cadena

CHAR(n), VARCHAR2(n)

DATE

DATE

REGISTRO DE HORA

TIMESTAMP(9) Hive TIMESTAMP tiene nanosegundos, segundos fraccionarios de 9 dígitos

TINYINT

NUMBER(3) preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido

SMALLINT 

NUMBER(5) preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido

INT  

NUMBER(10) preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido

BIGINT                    

NUMBER(19) preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido

DECIMAL(m)

NUMBER(n) donde m = n preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido

PLANTA                      

BINARY_FLOAT

DOBLE                     

BINARY_DOUBLE

BINARIA

RAW(n)

BOOLEANO

CHAR(n), VARCHAR2(n) donde n es >= 5, valores 'TRUE', 'FALSE'

BOOLEANO

NUMBER(1) preferiblemente, pero NUMBER o NUMBER(n) para cualquier valor de n es válido Valores 0 (false), 1 (true).

Acerca del descenso del procesamiento de un objeto grande de caracteres (CLOB)

Las consultas de datos de Hadoop pueden implicar el procesamiento de objetos grandes con millones de registros posibles. No es eficaz devolver estos objetos a Oracle Database para el filtrado y análisis. Oracle Cloud SQL puede proporcionar un aumento significativo del rendimiento al transferir el procesamiento de CLOB a sus propias celdas de procesamiento en el cluster de Hadoop. El filtrado en Hadoop reduce el número de filas devueltas a Oracle Database. El análisis reduce la cantidad de datos devueltos de una columna en cada fila filtrada.

Puede desactivar o volver a activar el procesamiento de CLOB según sus propias necesidades.

Esta funcionalidad se aplica actualmente solo a expresiones JSON que devuelven datos CLOB. Las expresiones de filtro JSON elegibles para la evaluación de capa de almacenamiento incluyen sintaxis simplificada, JSON_VALUE y JSON_QUERY.

Se proporcionará el mismo soporte para otros tipos de CLOB (como substr y instr), así como para los datos BLOB, en una versión futura.

Cloud SQL puede transferir el procesamiento a Hadoop para los CLOB con estas restricciones de tamaño:

  • Filtrado de columnas CLOB de hasta 1 MB de tamaño.

    La cantidad real de datos que se pueden consumir para evaluación en el servidor de almacenamiento puede variar en función del juego de caracteres utilizado.

  • Análisis de columnas de hasta 32 KB.

    Este límite hace referencia a la proyección de lista select desde el almacenamiento para el tipo de dato CLOB.

El procesamiento vuelve a Oracle Database solo cuando los tamaños de columna superan estos dos valores.

Procesamiento de documentos JSON

Para las consultas en documentos JSON grandes, la transferencia del procesamiento de CLOB a celdas de procesamiento de Cloud SQL en Hadoop puede ser muy eficaz. Considere el siguiente ejemplo, donde la información de orden de compra se almacena en JSON. Suponga que este registro podría tener hasta 25 KB de tamaño y que se deben procesar varios millones de dichos registros.
{"ponumber":9764,"reference":"LSMITH-20141017","requestor":"Lindsey Smith","email": "Lindsey@myco.com", "company":"myco" …}
Puede crear la tabla externa para acceder a estos datos de la siguiente manera. Observe que hay una única columna CLOB.
CREATE TABLE POS_DATA
  ( pos_info CLOB )
  ORGANIZATION EXTERNAL
  ( TYPE ORACLE_HDFS
    DEFAULT DIRECTORY DEFAULT_DIR
    LOCATION ('/data/pos/*')
  )
 REJECT LIMIT UNLIMITED;
A continuación, puede consultar los datos con esta sintaxis simple:
SELECT p.pos_info.email, p.pos_info.requestor
FROM POS_DATA p
WHERE p.pos_info.company='myco'

El ejemplo de consulta anterior aplica dos optimizaciones de eliminación de datos:

  • Los datos se filtran por las celdas de Cloud SQL en el cluster de Hadoop. Solo se analizan los registros que pertenecen a la compañía myco (y después del análisis, solo los datos seleccionados de estos registros se devuelven a la base de datos).

  • Las celdas de Cloud SQL del cluster analizan el juego filtrado de registros y, de cada registro, solo los valores de los dos atributos solicitados (p.pos_info.email y p.pos_info.requestor) se devuelven a la base de datos.

La siguiente tabla muestra otros ejemplos en los que el procesamiento de CLOB está soportado. Recuerde que las proyecciones (referencias a la parte seleccionada de la columna CLOB) están limitadas a 32 KB de datos CLOB, mientras que el descenso de predicados está limitado a 1 MB de datos CLOB.

consulta Comentario
SELECT count(*) FROM pos_data p WHERE pos_info is json; En este caso, el predicado garantiza que solo se devuelven las columnas que cumplen con el formato JSON.
SELECT pos_info FROM pos_data p WHERE pos_info is json; El mismo predicado que en el caso anterior, pero ahora se ha proyectado el valor CLOB.
SELECT json_value(pos_info, '$.reference') FROM pos_data p WHERE json_value(pos_info, '$.ponumber') > 9000 Aquí, el predicado se emite en un campo del documento de JSON y también se ejecuta un valor de JSON para recuperar el campo" referencia" encima del valor de JSON de CLOB proyectado.
SELECT p.pos_info.reference FROM pos_data p WHERE p.pos_info.ponumber > 9000; Funcionalmente, es la misma consulta que el ejemplo anterior, pero expresada en una sintaxis simplificada.
SELECT p.pos_info.email FROM po_data p WHERE json_exists(pos_info, '$.requestor') and json_query(pos_info, '$.requestor') is not null; En este ejemplo se muestra cómo también se pueden utilizar json_exists y json_query como predicados.

Acerca de la descarga de agregación

Oracle Cloud SQL utiliza la tecnología Oracle In-Memory para transferir el procesamiento de agregación a las celdas de Cloud SQL. Esto permite a Cloud SQL aprovechar la potencia de procesamiento del cluster de Hadoop para distribuir agregaciones entre los nodos de cluster.

El aumento del rendimiento puede ser significativamente más rápido en comparación con las agregaciones que no descargan, especialmente cuando hay un número moderado de agrupaciones de resumen. Para las consultas de una única tabla, la operación de agregación debe descargar coherentemente.

Las celdas de Cloud SQL soportan agregaciones de una única tabla y de varias tablas (por ejemplo, tablas de dimensiones que se unen a una tabla de hechos). Para agregaciones de varias tablas, Oracle Database utiliza la optimización de transformación de vector clave en la que los vectores clave se transfieren a las celdas del proceso de agregación. Este tipo de transformación es útil para las consultas SQL de unión en estrella que utilizan operadores de agregación típicos (como SUM, MIN, MAX y COUNT), que son comunes en las consultas de negocio.

Una consulta de transformación de vector es una consulta más eficaz que utiliza un filtro Bloom para las uniones. Cuando utiliza una consulta transformada de vector con celdas de Cloud SQL, el rendimiento de las uniones en la consulta mejora con la capacidad de descargar la filtración para las filas que se utilizan para la agregación. Verá una operación KEY VECTOR USE en el plan de consulta durante esta optimización.

En las celdas de Cloud SQL, las consultas transformadas de vector se benefician de un procesamiento más eficaz debido a la aplicación de columnas group-by (vectores clave) al índice de almacenamiento de Cloud SQL.

Puede que no vea los beneficios de la descarga de agregación en determinadas instancias:
  • Falta el predicado

    Si falta el predicado SYS_OP_VECTOR_GROUP_BY en el plan de ejecución, la descarga de agregación se verá afectada. Puede que falte el predicado por los siguientes motivos:
    • Presencia de un origen de fila no permitido entre la exploración de tabla y los orígenes de fila group-by.

    • La exploración de tabla no produce juegos de filas.

    • Presencia de un tipo de expresión o dato en la consulta que no se puede descargar.

    • group-by de vector se ha desactivado manualmente.

    • La tabla de exploración o configuración de tabla no espera aumentos de la descarga de agregación.

  • Falta la exploración inteligente

    Los bytes de interconexión de celdas devueltos por la exploración inteligente XT y los gránulos XT de celda solicitados para las estadísticas de descarga de predicado deben estar disponibles.

  • Faltan vectores clave

    El límite de los datos transmitidos a las celdas es de 1 MB. Si se supera este umbral, las consultas se pueden beneficiar del filtrado de vector clave inteligente, pero no necesariamente de la agregación descargada. Esta condición se conoce como modo de vector clave ligero. Debido a su tamaño grande, algunos vectores clave no se descargan totalmente. Se descargan en modo ligero junto con los vectores clave que no admiten la descarga de agregación. Los vectores clave no se serializan por completo en el modo ligero. La descarga group-by de vector está desactivada cuando los vectores clave se descargan en el modo ligero.

Nota

Consulte Optimización de la agregación en la guía de Oracle Database In-Memory para obtener más información sobre cómo funciona la agregación en Oracle Database.

Acerca de las estadísticas de Cloud SQL

Oracle Cloud SQL proporciona una serie de estadísticas que pueden contribuir datos para los análisis de rendimiento.

Cinco estadísticas de XT de celda e índice de almacenamiento claves

Si una consulta se puede descargar, las siguientes estadísticas relacionadas con XT pueden ayudarle a determinar qué tipo de ahorro de E/S se puede esperar de la descarga y la exploración inteligente.

  • gránulos XT de celda solicitados para la descarga de predicado

    El número de gránulos solicitados depende de una serie de factores, incluidos el tamaño de bloque de HDFS, la divisibilidad del origen de datos de Hadoop y la eficacia de la eliminación de particiones de Hive.

  • bytes de gránulo XT de celda solicitados para la descarga de predicado

    Número de bytes solicitados para la exploración. Es el tamaño de los datos en Hadoop que se investigarán después de la eliminación de particiones de Hive y antes de la evaluación del índice de almacenamiento.

  • bytes de interconexión de celdas devueltos por la exploración inteligente XT

    Número de bytes de E/S devueltos por una exploración inteligente XT a Oracle Database.

  • reintentos de descarga de predicado de gránulo XT de celda

    Número de veces que un proceso de Cloud SQL que se ejecuta en un nodo de datos no ha podido completar la acción solicitada. Cloud SQL reintenta automáticamente las solicitudes fallidas en otros nodos de datos que tengan una réplica de los datos. El valor de reintentos debe ser cero.

  • bytes de E/S de gránulo XT de celda guardados por el índice de almacenamiento

    Número de bytes filtrados por índices de almacenamiento en el nivel de celda de almacenamiento. Son los datos que no se han explorado, según la información proporcionada por los índices de almacenamiento.

Puede comprobar estas estadísticas antes y después de ejecutar consultas de la siguiente manera. Este ejemplo muestra los valores en nulo antes de ejecutar cualquier consulta.

SQL> SELECT sn.name,ms.value 
FROM V$MYSTAT ms, V$STATNAME sn 
WHERE ms.STATISTIC#=sn.STATISTIC# AND sn.name LIKE '%XT%'; 

NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          0 
cell XT granule bytes requested for predicate offload     0
cell interconnect bytes returned by XT smart scan         0 
cell XT granule predicate offload retries                 0
cell XT granule IO bytes saved by storage index           0 

Puede comprobar algunas de estas estadísticas o todas después de la ejecución de una consulta para probar la eficacia de la misma, como en el siguiente ejemplo:

SQL> SELECT n.name, round(s.value/1024/1024) 
FROM v$mystat s, v$statname n
WHERE s.statistic# IN (462,463)
AND s.statistic# = n.statistic#;

cell XT granule bytes requested for predicate offload  32768
cell interconnect bytes returned by XT smart scan   32

Cinco estadísticas de descarga de agregación

Las siguientes estadísticas pueden ayudarle a analizar el rendimiento de la descarga de agregación.

  • grupo de vectores por operaciones enviados a la celda

    Número de veces que se pueden descargar agregaciones a la celda.

  • grupo de vectores por operaciones no enviados a la celda debido a la cardinalidad

    Número de exploraciones no descargadas debido a un gran wireframe.

  • grupo de vectores por filas procesadas en la celda

    Número de filas agregadas en la celda.

  • grupo de vectores por filas devueltas por celda

    Número de filas agregadas devueltas por la celda.

  • grupo de vectores por juegos de filas procesados en la celda

    Número de juegos de filas agregadas a la celda.

Puede revisar estas estadísticas ejecutando las consultas de la siguiente manera:

SQL> SELECT count(*) FROM bdsql_parq.web_sales;

  COUNT(*)
----------
 287301291

SQL> SELECT substr(n.name, 0,60) name, u.value
FROM v$statname n, v$mystat u
WHERE ((n.name LIKE 'key vector%') OR
       (n.name LIKE 'vector group by%') OR
       (n.name LIKE 'vector encoded%') OR
       (n.name LIKE '%XT%') OR
       (n.name LIKE 'IM %' AND n.name NOT LIKE '%spare%'))
      AND u.sid=userenv('SID')
      AND n.STATISTIC# = u.STATISTIC#
      AND u.value > 0;


NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          808 
cell XT granule bytes requested for predicate offload     2.5833E+10
cell interconnect bytes returned by XT smart scan         6903552 
vector group by operations sent to cell                   1
vector group by rows processed on cell                    287301291
vector group by rows returned by cell                     808

Nueve estadísticas de vectores clave

Las siguientes estadísticas pueden ayudarle a analizar la eficacia de los vectores clave enviados a la celda.

  • vectores clave enviados a la celda

    Número de vectores clave descargados a la celda.

  • vector clave filtrado en la celda

    Número de filas filtradas por un vector clave en la celda.

  • vector clave sondeado en la celda

    Número de filas probadas por un vector clave en la celda.

  • filas de vectores clave procesadas por valor

    Número de claves de unión procesadas con su valor.

  • filas de vectores clave procesadas por código

    Número de claves de unión procesadas con el código de diccionario.

  • filas de vectores clave filtradas

    Número de claves de unión omitidas debido a la omisión de bits.

  • serializaciones de vectores clave en modo ligero para celda

    Número de veces que no se ha codificado un vector clave debido al formato o el tamaño.

  • vectores clave enviados a celda en modo ligero debido a cuota

    Número de vectores clave descargados a la celda para filtrado no exacto debido a la cuota de metadatos de 1 MB.

  • efilters de vectores clave creados

    No se ha enviado un vector clave a una celda, sino un efilter (similar a un filtro Bloom).

Puede revisar estas estadísticas ejecutando las consultas de la siguiente manera:

SELECT substr(n.name, 0,60) name, u.value
FROM v$statname n, v$mystat u
WHERE ((n.name LIKE 'key vector%') OR
       (n.name LIKE 'vector group by%') OR
       (n.name LIKE 'vector encoded%') OR
       (n.name LIKE '%XT%'))
      AND u.sid=userenv('SID')
      AND n.STATISTIC# = u.STATISTIC#


NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          250 
cell XT granule bytes requested for predicate offload     61,112,831,993
cell interconnect bytes returned by XT smart scan         193,282,128 
key vector rows processed by value                        14,156,958
key vector rows filtered                                  9,620,606
key vector filtered on cell                               273,144,333
key vector probed on cell                                 287,301,291
key vectors sent to cell                                  1
key vectors sent to cell in lite mode due to quota        1
key vector serializations in lite mode for cell           1
key vector efilters created                               1
Consejo

El blog sobre el inicio rápido de Big Data SQL, publicado en The Data Warehouse Insider, muestra cómo utilizar estas estadísticas para analizar el rendimiento de Big Data SQL. Cloud SQL y Big Data SQL comparten la misma tecnología subyacente, por lo que se aplican las mismas reglas de optimización. Consulte la parte 2, la parte 7 y la parte 10.