RE: 10053 Trace Question
Date: Fri, 9 Oct 2015 17:11:51 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A8B17_at_EXMBX01.thus.corp>
I don't think it's anything terribly exciting. At the moment my best guess is that Oracle is checking to see if there's an operation that's driving an index by rowid that has to run to completion (that's the blocking bit) before the index access starts. If not then the "sort cluster by rowid" would be the first blocking operation which might discourage the optimizer from choosing it.
e.g.
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 772 | | | | | 1 | NESTED LOOPS | | 1 | 48 | 0 |00:00:00.01 | 772 | | | | | 2 | SORT CLUSTER BY ROWID | | 1 | 62 | 20 |00:00:00.01 | 762 | 2048 | 2048 | 2048 (0)| | 3 | NESTED LOOPS | | 1 | 62 | 20 |00:00:00.01 | 762 | | | |
|* 4 | HASH JOIN | | 1 | 62 | 4 |00:00:00.01 | 756 | 1483K| 1483K| 1497K (0)|
|* 5 | TABLE ACCESS FULL | GRANDPARENT | 1 | 60 | 55 |00:00:00.01 | 253 | | | |
|* 6 | TABLE ACCESS FULL | PARENT | 1 | 110 | 105 |00:00:00.01 | 503 | | | |
|* 7 | INDEX RANGE SCAN | C_PK | 4 | 1 | 20 |00:00:00.01 | 6 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| CHILD | 20 | 1 | 0 |00:00:00.01 | 10 | | | |
---------------------------------------------------------------------------------------------------------------------------------
This plan starts with a full scan on grandparent to build the hash table; this is a blocking operation - the tablescan of parent can't start before the build of grandparent has completed; similarly the first index access of child can't start until (two steps away) the build of grandparent has finished; so the SORT CLUSTER BY ROWID in line 2 won't be introducing a blocking operation, which (possibly) is why the optimizer can decide to use it.
Having said that, I can't figure out the logic of the way this appears in the trace file, viz:
CBRID: GP _at_ SEL$1 - blocking operation in qb SEL$1 CBRID: P _at_ SEL$1 - blocking operation in qb SEL$1 CBRID: C _at_ SEL$1 - no blocking operation found
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of rob_at_oraclewizard.com [rob_at_oraclewizard.com] Sent: 09 October 2015 16:59
To: oracle-l_at_freelists.org
Subject: 10053 Trace Question
A developer passed a query over the qube walls and wanted me to look at and see if I can tune it.
Here is the redacted query that generated this.
select <REDACTED>_month_id,
count(incident_id) incident_count,
decode(min(decode(decode(ni.ddocname,null,null,nvl(dfm.tag_type,'Y')),'E',1,'W',2,'N',3,'Y',4,5)),1,'E',2,'W',3,'N',4,'Y',NULL) brv
from <REDACTED>_incident ni
left join dpw_field_metadata dfm on dfm.ddocname = ni.ddocname and dfm.active_flag = 'Y' group by <REDACTED>_month_id
)
Whenever I see blocking it tends to perk my ears up. Anyone able to direct me to documantion on the following mesage or have a good idea what is happening?
kkoqbc: finish optimizing query block SEL$2BFA4EE4 (#1) CBRID: NI _at_ SEL$2BFA4EE4 - blocking operation in qb SEL$2BFA4EE4 CBRID: DFM _at_ SEL$2BFA4EE4 - blocking operation in qb SEL$2BFA4EE4 apadrv-end
Be sure to vote for me in the Oracle Developers Choice Awards. Voting Ends Oct 15.
Logon to otn.oracle.com and vote. These are your awards. https://community.oracle.com/community/database/awards/db-design-voting
Robert P. Lockard
President Oraclewizard.com, Inc.
"When given the choice between two evils, I always take the one I have not tried." Mae West
(cell) 571.276.4790 (office) 410.766.6960 (fax) 410.766.0332
twitter _at_navonpilot
youtube https://www.youtube.com/user/n4281k blog: http://www.oraclewizard.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 09 2015 - 19:11:51 CEST