• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

Significance of Cursor Environment Mismatch?

Looking in OEM today i see:
Look for top reason for cursor environment mismatch in V$SQL_SHARED_CURSOR.

Performance Finding Details: Hard Parse Due to Sharing Criteria

Finding            SQL statements with the same text were not shared because of cursor environment mismatch. This resulted in additional hard parses which were consuming significant database time

In that V$SQL_SHARED_CURSOR view i'm seeing 'Y'(s)  in some of the columns.

TOP_LEVEL_DDL_MISMATCH
Is top-level DDL cursor


REMOTE_TRANS_MISMATCH
The remote base objects of the existing child cursor do not match



USER_BIND_PEEK_MISMATCH
Cursor is not shared because value of one or more user binds is different
 and this has a potential to change the execution plan

What is the significance of these?
0
xoxomos
Asked:
xoxomos
1 Solution
 
sdstuberCommented:
TOP_LEVEL_DDL_MISMATCH - this is DDL (like create or alter statements) - these are always hard parsed because they are modifying a structure so they are always new.


REMOTE_TRANS_MISMATCH - the signature of the remote object has changed.  This might be insignificant, but since it's a remote object the local cursor can't validate it on its own so it "plays it safe" and reparses.  It could also mean there was a structural change on the remote object so it must reparse.  It could also mean a public database link resolved to different objects for different users and must reparse.

USER_BIND_PEEK_MISMATCH - When parsing the cursor, the optimizer looked at the values of the bind variables and found  significant difference that it reparsed to determine if the plan might change.  For example -

select * from employees where date_of_birth < :B1;

as the value of the bind variable decreases, the likelihood data skew will impact the results increases because fewer records will be that old.  Eventually causing the optimizer to reparse the query to determine of the change in the number of expected rows will impact the plan.
1
 
xoxomosAuthor Commented:
Great!!!!!  A thousand thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now