Thursday Jul 11, 2013

More parses in 12c ?

More parses in 12.1 ?

Some customers upgrading from 11.2.0.3 to 12.1 might get alarmed because they see a spike in total number of parse count.
But not to worry, it is not a problem in 12.1. The problem is in 11.2.0.3.

Lets take the following example :

select a.name, b.value 
   from v$statname a, v$mystat b 
  where a.statistic# =  b.statistic# 
    and ( a.name like '%parse count%' 
       or a.name like 'session cursor cache%') 
    and value>0; 

 begin 
 for n in 1..100 loop 
 begin 
   execute immediate 'alter session set optimizer_goal=choose'; 
 exception 
  when others then 
  null; 
 end; 
 end loop; 
 end; 

 select a.name, b.value 
   from v$statname a, v$mystat b 
  where a.statistic# =  b.statistic# 
    and ( a.name like '%parse count%' 
       or a.name like 'session cursor cache%') 
    and value>0; 

I got the output of the queries and put them side by side in each version.

NAME                            11.1 VALUE   11.2 VALUE   12.1 VALUE
-----------------------------   ----------   ----------   ----------
session cursor cache hits                2            3            1
session cursor cache count               3           10           10
parse count (total)                     12           11           12
parse count (hard)                                    1   

PL/SQL procedure successfully completed.

NAME                            11.1 VALUE   11.2 VALUE   12.1 VALUE
-----------------------------   ----------   ----------   ----------
session cursor cache hits                3            3            1
session cursor cache count               4           11           11
parse count (total)                    114           13          114
parse count (hard)                     100          101          100
parse count (failures)                                           100


Notice the number of "parse count (total)" in 11.2 is less than "parse count (hard)" which makes no sense since "parse count (total)" should include Hard , Soft and Failed parses.

This might sound like an extreme case but there are other soft parsing scenarios that also reproduce this oddity.

For customers moving from 11.1 to 11.2.0.3 it might have looked as an improvement that there were less parses in total and then moving from 11.2.0.3 to 12.1 they would probably notice the spike and raise concerns.

So do not be alarmed, 12.1 does not do more parses, nor 11.2.0.3 does less parses, it is just a bug in the instrumentation of the stats, specifically :

Bug 13837105  statistics "parse count (total)" and "session cursor cache hits" miscounted [ID 13837105.8]

Tuesday May 21, 2013

Diagnosis of a High Version Count


In the past, I have participated in many situations where I had to debug the mechanisms that generate large number of child cursors and learned that ,more often than not ,a cursor is not shared for many reasons and those may change from version to version so I wrote a tool to help debug it.

I see pertinent first explaining a little what child cursors are and what a high version count is.

A cursor is the instance of query/dml in the library cache.
A hash key to locate the cursor in the library cache is calculated based on the text of the statement.
But if the text is the only criteria used to identify the cursor in the library cache what happens if the cursor is not compatible with the conditions that execute them ?
For example, how can the same cursor be used when the same query is executed by 2 users with different privileges or with different language or even different objects?

It creates a version of the cursor with those unique conditions that make it possible to execute.
A version of a cursor is also known as child cursor. So if there is a child, is there a parent ?
Yes, the parent cursor is the root where all those child cursors are linked.

In order for a session to find the correct version to execute, it attempts to match the query text in the library cache using the hash key generated by the text to the parent cursor and once found it scans the list of versions (or children) one by one to match which has the correct set of conditions and if it does not find one then it creates a new version and adds it to the list.

For example, lets say I have 3 schemas, A, B ,C and a table T in schema A and a public synonym for T.
Lets say further that A grants access on T to B.

What happens if I run the query : "SELECT * FROM T" from each schema ?

if the query is ran by A, "T" points to the schema object "A"."T" and the query runs ok. A version is created that indicates that a session connected with schema "A" can access "A"."T".
if the query is ran by B, "T" points to public synonym "T" and that points to "A"."T" and because A granted access on T to B then the query runs ok. A version is created that indicates that a session connected with schema "B" can access "A"."T".
if the query us ran by C, "T" points to public synonym "T" and that points to "A"."T" and but because A has not granted access on T to C then the query fails and no version is created.

