O autor do artigo é
Victor Varlamov (
varlamovVp18 ), OCP.
O artigo original foi publicado em 07/07/2017.
Agradecimentos especiais ao autor da tradução -
brutaltag .
Nosso sistema de relatórios geralmente executa centenas de solicitações demoradas que são acionadas por vários eventos. Os parâmetros de consulta são uma lista de clientes e um intervalo de tempo (diário, semanal, mensal). Devido aos dados desiguais nas tabelas, uma consulta pode produzir uma linha ou um milhão de linhas, dependendo dos parâmetros do relatório (clientes diferentes têm diferentes números de linhas nas tabelas de fatos). Cada relatório é feito na forma de um pacote com uma função principal que aceita parâmetros de entrada, executa transformações adicionais, abre um cursor estático com
variáveis associadas e, finalmente, retorna esse cursor aberto. O parâmetro do banco de dados CURSOR_SHARING está definido como FORCE.
Em tal situação, é preciso lidar com um desempenho ruim, tanto no caso da reutilização do plano de consulta pelo otimizador, quanto quando
a consulta é
completamente analisada com parâmetros na forma de
literais . Variáveis vinculadas podem causar um plano de consulta abaixo do ideal.
Em seu livro “Oracle Expert Practices”, Alex Gorbachev apresenta uma história interessante contada por Tom Kite. Toda segunda-feira chuvosa, os usuários tinham que lidar com um plano de consulta modificado. É difícil de acreditar, mas era:
“De acordo com as observações do usuário final, quando choveu muito na segunda-feira, o desempenho do banco de dados foi terrível. Em qualquer outro dia da semana ou segunda-feira não havia problemas sem chuva. Em uma conversa com o DBA, Tom Kite aprendeu que as dificuldades continuaram até que o banco de dados foi forçado a reiniciar, após o qual o desempenho se tornou normal. Essa foi uma solução alternativa: segunda-feira chuvosa - reinicie. ”
Este é um caso real e o problema foi resolvido completamente sem nenhuma mágica, apenas graças ao excelente conhecimento de como o Oracle funciona. Vou mostrar a solução no final do artigo.
Aqui está um pequeno exemplo de como as variáveis relacionadas funcionam.
Crie uma tabela com dados desiguais.
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
Em outras palavras, temos uma tabela VVP_HARD_PARSE_TEST com um milhão de linhas, onde, em 10.000 casos, o campo é C2 = 99, 8 registros com C2 = 1 e o restante com C2 = 1.000.000. O histograma no campo C2 indica o otimizador Oracle sobre essa distribuição de dados. Essa situação é conhecida como
distribuição desigual de dados , e um histograma pode ajudá-lo a escolher o plano de consulta correto com base nos dados solicitados.
Observamos consultas simples nesta tabela. Obviamente para o pedido
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :pse p = 1, INDEX RANGE SCAN é a melhor opção; para o caso p = 1000000, é melhor usar FULL TABLE SCAN. As consultas Query1 e Query1000000 são idênticas, com exceção do texto nos comentários, isso é feito para obter diferentes identificadores do plano 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);
Agora vamos ver os planos 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 você pode ver, um plano para solicitações diferentes é criado apenas uma vez, no momento da primeira execução (existe apenas um cursor filho com CHILD_NUMBER = 0 para cada solicitação). Cada solicitação é executada duas vezes (EXECUÇÃO = 2). Durante a análise forçada, o Oracle recupera os valores das variáveis associadas e seleciona um plano de acordo com esses valores. Mas ele usa o mesmo plano para a próxima execução, mesmo que as variáveis relacionadas tenham mudado na segunda execução. Planos não ideais são usados - Query1000000 com a variável C2 = 1 usa FULL TABLE SCAN em vez de INDEX RANGE SCAN e vice-versa.
Está claro que corrigir o aplicativo e usar parâmetros como literais na consulta é a maneira mais adequada de resolver o problema, mas leva ao SQL dinâmico com suas deficiências conhecidas. Outra maneira é desativar a consulta de variáveis relacionadas (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) ou excluir histogramas (
link ).
Uma das soluções possíveis é um uso alternativo de políticas de acesso a dados, também conhecido como
Banco de Dados Privado Virtual (controle de acesso detalhado, controle de acesso
refinado , controle de nível de linha). Isso permite alterar solicitações dinamicamente e, portanto, pode causar uma análise completa do plano de solicitações sempre que a solicitação usar controle de acesso detalhado. Essa técnica é descrita em detalhes em um
artigo de Randalph Geist . A desvantagem desse método é o número crescente de análises completas e a incapacidade de manipular planos de consulta.
Veja o que faremos agora. Após analisar nossos dados, decidimos dividir os clientes em três categorias - Grande, Médio e Pequeno (LMS ou 9-5-1) - de acordo com o número de transações ou transações durante o ano. Além disso, o número de linhas no relatório depende estritamente do período: mensal - grande, semanal - médio, diário - pequeno ou 9-5-1. Além disso, a solução é simples - tornaremos o predicado da política de segurança dependente de cada categoria e de cada período. Portanto, para cada solicitação, temos 9 possíveis cursores filhos. Além disso, consultas com políticas diferentes nos levarão aos mesmos identificadores de consulta, o que possibilita implementar o SQL PLAN MANAGEMENT (linha de base do plano 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;
Agora, se queremos incorporar essa tecnologia no relatório, precisamos adicionar HARD_PARSE_TABLE à consulta (isso não estragará um pouco) e chamar CALC_PREDICATES antes que a consulta principal seja executada.
Vamos ver como essa técnica pode transformar o exemplo 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);
Vejamos os planos de execução:
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
Parece ótimo! Cada consulta é executada duas vezes, com diferentes cursores filho e planos diferentes. Para o parâmetro C2 = 1.000.000, vemos FULL TABLE SCAN em ambas as consultas e, para o parâmetro C1 = 1, sempre vemos INDEX RANGE SCAN.
No final, dou uma solução para o caso das segundas-feiras chuvosas:
“Acontece que todo final de semana no domingo havia um backup a frio , para que todos os planos de consulta fossem regenerados na primeira execução na segunda-feira de manhã. Um dos funcionários geralmente iniciava o trabalho mais cedo do que os outros, e seu plano de solicitação era bem executado para outros usuários durante a semana. No entanto, se estava chovendo, esse usuário estava atrasado no início do dia útil devido a problemas com sua rota matinal. Em seguida, o cálculo em lote dos relatórios foi o primeiro a iniciar, mas o plano de consulta foi completamente ruim para os demais casos devido a valores inadequados das variáveis associadas. ”
E algumas visualizações úteis do sistema:
•
dba_tab_histograms, all_tab_histograms, user_tab_histogramsv$vpd_policyv$sql_bind_capture•
dba_hist_sqlbind