Nesse artigo vc tem exemplos de como mudar os valores.... http://www.dbis.informatik.uni-goettingen.de/Teaching/oracle-doc/admin-guide/appa_aix.htm
[]´s Sharif ----- Original Message ----- From: "Jemerson Dutra" <[EMAIL PROTECTED]> To: <oracle_br@yahoogrupos.com.br> Sent: Tuesday, April 11, 2006 1:51 PM Subject: [oracle_br] Re: Performance Horrivel Phael, fiz as alterações que vc disse mas segundo os exemplos do SHARIF o AIX nao pegou a configuracao, ele continua com os parametros defaults. # vmo -a memory_frames = 1572864 pinnable_frames = 1432380 maxfree = 144 minfree = 128 minperm% = 20 <----------- minperm = 290532 maxperm% = 80 <----------- maxperm = 1162131 strict_maxperm = 0 maxpin% = 80 maxpin = 1258292 maxclient% = 80 <----------- lrubucket = 131072 --- Em oracle_br@yahoogrupos.com.br, "Sharif G Raduan" <[EMAIL PROTECTED]> escreveu > > no metalink vc pode encontrar notas de tunning no AIX, mas o minperm / > maxparm é a quantidade de memória para cache de file systens, quando vc está > utilizando oracle não precisa dessas quantidades grandes que são default. > > Subject: AIX: Database performance gets slower the longer the > database is running > Doc ID: Note:316533.1 Type: PROBLEM > Last Revision Date: 24-MAR-2006 Status: PUBLISHED > > In this Document > Symptoms > Cause > Solution > References > > > > -------------------------------------------------------------------- ------------ > > > > Applies to: > Oracle Server - Enterprise Edition - Version: > AIX5L Based Systems (64-bit) > AIX Based Systems (32-bit) > Bull Escala RL AIX (64-bit) > AIX 4.3 Based Systems (64-bit) > Oracle databases running on AIX based systems. > Symptoms > Database performance continues to get slower and slower the longer the > database is left running. You may also notice a continuing increase in the > amount of paging space usage the longer the database is left running. > However, database performance returns to normal after rebooting the system, > or shutting down and restarting the database. > Cause > It is likely that you have not tuned the AIX Virtual Memory Manager (VMM). > The default values for the AIX VMM are generally not appropriate for use > with relational databases. The default values for the AIX VMM will > gradually allow up to 80% of physical memory to be used to buffer file I/O. > Since Oracle is already buffering file I/O in the SGA, the same data is > unnecessarily being buffered twice, and leaves only 20% of physical memory > to run the Oracle database(s) and all other programs. This causes the > majority of the Oracle database to be pushed out of physical memory to > paging space, thus greatly impacting database performance. > > The information in this article does not apply, or the impact will be much > less, if you are using one or more of the following storage types for the > database datafiles, because AIX does not buffer file I/O for these types: > > Raw logical volumes, filesystems using the Concurrent I/O (CIO) option, > filesystems using the Direct I/O (DIO) option > > Note that there is no "built-in" support for CIO or DIO in Oracle Database > 9iR2 (9.2.0) or lower, though you can force the use of CIO (JFS2) or DIO > (JFS) with filesystem mount options. > > Also note that tuning the AIX VMM is outside the scope of Oracle Support. > If you need help with checking, setting, or tuning the AIX VMM beyond what > is covered in this article, you must contact your AIX systems administrator > and/or IBM Support for further assistance. > > Solution > To check whether your system is using the untuned default values for the AIX > VMM, run the command: > > /usr/sbin/vmo -a > > If you do not have the /usr/sbin/vmo file you will need to have your AIX > systems administrator load the AIX fileset "bos.perf.tune". The vmo command > will list out all of the VMM parameters and their current values. The > parameters you want to examine are: > > MINPERM%, MAXPERM%, and MAXCLIENT% > > Here is an example of the vmo report: > > # vmo -a > memory_frames = 1572864 > pinnable_frames = 1431781 > maxfree = 1088 > minfree = 960 > minperm% = 20 > minperm = 294356 > maxperm% = 80 > maxperm = 1177427 > strict_maxperm = 0 > maxpin% = 80 > maxpin = 1258292 > maxclient% = 80 > lrubucket = 131072 > . > The untuned default settings are MINPERM%=20%, MAXPERM%=80%, and > MAXCLIENT%=80%. There is no "correct" value for these parameters and only > extensive testing will reveal the optimal values. The optimal value may be > different for different databases on the same system, so keep this in mind > when tuning the VMM and choose values which work best for all of the > databases you will be running on the system. Use the following values as a > starting point: > > MINPERM% = 10-15%, MAXPERM% = 20-30%, MAXCLIENT% = MAXPERM% > > To get a snapshot of how much physical memory is being used by AIX to buffer > file I/O, run the command: > > /usr/bin/svmon -G > > The svmon command is part of the same AIX fileset "bos.perf.tune" that vmo > belongs to. The last line of the svmon output should be "in use". Add the > values for "in use / pers" and "in use / clnt". Now divide the sum by the > value for "memory / size". For best database performance, this value should > generally not be higher than 30% (0.30). > > Here is an example of the svmon output: > > # svmon -G > > size inuse free pin virtual > memory 131072 129432 1640 11704 50091 > pg space 262144 100913 > > work pers clnt lpage > pin 11704 0 0 0 > in use 47062 76126 6244 0 > > In this example, (in use / pers) 76126 plus (in use / clnt) 6244 equals > 82370. 82370 divided by (memory / size) 131072 equals 0.628 or > approximately 63% of physical memory being used by AIX to buffer file I/O. > This indicates the AIX VMM needs to be tuned to allow more physical memory > to be used by Oracle and other processes, and less physical memory to be > used to buffer file I/O. > > Remember that although AIX associates this memory with the Oracle processes > (because Oracle requested the file I/O), all of the memory used to buffer > file I/O is completely allocated and controlled by AIX, not Oracle. If you > need help checking, setting, or tuning the AIX VMM, contact your AIX systems > administrator and/or IBM Support. You may also want to review the AIX > "Performance Management Guide" by IBM linked in the References section > below. > > UPDATE: After this article was originally written, IBM has introduced a new > VMM parameter which is also very helpful with this issue. The parameter > is... > > > lru_file_repage > The default value is "1", but it is recommended to set this to "0". This > setting hints to the VMM to only steal file pages (from the AIX file buffer > cache) and leave the computational pages (from the SGA) alone. > > This new lru_file_repage parameter is only available on AIX 5.2 ML04+ and > AIX 5.3 ML01+ > > > > > []´s > > Sharif > > ----- Original Message ----- > From: "Phael" <[EMAIL PROTECTED]> > To: <oracle_br@yahoogrupos.com.br> > Sent: Monday, April 10, 2006 11:10 AM > Subject: Re: [oracle_br] Re: Performance Horrivel > > > > Jemerson, > > > > Também não sou nenhum especialista em AIX. > > Na verdade quando tive problemas de performance no AIX > > fiz varios tunings na base e se esgotaram as > > tentativas de melhoras, tendo pouco sucesso. Chamei um > > tecnico para otimizar o AIX e ele me disse que esse parametro > > controla a distribuição da memória Cache da máquina sendo que > > por default ele libera 80% do recurso para o SO e 20% para > > demais softwares... vai entende isso???...se estiver falando besteira > > alguem me corrija por facor.....enfim mudei esse parametro!!! > > Como estavam tendo bastante paginação e swap a mudança > > dessa configuração pra 10% e 5% foi otima acabando com > > os problemas de paginações e swap. > > Esse procedimento foi feito em um AIX 4.3.3 mas acho que > > serve para o AIX 5.2. > > Não custa tentar, caso não der certo é só remover a linha e > > entrar em contato com algum especialista em AIX para > > entender melhor esses reajuste. > > > > atc > > > > Raphael > > > > > > ----- Original Message ----- > > From: "Jemerson Dutra" <[EMAIL PROTECTED]> > > To: <oracle_br@yahoogrupos.com.br> > > Sent: Monday, April 10, 2006 10:45 AM > > Subject: [oracle_br] Re: Performance Horrivel > > > > > > Raphael, desculpe minha ignorancia mas o que esses parametros > > controlam? e o que eles farao?? > > Jemerson > > > > --- Em oracle_br@yahoogrupos.com.br, "Phael" <[EMAIL PROTECTED]> escreveu > >> > >> aff... > >> errei o caminho. > >> /usj.... troque para /usr... > >> > >> vmtune:2:once:/usr/samples/kernel/vmtune -p5 -P10 -r8 -R16 - f128 - > > F144 > >> > >> Raphael > >> > >> ----- Original Message ----- > >> From: "Phael" <[EMAIL PROTECTED]> > >> To: <oracle_br@yahoogrupos.com.br> > >> Sent: Monday, April 10, 2006 8:48 AM > >> Subject: Re: [oracle_br] Re: Performance Horrivel > >> > >> > >> > Jemerson, > >> > > >> > Adicione esssa linha no seu arquivo /etc/inittab > >> > > >> > vmtune:2:once:/usj/samples/kernel/vmtune -p5 -P10 -r8 -R16 - f128 - > > F144 > >> > > >> > reinicie a maquina! > >> > > >> > E rode de novo o vmtune para ver se as alterações foram feitas: > >> > esses parametros deverão ser reajustados... > >> > minperm% = 20 para 5 > >> > maxperm% = 80 para 10 > >> > > >> > Atc > >> > > >> > Raphael > >> > > >> > > >> > ----- Original Message ----- > >> > From: "Jemerson Dutra" <[EMAIL PROTECTED]> > >> > To: <oracle_br@yahoogrupos.com.br> > >> > Sent: Friday, April 07, 2006 7:57 AM > >> > Subject: [oracle_br] Re: Performance Horrivel > >> > > >> > > >> > Raphael, segue o resultado do vmtune > >> > > >> > memory_frames = 1048576 > >> > pinnable_frames = 940632 > >> > maxfree = 128 > >> > minfree = 120 > >> > minperm% = 20 > >> > minperm = 193333 > >> > maxperm% = 80 > >> > maxperm = 773332 > >> > strict_maxperm = 0 > >> > maxpin% = 80 > >> > maxpin = 838861 > >> > maxclient% = 80 > >> > lrubucket = 131072 > >> > defps = 1 > >> > nokilluid = 0 > >> > numpsblks = 1081344 > >> > npskill = 8448 > >> > npswarn = 33792 > >> > v_pinshm = 0 > >> > pta_balance_threshold = 0 > >> > pagecoloring = 0 > >> > framesets = 2 > >> > mempools = 1 > >> > lgpg_size = 0 > >> > lgpg_regions = 0 > >> > num_spec_dataseg = 0 > >> > spec_dataseg_int = 512 > >> > memory_affinity = 1 > >> > htabscale = -1 > >> > force_relalias_lite = 0 > >> > relalias_percentage = 0 > >> > data_stagger_interval = 161 > >> > large_page_heap_size = 0 > >> > kernel_heap_psize = 4096 > >> > soft_min_lgpgs_vmpool = 0 > >> > vmm_fork_policy = 0 > >> > low_ps_handling = 1 > >> > mbuf_heap_psize = 4096 > >> > strict_maxclient = 1 > >> > cpu_scale_memp = 8 > >> > lru_poll_interval = 0 > >> > lru_file_repage = 1 > >> > memory_frames = 1048576 > >> > minpgahead = 2 > >> > memory_frames = 1048576 > >> > minpgahead = 2 > >> > maxpgahead = 8 > >> > pd_npages = 65536 > >> > maxrandwrt = 0 > >> > numclust = 1 > >> > numfsbufs = 196 > >> > sync_release_ilock = 0 > >> > lvm_bufcnt = 9 > >> > j2_minPageReadAhead = 2 > >> > j2_maxPageReadAhead = 128 > >> > j2_nBufferPerPagerDevice = 512 > >> > j2_nPagesPerWriteBehindCluster = 32 > >> > j2_maxRandomWrite = 0 > >> > j2_nRandomCluster = 0 > >> > j2_non_fatal_crashes_system = 0 > >> > j2_syncModifiedMapped = 1 > >> > jfs_clread_enabled = 0 > >> > jfs_use_read_lock = 1 > >> > hd_pvs_opn = 6 > >> > hd_pbuf_cnt = 1280 > >> > j2_inodeCacheSize = 400 > >> > j2_metadataCacheSize = 400 > >> > j2_dynamicBufferPreallocation = 16 > >> > j2_maxUsableMaxTransfer = 512 > >> > pgahd_scale_thresh = 0 > >> > hd_pendqblked = 0 > >> > psbufwaitcnt = 347632 > >> > fsbufwaitcnt = 1541572 > >> > rfsbufwaitcnt = 0 > >> > xpagerbufwaitcnt = 0 > >> > --- Em oracle_br@yahoogrupos.com.br, "Phael" <[EMAIL PROTECTED]> > > escreveu > >> >> > >> >> Ola Jemerson, > >> >> > >> >> Ja tive problemas com performance com AIX. > >> >> No meu caso o problema era com um parametro do SO. > >> >> Distrubuição da memória cache. > >> >> > >> >> executa esse arquivo ai: > >> >> > >> >> # /usjr/samples/kernel/vmtune > >> >> > >> >> colo o resultado ai. > >> >> > >> >> Raphael > >> >> > >> >> > >> >> > >> >> ----- Original Message ----- > >> >> From: "Jemerson Dutra" <[EMAIL PROTECTED]> > >> >> To: <oracle_br@yahoogrupos.com.br> > >> >> Sent: Thursday, April 06, 2006 2:35 PM > >> >> Subject: [oracle_br] Re: Performance Horrivel > >> >> > >> >> > >> >> Ae chiappa, da uma ajuda ai. > >> >> Jemerson > >> >> --- Em oracle_br@yahoogrupos.com.br, "Jemerson Dutra" > > <[EMAIL PROTECTED]> > >> >> escreveu > >> >> > > >> >> > Senhores, estamos com um servidor em producao que foi criado > > com > >> >> > alguns parametros default por causa de um ERP. Nos deparamos > > apos > >> >> 35 > >> >> > dias, que o servidor esta com uma performance horrivel. > > Gostaria > >> > da > >> >> > ajuda dos senhores, comentando/incluindo e ou alterando meu > >> >> init.ora. > >> >> > Servidor IBM AIX 5.2 > >> >> > 4GB RAM > >> >> > 4GB SWAP > >> >> > 2 CPUS DE 1.2GHZ > >> >> > ORACLE 10GB DE BANCO > >> >> > CRIADO BLOCK_SIZE 4096KB > >> >> > # initMFGPRO.ora - oracle instance parameter file > >> >> > # include database configuration parameters > >> >> > ifile = /Hdados2/admin/MFGPRO/config.MFGPRO > >> >> > open_cursors = 512 > >> >> > # NLS Parameters > >> >> > NLS_LANGUAGE = "AMERICAN" > >> >> > NLS_TERRITORY = "AMERICA" > >> >> > NLS_NUMERIC_CHARACTERS = ".," > >> >> > # tuning parameters > >> >> > db_files = 200 > >> >> > db_file_multiblock_read_count = 32 # LARGE > >> >> > # --- Autor : Jemerson - Data: 20/02/2006 > >> >> > # ---------------------------------------- > >> >> > shared_pool_size = 300000000 ## 400000000 > >> >> > # ---------------------------------------- > >> >> > sort_area_size = 262144 ## 1048576 # > >> > LARGE > >> >> > sort_area_retained_size = 262144 ## > >> >> 1048576 > >> >> > # LARGE > >> >> > large_pool_size = 155000000 #250000000 #155000000 ## > > 100000000 # > >> >> > 80MIL large_pool_size = 614400 > >> >> > java_pool_size = 20971520 ##100000000 > >> >> > # ---------------------------------------- > >> >> > log_checkpoint_interval = 10000 > >> >> > processes = 450 #500 # 600 # > >> >> LARGE > >> >> > log_buffer = 2048000 # 1048576 # > >> > LARGE > >> >> > max_dump_file_size = 10240 # limit trace file size to 5M ea > >> >> > compatible=9.2.0 > >> >> > UTL_FILE_DIR=* # if you want to use SQL Loader > >> >> > optimizer_mode = CHOOSE > >> >> > # ---------------------------------------- > >> >> > # liberado em 19/09/2005 > >> >> > CURSOR_SHARING = FORCE ##EXACT ##FORCE > >> >> > # ---------------------------------------- > >> >> > dispatchers="(PROTOCOL=TCP) (dispatchers=20)" > >> >> > service_names= MFGPRO > >> >> > instance_name= MFGPRO > >> >> > # ---------------------------------------- > >> >> > # melhoria no servicos MTS 10/10/2005 > >> >> > # Autor: Jemerson Dutra > >> >> > mts_max_servers=100 > >> >> > mts_servers=40 > >> >> > mts_max_dispatchers=40 > >> >> > O7_DICTIONARY_ACCESSIBILITY=TRUE > >> >> > GLOBAL_NAMES = TRUE > >> >> > dml_locks = 3408 # LARGE > >> >> > open_links = 4 > >> >> > sort_multiblock_read_count = 4 > >> >> > dbwr_io_slaves = 2 > >> >> > DB_WRITER_PROCESSES = 2 > >> >> > DB_CACHE_SIZE = 17825792 > >> >> > session_cached_cursors=100 > >> >> > timed_statistics = false; > >> >> > > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> -------------------------------------------------------------- --- > > --- > >> > ------------------------------------------------------ > >> >> Atenção! As mensagens deste grupo são de acesso público e de > >> > inteira > >> >> responsabilidade de seus remetentes. > >> >> Acesse: http://www.mail- archive.com/oracle_br@yahoogrupos.com.br/ > >> >> -------------------------------------------------------------- --- > > --- > >> > ------------------------------------------------------ > >> > __________________________________________________________________ > >> >> > >> >> Este Grupo recebe o apoio da SQL Magazine - > >> > www.devmedia.com.br/sqlmagazine > >> >> > > __________________________________________________________________ > >> >> O grupo Oracle_br não aceita anexos. Quando oferecer algum > > arquivo, > >> > tenha o > >> >> link do mesmo para evitar trafego(pedidos) desnecessário. > >> >> Links do Yahoo! Grupos > >> >> > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > --------------------------------------------------------------- --- > > -------------------------------------------------------- > >> > Atenção! As mensagens deste grupo são de acesso público e de > > inteira > >> > responsabilidade de seus remetentes. > >> > Acesse: http://www.mail- archive.com/oracle_br@yahoogrupos.com.br/ > >> > --------------------------------------------------------------- --- > > -------------------------------------------------------- > > __________________________________________________________________ > >> > > >> > Este Grupo recebe o apoio da SQL Magazine - > >> > www.devmedia.com.br/sqlmagazine > >> > __________________________________________________________________ > >> > O grupo Oracle_br não aceita anexos. Quando oferecer algum > > arquivo, tenha > >> > o > >> > link do mesmo para evitar trafego(pedidos) desnecessário. > >> > Links do Yahoo! Grupos > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > --------------------------------------------------------------- --- > > -------------------------------------------------------- > >> > Atenção! As mensagens deste grupo são de acesso público e de > > inteira > >> > responsabilidade de seus remetentes. > >> > Acesse: http://www.mail- archive.com/oracle_br@yahoogrupos.com.br/ > >> > --------------------------------------------------------------- --- > > -------------------------------------------------------- > > __________________________________________________________________ > >> > > >> > Este Grupo recebe o apoio da SQL Magazine - > >> > www.devmedia.com.br/sqlmagazine > >> > __________________________________________________________________ > >> > O grupo Oracle_br não aceita anexos. Quando oferecer algum > > arquivo, tenha > >> > o link do mesmo para evitar trafego(pedidos) desnecessário. > >> > Links do Yahoo! Grupos > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > > > > > > > > > > > > > ------------------------------------------------------------------ -------------------------------------------------------- > > Atenção! As mensagens deste grupo são de acesso público e de inteira > > responsabilidade de seus remetentes. > > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ > > ------------------------------------------------------------------ -------------------------------------------------------- __________________________________________________________________ > > > > Este Grupo recebe o apoio da SQL Magazine - > > www.devmedia.com.br/sqlmagazine > > __________________________________________________________________ > > O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha > > o > > link do mesmo para evitar trafego(pedidos) desnecessário. > > Links do Yahoo! Grupos > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------ -------------------------------------------------------- > > Atenção! As mensagens deste grupo são de acesso público e de inteira > > responsabilidade de seus remetentes. > > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ > > ------------------------------------------------------------------ -------------------------------------------------------- __________________________________________________________________ > > > > Este Grupo recebe o apoio da SQL Magazine - > > www.devmedia.com.br/sqlmagazine > > __________________________________________________________________ > > O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha > > o link do mesmo para evitar trafego(pedidos) desnecessário. > > Links do Yahoo! Grupos > > > > > > > > > > > > > > > > > > > > _______________________________________________________ > Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! > http://br.acesso.yahoo.com > -------------------------------------------------------------------------------------------------------------------------- Atenção! As mensagens deste grupo são de acesso público e de inteira responsabilidade de seus remetentes. Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ --------------------------------------------------------------------------------------------------------------------------__________________________________________________________________ Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine __________________________________________________________________ O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha o link do mesmo para evitar trafego(pedidos) desnecessário. Links do Yahoo! Grupos _______________________________________________________ Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz. http://br.info.mail.yahoo.com/ -------------------------------------------------------------------------------------------------------------------------- Atenção! As mensagens deste grupo são de acesso público e de inteira responsabilidade de seus remetentes. Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ --------------------------------------------------------------------------------------------------------------------------__________________________________________________________________ Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine __________________________________________________________________ O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha o link do mesmo para evitar trafego(pedidos) desnecessário. Links do Yahoo! Grupos <*> Para visitar o site do seu grupo na web, acesse: http://br.groups.yahoo.com/group/oracle_br/ <*> Para sair deste grupo, envie um e-mail para: [EMAIL PROTECTED] <*> O uso que você faz do Yahoo! Grupos está sujeito aos: http://br.yahoo.com/info/utos.html