Matthew Moisen's questions

É possível examinar o plano de execução de uma instrução SQL executada dentro de um bloco PLSQL?

DECLARE
    l_count PLS_INTEGER;
BEGIN
    SELECT COUNT(1) INTO l_count
    FROM foo;
END;
/

Para SQL regular, eu normalmente executaria o seguinte para verificar o plano de execução:

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

No entanto, isso apenas relata:

NOTE: cannot fetch plan for SQL_ID: 3q0sujncq54wy, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Eu tenho uma tabela com uma chave estrangeira auto-referencial e gostaria de atualizar um determinado pai e todos os descendentes do pai com a mesma atualização recursiva.

Ao usar um UPDATE com uma CTE recursiva que retorna apenas 10 linhas de 25.000 linhas, o otimizador usa uma semijunção de hash com varredura sequencial na tabela que está sendo atualizada, em vez do loop aninhado mais otimizado e varredura de índice. Tem um tempo de execução lento de cerca de 5-10ms.

Aumentar o tamanho dessa tabela para 250 mil linhas levará ao uso da verificação de índice. Ironicamente, o tempo de execução é realmente muito mais rápido (~0,5 a 1,0ms), por uma ordem de grandeza, comparado a uma tabela de 25K (~5-10ms segundos), precisamente porque está usando o índice em vez de varredura sequencial.

Meu palpite aqui é que o otimizador é incapaz de executar primeiro o CTE e depois planejar a atualização, em vez disso, ele precisa planejar com antecedência e está assumindo incorretamente que o CTE está retornando um número muito maior de linhas do que realmente é.

O Postgres não permite dicas do otimizador de índice. Além de definir enable_seqscan como desativado na produção, existe alguma solução alternativa para que o postgres use o índice?

Configurar:

drop table emp;

create table emp (id int primary key, manager_id int, department text);
create index emp_manager_id on emp (manager_id);

insert into emp 
select i id, 
    case when mod(i, 10) = 0 then null else i - 1 end manager_id, 
    null department
from generate_series(0, 25000) as i;

analyze emp;
vacuum emp;

Aqui está a atualização DML. Isso atualiza apenas 10 linhas. Não importa se eu uso IN, EXISTS ou atualização do CTE recursivo, todos eles resultam em uma varredura sequencial

explain 
with recursive foo as (
    select id, manager_id, department
    from emp
    where id = 1000

    union all

    select emp.id, emp.manager_id, emp.department
    from emp join foo on emp.manager_id = foo.id
)
update emp
set department = 'IT'
where id in (select id from foo);

Resulta em

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Update on emp  (cost=766.85..939.24 rows=101 width=74)
   CTE foo
     ->  Recursive Union  (cost=0.29..763.57 rows=101 width=40)
           ->  Index Scan using emp_pkey on emp emp_1  (cost=0.29..8.30 rows=1 width=40)
                 Index Cond: (id = 1000)
           ->  Nested Loop  (cost=0.29..75.32 rows=10 width=40)
                 ->  WorkTable Scan on foo foo_1  (cost=0.00..0.20 rows=10 width=4)
                 ->  Index Scan using emp_manager_id on emp emp_2  (cost=0.29..7.50 rows=1 width=40)
                       Index Cond: (manager_id = foo_1.id)
   ->  Hash Semi Join  (cost=3.28..175.67 rows=101 width=74)
         Hash Cond: (emp.id = foo.id)
         ->  Seq Scan on emp  (cost=0.00..145.01 rows=10001 width=14)
         ->  Hash  (cost=2.02..2.02 rows=101 width=32)
               ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32)

Explicar analisar dá o mesmo resultado. Estou usando explicar aqui por brevidade.

Essa semijunção de hash com varredura sequencial não é ideal, pois apenas 10 linhas de 25.000 estão sendo atualizadas. Um loop aninhado com uma varredura de índice seria ideal aqui.

Definir enable_seqscan=off reduz o tempo para ~0,1ms (de ~5-10ms)

Se eu não usar uma CTE recursiva, a seguinte atualização using generate_seriesmostra que o índice emp_id é usado corretamente para realizar a atualização por meio de um loop aninhado. Isso é o que eu esperaria da atualização recursiva do CTE.

explain 
update emp 
set department = 'IT' 
where id in (
    select i from generate_series(1000,1009) i
);


                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Update on emp  (cost=0.43..83.59 rows=11 width=74)
   ->  Nested Loop  (cost=0.43..83.59 rows=11 width=74)
         ->  HashAggregate  (cost=0.14..0.25 rows=11 width=32)
               Group Key: i.i
               ->  Function Scan on generate_series i  (cost=0.00..0.11 rows=11 width=32)
         ->  Index Scan using emp_pkey on emp  (cost=0.29..7.58 rows=1 width=14)
               Index Cond: (id = i.i)

Se eu aumentar o número de linhas na tabela para 250K de 10K, o plano de explicação resultará no uso ideal do índice. No entanto, com varredura de 25.000 linhas/seq, leva ~5-10ms para executar. Com 250 mil linhas, a verificação do índice leva ~ 0,5-0,1 ms.

Meu palpite aqui é que o postgres não é capaz de executar primeiro o CTE e depois calcular um plano para a atualização. Ele precisa calcular um plano antes de executar o CTE. Portanto, o postgres não pode saber que apenas 10 linhas estão sendo retornadas do CTE e, em vez disso, precisa adivinhar o número. Portanto, o postgres está supondo que o CTE retornará algo como 1000 linhas, o que faz com que ele prefira a varredura sequencial quando a tabela contém apenas 25K. A razão pela qual minha tabela de 250K usa a varredura de índice, suponho, é que o postgres continua a adivinhar que o CTE está retornando 1000 linhas, mas de 250K uma varredura de índice faz mais sentido.

