Re: Interesting performance challege (persistent child cursor mis-match)

Hi Chris, sorry if my response sounds like nitpicking, but I try to understand what you are saying.

The query text gets hashed and based on the hash it gets a parent handle allocated. Any query with the same text will end up at this parent handle. SQL cursor parent handles do not execute, a child cursor is needed to execute, and has an execution plan with it.

You said:
> 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)

A SQL_ID is another representation of the hash value (I believe Tanel Poder found this). I do not understand how a parent cursor / hash value / SQL_ID has 2 plans and no child cursors. The only way for identical SQL, thus a parent handle, thus a SQL_ID to execute alias do something is when it has a child handle (which is what I assume you mean with ‘child cursor’). The only way for identical SQL to have 2 plans is to have 2 children. Of course a parent can have a child at one time, which gets purged and then later has another child with another execution plan.

The language for an oracle client might be picked up from the OS language setting, so if you got different operating systems the languages might be different. But that is an hypothesis.

You could validate your language assumption by connecting from the different servers using an interactive client tool and execute ’select * from nls_session_parameters’ to see the settings, and then let them execute something identical and see if they get their own children.

The sharing criteria from v$sql_shared_cursor are properties of a child cursor to prevent from using a child cursor that could lead to differences in any form. One “trick” I use to get another child is to set a session parameter to a different value (I use db_file_multiblock_read_count for example). The way it works is it looks up the parent handle, then obtains the child (pointer) list in the parent’s heap 0, and scans the child list from the most recent child to the oldest. If it finds a suitable/compatible child, it will use it, if it doesn’t, it will start creating a new one.

For every child it’s investigating, it looks at all the properties that could mean the child is not the most optimal child to use. That is what v$sql_shared_cursor shows.

Frits Hoogland

http://fritshoogland.wordpress.com <http://fritshoogland.wordpress.com/> frits.hoogland_at_gmail.com <mailto:frits.hoogland_at_gmail.com> Mobile: +31 6 14180860

> On 2 May 2020, at 19:12, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:
>
> Env:
> 12.1.0.2 x86-64
> Exadata / OS: Oracle Linux / 4-Node RAC
> Latest Patches
>
> Background:
> 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....)
>
> Chris
>
>
>

--
http://www.freelists.org/webpage/oracle-l