An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values. The CURSOR_SHARING…

Question

Options

• AThe number of latch gets in the library cache will be reduced.
• BBind peeking will take place and subsequent execution of queries can have different plans based on
• CBind peeking will take place and subsequent execution of queries can have different plans only when
• DBind peeking will not happen and the optimizer will use the same plan for all bind values if no histograms
• EBind peeking will happen and subsequent execution of queries will have the same parent cursor but

• A
  77% (37)
• C
  6% (3)
• D
  4% (2)
• E
  13% (6)

Explanation

CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Only allows statements with identical text to share the same cursor. EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed will create a new entry in V$SQL, it will be hard-parsed, and an execution plan will be created just for it. There can be hundreds or thousands of very similar queries in the shared pool that differ only in the literals used in the SQL statement itself. This implies that the database is forced to hard- parse virtually every query, which, in turn, not only consumes a lot of CPU cycles but also leads to decreased scalability. The database just cannot hard-parse hundreds or thousands of SQL statements concurrently--the application ends up waiting for the shared pool to become available. One of the major scalability inhibitors in the database is not using bind variables. That was the motivation behind adding CURSOR_SHARING=FORCE .

No community discussion yet for this question.