Implement cursor_sharing=force
I
worked on a 9.0.2.4 database in Toronto, Canada, about which
the end users had complaints of poor performance right after a
new manufacturing plant was added to the existing database. A
quick look at the STATSPACK top five timed events looked like
this:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue 25,901 479,654 46.71
db file scattered read 10,579,442 197,205 29.20
db file sequential read 724,325 196,583 9.14
latch free 1,150,979 51,084 4.97
log file parallel write 148,932 39,822 3.88
My
first look was into the SQL section of the STATSPACK report,
where I noted that almost all of the SQL used “literals” in
the WHERE clause of all queries.
