Início / dba / Perguntas / 330570
Accepted
Isaac PM
Asked: 2023-08-24 13:04:15 +0800 CST

Como saber quanta memória é usada por cada usuário do banco de dados no buffer de cache?

  • 772

Usando esta consulta:

SELECT
        A.CACHE_BUFFER_TOTAL,
        B.CACHE_BUFFER_USED,
        A.CACHE_BUFFER_TOTAL - B.CACHE_BUFFER_USED AS CACHE_BUFFER_FREE
    FROM
        (SELECT BYTES / 1024 / 1024 AS CACHE_BUFFER_TOTAL FROM V$SGAINFO WHERE NAME = 'Buffer Cache Size') A,
        (SELECT (SELECT COUNT(*) FROM V$BH) * (SELECT BLOCK_SIZE FROM V$BUFFER_POOL) / 1024 / 1024 AS CACHE_BUFFER_USED FROM DUAL) B;

... posso determinar o tamanho total do cache do buffer e o total utilizado; entretanto, gostaria de saber para cada usuário quanto as consultas do usuário estão ocupando o cache do buffer.

Existe uma maneira simples de conseguir isso no Oracle 21C?

oracle

1 respostas

  1. Best Answer

    Você tem uma pergunta interessante. No entanto, de acordo com meu entendimento do Oracle e dos vários caches que existem no SGA e no PGA, não há meios reais de determinar o uso do cache de cada usuário individual, porque isso anularia o conceito de cache (eficiente).

    A ideia geral por trás do cache (ou Arquitetura de Memória da Oracle) é armazenar informações na RAM para permitir a recuperação e reutilização eficiente de dados, instruções, planos de consulta, etc. para cada usuário que consulta uma instância Oracle.

    A base para gerenciamento de memória em uma instância Oracle está documentada da seguinte forma:

    ( ênfase minha)

    Introdução às estruturas de memória do banco de dados Oracle

    Quando uma instância é iniciada, o Oracle Database aloca uma área de memória e inicia processos em segundo plano.

    A área de memória armazena informações como as seguintes:

    • Código do programa
    • Informações sobre cada sessão conectada, mesmo que não esteja ativa no momento
    • Informações necessárias durante a execução do programa, por exemplo, o estado atual de uma consulta da qual as linhas estão sendo buscadas
    • Informações como dados de bloqueio que são compartilhados e comunicados entre processos
    • Dados armazenados em cache, como blocos de dados e registros redo, que também existem no disco

    Isso é dividido em:

    ( ênfase minha)

    Estruturas Básicas de Memória

    O Oracle Database inclui diversas áreas de memória, cada uma contendo vários subcomponentes.

    As estruturas básicas de memória associadas ao Oracle Database incluem:

    • Área global do sistema (SGA)

      O SGA é um grupo de estruturas de memória compartilhada, conhecidas como componentes SGA, que contêm dados e informações de controle para uma instância do Oracle Database. Todos os processos do servidor e em segundo plano compartilham o SGA. Exemplos de dados armazenados no SGA incluem blocos de dados em cache e áreas SQL compartilhadas. (... e planos de execução )

    • Área global do programa (PGA)
      Uma PGA é uma região de memória não compartilhada que contém dados e informações de controle exclusivamente para uso por um processo Oracle. O Oracle Database cria o PGA quando um processo Oracle é iniciado.
      Existe um PGA para cada processo do servidor e processo em segundo plano. A coleção de PGAs individuais é a instância total PGA ou instância PGA. Os parâmetros de inicialização do banco de dados definem o tamanho da instância PGA, não dos PGAs individuais.

    • Área global do usuário (UGA)
      A UGA é a memória associada a uma sessão do usuário.

    • Áreas de código de software
      As áreas de código de software são partes da memória usadas para armazenar código que está sendo executado ou que pode ser executado. O código do Oracle Database é armazenado em uma área de software que normalmente fica em um local diferente dos programas do usuário – um local mais exclusivo ou protegido.

    Referência: Arquitetura de Memória (Oracle | Docs)

    Há uma imagem muito boa na documentação acima, que mostra os diferentes componentes da Arquitetura de Memória da Oracle:

    Estruturas de memória do banco de dados Oracle

    Cache de buffer do banco de dados

    O cache do buffer do banco de dados conterá dados que já foram recuperados do disco e que o Oracle Database Memory Manager considera dignos de serem mantidos na memória para recuperação futura de qualquer pessoa que consulte a mesma instância Oracle . Não faria sentido armazenar esses dados para cada usuário individual, pois ocuparia muito espaço na RAM.

    Portanto, você não poderá determinar a qual usuário os dados pertencem.

    Área SQL Compartilhada

    O mesmo também é válido para a Área SQL Compartilhada. Por que armazenar múltiplos da mesma consulta, se é mais eficiente armazenar apenas uma versão da (mesma) consulta e depois executar a consulta para qualquer pessoa que queira realizar a mesma consulta?

    Área SQL Privada

    Agora, isso pode parecer promissor como uma área para resumir o cache de cada usuário. No entanto, as informações armazenadas nesta área não parecem muito interessantes:

    ( ênfase minha)

    A área SQL privada contém informações exclusivas para cada usuário, como variáveis ​​de ligação e buffers de tempo de execução e, por sua vez, está associada à área SQL compartilhada que contém o código SQL real analisado .

    Respondendo sua pergunta

    ... Gostaria de saber para cada usuário o quanto as consultas do usuário estão ocupando o cache do buffer.

    Devido ao conceito e à natureza do cache de buffer, não será possível determinar as consultas do usuário que ocupam o cache de buffer.

    De certa forma, tudo o que um usuário executa é "desnormalizado" para que todos possam se beneficiar de uma recuperação eficiente de dados/instruções/planos de execução de consultas.

    Espero que esta informação forneça um bom ponto de partida.

    A propósito, alguns dos SGBDs mais conhecidos gerenciarão a memória de acordo com princípios semelhantes. (Microsoft SQL Server, MySQL, PostgreSQL, ....) Nem todos terão a mesma Arquitetura de Memória, mas sim semelhantes o suficiente para você entender como a memória é gerenciada em um DMBS (Database Management System).

    Boa sorte.

    • 4

relate perguntas