Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle

Question

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 CHAR, VARCHAR2 and 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 CHAR and NUMBER datatypes, not DATE or 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 :

1. Is the above function can be a CPU intense one? Can refactoring and using DATE and TIMESTAMP data 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.

2. 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:

3. Is there a performance downside for using CHAR and NUMBER instead of DATE and TIMESTAMP?

4. What would you recommend to my client?

Update:

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?

Answer 1

You want to flag the function for the reason of CPU bottleneck, based on what?

You could say you found the sql_id, and checked v$sql, compared elapsed_time to plsql_exec_time and found that a huge portion of elapsed_time comes from plsql_exec_time and cpu_time is also high.

Verify this with:

select elapsed_time, plsql_exec_time, cpu_time from v$sql where sql_id = '...';

Or even better, you can check where the time is actually spent with:

alter session set statistics_level=all;

Run the statement:

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');

Then view the actual statistics of this execution with:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

The above gives you exact execution count, timing, cardinality, buffer gets, disk reads, temp, memory, etc. information about each step of SQL execution. This works in any edition of the database and does not require any licensed option. There are various ways of achieving the above

There is no index on the LIST_JOURNAL table, but it has 3.7 million rows and the above output indicates that your statement performed a TABLE ACCESS FULL on LIST_JOURNAL with 100000 buffer gets but 0 reads because the table is in cache, but this step actually returned only 5 rows (A-Rows) and this step took 10 seconds, while the whole execution took 10.2 seconds? Sure, you are very likely to have a missing index problem. What index to create? We do not know, because the distribution of the data is unknown to us. You may need a composite index with all columns in the predicates or just 3 of them or a simple index on only the TERM_ID column may suffice.

Or the time spent in most steps is insignificant, but the time spent in the UPDATE step is high, combined with plsql_exec_time being relatively high compared to elapsed_time and a relatively low amount of affected rows? Sure, check the function.

The above is the absolute minimum that I would check, before even thinking about the datatypes used in the function. Collect the facts first before theorizing without the slightest idea where the problem originates from.

Some answers to the original questions:

1. Yes, it can be. Even with the proper datatypes.
2. Yes, it can. Depends on the data distribution, number of affected rows.
3. Depends. Typically yes, when storing dates.

Answer 2

If the table used correct datatypes for date fields, the need for foo_function disappears; you can just subtract two date datatypes from each other and it yields the number of days and fractional portion of a day difference. Oracle stores date data types internally as numbers, and so you can do simple date arithmetic, and it has a plethora of built-in optimized functions for doing more advanced date manipulation. As your example is written, Oracle has to do a context switch between SQL and the function's PL/SQL engine; perhaps for each row. There is your bottleneck, combined with extra code to convert CHAR and NUMBER datatypes around inside FOO_FUNCTION.

If you cannot alter the schema, but can use virtual column definitions (depending on your version of Oracle and your vendor's software) you could create a virtual column START_DATE_TIME of the correct DATE datatype and then no need to call FOO_FUNCTION.

And if all you can do is add an index, just

create index I_LIST_JOURNAL ON LIST_JOURNAL 
  (TERM_ID, NODE_CD, GROUP_CD, STATUS);

The order of the columns should probably be different based on cardinality, etc. of each column. The STATUS column should be last as it is a range check, not equality check. And if STATUS is just a small number of values, say 20 or less, you would actually omit that from the above index and also create a bit-mapped index on just the STATUS column, if there is not a lot of concurrent access to the table during these updates. (A bit-mapped index will briefly lock the table during any update.)