Reducing shared pool latch contention
I see a lot of shared pool latch contention on my 11gR2 database because there are over 100 identical schemas that run identical SQL. This causes lots of child cursors to be created. Scheduled jobs kick off hourly for every schema at the same time and that produces contention for shared pool latches for the same parent cursor.
I want to reduce that contention. Here's my question...
If I make the SQL text unique by either
(a) changing the object referenbes from "TableName" to "SchemaName.TableName", or
(b) adding a comment to the SQL that includes the SchemaName
...will that cause a separate cursor to be created in the shared pool?
I know in older versions even a change in a comment would cause Oracle to treat it as a separate statement. Is that still true in 11gR2?
FWIW cursor_sharing is set to EXACT.