repettasLife Cycle of a Cursor
The following describes the steps carried out during the processing a cursor:
Open Cursor: A memory structure for the cursor is allocated in the server-side memory of the server process associated with the session - user global area (UGA). At this there isn’t a SQL statement associated with the cursor yet.
Parse Cursor: A SQL statement is associated with the cursor at this point. Its parsed representation which includes the execution plan is loaded into the shared pool - library cache. The structure in the UGA is updated with the pointer to the location of the shareable cursor in the library cache.
Define Output Variables: If the SQL statement will return data, the variables to receive that data must be defined. This holds true for queries, DELETE, INSERT, and UPDATE statements that use the RETURNING clause.
Bind Input Variables: If the SQL statement uses bind variables; their values have to be specified (provided). Checking isn’t performed during the binding stage. If the data being passed is invalid then a runtime error will be raised during the actual execution.
Execute Cursor: The SQL statement is executed but be warned that the Engine doesn’t always perform anything of significance during this phase. Generally, for many types of queries, the actual processing occurs during the fetch phase.
Fetch Cursor: If the SQL statement is going to return data then this is the step that retrieves that data. This is the step (phase) where most of the processing is performed, especially for queries. For queries, rows might be partially fetched - meaning that the cursor might be closed before all of the rows are fetched!
Close Cursor: The resources associated with the cursor in the UGA are freed and made available for other cursors. The shareable cursor in the library cache remains and in the future it may be reused - that is the hope.
In order to understand this process it may be best to think about each step being executed separately in the order show below. In practice though, different optimization methods (techniques) are used to attempt to speed up the overall processing.
Parsing Processing Narrative
VPD Predicates: If you are using Virtual Private Database (VPD), formerly know as row-level security and it is active for one of the tables referenced in the parsed SQL statement, the predicates generated by the security policies are included in the WHERE clause.
Check Syntax, Semantics and Access Rights: This stage validates that the SQL statement is in fact correctly formed in terms of syntax, semantics and that all of the objects referenced by the statement exist and that the user has the necessary privileges to access them.
Store Parent Cursor in Library Cache: When a shareable parent cursor is not available yet then memory is allocated from the library cache and a new parent cursor is stored within that memory. The key information associated with the parent cursor is the text of the SQL statement.
Logical Optimization: It is during this phase that the new and semantically equivalent SQL statements are produced by applying different transformation techniques. The number of execution plans considered search space increases. The transformations increase the number of execution plans considered otherwise, the search space is limited.
Physical Optimization: Several options are performed during this stage the first step is to generate the execution plans related to each SQL statement resulting from the previous - Logical Optimization step. Then, based on the statistics found in the data dictionary or gather by dynamic sampling a cost is mapped (assigned) to each execution plan. Then the execution plan with the lowest cost is selected - hopefully, the most efficient execution plan is chosen from all of those generated in the search space.
Store Child Cursor: Memory is allocated and the shareable child cursors occupy that memory space and the children are mapped back to the parent cursor. The key elements associated with the child cursor are the execution plan and the execution environment.
Once the parent and child cursors are stored in the library cache they can be viewed via the views v$sqlarea and v$sql - the parent via v$sqlarea and the children via v$sql. The cursors are identified by accessing three columns address, hash_value, and child_number. In 10g+ you can use sql_id instead of the pair address and hash_value.
When shareable parent and child cursors are available and only the first two operations are carried out then the parse is termed a soft parse and when all of the operations are performed that is termed a hard parse.
Performance Implications
In regards to performance hard parses should be avoided like the plague. The whole purpose, the existence of shareable cursors in the library cache is to reuse the shareable cursors in the library cache. There are two primary reasons why hard parses are expensive in terms of resource consumption: the first is that the logical and physical optimizations are highly intensive CPU operations and the second is that memory is required for storing the parent and child cursors in the library cache. The library cache is shared across all sessions requiring the memory allocations for the library cache to be serialized. A latch protects the shared pool has to be obtained before the memory can be allocated for both the parent and the child cursors. Even if the impact of soft parses are lower than that of hard parses it is desirable to avoid soft parses as well because they require memory serialization the same as hard parses.
In order to search for a shareable cursor a latch must be obtained before the library cache can be searched. In summary, both soft and hard parses should be avoided as much as possible because by their very nature they limit the scalability of applications.
Shareable Cursors
The result or outcome of a parse operation is the generation of a parent and child cursor that ends up stored in the library cache. Obviously, the purpose of storing these two objects is so that they can be reused and thereby result in avoidance of a hard parse.
To understand how the sharing of parent and child cursors works several examples will be used to help illustrate the concept. The first example will illustrate the case where a parent cursor can not be shared. The essential information related to a parent cursor is its SQL text. Several SQL statements share the same parent cursor if their text is exactly the same and this is one of the basic requirements. The most common examples of demonstrating this concept is to use SQL statements that differ only in case (upper and lower) and then statements that have whitespace differences.
select oprid from psoprdefn where oprid != NULL;
select OPRID FROM PSOPRDEFN WHERE oprid != null;
select oprid from psoprdefn where oprid != NULL;
select oprid FROM PSOPRDEFN WHERE oprid != null;
The 1st and 3rd SQL statements will share the same parent cursor, however, the 2nd and 4th statements will each show up in v$sqlarea with unique plans because of the whitespace in statement 4 and statement 2 won’t match statements 1 or 3 because of case.
Use select sql_id, sql_text, executions from v$sqlarea where sql_text like ‘oprid’;
The next set of SQL statements illustrate the case where the parent cursor but not the child cursor can be shared. The key here is that the execution environment is related to the child cursor as well as the execution plan. The execution environment is a key because if it changes then the execution may change as well. As a result SQL statements are able to share the same child cursor only if they share the same parent cursor and their exeuction environments are compatible. To show this the same SQL statement is executed with two different values used for the initialization parameter optimizer_mode.
alter session set opimizer_mode=all_rows;
select count(*) from psoprdefn;
alter session set optimizer_mode=first_rows_10;
select count(*) from psoprdefn;
The result is that a single parent cursor and two child cursors are created. It is key to note that both child cursors have the same execution plan (the column plan_hash_value are the same, have the same value). This illustrates very well that a new child cursor was generated because of a difference in execution environment and not because another execution plan was generated.
select sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
from v$sql
where sql_id = (select prev_sql_id from v$session where sid=sys_context(’userenv’,’sid’));
In order to discover which mismatch led to several child cursors it is possible to query the view v$sql_shared_cursor. You may be able to find that for each child cursor except the first one, why it was not possible to share a previously created child cursor. For several types of incompatibility there is a column that is set to either N (not a mismatch) or Y (mismatch) - there are 60 types of incompatibility in Oracle 11g. Use the following query to confirm that for the previous example, the mismatch for the second child cursor was because of a different optimizer mode. You need to be aware that this view may not provide you with a reason for ALL situations.
select child_number, optimizer_mode_mismatch
from v$sql_shared_cursor where sql_id = ”;
Get the from the previous SQL statement.
It is common to see quite a few hard parses caused by nonshared parent cursors than nonshared child cursors. In fact it is more common to discover that there are few child cursors for each parent cursor. If the parent cursors cannot be shared then that means the text of the SQL statements is constantly changing. This is common if either the SQL statements are dynamically generated or literals are used instaed of bind variables. Dynamically generated SQL statements cannot be avoided. It may be possible to use bind variables but it is not always good to use them for some sitations.
Bind Variables
Bind variables impact applications and especially PeopleSoft in two ways. They make programming either easier or more difficult depending on whether or not less or more code is written. If you are writing PL/SQL code it is easier to execute that code with bind variables. If you are programming using Java then it may be easier to execute your code without the use of bind variables. In terms of performance bind variables can be a good and a bad influence.
Advantage
The postive side of bind variables is their use can influence the sharing of cursors located in the library cache in a positive way and avoid the hard parses and the overhead associated with hard parses.
The use of bind variables can cause fewer child cursors to be generated and result in more matches and hence fewer hard parses. However, there are situations where child cursors are generated when bind variables are used. An example is a SQL statement where the size of a variable changes and this will result in a child cursor being created because the execution environment will have changed if an earlier (previous) incarnation of a similar SQL statement used a different size for the same variable.
example:
variable oprid number
variable addr1 varchar2(32)
execute :oprid := 232911; :addr1 := ‘380 Lake Avenue’;
insert into psoperdefn (oprid, addr1) values (:oprid, :addr1);
execute :oprid := 23912; :addr1 := ‘400 Michigan Street’;
insert into psoperdefn (oprid, addr1) values (:oprid, :addr1);
execute :oprid := 23913; :addr1 := ‘500 Gator Way’;
insert into psoprdefn (oprid, addr1) values (:oprid, :addr1);
select sql_id, child_number, executions
from v$sql where sql_text = ‘insert into psoprdefn (oprid, addr1) values (:oprid, :addr1)’;
SQL_ID CHILD_NUMBER EXECUTIONS
————- ———— ———-
953fbsj1crntc 0 6
I ran the procedure twice and the above shows that no child cursors were generated and I used bind variables. The next example below is going to change the size of addr1 by 1 to a size of 33 - this will be the only change.
variable oprid number
variable addr1 varchar2(33)
execute :oprid := 232914; :addr1 := ‘600 West Lake Avenue’;
insert into psoprdefn (oprid, addr1) values (:oprid, :addr1);
execute :oprid := 232915; :addr1 := ‘400 North Michigan Street’;
insert into psoprdefn (oprid, addr1) values (:oprid, :addr1);
execute :oprid := 232916; :addr1 := ‘500 East Gator Way’;
insert into psoprdefn (oprid, addr1) values (:oprid, :addr1);
SQL_ID CHILD_NUMBER EXECUTIONS
————————- ———— —————–
953fbsj1crntc 0 3
953fbsj1crntc 1 3
The new child cursor was created because of the environment change as described above. The mismatch can be confirmed with the following SQL:
select child_number, bind_mismatch
from v$sql_shared_cursor
where sql_id = ‘953fbsj1crntc’
/
CHILD_NUMBER BIN
—————————
0 N
1 Y
This happens because the database engine applies what is know as bind variable graduation. The purpose of this feature is to minimize the number of child cursors by graduating bind variables (which vary in size) into four groups driven by size. The first group contains the bind variables with up to 32 bytes, the second contains bind variables between 33 and 128 bytes and the third contains the bind variables between 129 and 2,000 bytes while the last group contains the bind variables of more than 2,000 bytes in size. Bind variables of datatype NUMBER are graduated to their maximum lenghth which is 22 bytes. As the next SQL statement illustrates the view v$sql_bind_metadata displays the maximum size of a group. Notice how the value of 128 is used, even if the variable of child cursor 1 is defined as 33.
select s.child_number, m.position, m.max_length,
decode(m.datatype,1,’VARCHAR2′,2,’NUMBER’,m.datatype) as datatype
from v$sql s, v$sql_bind_metadata m
where s.sql_id = ‘953fbsj1crntc’
and s.child_address = m.address
order by 1, 2
/
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
———— ———- ———- —————————————-
0 1 22 NUMBER
0 2 32 VARCHAR2
1 1 22 NUMBER
1 2 128 VARCHAR2
Each time a new child cursor is created, a new execution plan is generated. Whether this new execution plan is equal to one used by another child cursor will also depend on the value of the bind variables.
Filed under: DBA, Oracle, Performance | No Comments »
