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?
xoxomosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Great!!!!!  A thousand thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.