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 @ SEL$1 - blocking operation in qb SEL$1
CBRID: P @ SEL$1 - blocking operation in qb SEL$1
CBRID: C @ SEL$1 - no blocking operation found
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of rob@xxxxxxxxxxxxxxxx [rob@xxxxxxxxxxxxxxxx]
Sent: 09 October 2015 16:59
To: oracle-l@xxxxxxxxxxxxx
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 @ SEL$2BFA4EE4 - blocking operation in qb SEL$2BFA4EE4
CBRID: DFM @ 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 @navonpilot
youtube https://www.youtube.com/user/n4281k
blog: http://www.oraclewizard.com
