Hello everyone,
First of all, a disclaimer: we are a quickly growing software company which also provides a SaaS-like solution, and hosts an Oracle Database for our own software running at customer locations. I'm not a DBA yet, but I've been learning for a while now and will follow proper courses, next year. This problem is getting rather urgent, though.
We have an Oracle 12c (12.1.0.1.7) running on rather beefy hardware (8 cores, 72GB, SSD card) and it performs very well. One of our applications fires queries that execute slowly very often, but when I execute the same query via Toad, it's very quick again. Sometimes I can force the same query to be slow again by changing parameters, adding values to an in(), etc, but not always. By not being able to find out WHY the query is slow, I also can't find a solution. We've been struggling with this issue for quite some time (more than a year, I believe) and had the same issue when still running Oracle 11.
A few more bits of information that might be relevant:
- This user sets cursor_sharing to force, per my request (the software itself doesn't use binds)
- I've set optimizer_index_caching to 25, because I expect the entire DB to be in-memory.
- I've set optimizer_index_cost_adj to 50, because I saw too many full table scans for my liking. Both settings where made while the queries were performing badly already.
- Memory: memory_target=40GB, db_cache_size=5.5GB, pga_aggregate_target=8GB, sga_target=30GB
- Optimizer_dynamic_sampling is set to the default, 2.
- I've seen some query plans have a note, something like "Explain plan has been made with statistics from previous executions". Not all have this, though.
- I've seen some explain plans where the actual records differ a large amount from the expected records, somethings expected was 1, actual was ~4 billion...
- We've actually had to add /* ordered */ to some queries to speed up execution. This made some other queries perform better (20 sec each time! to 200ms), but worse than when Oracle finally found the proper execution plan (40ms).
- Many queries seemed to start with the wrong tables for our liking.
- A copy of the database, running locally, also had some issues with queries - the explain plan took 2 seconds, the query itself only a few ms. This same database of course doesn't receive any insert/update statements.
It might be important to note that this schema receives quite a lot of updates and inserts, so I thought it might screw up the statistics?
As I wrote, we've been struggling with this issue for a long, long time. We've made changes to hardware, to Oracle settings, to the client software, everywhere, but are no closer to a permanent solution. Any help would be greatly appreciated!
Regards,
Jelmer
Message was edited by: 1449188
optimizer_cursor_caching -> optimizer_index_caching