{"id":853,"date":"2010-12-22T16:09:29","date_gmt":"2010-12-22T18:09:29","guid":{"rendered":"http:\/\/www.thiagovespa.com.br\/blog\/?p=853"},"modified":"2025-10-26T22:41:57","modified_gmt":"2025-10-27T01:41:57","slug":"otimizar-memoria-oracle-db-em-desenvolvimento","status":"publish","type":"post","link":"https:\/\/thiagovespa.com.br\/blog\/2010\/12\/22\/otimizar-memoria-oracle-db-em-desenvolvimento\/","title":{"rendered":"Otimizar mem\u00f3ria Oracle DB em Desenvolvimento"},"content":{"rendered":"

M\u00e1quina de desenvolvedor deveria ter mem\u00f3ria infinita \ud83d\ude42 ou pr\u00f3ximo disso. Como isso ainda n\u00e3o \u00e9 poss\u00edvel, utilizamos algumas maneira de otimizar o consumo de mem\u00f3ria. Vamos ver como fazer isso para o Oracle 10g XE em ambiente de desenvolvimento. N\u00e3o fa\u00e7a isso em ambientes de produ\u00e7\u00e3o! N\u00f3s iremos modificar o SGA (System Global Area) do Oracle. Para mais informa\u00e7\u00f5es sobre o que \u00e9 SGA, veja nesse site<\/a>.<\/p>\n

    \n
  1. Fa\u00e7a backup do seguinte arquivo: $ORACLE_HOME\/dbs\/spfileXE.ora que \u00e9 o arquivo que iremos alterar. No meu linux ele est\u00e1 localizado em: \/usr\/lib\/oracle\/xe\/app\/oracle\/product\/10.2.0\/server\/dbs\/spfileXE.ora<\/li>\n
  2. Abra o SQL Plus (Linux: \"sqlplus \/nolog\"<\/em> | Windows: \"sqlplus.exe \/nolog<\/em>\").<\/li>\n
  3. Conecte com um usu\u00e1rio SYSDBA: \"conn \/ as sysdba\"<\/em><\/li>\n
  4. Verifique a SGA com o comando: \"show sga\"<\/em>. Deve aparecer algo parecido com isso:\n
    SQL> show sga\n\nTotal System Global Area\u00a0 805306368 bytes\nFixed Size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0       1261444 bytes\nVariable Size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0    213909628 bytes\nDatabase Buffers\u00a0\u00a0 \u00a0\u00a0     587202560 bytes\nRedo Buffers\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0     2932736 bytes<\/pre>\n<\/li>\n
  5. Verifique os parametros do SGA com o comando: \"show parameter sga<\/em>\".\n
    SQL> show parameter sga\n\nNAME\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0 \u00a0 VALUE\n------------------------------------ ----------- ------------------------------\nlock_sga\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 boolean\u00a0\u00a0 \u00a0 FALSE\npre_page_sga\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0 boolean\u00a0\u00a0 \u00a0 FALSE\nsga_max_size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0 big integer 768M\nsga_target\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0  big integer 768M<\/pre>\n<\/li>\n
  6. Altere o sga_target para 130M (creio que seja mais que suficiente para m\u00e1quina de desenvolvimento): \"alter system set SGA_TARGET=130M scope=spfile;<\/em>\"<\/li>\n
  7. Altere o sga_max_size para 256M: \"alter system set sga_max_size=256M scope=spfile;<\/em>\"<\/li>\n
  8. Desligue a base: \"shutdown;\"<\/em><\/li>\n
  9. Ligue novamente: \"startup;\"<\/em><\/li>\n
  10. Execute o: \"show sga<\/em>\":\n
    SQL> show sga\n\nTotal System Global Area\u00a0 268435456 bytes\nFixed Size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0       1258392 bytes\nVariable Size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0    188746856 bytes\nDatabase Buffers\u00a0\u00a0 \u00a0\u00a0\u00a0     75497472 bytes\nRedo Buffers\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0     2932736 bytes<\/pre>\n<\/li>\n
  11. Execute o: \"show parameter sga<\/em>\":\n
    SQL> show parameter sga\n\nNAME\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0 \u00a0 VALUE\n------------------------------------ ----------- ------------------------------\nlock_sga\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 boolean\u00a0\u00a0 \u00a0 FALSE\npre_page_sga\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0 boolean\u00a0\u00a0 \u00a0 FALSE\nsga_max_size\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0 big integer 256M\nsga_target\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 big integer 132M<\/pre>\n<\/li>\n<\/ol>\n
    Como podemos ver, o uso de mem\u00f3ria do SGA est\u00e1 bem reduzido. Caso voc\u00ea tenha algum problema similar a esse:<\/p>\n
    ORA-00604: error occurred at recursive SQL level 1
    \nORA-04030: out of process memory when trying to allocate 16 bytes (sql area,kglhin: temp)<\/p><\/blockquote>\n
    restaure o backup do arquivo spfileXE.ora e se voc\u00ea esqueceu de fazer o backup utilize o procedimento de reinstala\u00e7\u00e3o do oracle-xe<\/a>.<\/p>\n