RE: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 15 Sep 2015 14:21:52 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A3FBE_at_EXMBX01.thus.corp>

It may only be an accounting error, but it may have some significance. After flushing shared pool, creating data, and running a parallel tablescan with bind once (parallel 4) I got a few cases where critical child cursor stats didn't behave repeatably.

 SQL> select child_number, parse_calls, executions, px_servers_executions from v$sql where sql_id = '7pyfv2kzz1syh';

CHILD_NUMBER PARSE_CALLS EXECUTIONS PX_SERVERS_EXECUTIONS

------------ ----------- ---------- ---------------------
           0           4          1                     0
           1           1          0                     4

CHILD_NUMBER PARSE_CALLS EXECUTIONS PX_SERVERS_EXECUTIONS

------------ ----------- ---------- ---------------------
           0           5          1                     0
           1           0          0                     4

In this case the top one is 11g, the bottom is 12c (but the 11g didn't reproduce consistently) It seem from the disk reads and buffer gets as if child 0 was executed by the QC and the lower executed by the PX slaves. The anomaly, though, is in the parse calls - I'm not surprised that they sum to 5, but I feel the spread should be 1/4 or 5/0, and not 4/1.

Another query, two layers of slaves so 8 slaves in total, a simple scan and group by, showing another accounting anomaly (12c)

CHILD_NUMBER PARSE_CALLS EXECUTIONS PX_SERVERS_EXECUTIONS DISK_READS BUFFER_GETS

------------ ----------- ---------- --------------------- ---------- -----------
           0           2          1                     0          0           8
           1           7          0                     8      28740       29130

The total number of parse calls for a single execution doesn't surprise me - and I'd probably expect at least N-1 of N slaves to have at least a moment when they were in "pin S wait X", but that 2/7 split suggests to me that there's a path through the code that shouldn't be possible, and maybe that's where your long pins are managing to appear.

I didn't ask how the number of CPUs compares with the DOP, and how busy the system is while these anomalies occur.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Stefan Knecht [knecht.stefan_at_gmail.com] Sent: 15 September 2015 12:48
To: Jonathan Lewis
Cc: Herald.ten.Dam_at_superconsult.nl; oracle-l-freelists Subject: Re: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds

I can even reduce it to this:

SELECT /*+ parallel(AV,4) full(AV) */ AV.ATTRIB_VALUE   from retven_attrib_value av
 where av.retailer_vendor_key = :B2
/

And we're still seeing huge waits:

POC2CC1_p021_20398_MAN3.trc:WAIT #47097645114664: nam='cursor: pin S wait on X' ela= 146685 idn=1536799127 value=25769803776 where=21474836480 obj#=-1 tim=1442317596770271
POC2CC1_p022_20400_MAN3.trc:WAIT #47408974734632: nam='cursor: pin S wait on X' ela= 146505 idn=1536799127 value=25769803776 where=21474836480 obj#=-1 tim=1442317596770254
POC2CC1_p023_20402_MAN3.trc:WAIT #47208074585384: nam='cursor: pin S wait on X' ela= 146383 idn=1536799127 value=25769803776 where=21474836480 obj#=-1 tim=1442317596770255

Stefan

On Tue, Sep 15, 2015 at 6:32 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:

Herald,

Interesting that if you search MoS on that bug number the first bug that appeared on my listing referencing that as the base bug was:

Bug 17950283: HIGH PARSE TIME ELAPSED AND HIGH CURSOR : PIN S WAIT ON X WAIT EVENT WITH PARALL

Sounds exactly like Stefan's problem - though the example he's just posted doesn't look like it's got the temp table problem that explains this one.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Herald ten Dam [Herald.ten.Dam_at_superconsult.nl<mailto:Herald.ten.Dam_at_superconsult.nl>] Sent: 15 September 2015 12:14
To: oracle-l-freelists
Subject: RE: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds

Hi,

maybe is it this bug/improvement: Bug 17805316 : IMPROVE CURSOR SHARING FOR PX SLAVES FOR STATEMENTS WITH CDT. There are some related bugs but they should already been included in 12c

Herald ten Dam
Superconsult

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




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

Received on Tue Sep 15 2015 - 16:21:52 CEST