Hi there,

Thank you very much for the feedback. I have gone deeper into the database to investigate this high CPU usage. I ran several AWR reports and that the execute to parse ratio is only 3%. I checked out several queries that were being run (and that are consistently run throughout the day) that most likely sap all the CPU. One such query had a cost of 141913! It was performing 4 full table scans (out of which one table is 37 million rows!). Altough the cost was higher on the hash joins it the query was trying to perform. Any advice on reducing this (generally)?

I also noticed that stats were not update to on any of the tables (e.g the stats for the table which had 37 mill rows dated back 3 weeks). As a first point of action i have suggest an analyze on the schema.

It seems like that this ineffiecient SQL code is the reason for this high CPU usage.

I also have a high number of waits on db file sequential read:

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
-----------------------------------------------------------------------
db file sequential read 3,292,788 8,545 37.10 User I/O

Is this due to ineff sql as well due to the high amounts of i/o produced by these queries?


Thanks in advance,
Chucks