what is a cursor , cursor sharing, adaptive cursor sharing in oracle?

what is a cursor , cursor sharing, adaptive cursor sharing in oracle?

A cursor is referred to as a work area  in the database memeory for a sql statement executed by the user. For every sql statement execution 

oracle will generate 2 cursors called as parent cursor and a child cursor. 

A parent cursor will hold the sql statement information and a child cursor will hold information about bind values,literals,schemas and statistics information which 

essentially makes a child cusrosr as deciding factor to go for hard or soft parsing.

You may come across a situation where 2 sql statement which is  executed by the user has same patrent cursors , but the child cursor is not shareable to SQL goes for hard parse (re-compile)

Views:

==== 

Parent cursor info can be viewed in v$sqlarea

version_count column in sql area will tell about number of child cursors for a parent cursor 

In order to check whether a child cursor is shared or not we can use the view V$SQL_SHARED_CURSOR

Cursor_sharing parameter tell us what kind of sql statements can share the same cursor. It is determined using 3 values (exact,force,similar)

SIMILAR determines the sql statement Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. 

Force determines the sql statement   Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

In the absence of adaptive cursor sharing you would see lot of child cursors getting created if cursor_sharing was set to similar, for each unique value in the presence of histogram because of data skew after bind variable value peeking. Even if the plan is same you would see a unique child cursor for a distinct value. Even the length of the bind can cause a new child cursor to be created.

Adaptive cursor sharing is a good feature of oracle database  as it eliminates the creation of multiple child cursors with different EP  for a sql statement.

Imagine I have a table with large data and I have written a query with a where condition , let us say a new EP is generated for the bind value . Imagine for the 

same SQL statement the same EP may not be helpful if the bind variable has a different value I mean at times  index scan may be a better choice  than a full table scan 

With ACS for bind variable oracle will generate or use best optimal EP for each sql statement with a bind variable .

ACS determines the EP based on cardinality using 2 parameters is_bind_sensitive is_bind_aware 

in the first it will generate EP and decided ths best plan , in the second it will compare the EP and selects the best optimized plan 

Advantages

Overcome the use of single execution plan in case of bind variables.

Adaptive cursor sharing is a solution to provide the shareability of binds, with the plan adaptability of literals.

Disadvantages

There may be some extra overhead associated with Adaptive Cursor Sharing:

More Hard Parses (CPU) – Extra Hard Parses will be required when a cursor becomes “Bind Aware” as we attempt to generate the better matched execution plans for the bind selectivity.

More Child Cursors (SGA) – It is recommended that some consideration be taken to increase the size of the shared_pool on upgrade from 10g to 11g, given the extra cursors that may be required to accommodate this feature.

More Work to Match the Cursor (CPU) – More Child Cursors and the requirement to match a query to the best execution plan for its predicate selectivity.

 

 Child cursors are created to improve the performance of SQL statements in Oracle. By caching execution plans, Oracle can reuse them for similar SQL statements, reducing the amount of time needed for parsing and compiling statements. However, the creation of too many child cursors can also impact performance, as it consumes system resources such as memory and CPU time. This is why monitoring the v$child_cursor table can be useful in identifying potential performance issues related to child cursors.

You can use the V$CHILD_CURSOR view to compare the execution plans for each child cursor, and look for differences that might explain why the statement is running slow. For example, you might find that one child cursor is using an index, while another is doing a full table scan. Or you might find that one child cursor is using a different join method or access path.

Once you've identified the child cursor with the problematic execution plan, you can use the ALTER SYSTEM FLUSH SHARED_POOL command to flush the shared pool and force the optimizer to generate a new execution plan for the SQL statement. This will create a new child cursor with a new execution plan.

- Identify the SQL_ID of the slow-running statement

SELECT sql_id, executions, elapsed_time/1000000 "Elapsed Time (sec)", cpu_time/1000000 "CPU Time (sec)", buffer_gets, disk_reads, parse_calls FROM v$sql WHERE sql_text LIKE '%SELECT * FROM employees WHERE salary > 10000%';

-- Identify the child cursors for the SQL statement

SELECT child_number, executions, optimizer_cost, parsing_schema_name, child_address, sql_textFROM v$child_cursor WHERE sql_id = '<sql_id>';

-- Examine the execution plans for each child cursor and look for differences

SELECT child_number, plan_hash_value, optimizer_cost, execution_starts,  disk_reads_delta, buffer_gets_delta, sql_text FROM v$sql_plan WHERE sql_id = '<sql_id>' AND child_number = <child_number>;

-- Flush the shared pool to force a new execution plan

ALTER SYSTEM FLUSH SHARED_POOL;

-- Re-execute the SQL statement to create a new child cursor

SELECT /*+ FULL(employees) */ * FROM employees WHERE salary > 10000;

-- Repeat the process until you find a good execution plan