So, with DB CPU using 68.84% of the DB time, it appears to me our database is CPU starved.
Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 7,421 68.84 db file sequential read 567,055 1,988 4 18.44 User I/O direct path read 23,035 530 23 4.92 User I/O log file sync 33,881 325 10 3.01 Commit read by other session 245,776 314 1 2.91 User I/O
we see db file sequential read is using 18.44% of CPU timeIt is actually 18.44% of DB Time. DB Time is CPU Time + Wait Time (other than Idle Waits) for database sessions. Your average wait time is 4ms which is not high. The average wait time depends on the presence of filesystem buffer cache, SAN storage cache (and the hit ratios in these caches) in addition to disk i/o seek+access+transfer time.
yeah, I know that tuning the SQL is where the best gains are returned, but I'm only the DBA and anyway, the sql is all provided by the vendor and we can't really change it without violating our certification and contract with the vendor. (Although some of our reports may be able to be modified. Actually, I'd prefer to get the reports out of the database entirely and put them into another database on another server that they can run the reports from. This could be done with MVs I suppose, but would still be a lot of trouble to set up and would still impact the primary database because any updates to the tables that had MVs on them would now have to update across a db link to another database - more cpu processing).OK, well if you can't change the SQL then there isn't much you can do about it. Every system has to have waits so maybe the SQL is tuned as much as possible and this is the costs associated with running the system and it's running optimally...
So, what do you make of this:My first response would be, did you make all of these changes at once!?
I ran the AWR report a month ago and it shows a very low efficiency for Soft Parse % (53.80%).
Now, I've made a few changes such as setting cursor_sharing to FORCE, and increased shared_pool_reserved_size, increased session_cached_cursors, and also unrelated, but set filesystemio_options to SETALL (was async). I also set btree_bitmap_plans to FALSE.
Now, my Soft Parse % is at 99.23%. Do you think this is related to the changes I made?Overall, I'd say that providing everything else remains the same (load, user queries, etc) and you made the changes and saw this effect then yes. Which ones of the changes resulted in this you can't be as sure about if you made all of them at the same time. I'd say, though, that it's going to be the session cached cursors and cursor sharing parameters which made the biggest difference to that stat.
No... only changes that relate to each other. Then, we monitor and wait at least a week before making another change.
My first response would be, did you make all of these changes at once!?
Actually, I don't know what it was originally set to.
What was your cursor_sharing parameter set to originally? Does the application code not use bind variables?
Yes, but regarding shared_pool_reserved_size, I don't know why it was increased.
Are you using automatic memory management?
I read some web pages that there is an Oracle bug that causes the CBO to think there were bitmapped indexes and then it tries to use them causing performance problems.
Why did you change the _b_tree parameter to FALSE?