It's a bug.

Just wanted to let ya all know that the delayed block cleanout issue I logged with Oracle last December is an official bug:

Bug 17912562 : DELAYED BLOCK CLEANOUT NOT WORKING IN12C

Memory Used in a Sort

Found a little issue with the data displayed by DBMS_XPLAN.DISPLAY_CURSOR when it comes to the Used-Tmp column. This is certainly something to be aware of when you look at that column in the output. 

Take a look at this simple statement:

SQL> SELECT PLAN_TABLE_OUTPUT

  2    FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3    ('g4tr51k11z5a0',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  g4tr51k11z5a0, child number 0

-------------------------------------

select * from big_tab order by owner

Plan hash value: 3765827574

-----------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

-----------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |      1 |        |   2868K|00:00:46.50 |   48224 |  97395 |  49190 |       |       |          |         |

|   1 |  SORT ORDER BY     |         |      1 |   2868K|   2868K|00:00:46.50 |   48224 |  97395 |  49190 |   432M|  6863K|  100M (1)|     385K|

|   2 |   TABLE ACCESS FULL| BIG_TAB |      1 |   2868K|   2868K|00:00:04.26 |   48215 |  48205 |      0 |       |       |          |         |

-----------------------------------------------------------------------------------------------------------------------------------------------

Looking at the Used-Tmp column it appears this didn’t use much temp space, only 385K for a sort that has a Used-Mem of 100M, hey that’s really great!  But how can that be?  Well it can’t is the short answer.  There appears to be a bug here. 

The data used by this function is coming from V$SQL_PLAN_STATISTICS_ALL.  If we look at that view for this query we can see that value is in the LAST_TEMPSEG_SIZE column (394240/1024 = 385K).  The documentation says that all these column are in bytes.  But It certainly appears that LAST_TEMPSEG_SIZE isn’t.

SQL> SELECT SQL_ID, ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE

  2  FROM v$sql_plan_statistics_all

  3  WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE

------------- ---------------------- ---------------------- ---------------- -----------------

g4tr51k11z5a0

g4tr51k11z5a0              453454848                7027712        104879104            394240

g4tr51k11z5a0

How is it that I can say with any level of certainty that this isn’t in bytes?  One thing is it certainly seems odd that a sort that used 100M would only use a couple hundred bytes of temp space.  But also if we look at a different view V$SQL_WORKAREA we see:

SQL> SELECT SQL_ID, ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE

  2  FROM V$SQL_WORKAREA

  3  WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE

------------- ---------------------- ---------------------- ---------------- -----------------

g4tr51k11z5a0              453454848                7027712        104879104         403701760

This appears to be in bytes.  Note that 40370176/1024 = 394240 the number we saw in V$SQL_PLAN_STATISTICS_ALL.  

So it sure looks like the view V$SQL_PLAN_STATISTICS_ALL is already converting the temp segment size into Kilobytes, but the function DBMS_XPLAN.DISPLAY_CURSOR thinks it’s in bytes and covert it to Kilobytes.  This actually makes it Megabytes, so it wasn’t using 385K it was using 385M.

ANSI or Classic?

I’m an old dog and I have yet to embrace ANSI syntax for writing SQL.  There is coming a day that I will likely have to move to ANSI and it appears to be coming sooner rather than later.  So I’ve started on this path.  While look around on the web the other day I stumbled on to a post saying that the old syntax is “bad” and the ANSI syntax is better (http://www.orafaq.com/node/2618).  So just for fun I pulled the queries used to see what the difference was.  

There isn’t any.

The author had stated that the classic syntax “is generating a cartesian product, and then filtering the result set with a predicate.”

The reality is that both the classic and the ANSI syntax create exactly the same plan.  Running both I then looked at V$SQL to see the plan created:

SQL> SELECT *

  2  FROM   emp,

  3         dept

  4  WHERE  emp.deptno = dept.deptno

  5         AND dept.dname = 'SALES';

SQL> SELECT *

  2  FROM   emp

  3         join dept USING(deptno)

  4  WHERE  dname = 'SALES';

SQL> select sql_id, child_number,plan_hash_value, sql_text from v$sql where sql_text like 'SELECT * FROM   emp%'

SQL_ID           CHILD_NUMBER PLAN_HASH_VALUE

------------- --------------- ---------------

SQL_TEXT

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

4ug91aycb85jh               0      1688427947

SELECT * FROM   emp        join dept USING(deptno) WHERE  dname = 'SALES'

6snrnnz2qfwms               0      1688427947

SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno        AND dept.dname = 'SALES'

Notice they both have the exact same PLAN_HASH_VALUE, this means they both use the exact same plan:

SQL> SELECT PLAN_TABLE_OUTPUT

  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3      ('4ug91aycb85jh',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

SQL_ID  4ug91aycb85jh, child number 0

-------------------------------------

SELECT * FROM   emp        join dept USING(deptno) WHERE  dname =

'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |

|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |

|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |

|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |

|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("DEPT"."DNAME"='SALES')

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO" IS NOT NULL)

SQL> SELECT PLAN_TABLE_OUTPUT

  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3      ('6snrnnz2qfwms',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

SQL_ID  6snrnnz2qfwms, child number 0

-------------------------------------

SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno

AND dept.dname = 'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |

|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |

|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |

|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |

|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("DEPT"."DNAME"='SALES')

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO" IS NOT NULL)

Is this going to be true for all statement?  I certainly doubt it, there are always exceptions.  My inclination is that switching between the two syntax types is likely to produce the same plan more times than not.    Of course a test is worth 1000 opinions. 

Database used for this test was:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Running on Windows 7 Professional.