Here is a problem query (some columns are hidden to preserve readability):
SQL Plan Monitoring Details (Plan Hash Value=40624586) ================================================================================================================================================ | Id | Operation | Name | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | | (Actual) | Reqs | Bytes | (%) | (# samples) | ================================================================================================================================================ | 0 | SELECT STATEMENT | | 1 | 0 | | | | | | 1 | NESTED LOOPS | | 1 | 0 | | | | | | 2 | NESTED LOOPS | | 1 | 62594 | | | | | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED | MAIN_TABLE | 1 | 62594 | 22777 | 178MB | 25.89 | Cpu (1) | | | | | | | | | | db file parallel read (28) | | 4 | INDEX RANGE SCAN | MAIN_TABLE_I | 1 | 62594 | 25 | 200KB | | | | 5 | INDEX UNIQUE SCAN | CHILD_TABLE_PK | 109K | 62594 | 63798 | 498MB | 71.43 | Cpu (2) | | | | | | | | | | db file parallel read (78) | | 6 | TABLE ACCESS BY INDEX ROWID | CHILD_TABLE | 105K | 0 | 1402 | 11MB | 1.79 | db file parallel read (2) | ================================================================================================================================================You can see that we've obtained 62K rows at step 3, but notice a number of "Execs" at step 5: 109K. It's almost twice as higher than 62K.
Number of rows ("Rows (Actual)") is correct, though.
I checked relevant columns in V$SQL_PLAN_MONITOR view (STARTS/OUTPUT_ROWS) and verified that there's no contradiction between V$SQL_PLAN_MONITOR view and DBMS_SQLTUNE.REPORT_SQL_MONITOR output.
I investigated this issue further and constructed a simple test case which can be used to reproduce the issue.
SQL> create table fact
2 as
3 select date '2015-08-01' + trunc(level/4)/86400 fact_date,
4 lpad('x', 240, 'x') padding,
5 mod(level, 100000) dim_id
6 from dual
7 connect by level<=4*86400;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats( '', 'fact')
PL/SQL procedure successfully completed.
SQL>
SQL> create table dim
2 as
3 select trunc(dbms_random.value(1,100000)) id,
4 lpad('x', 340, 'x') padding
5 from dual
6 connect by level<=2*86400;
Table created.
SQL>
SQL> create index dim_i on dim(id);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats( '', 'dim')
PL/SQL procedure successfully completed.
I created 2 tables: FACT and DIM, populated them with data and gathered statistics.Now, I will flush the buffer cache and execute a test query:
SQL> alter system flush buffer_cache;
System altered.
SQL>
SQL> select /*+ monitor leading(f) use_nl(d) full(f)*/
2 count(f.padding),
3 count(d.padding)
4 from fact f,
5 dim d
6 where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
7 and d.id = f.dim_id;
COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
4214 4214
1 row selected.
SQL>
Let's see the DBMS_SQLTUNE report for the last query:
Ok, we've just reproduced the issue: row source 4 of the plan generated 2404 rows, but the number of "Execs" (2539) at line 5 is equals to 2539, which is slightly greater.
It was not by accident that I flushed the buffer cache before I execute a query.
If I'll execute a query once again, then dbms_sqltune.report_sql_monitor report has number of "Execs" in line 5 equals to number of rows in line 4:
SQL Plan Monitoring Details (Plan Hash Value=85884857) ====================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ====================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | | | 2 | NESTED LOOPS | | | | 1 | +0 | 1 | 4214 | | | | | | 3 | NESTED LOOPS | | 5025 | 11890 | 1 | +0 | 1 | 4214 | | | | | | 4 | TABLE ACCESS FULL | FACT | 2408 | 3535 | 1 | +0 | 1 | 2404 | 108 | 100MB | | | | 5 | INDEX RANGE SCAN | DIM_I | 2 | 1 | 1 | +0 | 2404 | 4214 | | | | | | 6 | TABLE ACCESS BY INDEX ROWID | DIM | 2 | 4 | 1 | +0 | 4214 | 4214 | | | | | ======================================================================================================================================================I believe, that differences in execs/rows were caused by NLJ batching/Table prefetching.
How can we prove it?
First, I'll show how changes "physical reads cache prefetch" before/after query execution.
SQL> select n.name, s.value
2 from v$statname n, v$mystat s
3 where n.name like '%prefetch%'
4 and s.statistic#=n.statistic#
5 and s.value>0
6 order by n.name;
NAME VALUE
------------------------------ ----------
physical reads cache prefetch 127987
1 row selected.
SQL>
SQL> select /*+ monitor leading(f) use_nl(d) full(f)*/
2 count(f.padding),
3 count(d.padding)
4 from fact f,
5 dim d
6 where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
7 and d.id = f.dim_id;
COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
4214 4214
1 row selected.
SQL>
SQL> select n.name, s.value
2 from v$statname n, v$mystat s
3 where n.name like '%prefetch%'
4 and s.statistic#=n.statistic#
5 and s.value>0
6 order by n.name;
NAME VALUE
------------------------------ ----------
physical reads cache prefetch 131321
1 row selected.
Statistic increased by the 131321-127987=3334 blocks.
Second, when I disable nlj_batching, there are no differences in execs/rows:
Or, if we want to obtain "more-classic" NLJ plan shape (I added no_nlj_prefetch(d) hint):
I have searched for a similar issues through MOS and found a Bug 13634445 : V$SQL_PLAN_MONITOR AND NL BATCHING IN 11G, which has status 92 "Closed, Not a Bug".
Another interesting observation is that by adding a "gather_plan_statistics" hint, I'll disable prefetching for that case and fix V$SQL_PLAN_MONITOR "discrepancy"
