Mohamed Houri’s Oracle Notes

October 13, 2017

Execution plans : blocking and non-blocking operations

Filed under: explain plan — hourim @ 6:52 pm

Below are two 12cR2 row-source execution plans of the same query:

SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |      5 |
|*  1 |  VIEW                    |      |      1 |   1000 |      5 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL     | T1   |      1 |   1000 |   1000 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)
SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |   1000 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FULL SCAN     | IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)
 

Forget the switch from a FULL TABLE scan to an INDEX FULL scan at line n° 3 and the switch from a SORT to a NOSORT operation at line n°2. What other remarkable difference we can still spot out there? Have you noticed that operation n°3 produced 1000 rows in the first execution plan and only 6 rows in the second one?

Here’s the model with which I obtained the above execution plans respectively:

create table t1 
as select 
    rownum n1
   ,trunc((rownum-1/3)) n2 
from dual 
connect by level <=1e3;

create unique index idx_t1_n1_uk on t1(n1);

exec dbms_stats.gather_table_stats(user, 't1');

select /*+ gather_plan_statistics */ n1
from 
   (select 
       n1
	  ,rank() over (order by n1) rn
	from t1)
where
   rn <=5;

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));
alter table t1 modify (n1 not null);

-- execute the same query

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));

What happened here so that an operation without a filter predicate produces 6 rows where it is supposed to generate the entire set of rows in the table i.e. 1,000 rows?

The explanation comes from the way Oracle handles blocking and non-blocking operations. A blocking operation requires receiving all rows from its child operation before a single row can be processed. A non-blocking operation consumes and produces rows at the same time.

Applied to the current situation, we see that the two previous execution plans have both a blocking operation at line n°2 which is the VIEW operation.

Right?

In fact, as magnificiently explained by Jonathan Lewis in this post, the blocking nature of the VIEW operation is function of its first child operation. If this one is a blocking operation then the VIEW is a blocking operation as well. If, however, the child operation is not blocking then its VIEW parent operation is a non-blocking operation that can consume and produce rows at the same time.

The first child of the two VIEW operations in the above execution plans is respectively:

|*  2 |   WINDOW SORT PUSHED RANK|

|*  2 |   WINDOW NOSORT STOPKEY| 

The WINDOW SORT PUSHED RANK child operation of the VIEW is a sort operation. Sorts,as you know, are blocking operations. This is why the VIEW in the first execution plan is a blocking operation. Oracle has to retrieve all rows from table t1 (A-Rows = 1000) at operation n°3, sort them totally at operation n°2 before applying the filter predicate n°2 reducing as such the number of retrieved rows to only 5.

In the second execution plan the WINDOW NOSORT STOPKEY child operation of the VIEW is a non-blocking operation thanks to the ordered list of keys it receives from its INDEX FULL SCAN child operation. The VIEW asks for the first row from its first child operation which, at its turn, asks for the same row from its child operation the INDEX FULL SCAN at line 3. The index gives the first ordered key it has found to its parent operation, the WINDOW NOSORT STOPKEY,which feeds back again it parent operation, the VIEW, provided the index key survives the filter predicate n°2. The VIEW asks then for the second row, for the third row, and so on until the WINDOW NOSORT STOPKEYdeclares the end of the query. Indeed, the WINDOW NOSORT STOPKEY knows the upper bound it can’t go beyound. This is why when it receives the 6th row from the index full scan operation (A-Rows= 6) it realises that this row spans its upper bound and that it is now time to signal to the coordinator (select statement) the end of the query.

Here’s another example demonstrating that even with FULL segment scans Oracle is able to stop generating rows at the appropriate moment. All what it requires from the parent operation is to be a non-blocking operation able to consume and produce rows at the same time:

-- n1  is declared nullable

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      5 |
|*  1 |  VIEW                  |      |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |   1000 |      6 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)
-- n1 is declared not null

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FAST FULL SCAN| IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)

Summary

Sometimes we need to read and interpret execution plans not only in their order of operation using the “first child first, recursive descent” rule, but also in their order of data flow propagation (or row-source generation). In the latter case, knowing how to identify blocking and non-blocking operations is very important.

Advertisements

3 Comments »

  1. Hi Mohamed,

    thank a lot for the post, read and understood. But how does one know which row-source generation operation is blocking and which is non-blocking?
    It’s clear in the case of sort/nosort if you think about it, but what about others? It is no possible to see this in the dbms_xplan-output, is it?

    Best regards
    Salek

    Comment by Salek Talangi — October 14, 2017 @ 10:50 am | Reply

  2. Salek

    Excellent question. I think practice coupled with interest for details and curiosity can make someone paying attention to actual rows(A-Rows) generated by individual operations so that he can point out blocking operations. I don’t have a rule to find blocking operations but will try to dig into this in near future. A HASH GROUP BY can be a blocking operation. A HASH JOIN BUFFERED(http://oracle-randolf.blogspot.fr/2012/12/hash-join-buffered.html ) in parallel execution is an artificial blocking operation ensuring the rule “at most two DFO can be active by DFO tree at the same time”, etc…

    Best regards
    Mohamed

    Comment by hourim — October 15, 2017 @ 8:00 am | Reply

  3. Hello Mohamed,
    Thank you. Good to remember , it remind me the book from christian antognoni about blocking operation.
    Have a good day

    Comment by tgascard — October 16, 2017 @ 7:27 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: