I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project. The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as platform. They had this server crashing once in a while, so they increased the resources but they still have high CPU usage. From my understanding, high CPU usage itself does not imply an issue, but when I drilled down a bit, it got more interesting:
There was a query which had a significant amount of elapsed time per each execution. As they used Oracle Standard edition, I was unable to use the benefits of AWR but, as I checked the query, I saw something very strange.
The query is as follows, which uses function foo:
update LIST_JOURNAL set STATUS = '4', END_DATE = :b1, END_TIME = :b2, END_TYPE = '1', USER_ID = 'SYSTEM', ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2) where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and STATUS < '4');
They used only
NUMBER as their datatypes (don't know the reason, maybe due to some migration from another database) and there is this
FOO_FUNCTION in this query that calculates the interval between two timestamps, say one of them is a record in database and one of them is sysdate, (date and time are stored with
NUMBER datatypes, not
TIMESTAMP) the function converts sysdate to string using
TO_CHAR, then concatenate with TIME field, then do a
TO_DATE, then subtract and then multiply to seconds in a day (60*60*24) and return the results. (For contract reasons, I cannot disclose the function code)
The function looks very strange to me and I want to flag it as a very important CPU bottleneck my report for database performance boost. I haven't seen the rest of the functions but I'm pretty sure wherever
DATE is needed, something similar happens.
But I'm a bit reluctant to do so, as I've not seen any evidence in Oracle docs or somewhere else that this might be a problem. (I know, the function is so oddly written that my reluctance looks ridiculous) Also there is a good chance that software vendor does not accept this as a change.
My questions are :
Is the above function can be a CPU intense one? Can refactoring and using
TIMESTAMPdata types help boost the performance? Why? I can deduct some reasons, but as I need to present this, if there are some documents that have discussed the issue, I would be most grateful to see the links.
The table itself does not have any indexes - except for primary key - If we are stuck with the current code and situation, can an index help boosting the performance? How effective and sustainable can that be? I know I cannot expect detailed answers, but the table contains about 3.7M rows and I think for an update query, some indexes can help.
And my general questions:
Is there a performance downside for using
What would you recommend to my client?
I finally got the results. We added the following composite index :
create index I_LIST_JOURNAL ON LIST_JOURNAL (TERM_ID, NODE_CD, STATUS);
We instantly got some good results, CPU got down to 80 percent and execution time of a couple of queries decreased substantially (The query in question had average execution time of 15 million seconds, now it's about 2400 seconds average) but again, after a couple of days, it went up to 99 percent again.. I am looking to do another iteration of stat gathering and explain plan. But I'm not sure whether this helps reducing the load or not. I am thinking of adding a couple of other composite or simple indexes on the tables which have long running queries to eliminate "Table access full" in explain plan, but given the situation above and the tools I have, is there any other method I can use to get better results?