El autor del artículo es
Victor Varlamov (
varlamovVp18 ), OCP.
El artículo original fue publicado el 07/07/2017.
Un agradecimiento especial al autor de la traducción:
brutaltag .
Nuestro sistema de informes generalmente ejecuta cientos de solicitudes largas que son activadas por varios eventos. Los parámetros de consulta son una lista de clientes y un intervalo de tiempo (diario, semanal, mensual). Debido a los datos desiguales en las tablas, una consulta puede producir una fila o un millón de filas, dependiendo de los parámetros del informe (diferentes clientes tienen diferentes números de filas en las tablas de hechos). Cada informe se realiza en forma de paquete con una función principal que toma parámetros de entrada, realiza transformaciones adicionales, luego abre un cursor estático con
variables asociadas y finalmente devuelve este cursor abierto. El parámetro DB CURSOR_SHARING está establecido en FORCE.
En tal situación, uno tiene que lidiar con un bajo rendimiento, tanto en el caso de la reutilización del plan de consulta por el optimizador, como cuando
la consulta se
analiza por completo con parámetros en forma de
literales . Las variables enlazadas pueden causar un plan de consulta subóptimo.
En su libro "Oracle Expert Practices", Alex Gorbachev cuenta una interesante historia contada por Tom Kite. Cada lunes lluvioso, los usuarios tenían que lidiar con un plan de consulta modificado. Es difícil de creer, pero fue:
“Según las observaciones del usuario final, cuando llovió mucho el lunes, el rendimiento de la base de datos fue terrible. En cualquier otro día de la semana o el lunes no hubo problemas sin lluvia. De una conversación con el DBA, Tom Kite se enteró de que las dificultades continuaron hasta que la base de datos se vio obligada a reiniciarse, después de lo cual el rendimiento se normalizó. Esa fue una solución: lunes lluvioso, reiniciar ”.
Este es un caso real, y el problema se resolvió completamente sin ningún tipo de magia, solo gracias al excelente conocimiento de cómo funciona Oracle. Mostraré la solución al final del artículo.
Aquí hay un pequeño ejemplo de cómo funcionan las variables relacionadas.
Crea una tabla con datos desiguales.
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
En otras palabras, tenemos una tabla VVP_HARD_PARSE_TEST con un millón de filas, donde en 10,000 casos el campo es C2 = 99, 8 registros con C2 = 1 y el resto con C2 = 1,000,000. El histograma en el campo C2 indica al optimizador de Oracle sobre esta distribución de datos. Esta situación se conoce como
distribución desigual de datos , y un histograma puede ayudarlo a elegir el plan de consulta correcto en función de los datos solicitados.
Observamos consultas simples a esta tabla. Obviamente por la solicitud
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :psi p = 1, entonces INDEX RANGE SCAN es la mejor opción, para el caso p = 1000000 es mejor usar FULL TABLE SCAN. Las consultas Query1 y Query1000000 son idénticas, con la excepción del texto en los comentarios, esto se hace para obtener diferentes identificadores del plan de consulta.
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Ahora veamos los planes de consulta:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Como puede ver, se crea un plan para diferentes solicitudes solo una vez, en el momento de la primera ejecución (solo existe un cursor secundario con CHILD_NUMBER = 0 para cada solicitud). Cada solicitud se ejecuta dos veces (EJECUCIÓN = 2). Durante el análisis duro, Oracle recupera los valores de las variables asociadas y selecciona un plan de acuerdo con estos valores. Pero usa el mismo plan para la próxima ejecución, a pesar de que las variables relacionadas cambiaron en la segunda ejecución. Se utilizan planes no óptimos: Query1000000 con la variable C2 = 1 usa ESCANEO DE TABLA COMPLETA en lugar de ESCANEO DE RANGO DE ÍNDICE y viceversa.
Está claro que arreglar la aplicación y usar parámetros como literales en la consulta es la forma más adecuada de resolver el problema, pero conduce a un SQL dinámico con sus defectos conocidos. Otra forma es deshabilitar la consulta para variables relacionadas (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) o eliminar histogramas (
enlace ).
Una de las posibles soluciones es un uso alternativo de políticas de acceso a datos, también conocido como
Base de datos privada virtual (control de acceso detallado, control de acceso de
grano fino , control de nivel de fila). Esto le permite cambiar las solicitudes sobre la marcha y, por lo tanto, puede hacer un análisis completo del plan de solicitud cada vez que la solicitud utiliza un control de acceso detallado. Esta técnica se describe en detalle en un
artículo de Randalph Geist . La desventaja de este método es el creciente número de análisis completos y la incapacidad de manipular los planes de consulta.
Mira lo que haremos ahora. Después de analizar nuestros datos, decidimos dividir a los clientes en tres categorías: grande, mediana y pequeña (LMS o 9-5-1), de acuerdo con el número de transacciones o transacciones durante el año. Además, el número de líneas en el informe depende estrictamente del período: mensual - grande, semanal - medio, diario - pequeño o 9-5-1. Además, la solución es simple: haremos que el predicado de la política de seguridad dependa de cada categoría y de cada período. Entonces, por cada solicitud, obtenemos 9 posibles cursores secundarios. Además, las consultas con diferentes políticas nos llevarán a los mismos identificadores de consulta, esto permite implementar SQL PLAN MANAGEMENT (línea de base del plan sql).
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
Ahora, si queremos incorporar dicha tecnología en el informe, debemos agregar HARD_PARSE_TABLE a la consulta (esto no lo arruinará un poco) y llamar a CALC_PREDICATES antes de que se ejecute la consulta principal.
Veamos cómo esta técnica puede transformar el ejemplo anterior:
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Veamos los planes de ejecución:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Se ve genial! Cada consulta se ejecuta dos veces, con diferentes cursores secundarios y diferentes planes. Para el parámetro C2 = 1,000,000, vemos ESCANEO DE TABLA COMPLETA en ambas consultas, y para el parámetro C1 = 1 siempre vemos ESCANEO DE RANGO DE ÍNDICE.
Al final doy una solución al caso de los lunes lluviosos:
“Resulta que todos los fines de semana del domingo había una copia de seguridad en frío , por lo que todos los planes de consulta se regeneraron en la primera ejecución el lunes por la mañana. Uno de los empleados generalmente comenzó su trabajo antes que los demás, y su plan de solicitud se ejecutó bien para otros usuarios durante la semana. Sin embargo, si estaba lloviendo, este usuario llegaba tarde al comienzo de la jornada laboral debido a problemas con su ruta matutina. Luego, el cálculo por lotes de los informes fue el primero en comenzar, pero el plan de consulta fue completamente malo para los casos restantes debido a valores inapropiados de las variables asociadas ".
Y algunas vistas útiles del sistema:
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind