Hi All,
We're running an Oracle Database 11g Enterprise Edition Release 11.2.0.3.11 - 64bit Production.
As a scheduled job, we're running a query:
SELECT *
FROM (
SELECT T.*, LAG( DATE_FROM, 1, NULL ) OVER (PARTITION BY OUR_ID ORDER BY DATE_TO ) AS PREV_FROM, LAG( DATE_TO, 1, NULL ) OVER (PARTITION BY OUR_ID ORDER BY DATE_FROM) AS PREV_TO, LEAD(DATE_FROM, 1, NULL ) OVER (PARTITION BY OUR_ID ORDER BY DATE_FROM) AS NEXT_FROM
FROM STOCK_A T
WHERE OUR_ID = :B1 )
WHERE DATE_FROM = :B2
This query has run every minute for 3 years. The size of the STOCK_A table has grown some over the years: thousands of records go in, no records go out.
As since last Sunday, we are experiencing huge performance issues. The query had an execution time of 0.02 seconds. Now it's up to 1,4 seconds. Since it has to process thousands of rows, this huge increase in execution time is problematic.
When analyzing this query, we found out that the CPU usage has increased hugely. From approx 20% of the execution time to 90% of the execution time. This is probably caused by the enormous increase of the buffer gets. From thousands to millions. Since we haven't made any changes to the database instance, we're stuck as to how this performance problem has suddenly appeared. It literally happened overnight.
Has anyone any experience with this problem? Any help would be very much appreciated.
Kind regards,
Lupker