Eu tenho uma consulta que obtém diferentes planos de consulta ao longo do tempo e um deles leva menos de um minuto para ser executado, o outro leva dias. Posso dizer ao banco de dados para usar um determinado plano (retirado de explicação ou usando instruções inline) para que o bom plano seja sempre usado ou pelo menos o catastrófico seja sempre evitado? O bom plano tem apenas ref joins enquanto o ruim também tem index e eq_ref e uma ordenação diferente.
Eu tenho uma consulta em execução geralmente em poucos segundos/minutos que se torna extremamente lenta depois de um tempo (cerca de uma semana) levando dias! executar. A consulta fica apenas em 'Enviando dados' e o uso da CPU é 100%. O servidor é um Mariadb 10.4 e o sistema está executando muitas outras consultas complexas sem problemas, apenas essa consulta específica parece atingir algumas limitações do servidor ou um bug de desempenho.
A quantidade de dados não parece ser relevante, pois a consulta é executada em diferentes bancos de dados que são criados e excluídos para cada projeto específico com diferentes quantidades de registros, mas o problema ocorre mesmo para projetos menores.
Uma reinicialização do servidor faz com que a consulta seja executada rapidamente novamente por um tempo, mas o problema ocorre repetidamente. O problema não parece ocorrer antes que o servidor atinja sua quantidade máxima permitida de RAM, mesmo que ainda haja RAM livre no servidor para usar (reduzi o tamanho do buffer especificamente para testá-lo). Uma vez que o problema se manifesta, isso acontece com os mecanismos InnoDB e MyISAM. Como a consulta é executada muito rapidamente após a reinicialização do servidor, não parece ser um problema de índices ausentes ou algo semelhante. Alguma dica do que pode causar o comportamento e como investigar/resolver?
Segue a consulta:
CREATE TABLE counts_otus (
_sample_id INT,
_region_sample_id INT,
sequencesPerOtu INT,
PRIMARY KEY (_region_sample_id),
INDEX (_sample_id)
) ENGINE=InnoDB AS
SELECT _sample_map._sample_id, _sample_map._region_sample_id, (
SELECT COUNT(*)
FROM cluster AS otu
WHERE otu._cluster_sample_id = _sample_map._region_sample_id
) + (
SELECT count(*)
FROM cluster AS otu
INNER JOIN cluster AS mem
ON otu._region_sample_id = mem._cluster_sample_id
WHERE otu._cluster_sample_id = _sample_map._region_sample_id
) + 1 AS sequencesPerOtu
FROM Region
INNER JOIN _sample_map USING (primaryAccession)
INNER JOIN sample USING (_sample_id)
WHERE regionTag is NULL
AND sampleTag is NULL
AND sample_type <> 'otumap'
;
Os planos de consulta são realmente diferentes, o que pode ser determinante para acertar o problema: O plano ao executar rápido é
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
| 1 | PRIMARY | sample | ALL | PRIMARY,id_sample_type | NULL | NULL | NULL | 10 | Using where |
| 1 | PRIMARY | _sample_map | ref | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4 | silvangs_slv_main_pid23875_rid26315.sample._sample_id | 52186 | Using where |
| 1 | PRIMARY | Region | ref | PRIMARY,fk_rgnTag | fk_rgnTag | 100 | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | otu | ref | PRIMARY,id_cluster | id_cluster | 4 | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | mem | ref | id_cluster | id_cluster | 4 | silvangs_slv_main_pid23875_rid26315.otu._region_sample_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | otu | ref | id_cluster | id_cluster | 4 | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id | 1 | Using index |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
O plano ao ser executado extremamente lento (eliminou a consulta em execução e levou a explicação de sua seleção logo em seguida:
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
| 1 | PRIMARY | sample | ALL | PRIMARY,id_sample_type | NULL | NULL | NULL | 10 | Using where |
| 1 | PRIMARY | _sample_map | ref | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4 | silvangs_slv_main_pid23875_rid26315.sample._sample_id | 41361 | Using where |
| 1 | PRIMARY | Region | ref | PRIMARY,fk_rgnTag | fk_rgnTag | 100 | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | mem | index | id_cluster | id_cluster | 4 | NULL | 738041 | Using index |
| 3 | DEPENDENT SUBQUERY | otu | eq_ref | PRIMARY,id_cluster | PRIMARY | 4 | silvangs_slv_main_pid23875_rid26315.mem._cluster_sample_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | otu | ref | id_cluster | id_cluster | 4 | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id | 57226 | Using index |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
Portanto, não existem apenas os tipos de junção "ref" ao executar lentamente, mas também "index" e "eq_ref", que devem ser melhores até onde posso dizer, mas acabam presos por dias.
A pergunta foi postada originalmente no stackoverflow, onde recebi a sugestão de que seria mais adequado aqui no dba, aqui o link para a pergunta: https://stackoverflow.com/questions/60952661/why-does-a-query-becomes- extremamente-lento-independentemente-da-quantidade-de-dados
É possível executar um arquivo sql (um arquivo de texto contendo várias instruções SQL) em um banco de dados oracle a partir de uma linha de comando bash shell (por exemplo, sem GUI gráfica e sem clientes interativos)
Algo como: sqlplus -f consultas.sql ...
Espera-se que o comando execute todas as instruções no arquivo queries.sql e saia com um código de saída apropriado (por exemplo, 0 se todas as consultas forem executadas corretamente, diferente de zero caso contrário)