0

I would like to know the tradeoff in setting cursor_sharing parameter in Oracle as "FORCE" . As this would try to soft-parse any SQL statement, and certainly the performance must be improved. But the default value is "EXACT", so I would like to know if there is any danger in setting it as FORCE or SIMILAR.

Remis Haroon
  • 31
  • 2

2 Answers2

1

Unless you really know what you're doing, I'd recommend not changing this setting.

Usually, if you've got a high number of hard parses, it's an indication of bad application design.

A typical example for selecting all products for a given category (pseudocode):

stmt = 'select * from products where category = ' || my_category
results = stmt.execute

This is flawed for a number of reasons:

  • it creates a different SQL statement for each category, therefore increasing the number of hard parses dramatically
  • it is vulnerable to SQL injection attacks
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
1

A good application runs perfectly OK with cursor_sharing = exact. A good application can use literals for specific reasons, for example select orders with state = new. That use of literals is OK. If the application uses literals to identify an order by ID it would be different since the will be many different order ID's.

Best is to clean-up the app to use literals in the correct way or to start using prepared statements for best performance.

IF you happen to have an application that only uses literals, set cursor_sharing to FORCE. In 11g there are mechanisms, like cardinality feedback to be able to adjust an execution plan based on un expected row counts that came from a query to make sure that the plans that are originally planned for a query are corrected based on the input and output, for the next time it is used.