O Postgres não permite dicas do otimizador de índice. Além de definir enable_seqscan como desativado na produção, existe alguma solução alternativa para que o postgres use o índice?

A solução de @a_horse_with_no_name usando emp.id = any(array(select id from foo))é ótima. Isso resulta na seguinte explicação simples, que é um pouco diferente:

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Update on emp  (cost=44.19..48.93 rows=10 width=46)
   CTE foo
     ->  Recursive Union  (cost=0.00..42.17 rows=101 width=11)
           ->  Seq Scan on emp emp_1  (cost=0.00..3.08 rows=1 width=11)
                 Filter: (id = 0)
           ->  Hash Join  (cost=0.33..3.71 rows=10 width=11)
                 Hash Cond: (emp_2.manager_id = foo.id)
                 ->  Seq Scan on emp emp_2  (cost=0.00..2.66 rows=166 width=11)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4)
                       ->  WorkTable Scan on foo  (cost=0.00..0.20 rows=10 width=4)
   InitPlan 2 (returns $2)
     ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
   ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
         Filter: (id = ANY ($2))

Alguém pode explicar a diferença entre essas duas partes:

Original com enable_seqscan=off:

   ->  Nested Loop  (cost=2.56..294.11 rows=101 width=74) (actual time=0.091..0.118 rows=10 loops=1)
         ->  HashAggregate  (cost=2.27..3.28 rows=101 width=32) (actual time=0.076..0.080 rows=10 loops=1)
               Group Key: foo.id
               Batches: 1  Memory Usage: 24kB
               ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32) (actual time=0.024..0.068 rows=10 loops=1)
         ->  Index Scan using emp_pkey on emp  (cost=0.29..2.88 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=10)
               Index Cond: (id = foo.id)

Usando any(array(...)):

   InitPlan 2 (returns $2)
     ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
   ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
         Filter: (id = ANY ($2))

Primeiro, minha consulta original resulta em um HashAggregate do cte recursivo foo.iddepois de executar a varredura CTE. Somente depois disso ele percorre o empíndice. Não entendo porque está fazendo isso. Usando any(array(...)), ele pulará esta etapa e simplesmente fará um loop aninhado sobre a varredura cte e a varredura de índice.

Segundo, e provavelmente o mais importante, usar any(array(...))resultados neste arquivo InitPlan 2. Acredito que o que está acontecendo aqui é que de any(array(...))alguma forma força o planejador de consultas a executá-las como duas consultas diferentes. Primeiro ele executa o CTE, que retorna apenas 10 linhas. Então, o planejador sabe que com apenas 10 linhas ele pode usar uma varredura de índice em vez de seqscan. Minha solução original, por algum motivo, não pode forçar o planejador de consultas a executá-las como duas consultas diferentes, portanto, o planejador de consultas não sabe de antemão quantas linhas estão sendo retornadas.

Alguma ideia?

Normalmente, para verificar a existência no Oracle, farei:

SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'

No entanto, se a footabela contiver várias linhas em que bar='baz', essa consulta examinará desnecessariamente a tabela inteira para relatar a contagem total.

Como faço para verificar rapidamente se uma coluna em uma tabela contém pelo menos uma linha com um valor especificado e tenho a consulta em curto-circuito, de modo que assim que detectar que o valor existe, a consulta será interrompida e retornará, ao contrário para digitalizar a mesa inteira?

Idealmente, eu gostaria que essa consulta retornasse um valor escalar, independentemente de quantas linhas na tabela contenham o valor.

A solução a seguir parece causar um curto-circuito (em comparação com SELECT COUNT(1), isso é muito mais rápido para uma tabela grande), mas retornará 1 se o valor existir ou não retornará nenhuma linha se o valor não existir. Seria melhor se uma linha fosse sempre retornada.

SELECT 1
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)

DBMS_SCHEDULER está ocupando todo o meu tablespace SYSAUX. Eu executei dbms_scheduler.purge_logque excluí 100 milhões de linhas de dba_scheduler_job_run_details, no entanto, v$sysaux_occupantso tamanho do arquivo de dados permanece inalterado. Existe alguma ação adicional que preciso tomar para limpar o espaço de tabela SYSAUX dos dados gerados pelo DBMS_SCHEDULER?

A tentativa de inserir uma única linha em uma tabela recém-criada em meu tablespace regular falha com:

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

Posso ver que JOB_SCHEDULERestá ocupando 92,4% do tablespace SYSAUX consultando v$sysaux_occupants:

SELECT
    occupant_name,                    
    round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants                  
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;

Eu originalmente tinha mais de 100 milhões de linhas em dba_scheduler_job_run_details.

Ontem, executei o comando purge (que levou 3,5 horas):

BEGIN
    dbms_scheduler.purge_log;
END;
/

Hoje, dba_scheduler_job_run_detailstem menos de 1 mil linhas.

No entanto, a consulta v$sysaux_occupantspermanece inalterada; hoje ainda diz que JOB_SCHEDULERestá ocupando 92,4%. Da mesma forma, consultar os tamanhos dos meus arquivos de dados mostra que o SYSAUX ainda está no máximo:

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
    v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

Parece que na verdade não excluí o espaço consumido por DBMS_SCHEDULER.

Existe alguma etapa que estou faltando para limpar todo o espaço consumido pelo DBMS_SCHEDULER?