If now, "A" grants access on "T" to "C" and runs the query again, then "T" is resolved again to to public synonym "T" and that points to "A"."T" and since it now has privileges the query runs ok and a version is created that indicates that a session connected with schema "C" can access "A"."T".
If now, "A" revokes access on "T" to "B", then the version associated with schema "B" is invalidated, to let any session that has a pointer to that child cursor that it is no longer valid.

If the query is ran by "A" but on a client that has German NLS settings and all the previous ones were done in English then a new version is created for German NLS settings.

I can easily continue the examples as there are over 50 different reasons why a child needs to be created.
These are tracked in V$SQL_SHARED_CURSOR.

There are reasons related to Binds, privileges, languages, parallel execution, optimization, and more,
and reasons are added, removed or change in meaning with each version.

Even if in theory there could be 50+ combinations of reasons and each reason may have "n" number of variables that can make it non share-able to anyone but who has those same conditions , the number of versions usually remains low. The norm is that most cursors will have less than 100 versions but there are times when hundreds or thousands of versions are made and their management cause significant performance degradation in the system.

As each reason is related to different features in the database there is not one way to debug a high version count situation.
For example, in order to diagnose when the reason for not sharing is language, it might be necessary to know what languanges are involved.
If the reason is binds, it might be necessary to know how many, what datatype, and what size are the binds.
and so on.

For a more in depth explanation see : Troubleshooting: High Version Count Issues [ID 296377.1]

After working several of these high version count cases I came to identify some common diagnostics , their presentation and their interpretation so I made a diagnostic tool to collect them.

Roudtrips between support and the customer have always been annoying and riddled with mistakes and omitions so the tool has the objective to collect as much relevant information as possible to only the reasons identified.

The tool is VERSION_RPT and can be found in : High SQL Version Counts - Script to determine reason(s) [ID:438755.1]

The tool adapts to the number of reasons in each version so it works from 9.2 to 11gR2 and I expect to work too on 12c once released.
The tool can be used to diagnose one or many cursors, including pseudocursors, using SQL_ID or HASH_VALUE.

The output of the report shows a summary of the reasons and information about the factors that each reason may take into consideration to decide to share or not, like Optimizer parameters when the reason is  OPTIMIZER_MISMATCH.

These reasons are searchable in MOS. Searching for the phrase :

"Cursor not shared due to USER_BIND_PEEK_MISMATCH"

Can found BugTag Notes like Bug 8981059 - High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking [ID 8981059.8]

To collect data for a group of SQLs it can be done


SELECT b.*
FROM v$sqlarea a ,
     TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;

Here is an example of its use for one cursor:

===============

SQL> set pages 2000 lines 180
SQL> SELECT * FROM TABLE(version_rpt('8swypbbr0m372'));

Version Count Report Version 3.2.1 -- Today's Date 02-may-12 11:50

RDBMS Version :11.2.0.3.0 Host: XXXXXXXXXXXXXXXXXXXX Instance 1 : XXXXXXXXXX
==================================================================
Addr: 00000003D8B6A570  Hash_Value: 3993603298  SQL_ID 8swypbbr0m372
Sharable_Mem: 208913 bytes   Parses: 477
Stmt:
0 select order#,columns,types from access$ where d_obj#=:1
1

Versions Summary
----------------
OPTIMIZER_MISMATCH :10
BIND_MISMATCH :1
LANGUAGE_MISMATCH :1
OPTIMIZER_MODE_MISMATCH :3
PX_MISMATCH :2
Total Versions:10
~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
      872636971 11
              0 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MISMATCH :

1 versions with is_recur_flags = 167
2 versions with is_recur_flags = 161
9 versions with _pga_max_size = 614400 KB
7 versions with optimizer_mode = choose
3 versions with is_recur_flags = 35
7 versions with hash_area_size = 10485760
1 versions with optimizer_mode = rule
2 versions with advanced_queuing_internal_cursor = 1
2 versions with is_recur_flags = 163
1 versions with parallel_execution_enabled = false
1 versions with _pga_max_size = 204800 KB
10 versions with _optimizer_order_by_elimination_enabled = false
2 versions with is_recur_flags = 39
1 versions with hash_area_size = 2000000
1 versions with sort_area_size = 1000000
8 versions with _db_file_optimizer_read_count = 128
10 versions with _optimizer_cost_based_transformation = on
1 versions with parallel_threads_per_cpu = 4
1 versions with parallel_max_degree = 16
1 versions with _parallel_syspls_obey_force = false
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for BIND_MISMATCH :

Consolidated details for :
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)
======== ======== =============== =============== ======== ================
      11        1              22              22        2 (,)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
          0                 0             0            9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for LANGUAGE_MISMATCH :

No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for OPTIMIZER_MODE_MISMATCH :

1 versions with RULE
9 versions with CHOOSE
2 versions with ALL_ROWS
~~~~~~~~~~~~~~~~~~~~~~~~~

Details for PX_MISMATCH :

No details available

####

To further debug Ask Oracle Support for the appropiate level LLL.

alter session set events 'immediate trace name cursortrace address 3993603298, level LLL';

To turn it off do use address 1, level 2147483648
================================================================

84 rows selected.


Monday Apr 29, 2013

Parse and Parse again.

Since the first release of JDBC and event longer ago with Pro* Precompilers to this day I still see the following issue :

Please notice below in this abstract of a 30 min AWR the number of Parse Calls vs Executions, the SQL Module and in the case of the first SQL, the SQL Text too. 

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
339,567 339,566 15.93 0mr7azgm9psws JDBC Thin Client SELECT COUNT(*) FROM DUAL
101,574 101,880 4.77 5uta56d6syjv3 JDBC Thin Client select TRIM(emp_fname || ' ' |...
46,800 46,799 2.20 6vw7kzhpb1dm3 JDBC Thin Client select "STATUS_ID" as "status...
35,509 35,509 1.67 648yjr5s1w7uc JDBC Thin Client SELECT "PROTOCOL_GROUP_ID" as...
31,292 32,540 1.47 0jvyqkxaytndr bvlab.exe select crf.CRF_LABEL from COMP...

Since the introduction of the shared pool in Oracle7 it was available the capability for clients to have a SQL Statement Cache in order to do the famous "Parse once, Execute Many" best practice. This feature is controlled on the RDBMS by the SESSION_CACHED_CURSORS parameter and this makes many people believe that the feature is managed at the RDBMS side alone but this far from the truth.

When this feature is not used then the client software parses the statement for each execution does not matter if the parameter is set in the database.
Many take this feature for granted because SQL*Plus uses it, so they assume it is true for other client software but it is a feature that has to be explicitly managed by the client software and works in conjunction with the RDBMS when the parameter is set.

In JDBC it is necessary to explicitly enable it and has an implicit setImplicitCachingEnabled and an explicit setExplicitCachingEnabled way to manage the cache

It is a little easier with the precompilers by setting are precompile time the HOLD_CURSOR and RELEASE_CURSOR parameters.

Take a moment to look at your own AWR or Statspack and verify your application is using the Client Statement Cache as intended.

Now the cherry of the sundae is the SQL "SELECT COUNT(*) FROM DUAL".

Over the years I've seen variations of queries over DUAL coming from JDBC in large quantities. I have been given the explanation from Customers and Developers that these queries are used as a heartbeat, to confirm the connection to the database is stil open.

I can understand that was necessary when jdbc and connection pooling was a young technology but isn't it anything better nowadays that will do the same thing without parsing and parsing the same useless statement without end ?

I would like to hear from people about what other techniques/features are used that can avoid the need to parse a bogus SQL to the database. 

swing out sister - windmills of your mind

About

I am Abel Macias email, an Oracle support engineer that specialized in Performance that belongs to Exadata Support.
Disclaimer This blog looks to broadcast my experiences while diagnosing performance problems on Oracle's RDBMS, mostly on Exadata.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today