What is cursor_sharing parameters ?
CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
What is possible values for this parameter ?
1. EXACT (default)
Only allows statements with identical text to share the same cursor.
2. FORCE
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.
3. SIMILAR
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.
When we have to / should use this parameter ?
Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.
Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:
Are there statements in the shared pool that differ only in the values of literals?
Is the response time low due to a very high number of library cache misses?
CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
What is possible values for this parameter ?
1. EXACT (default)
Only allows statements with identical text to share the same cursor.
2. FORCE
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.
3. SIMILAR
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.
When we have to / should use this parameter ?
Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.
Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:
Are there statements in the shared pool that differ only in the values of literals?
Is the response time low due to a very high number of library cache misses?
Performance improvement when we set cursor_sharing=force ?
When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.
Side Effects on database when set cursor_sharing=FORCE/SIMILAR
Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.
Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".
When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.
Side Effects on database when set cursor_sharing=FORCE/SIMILAR
Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.
Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".
We need to down our database to set this parameter
No, we can set this parameter when our database is open.
Alter system set CURSOR_SHARING=force SCOPE=both;
No, we can set this parameter when our database is open.
Alter system set CURSOR_SHARING=force SCOPE=both;
What is Latch ?
A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.
A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.
During DB performance we will see LATCH event ...so what is latch event and how many types of latch events?
A latch is a low-level internal lock used by Oracle to protect memory structures.
The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.
A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.
A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.
During DB performance we will see LATCH event ...so what is latch event and how many types of latch events?
A latch is a low-level internal lock used by Oracle to protect memory structures.
The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.
Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.
0 comments:
Post a Comment