{"id":665,"date":"2016-05-01T22:28:02","date_gmt":"2016-05-02T01:28:02","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=665"},"modified":"2019-11-11T18:57:51","modified_gmt":"2019-11-11T21:57:51","slug":"saiba-como-um-erro-simples-de-aplicacao-pode-detonar-um-banco-de-dados","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/saiba-como-um-erro-simples-de-aplicacao-pode-detonar-um-banco-de-dados\/","title":{"rendered":"Saiba como um erro simples de aplica\u00e7\u00e3o pode detonar um banco de dados"},"content":{"rendered":"<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">O problema tratado neste artigo ocorreu em um de nossos clientes com uma aplica\u00e7\u00e3o de um terceiro, esse problema causou um grande impacto no banco de dados pois fez o banco aumentar o consumo de CPU e atingir um LOAD 30 nos dois nodes do RAC. Para simular o problema vamos utilizar uma base Oracle 11.2.0.4 instalada num servidor virtual com 1 processador, essa configura\u00e7\u00e3o \u00e9 muito diferente da configura\u00e7\u00e3o de produ\u00e7\u00e3o onde o problema ocorreu mas serve para ilustrar e facilitar o entendimento do problema.<\/span><\/p>\n<h2 style=\"text-align: justify;\"><span style=\"font-size: 14pt;\">Vis\u00e3o geral do problema<\/span><\/h2>\n<p style=\"text-align: justify;\"><strong><span style=\"font-size: 12pt;\">Cen\u00e1rio:<\/span><\/strong><\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Banco de dados Oracle RAC 11.2.0.4 com servidor Oracle Linux el6 x86_64;<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Aplica\u00e7\u00e3o WEB com milhares de usu\u00e1rios que num determinado dia do m\u00eas tem uma volume\u00a0muito grande de acessos;<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">O problema ocorre nesse dia de pico de acesso;<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"font-size: 12pt;\">Sintomas:<\/span><\/strong><\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">O Consumo de CPU dos 2 nodes do RAC aumenta e atinge um LOAD de 30;<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">N\u00e3o foi identificada qualquer instru\u00e7\u00e3o SQL que estivesse com auto consumo de CPU;<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Relat\u00f3rio AWR indica os eventos &#8220;library cache lock&#8221; e &#8220;library cache: mutex X&#8221; entre os tr\u00eas maiores eventos de WAIT;<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Na se\u00e7\u00e3o Time Model do AWR verificamos que a estat\u00edstica &#8220;failed parse elapse time&#8221; atigiu 22% do DBTime da base;<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong><span style=\"font-size: 12pt;\">Causa:<\/span><\/strong><\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Foi identificada uma consulta de uma tabela que n\u00e3o existia na base e era executada por quase todas as sess\u00f5es que estavam acessando a aplica\u00e7\u00e3o;<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong><span style=\"font-size: 12pt;\">Contorno:<\/span><\/strong><\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Cria\u00e7\u00e3o da tabela inexistente;<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"font-size: 12pt;\">Solu\u00e7\u00e3o:<\/span><\/strong><\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Solicitar ao fornecedor da aplica\u00e7\u00e3o para alterar o c\u00f3digo e excluir a consulta que esta causando o problema, j\u00e1 que aparentemente ela n\u00e3o afeta a funcionalidade da aplica\u00e7\u00e3o.<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-size: 14pt;\">Simula\u00e7\u00e3o do problema<\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Para simular o problema utilizamos uma base Oracle 11.2.0.4 num servidor Linux virtual com 1 processador e realizamos os seguintes passos:<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">1. Criamos um programa Java para simular a aplica\u00e7\u00e3o enviando 500 vezes por segundo uma consulta que acessa uma tabela inexistente;<\/span><\/p>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:800px;height:520px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/>10<br \/>11<br \/>12<br \/>13<br \/>14<br \/>15<br \/>16<br \/>17<br \/>18<br \/>19<br \/>20<br \/>21<br \/>22<br \/>23<br \/>24<br \/>25<br \/>26<br \/>27<br \/>28<br \/>29<br \/>30<br \/>31<br \/>32<br \/>33<br \/>34<br \/>35<br \/>36<br \/>37<br \/>38<br \/>39<br \/>40<br \/>41<br \/>42<br \/>43<br \/>44<br \/>45<br \/>46<br \/>47<br \/>48<br \/>49<br \/>50<br \/>51<br \/>52<br \/>53<br \/>54<br \/>55<br \/>56<br \/>57<br \/>58<br \/>59<br \/>60<br \/>61<br \/>62<br \/>63<br \/>64<br \/>65<br \/>66<br \/>67<br \/>68<br \/>69<br \/>70<br \/>71<br \/><\/div><\/td><td><div class=\"php codecolorer\">import java<span class=\"sy0\">.<\/span>sql<span class=\"sy0\">.*;<\/span><br \/>\n<br \/>\n<span class=\"kw2\">public<\/span> <span class=\"kw2\">class<\/span> OracleJDBC <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; <span class=\"kw2\">public<\/span> static void main<span class=\"br0\">&#40;<\/span>String<span class=\"br0\">&#91;<\/span><span class=\"br0\">&#93;<\/span> argv<span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;-------- Oracle JDBC Query Test ------------&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; try <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"kw2\">Class<\/span><span class=\"sy0\">.<\/span>forName<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;oracle.jdbc.driver.OracleDriver&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; <span class=\"br0\">&#125;<\/span> catch <span class=\"br0\">&#40;<\/span>ClassNotFoundException e<span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;Where is your Oracle JDBC Driver?&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e<span class=\"sy0\">.<\/span>printStackTrace<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"kw1\">return<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; <span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;Oracle JDBC Driver Registered!&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n<br \/>\n&nbsp; &nbsp; try <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; Connection connection <span class=\"sy0\">=<\/span> DriverManager<span class=\"sy0\">.<\/span>getConnection<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;jdbc:oracle:thin:@192.168.57.111:1521:lab11&quot;<\/span><span class=\"sy0\">,<\/span><span class=\"st0\">&quot;dbtw&quot;<\/span><span class=\"sy0\">,<\/span><span class=\"st0\">&quot;oracle&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Statement stmt <span class=\"sy0\">=<\/span> connection<span class=\"sy0\">.<\/span>createStatement<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int <a href=\"http:\/\/www.php.net\/count\"><span class=\"kw3\">count<\/span><\/a> <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"kw1\">while<\/span> <span class=\"br0\">&#40;<\/span><a href=\"http:\/\/www.php.net\/count\"><span class=\"kw3\">count<\/span><\/a> <span class=\"sy0\">&lt;<\/span> <span class=\"nu0\">100000<\/span><span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;try <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSet rset <span class=\"sy0\">=<\/span> stmt<span class=\"sy0\">.<\/span>executeQuery<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;select * from dbtw_users where cont=2&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rset<span class=\"sy0\">.<\/span><a href=\"http:\/\/www.php.net\/next\"><span class=\"kw3\">next<\/span><\/a><span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println <span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;Line number: &quot;<\/span> <span class=\"sy0\">+<\/span> rset<span class=\"sy0\">.<\/span>getInt<span class=\"br0\">&#40;<\/span><span class=\"nu0\">1<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span> &nbsp;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rset<span class=\"sy0\">.<\/span>close<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"br0\">&#125;<\/span> catch <span class=\"br0\">&#40;<\/span>SQLException e<span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;Query Failed! (&quot;<\/span><span class=\"sy0\">+<\/span><a href=\"http:\/\/www.php.net\/count\"><span class=\"kw3\">count<\/span><\/a><span class=\"sy0\">+<\/span><span class=\"st0\">&quot;)&quot;<\/span> <span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span>e<span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<a href=\"http:\/\/www.php.net\/count\"><span class=\"kw3\">count<\/span><\/a><span class=\"sy0\">++;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;try <span class=\"br0\">&#123;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Thread<span class=\"sy0\">.<\/span><a href=\"http:\/\/www.php.net\/sleep\"><span class=\"kw3\">sleep<\/span><\/a><span class=\"br0\">&#40;<\/span><span class=\"nu0\">2<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"co1\">\/\/1000 milliseconds is one second.<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"br0\">&#125;<\/span> catch<span class=\"br0\">&#40;<\/span>InterruptedException ex<span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Thread<span class=\"sy0\">.<\/span>currentThread<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">.<\/span>interrupt<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; <span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; stmt<span class=\"sy0\">.<\/span>close<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; connection<span class=\"sy0\">.<\/span>close<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; <span class=\"br0\">&#125;<\/span> catch <span class=\"br0\">&#40;<\/span>SQLException e<span class=\"br0\">&#41;<\/span> <span class=\"br0\">&#123;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;Connection Failed! Check output console&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; e<span class=\"sy0\">.<\/span>printStackTrace<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n&nbsp; &nbsp; &nbsp; <span class=\"kw1\">return<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; <span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n&nbsp; &nbsp; <a href=\"http:\/\/www.php.net\/system\"><span class=\"kw3\">System<\/span><\/a><span class=\"sy0\">.<\/span>out<span class=\"sy0\">.<\/span>println<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;End of program&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\n&nbsp; <span class=\"br0\">&#125;<\/span><br \/>\n<br \/>\n<span class=\"br0\">&#125;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">2. Abrimos 10 sess\u00f5es CMD num desktop Windows e acionando o programa\u00a0Java acima;<\/span><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">3. Aguardamos alguns segundos e o servidor de banco atingiu um Load 8;<\/span><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-671 aligncenter\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP-1024x576.jpg\" alt=\"Linux TOP\" width=\"702\" height=\"395\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP-1024x576.jpg 1024w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP-300x169.jpg 300w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP.jpg 1366w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><\/a><\/p>\n<p><span style=\"font-size: 12pt;\">4. Emitimos um relat\u00f3rio AWR da base e verificamos os eventos &#8220;library cache lock&#8221; e &#8220;library cache: mutex X&#8221; aparecem na se\u00e7\u00e3o &#8220;Top 10 Foreground Events By Total Wait Time&#8221;;<\/span><\/p>\n<p><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-676 size-full\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_1.jpg\" alt=\"AWR Top Wait\" width=\"615\" height=\"223\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_1.jpg 615w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_1-300x109.jpg 300w\" sizes=\"auto, (max-width: 615px) 100vw, 615px\" \/><\/a><\/p>\n<p><span style=\"font-size: 12pt;\">5. No site de suporte da Oracle \u00a0existe um \u00a0documento \u00a0que \u00a0mostra \u00a0que a estat\u00edstica &#8220;failed parse elapsed time&#8221; \u00a0 causa \u00a0conten\u00e7\u00e3o \u00a0 &#8220;library cache lock&#8221; \u00a0e \u00a0&#8220;library cache: Mutex X&#8221;: \u00a0 \u00a0\u00a0<span style=\"font-size: 10pt;\">&#8220;ID 1566018.1 : High Waits for &#8216;library cache lock&#8217; and &#8216;library cache: mutex X&#8217; Due to Parse Failures When Using JDBC ResultSet .TYPE_SCROLL_SENSITIVE&#8221;<\/span><\/span><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">6. Verificamos na se\u00e7\u00e3o &#8220;Time Model Statistics&#8221; que &#8220;failed parse elapse time&#8221; representa 52% do DBTime da base;<\/span><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-677 size-full\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_1.jpg\" alt=\"AWR Top Model\" width=\"351\" height=\"342\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_1.jpg 351w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_1-300x292.jpg 300w\" sizes=\"auto, (max-width: 351px) 100vw, 351px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">7. No site de suporte da Oracle existe um documento &#8220;ID 1353015.1 : How to Identify Hard Parse Failures&#8221;, a partir desse documento pudemos identificar a instru\u00e7\u00e3o SQL que estava apresentado erro. O procedimento consiste em habilitar o evento 10035, aguardar que alguma sess\u00e3o execute a instru\u00e7\u00e3o SQL com erro, desabilitar o evento 10035 e verificar o arquivo Log Alert;<\/span><\/p>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:800px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/>10<br \/>11<br \/>12<br \/>13<br \/>14<br \/>15<br \/>16<br \/><\/div><\/td><td><div class=\"php codecolorer\">SQL <span class=\"sy0\">&gt;<\/span> ALTER SESSION SET EVENTS <span class=\"st_h\">'10035 trace name context forever, level 1'<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nSession altered<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span> select <span class=\"sy0\">*<\/span> from dbtw_users where cont<span class=\"sy0\">=<\/span><span class=\"nu0\">2<\/span><span class=\"sy0\">;<\/span><br \/>\nselect <span class=\"sy0\">*<\/span> from dbtw_users where cont<span class=\"sy0\">=<\/span><span class=\"nu0\">2<\/span><br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">*<\/span><br \/>\nERROR at line <span class=\"nu0\">1<\/span><span class=\"sy0\">:<\/span><br \/>\nORA<span class=\"sy0\">-<\/span><span class=\"nu19\">00942<\/span><span class=\"sy0\">:<\/span> table or view does not exist<br \/>\n<br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span> ALTER SESSION SET EVENTS <span class=\"st_h\">'10035 trace name context off'<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nSession altered<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\">Abaixo parte do arquivo Log Alert com o erro de PARSE da consulta:<\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/05\/Log_Alert.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-689\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/05\/Log_Alert.jpg\" alt=\"log alert\" width=\"801\" height=\"245\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/05\/Log_Alert.jpg 801w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/05\/Log_Alert-300x92.jpg 300w\" sizes=\"auto, (max-width: 801px) 100vw, 801px\" \/><\/a><\/p>\n<p>&nbsp;<br \/>\n<span style=\"font-size: 12pt;\">8. Para contornar o problema criamos a tabela referenciada na consulta;<\/span><\/p>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:800px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/><\/div><\/td><td><div class=\"php codecolorer\">SQL <span class=\"sy0\">&gt;<\/span> create table dbtw_users <span class=\"br0\">&#40;<\/span>cont number<span class=\"br0\">&#40;<\/span><span class=\"nu0\">5<\/span><span class=\"sy0\">,<\/span><span class=\"nu0\">2<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nTable created<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span> <a href=\"http:\/\/www.php.net\/exec\"><span class=\"kw3\">exec<\/span><\/a> dbms_stats<span class=\"sy0\">.<\/span>gather_table_stats<span class=\"br0\">&#40;<\/span>ownname<span class=\"sy0\">=&gt;<\/span> USER<span class=\"sy0\">,<\/span> tabname<span class=\"sy0\">=&gt;<\/span> <span class=\"st_h\">'DBTW_USERS'<\/span><span class=\"sy0\">,<\/span> cascade<span class=\"sy0\">=&gt;<\/span> <span class=\"kw4\">true<\/span><span class=\"sy0\">,<\/span> estimate_percent<span class=\"sy0\">=&gt;<\/span> <span class=\"kw4\">null<\/span><span class=\"sy0\">,<\/span> method_opt<span class=\"sy0\">=&gt;<\/span> <span class=\"st_h\">'FOR ALL COLUMNS SIZE 254'<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nPL<span class=\"sy0\">\/<\/span>SQL procedure successfully completed<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">9. Abrimos 10 sess\u00f5es CMD num desktop Windows e acionamos a aplica\u00e7\u00e3o Java novamente;<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">10. Aguardamos alguns segundos e o servidor de banco atingiu um Load 4.2;<\/span><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-680\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP2-1024x576.jpg\" alt=\"Linux TOP\" width=\"860\" height=\"484\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP2-1024x576.jpg 1024w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP2-300x169.jpg 300w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TOP2.jpg 1067w\" sizes=\"auto, (max-width: 860px) 100vw, 860px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">11. Emitimos\u00a0um relat\u00f3rio AWR da base e verificamos que os eventos &#8220;library cache lock&#8221; e &#8220;library cache: mutex X&#8221; n\u00e3o aparecem na se\u00e7\u00e3o &#8220;Top 10 Foreground Events By Total Wait Time&#8221;;<\/span><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-682\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_2.jpg\" alt=\"AWR TOP Wait\" width=\"611\" height=\"215\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_2.jpg 611w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/Top10_WaitEvents_2-300x106.jpg 300w\" sizes=\"auto, (max-width: 611px) 100vw, 611px\" \/><\/a><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_2.jpg\"><br \/>\n<\/a><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">12.Verificamos na se\u00e7\u00e3o &#8220;Time Model Statistics&#8221; que &#8220;failed parse elapse time&#8221; n\u00e3o aparece nessa se\u00e7\u00e3o;<\/span><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-681\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_2.jpg\" alt=\"AWR Top Wait\" width=\"350\" height=\"338\" srcset=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_2.jpg 350w, http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2016\/04\/TimeModelStatistics_2-300x290.jpg 300w\" sizes=\"auto, (max-width: 350px) 100vw, 350px\" \/><\/a><\/p>\n<h2 style=\"text-align: justify;\"><span style=\"font-size: 14pt;\"><strong>Conclus\u00e3o<\/strong><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Existem problemas cujos sintomas n\u00e3o impactam o banco de dados quando o Workload da base \u00e9 pequeno, porem quando esse Workload cresce o impacto pode ser extremamente prejudicial para o tempo de resposta do banco, esse tipo de problema pode ser detectado quando submetemos uma nova aplica\u00e7\u00e3o a um processo de &#8220;Stress Test&#8221;, porem mesmo assim ainda estamos sujeitos a enfrentar esse problema introduzido por um simples plano de mudan\u00e7a na aplica\u00e7\u00e3o, onde na maioria das vezes \u00e9 invi\u00e1vel a realiza\u00e7\u00e3o de &#8220;Stress Test&#8221;.<\/span><\/p>\n<h2 style=\"text-align: justify;\"><span style=\"font-size: 14pt;\"><strong>Refer\u00eancias<\/strong><\/span><\/h2>\n<p><span style=\"font-size: 12pt;\"><strong>ID 1566018.1<\/strong> : High Waits for &#8216;library cache lock&#8217; and &#8216;library cache: mutex X&#8217; Due to Parse Failures When Using JDBC ResultSet .TYPE_SCROLL_SENSITIVE<\/span><\/p>\n<p><span style=\"font-size: 12pt;\"><strong>ID 1353015.1<\/strong> : How to Identify Hard Parse Failures<\/span><\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n<a href=\"http:\/\/dbtimewizard.com.br\/treinamento\/\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-1255\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2018\/10\/promo_cur02.jpg\" alt=\"Promo-D75L\" width=\"330\" height=\"242\" \/><\/a><\/p>\n<p><a href=\"https:\/\/youtu.be\/l6OphhTle9I\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1215\" src=\"http:\/\/dbtimewizard.com.br\/wp-content\/uploads\/2018\/01\/video-02-300x168.jpg\" alt=\"video-02\" width=\"330\" height=\"242\" \/><\/a><br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>O problema tratado neste artigo ocorreu em um de nossos clientes com uma aplica\u00e7\u00e3o de um terceiro, esse problema causou um grande impacto no banco de dados pois fez o banco aumentar o consumo de CPU e atingir um LOAD 30 nos dois nodes do RAC. Para simular o problema vamos utilizar uma base Oracle 11.2.0.4 instalada num servidor virtual<\/p>\n","protected":false},"author":2,"featured_media":666,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[122],"tags":[124,126,22,125,123],"class_list":["post-665","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-parse","tag-application-bug","tag-automatic-workload-repository","tag-awr","tag-erro-de-aplicacao","tag-parse-time"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":39,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":2098,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/665\/revisions\/2098"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/666"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}