Interesting performance challege (persistent child cursor mis-match)

From: Chris Taylor <>
Date: Sat, 2 May 2020 13:12:56 -0400
Message-ID: <>

Env: x86-64
Exadata / OS: Oracle Linux / 4-Node RAC Latest Patches

This week we added an index for a very important ETL process to drive execution time down.
Subsequently we had a performance change in data ingestion process (100k records per hour) for a SQL_ID.

Ultimately, we put in 2 SQL Profiles and 2 SQL Baselines as the plan for the query will rotate between 2 acceptable plans and varies by day as there is a nightly job that rebuilds a temp table and repoints a synonym.
(PL/SQL now exists to rotate the 2 SQL profiles based on the synonym).

The INTERESTING part is that no matter what, we were still getting suboptimal child plans/cursors that were driving me a bit bananas. Even after flushing/invalidating all the plans for that particular SQL id.

This morning, I wanted to take a fresh look at it and approach it from a cursor level and understand what was different.

Which led me to GV$SQL_SHARED_CURSOR and mismatch information. (Which I was familiar with already but due to the "fire" of this performance problem hadn't had time to adequately research).

The MISMATCH for this cursor was on LANGUAGE_MISMATCH. I was like, "Hey, that's very interesting. Why is that?"

Turns out we have 2 separate (or more) client servers that go through the same code, and execute the same SQL.

The primary connecting server doesn't set NLS_LANG or OS LANG variable at all. And those sessions come in as WE8MSWIN1252.

The other connecting servers have LANG=en_US.UTF-8

In this particular case the SQL_ID was being parsed first by clients connecting with the en_US.UTF-8 variable set.

Secondary connections to this node by clients with WE8MSWIN1252 are getting a child cursor with "LANGUAGE_MISMATCH".

Normally this SQL_ID only ever has 2 plans and no child cursors but due to the time of day we were working and batch jobs running, we had multiple servers parsing this SQL_ID after purging it. (nightly jobs)

I *theorize *that UTF-8 plans are unsharable with clients who connect as WE8MSWIN1252 .... but SQLs that are *first *parsed by WE8MSWIN1252 connected sessions *can be *re-used by clients that come in using UTF-8 .

*Does that sound like a reasonable theory?*

I'm currently waiting for for the last 3 remaining batch jobs to finish their work so that I can flush the SQL_ID again and I expect that will return us to our normal behavior of only seeing 1 of 2 plans every day.

(Now, if this particular pot would hurry up and boil, I'd be able to tell
for sure....)


Received on Sat May 02 2020 - 19:12:56 CEST

Original text of this message