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

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 (606024) 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 no

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:

• Yes, it can be. Even with the proper datatypes.

• Yes, it can. Depends on the data distribution, number of affected rows.

• Depends. Typically yes, when storing dates.

StackExchange Database Administrators Q#214067, answer score: 2