Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle
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 &lt; '4');


Then view the actual statistics of this execution with:

select * from table(dbms_xplan.display_cursor(format=&gt;'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.