There was an Incident reported today where suddenly one of the job started working slow. Actually, it was working slow since couple of weeks now and since the final output of the job was to download a file, initial focus was to troubleshoot on the application side of the things to see if something has changed or to find out any potential errors/warnings on application side of the things.
Eventually, the request came to DBA’s where I saw there are queries which are running longer than what they were before.
Execution plan of the long running query clearly shows the problem
So all the cost is coming from Step 7 which is Full table scan of a table.
Table was missing the required Index on the column being used in Where clause and this particular condition was selecting 1 row out of 900k rows.
Putting up an index reduced the cost of whole operation from 48K to 8.
And Number of buffer gets from 188768 per Execution to just 11 per execution.
Now the question is why this thing was working fine until few weeks back and why it started working slow now?
Most probably, the table in question here “ECO_DOWNLOAD_INITIAL_MSG” has grown over time and caused this performance issue.
Important thing to note here is “I said most Probably” but I am not 100% sure.
So, to change this “most Probably” thing to a certain thing, its necessary that we not only capture the database growth which normally you get easily with Oracle Enterprise Manager repository, But we should also capture the object level growth for our databases.
Answers to situations like these can easily be made if we have correct numbers on how my database objects are growing.
There can be number of ways to achieve this. One simple way that I use is to have a custom schema in my database and run an automated job to capture Size of the table and other objects from DBA Segments and dump that into a table in my custom schema.
There can be better ways of doing this thing. Important is to have one of the approach to save this information to analyze the growth of not only your database but also database objects.