A look at Parsing and Sharing of Cursors
Parsing is the first stage in processing a SQL statement – the other two being Execute and Fetch stages.
Parse once – execute many is a very important performance tuning goal.
What does parsing involve
Syntax check – is the SQL statement syntactically correct
Semantic check – is the SQL statement meaningful or semantically correct. Does the table exist, are the columns in the SQL part of the table, does the user have the required privileges on the table etc
Shared Pool check – the database uses a hashing algorithm to generate a hash value for every SQL statement executed and this hash value is checked in the shared pool to see if any existing and already parsed statement has the same hash value. It can then be reused.
Hard Parse vs. Soft Parse
Soft Parse – when the parsed representation of a submitted SQL statement exists in the shared pool and can be shared – it is a library cache hit. Performs syntax and semantic checks but avoids the relatively costly operation of query optimization. Reuses the existing shared SQL area which already has the execution plan required to execute the SQL statement.
Hard Parse – if a statement cannot be reused or if it the very first time the SQL statement is being loaded in the library cache, it results in a hard parse. Also when a statement is aged out of the shared pool (because the shared pool is limited in size), when it is reloaded again, it results in another hard parse. So size of the shared pool can also affect the amount of parse calls.
Hard parse is thus when a SQL statement is executed, and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared. – it is a library cache miss
Hard parse is a CPU intensive operation as the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When it does this it has to take out a latch which is a low level lock.
Excessive hard parsing can cause performance degradation due to library cache locking and mutexes.
What happens the first time a SQL statement is executed?
The RDBMS will create a hash value for text of the statement and then uses that hash value to check for parsed SQL statements already existing in the shared pool with the same hash value. Since it is the first time it is being executed, there is no hash value match and a hard parse occurs.
Costly phase of query optimization is performed now. It then stores the parse tree and execution plan in a shared SQL area.
When it is first parsed a parent and a single child are created.
Sharing of SQL code – next time same SQL is executed
The text of the SQL statement being executed is hashed.
If no matching hash value found, perform a hard parse.
If matching hash value exists in shared pool, compare the text of matched statement in shared pool with the statement which has been hashed. They need to be identical character for character, white spaces, case, comments etc.
Is objects referenced in the SQL statement match the objects referenced by the statement in the shared pool which is being attempted to be shared. If user A and user B both own EMP, SELECT * FROM EMP issued by user A will not be the same as the same identical statement being issued by user B.
Bind variables being used must match is terms of name, data type and length
Both the session’s environment needs to be identical. For example if at the database level we have OPTIMIZER_MODE=ALL_ROWS, but user A has used an ALTER SESSION command to change optimization goal for his session to say FIRST_ROWS, then that session will notbe able to use existing shared SQL areas which have a different optimization goal for them.
What happens when SQL is not shared
If using any of the criteria above, an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool.
A statement with the same syntax but different semantics uses a child cursor.
Shared SQL area
Exists in the shared pool and contains the parse tree and execution plan for a single SQL statement.
Private SQL Area
Exists in the PGA of each separate session executing a SQL statement and points to a shared SQL area (in SGA). Many sessions PGA can point to the same Shared SQL Area
Let us run these three SQL statements.
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='TABLE';
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='INDEX';
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='RULE';
Note each SQL statement is unique and has its own SQL_ID.
Since each statement is being executed the first time, it is (hard) parsed as well.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS
------------- ---------- ---------- -----------
2tj15h6w34xcc 1 1 1
63zqvyxt1a0an 1 1 1
d6tqd33w7u8xa 1 1 1
Now let us run one of the earlier SQL statements again.
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=’TABLE';
Since the parsed form of this SQL statement already exists in the shared pool, it is re-used. Note loads remain the same(1), but both executions and parse_calls have increased (2). In this case a soft parse has happened.
How do we tell if a parse has been a hard parse or a soft parse? The V$SQL or V$SQLAREA views will not provide us this information. We will have to turn on tracing and use TKPROF to identify this.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS
------------- ---------- ---------- -----------
2tj15h6w34xcc 1 1 1
63zqvyxt1a0an 1 1 1
d6tqd33w7u8xa 2 1 2
We can also see how long a statement has been around in the shared pool. Note SQL statements will get invalidated and be flushed from the shared pool and will also age out in case Oracle needs to load new statements in the shared pool. Once the shared pool gets full, Oracle will use a LRU algorithm to age out the ‘older’ SQL statements.
SQL> select first_load_time from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
FIRST_LOAD_TIME
----------------------------------------------------------------------------
2012-09-04/05:26:29
2012-09-04/05:26:40
2012-09-04/05:25:31
Let us now look at the Version Count.
Remember that non-sharing of SQL and consequent high version count is one of the top causes of library cache contention and database ‘hang’ situations.
If there are too many versions if the same cursor, the parse engine has to search though a complete list of all the versions until it finds the ‘right’ version. This activity can take a lot of CPU cycles as well.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS,version_count from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS VERSION_COUNT
------------- ---------- ---------- ----------- -------------
2tj15h6w34xcc 1 1 1 1
63zqvyxt1a0an 1 1 1 1
d6tqd33w7u8xa 2 1 2 1
SQL> select SQL_ID,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
2tj15h6w34xcc 0 1 1 1
63zqvyxt1a0an 0 1 1 1
d6tqd33w7u8xa 0 2 1 2
Let us now connect as another user, SCOTT who also owns a table called MYOBJECTS.
SQL> select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=’TABLE';
We see that because there are two objects existing (SYSTEM.MYOBEJCTS and SCOTT.MYOBJECTS), the query “select /*TEST_NO_BINDS*/ distinct owner from myobjects” has led to another child and another version of the SQL statement being created in the shared pool.
Note the SQL_ID does not change regardless of the version count increasing!
SQL> select child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_id='d6tqd33w7u8xa';
CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------ ---------- ---------- -----------
0 2 1 2
1 1 3 1
What happens if we change some optimization setting in the users environment and run the same SQL statement again.
SQL> alter session set optimizer_mode=FIRST_ROWS;
Session altered.
SQL> select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='TABLE';
SQL> select child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_id='d6tqd33w7u8xa';
CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------ ---------- ---------- -----------
0 2 1 2
1 1 3 1
2 1 2 1
SQL> select version_count,invalidations from v$sqlarea where sql_id='d6tqd33w7u8xa';
VERSION_COUNT INVALIDATIONS
------------- -------------
3 3
Why was the cursor not shared?
SQL> select hash_value,address from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=''TABLE''';
HASH_VALUE ADDRESS
---------- ----------------
4168950698 000000008D920748
SQL> select * from v$sql_shared_cursor where address ='000000008D920748';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
d6tqd33w7u8xa 000000008D920748 00000000985A42D8 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
d6tqd33w7u8xa 000000008D920748 000000008A8D4E68 1 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
d6tqd33w7u8xa 000000008D920748 000000008D9D9DA0 2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N
Check the columns in the V$SQL_SHARED_CURSOR view and look for occurrences of ‘Y’.
We can see that the reason why the cursor was not shared is because of AUTH_CHECK_MISMATCH and TRANSLATION_MISMATCH in the first case and because of OPTIMIZER_MODE_MISMATCH reason in the second case
Parsing is the first stage in processing a SQL statement – the other two being Execute and Fetch stages.
Parse once – execute many is a very important performance tuning goal.
What does parsing involve
Syntax check – is the SQL statement syntactically correct
Semantic check – is the SQL statement meaningful or semantically correct. Does the table exist, are the columns in the SQL part of the table, does the user have the required privileges on the table etc
Shared Pool check – the database uses a hashing algorithm to generate a hash value for every SQL statement executed and this hash value is checked in the shared pool to see if any existing and already parsed statement has the same hash value. It can then be reused.
Hard Parse vs. Soft Parse
Soft Parse – when the parsed representation of a submitted SQL statement exists in the shared pool and can be shared – it is a library cache hit. Performs syntax and semantic checks but avoids the relatively costly operation of query optimization. Reuses the existing shared SQL area which already has the execution plan required to execute the SQL statement.
Hard Parse – if a statement cannot be reused or if it the very first time the SQL statement is being loaded in the library cache, it results in a hard parse. Also when a statement is aged out of the shared pool (because the shared pool is limited in size), when it is reloaded again, it results in another hard parse. So size of the shared pool can also affect the amount of parse calls.
Hard parse is thus when a SQL statement is executed, and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared. – it is a library cache miss
Hard parse is a CPU intensive operation as the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When it does this it has to take out a latch which is a low level lock.
Excessive hard parsing can cause performance degradation due to library cache locking and mutexes.
What happens the first time a SQL statement is executed?
The RDBMS will create a hash value for text of the statement and then uses that hash value to check for parsed SQL statements already existing in the shared pool with the same hash value. Since it is the first time it is being executed, there is no hash value match and a hard parse occurs.
Costly phase of query optimization is performed now. It then stores the parse tree and execution plan in a shared SQL area.
When it is first parsed a parent and a single child are created.
Sharing of SQL code – next time same SQL is executed
The text of the SQL statement being executed is hashed.
If no matching hash value found, perform a hard parse.
If matching hash value exists in shared pool, compare the text of matched statement in shared pool with the statement which has been hashed. They need to be identical character for character, white spaces, case, comments etc.
Is objects referenced in the SQL statement match the objects referenced by the statement in the shared pool which is being attempted to be shared. If user A and user B both own EMP, SELECT * FROM EMP issued by user A will not be the same as the same identical statement being issued by user B.
Bind variables being used must match is terms of name, data type and length
Both the session’s environment needs to be identical. For example if at the database level we have OPTIMIZER_MODE=ALL_ROWS, but user A has used an ALTER SESSION command to change optimization goal for his session to say FIRST_ROWS, then that session will notbe able to use existing shared SQL areas which have a different optimization goal for them.
What happens when SQL is not shared
If using any of the criteria above, an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool.
A statement with the same syntax but different semantics uses a child cursor.
Shared SQL area
Exists in the shared pool and contains the parse tree and execution plan for a single SQL statement.
Private SQL Area
Exists in the PGA of each separate session executing a SQL statement and points to a shared SQL area (in SGA). Many sessions PGA can point to the same Shared SQL Area
Let us run these three SQL statements.
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='TABLE';
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='INDEX';
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='RULE';
Note each SQL statement is unique and has its own SQL_ID.
Since each statement is being executed the first time, it is (hard) parsed as well.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS
------------- ---------- ---------- -----------
2tj15h6w34xcc 1 1 1
63zqvyxt1a0an 1 1 1
d6tqd33w7u8xa 1 1 1
Now let us run one of the earlier SQL statements again.
select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=’TABLE';
Since the parsed form of this SQL statement already exists in the shared pool, it is re-used. Note loads remain the same(1), but both executions and parse_calls have increased (2). In this case a soft parse has happened.
How do we tell if a parse has been a hard parse or a soft parse? The V$SQL or V$SQLAREA views will not provide us this information. We will have to turn on tracing and use TKPROF to identify this.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS
------------- ---------- ---------- -----------
2tj15h6w34xcc 1 1 1
63zqvyxt1a0an 1 1 1
d6tqd33w7u8xa 2 1 2
We can also see how long a statement has been around in the shared pool. Note SQL statements will get invalidated and be flushed from the shared pool and will also age out in case Oracle needs to load new statements in the shared pool. Once the shared pool gets full, Oracle will use a LRU algorithm to age out the ‘older’ SQL statements.
SQL> select first_load_time from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
FIRST_LOAD_TIME
----------------------------------------------------------------------------
2012-09-04/05:26:29
2012-09-04/05:26:40
2012-09-04/05:25:31
Let us now look at the Version Count.
Remember that non-sharing of SQL and consequent high version count is one of the top causes of library cache contention and database ‘hang’ situations.
If there are too many versions if the same cursor, the parse engine has to search though a complete list of all the versions until it finds the ‘right’ version. This activity can take a lot of CPU cycles as well.
SQL> select SQL_ID,EXECUTIONS,LOADS,PARSE_CALLS,version_count from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID EXECUTIONS LOADS PARSE_CALLS VERSION_COUNT
------------- ---------- ---------- ----------- -------------
2tj15h6w34xcc 1 1 1 1
63zqvyxt1a0an 1 1 1 1
d6tqd33w7u8xa 2 1 2 1
SQL> select SQL_ID,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_NO_BINDS*/%';
SQL_ID CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
2tj15h6w34xcc 0 1 1 1
63zqvyxt1a0an 0 1 1 1
d6tqd33w7u8xa 0 2 1 2
Let us now connect as another user, SCOTT who also owns a table called MYOBJECTS.
SQL> select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=’TABLE';
We see that because there are two objects existing (SYSTEM.MYOBEJCTS and SCOTT.MYOBJECTS), the query “select /*TEST_NO_BINDS*/ distinct owner from myobjects” has led to another child and another version of the SQL statement being created in the shared pool.
Note the SQL_ID does not change regardless of the version count increasing!
SQL> select child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_id='d6tqd33w7u8xa';
CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------ ---------- ---------- -----------
0 2 1 2
1 1 3 1
What happens if we change some optimization setting in the users environment and run the same SQL statement again.
SQL> alter session set optimizer_mode=FIRST_ROWS;
Session altered.
SQL> select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type='TABLE';
SQL> select child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_id='d6tqd33w7u8xa';
CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------ ---------- ---------- -----------
0 2 1 2
1 1 3 1
2 1 2 1
SQL> select version_count,invalidations from v$sqlarea where sql_id='d6tqd33w7u8xa';
VERSION_COUNT INVALIDATIONS
------------- -------------
3 3
Why was the cursor not shared?
SQL> select hash_value,address from v$sqlarea where sql_text like 'select /*TEST_NO_BINDS*/ distinct owner from myobjects where object_type=''TABLE''';
HASH_VALUE ADDRESS
---------- ----------------
4168950698 000000008D920748
SQL> select * from v$sql_shared_cursor where address ='000000008D920748';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
d6tqd33w7u8xa 000000008D920748 00000000985A42D8 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
d6tqd33w7u8xa 000000008D920748 000000008A8D4E68 1 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
d6tqd33w7u8xa 000000008D920748 000000008D9D9DA0 2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N
Check the columns in the V$SQL_SHARED_CURSOR view and look for occurrences of ‘Y’.
We can see that the reason why the cursor was not shared is because of AUTH_CHECK_MISMATCH and TRANSLATION_MISMATCH in the first case and because of OPTIMIZER_MODE_MISMATCH reason in the second case
No comments:
Post a Comment