hi, i am using 11.2.0.3 version of oracle. We have recently migrated to 11g, after 1 month of smooth and comparatively better performance, we are suddenly facing performance issues with our database and it got crashed twice within 5 days. even we didnt push any new code to our database in recent past, atleast after the 11G migration. And after getting feedback from the ORACLE corporation guys , they pointed out about the default database stats gathering job, which was eating most of the CPU, because of the default degree mentioned So it was running in 160 parallel threads causing resource starvation.so we reduce the degree of the stats gathering job to 8 .

But the database crashed again two days back, and rebooted within 3 mins to back to normal, even after this default degree changed to 8. And i am observing around some specific time its happening,dont know if its just a coincidence!!! So if you can provide some help , how to dig down to the base , if this is happening due to any specific application related sql or anything else.

Lalit Kumar BMessages: 3078Registered: May 2013 Location: World Wide on the Web

Senior Member

VIP2013 wrote on Sun, 01 September 2013 16:16

And i am observing around some specific time its happening,dont know if its just a coincidence!!!

Nothing in Oracle could be a coincidence. It has to have a reason behind anything. When you have observed that it crashes at a particular time of the day, then you would have the information logged in the crash dump file. But it is important to know the OS as already pointed out by Blackswan. Even if you reach out to Oracle support, they would ask you to provide all such information. You need to know the crash dump configuration to dig into the information hidden int the crash dump file.

Actually we have got below sql reported by Oracle corporation guys, which was consuming much the database resources and cause of resource starvation.

I have Changed some of the table names/aliases. Now the issue is that, this query is taking ~14hrs for complete execution resulting into 1027 records for company-1.
So then i tried executing the query for a different company - 2, it resulted into 3 records and completed within few seconds. Then i tried forcing the same plan with company-1, and it completes within ~20 minutes. So ideally the optimizer should follow the optimal path for company-1 too, but its not doing somehow due to some reason. Less optimal plan ,using index id2 for scanning table IV rather Id1. i want to identify the reason. given below is the query + test details. Here xmvl is the views. having below inline query. Id1 is on column (companypk,c2,inorg,dt1) of IV. and Id2 is on column (payeeid,companypk).

Few things want to understand...
what is the exact stat which drives the optimizer for picking up the less optimal plan or index id2 for querying iv.
if any wrong cardinality estimation due to wrong stats or any missing histogram etc..