Oracle cursor_sharing=force

Oracle Tips by Burleson Consulting

cursor_sharing=force

An Oracle9i database had experienced poor performance immediately after a new manufacturing plant was added to the existing database. Since the AWR was not available in version 9i, a standard STATSPACK report was used to isolate the top five wait events which looked similar to 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

A review of the SQL section of the STATSPACK report revealed that almost all of the SQL used literals in the WHERE clause of all queries.

WHERE customer_state = ‘Alabama’ and customer_type = ‘LAWYER’;

The cursor_sharing parameter was the only fast solution because the application was a vendor package with dynamically generated SQL and it could not easily be changed without using Optimizer Plan Stability (Stored Outlines), a very time-consuming task.

Setting cursor_sharing =force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

SEE CODE DEPOT FOR FULL SCRIPTS