Memória Manual: SORT_AREA_SIZE

O parâmetro PGA_AGGREGATE_TARGET foi introduzido no 9i para simplificar a utilização das áreas de memória privadas de usuários.
Documentação do 9iR2: http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1158.htm#REFRN10165

Antes do 9i, as seguintes áreas tinham que ser gerenciadas manualmente:
SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE

A memória automática (tanto de SGA quanto PGA) atende muito bem a maioria dos casos (como quase tudo no Oracle Database).

Mas a utilização manual ainda é possível até hoje, no 12.1.0.2, e ainda tem sua função.

Para habilitar as área manuais, é preciso alterar o parâmetro WORKAREA_SIZE_POLICY para MANUAL, e atribuir o valor desejado para a área em questão.

Este parâmetro pode ser alterado em SESSION, o que seria ideal para uma carga específica.

Fiz um teste com o SQL abaixo, que faz um grande HASH JOIN.

SELECT T1.OBJECT_TYPE, COUNT(T1.OBJECT_TYPE) FROM T1, T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID GROUP BY T1.OBJECT_TYPE ORDER BY 2;

Utilizando PGA_AGGREGATE_TARGET de 512M, e apenas uma sessão conectada na instância, este SQL possuia o plano de execução abaixo:

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     40 |00:06:50.58 |     384K|    429K|  45508 |       |       |      		|        |
|   1 |  SORT ORDER BY       |      |      1 |     40 |     40 |00:06:50.58 |     384K|    429K|  45508 |  2048 |  2048 | 2048   (0)|        |
|   2 |   HASH GROUP BY      |      |      1 |     40 |     40 |00:06:50.58 |     384K|    429K|  45508 |  1200K|  1200K| 1379K  (0)|        |
|*  3 |    HASH JOIN         |      |      1 |   1443M|   1463M|00:04:48.25 |     384K|    429K|  45508 |   465M|    23M|   93M  (1)|    368K|
|   4 |     TABLE ACCESS FULL| T2   |      1 |     11M|     11M|00:00:13.86 |     192K|    192K|      0 |       |       |      		|        |
|   5 |     TABLE ACCESS FULL| T1   |      1 |     11M|     11M|00:00:11.06 |     192K|    192K|      0 |       |       |      		|        |
----------------------------------------------------------------------------------------------------------------------------------------------

Veja que é utilizada TEMP (Used-Tmp), e para isso gravações (Writes).

Os tempos desta execução foram os abaixo (gosto de fazer teste três vezes seguidas, para eliminar os pontos fora da curva):

Elapsed: 00:06:50.60
Elapsed: 00:06:41.43
Elapsed: 00:06:49.06

Em seguida alterei a memória de usuário para MANUAL, e coloquei 256M para a área responsável pelo SORT (ou seja, metade do que já estava disponível pela PGA).

SQL> ALTER SESSION SET workarea_size_policy = MANUAL;
SQL> ALTER SESSION SET sort_area_size = 268435456;

O plano de execução passou então a ser o seguinte:

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     40 |00:06:20.50 |     384K|    384K|       |       |          |
|   1 |  SORT ORDER BY       |      |      1 |     40 |     40 |00:06:20.50 |     384K|    384K|  3072 |  3072 | 2048  (0)|
|   2 |   HASH GROUP BY      |      |      1 |     40 |     40 |00:06:20.50 |     384K|    384K|  1214K|  1214K| 9972K (0)|
|*  3 |    HASH JOIN         |      |      1 |   1443M|   1463M|00:04:20.75 |     384K|    384K|   465M|    23M|  633M (0)|
|   4 |     TABLE ACCESS FULL| T2   |      1 |     11M|     11M|00:00:11.64 |     192K|    192K|       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |     11M|     11M|00:00:11.25 |     192K|    192K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Além da eliminação do uso da TEMP, os tempos foram reduzidos em 8%-10%. Parece pouco, mas se o SQL for executado em concorrência, a contenção de I/O causada pelo uso da TEMP será um sério limitador de escalabilidade.

Elapsed: 00:06:20.56
Elapsed: 00:06:17.79
Elapsed: 00:06:20.89

4 comments

  1. Portilho, ótimo artigo. O que você fez aí na verdade foi aumentar o tamanho da sort area e isso teve impacto positivo no SQL pq ele deixou de usar o TEMP e passou a fazer a ordenação em memória. Para isso não haveria necessidade de mudar para a pga manual, vocè poderia ter configurado apenas o valor de 256 MB p/ o parâmetro sort_area_size e o efeito (acredito eu) teria sido o mesmo, pois ele passaria a ser o valor mínimo daquela subdivisão da PGA. Você chegou a fazer esse teste? Se possível nos dê um feedback!

    []s

      1. Verdade! O que eu falei se aplica somente às subdivisões da SGA, conforme descrito abaixo no mesmo link que vc passou:

        “To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value.”

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.