Below is the content of an email I wrote this morning. We could not login to a single instance database due to the statistics gathering job running long. What is odd is that when we tried to troubleshoot, we couldn’t even login using sqlplus -prelim \/ as sysdba…not good.<\/p>\n
—————————————————————————————————————————<\/p>\n
At around 11:20AM on Friday, November 12th, an alert was generated via email every five minutes until noon requesting that the WCAS service be \u201cverified\u201d, as a test query against it did not return in under 60 seconds. This query normally takes less than two seconds. At noon that same day, the DBA team logged in to troubleshoot. We found that we could not login to the instance, as our login would \u201chang\u201d. We found LDAP error messages in the OS system log file. Thinking (erroneously) these messages may be related, we elected to bounce the instance and troubleshoot after the fact. After the instance was restarted, we found we could not find any useful information, but the issue had \u201cgone away\u201d as well.<\/p>\n
On Monday morning, November 15th at 9:43AM, the same alert that was generated on Friday began to be propagated to operations, the DBA\u2019s, and the application support teams. Once again, the DBA\u2019s logged in and found they could not login to the instance. We were able to generate \u201csystem state dumps\u201d, which can contain useful information to troubleshoot an issue after the fact. However, when we found no \u201csmoking gun\u201d, we elected to once again restart the instance.<\/p>\n
This time however, the problem appeared again shortly after it was restarted. We found we could login, but only intermittently. When we did get logged in, we elected to run a \u201changanalyze\u201d. This command prints in human readable format a list of what sessions are blocking others, as well as the operational source of the block (what are they doing). When we did this, we found almost all sessions were blocked waiting for \u201ccpu quantum\u201d. This code tree is taken when a \u201cresource manager\u201d plan is enabled in the instance, and is acting as a CPU traffic cop, of sorts. The particular plan was the one that is enabled when query optimizer statistics are being gathered. Our understanding of this is that Oracle enables this plan to *protect* online users from CPU resource starvation. In other words, the statistics gathering job steps aside when a \u201creal\u201d user comes in.<\/p>\n
Obviously, either:<\/p>\n
I\u2019m thinking number 2 \ud83d\ude42<\/p>\n
For now, we have disabled the statistics gathering job as we dig into exactly what it does to ensure:<\/p>\n
For now the problem has been resolved pending further investigation.<\/p>\n
—————————————————————————————————————————<\/p>\n","protected":false},"excerpt":{"rendered":"
Below is the content of an email I wrote this morning. We could not login to a single instance database due to the statistics gathering job running long. What is odd is that when we tried to troubleshoot, we couldn’t…<\/p>\n