I'm developing an application on Oracle Database, where I need to determine a value for each record at the execution time.
(1) I've created a FUNCTION to determine the value for each record.
(2) The FUNCTION is declared DETERMINISTIC & RESULT_CACHE
(3) The SQL query and the FUNCTION code is tuned. (The SQL Query is working consistently if I exclude the FUNCTION call)
However the performance of the application degrades:
(1) When 200 users try to hit the same page at the same time
(2) When the data in the table increase to 100,000+
Please advice, if I have any better option.