Ric Ramblings: 2018

Monday, December 10, 2018

WITH and the MATERIALIZE hint in the cloud or not

Recently in class a student mentioned that she’d be told that for the WITH clause you don’t need to use the MATERIALIZE hint anymore. Maybe. It turns out that if you REALLY want the subquery factor to be materialized, you do need to use it.

Keep in mind that the optimizer sees your SQL statement as just that, a statement. It can and will rearrange and rewrite your SQL into something it likes better. And this means it can merge your subquery factors defined in the WITH clause into the statement.

I’ll use a simple statement to illustrate this point. Clearly this select could (and really should) be written without a WITH clause at all. This is just a simple example to illustrate the point. Once you have a better understanding of how it works you can decide what is best for your particular situation.

Edit: Tests were done on an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0, on a windows laptop.

SQL> -- no hints

SQL> WITH inline_view AS

2 (

3 SELECT /*+ qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders

4 FROM ord2

5 GROUP BY CUST_NO

6 )

7 SELECT /*+ qb_name(main) */ *

8 FROM inline_view where total_orders > 1400000;

CUST_NO TOTAL_ORDERS

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

9379 1409495.95

…

If we run this the optimizer will merge the subquery factor and the main query together into one statement. Which really makes sense here, the subquery factor isn’t really doing anything special. Here is the plan, notice the query block MAIN disappears completely and the only reference to the CUST_TOTALS block is the table alias:

SQL_ID bnzfdp1yvk7vc, child number 0

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

WITH inline_view AS ( SELECT /*+ qb_name(cust_totals)*/ CUST_NO,

SUM(TOTAL_ORDER_PRICE) AS total_orders FROM ord2 GROUP BY CUST_NO )

SELECT /*+ qb_name(main) */ * FROM inline_view where total_orders >

1400000

Plan hash value: 2809195938

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

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

| 0 | SELECT STATEMENT | | | | 69 (100)| |

|* 1 | FILTER | | | | | |

| 2 | HASH GROUP BY | | 50 | 550 | 69 (2)| 00:00:01 |

| 3 | TABLE ACCESS FULL| ORD2 | 12890 | 138K| 68 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$8092F496

3 - SEL$8092F496 / ORD2@CUST_TOTALS

Predicate Information (identified by operation id):

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

1 - filter(SUM("TOTAL_ORDER_PRICE")>1400000)

Now to use the MATERIALIZE hint and the subquery factor defined in the WITH clause stays as a separate entity.

SQL> -- materialize hint

SQL> WITH inline_view AS

2 (

3 SELECT /*+ materialize qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders

4 FROM ord2

5 GROUP BY CUST_NO

6 )

7 SELECT /*+ qb_name(main) */ *

8 FROM inline_view where total_orders > 1400000;

CUST_NO TOTAL_ORDERS

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

9379 1409495.95

…

In the plan this time we can see the both query blocks and a new one which is the materialized data from the subquery factor.

SQL_ID 65bmr36y814d3, child number 0

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

WITH inline_view AS ( SELECT /*+ materialize qb_name(cust_totals)*/

CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders FROM ord2 GROUP BY

CUST_NO ) SELECT /*+ qb_name(main) */ * FROM inline_view where

total_orders > 1400000

Plan hash value: 1306428065

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

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

| 0 | SELECT STATEMENT | | | | 72 (100)| |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6653_4B0C1F7 | | | | |

| 3 | HASH GROUP BY | | 1000 | 11000 | 69 (2)| 00:00:01 |

| 4 | TABLE ACCESS FULL | ORD2 | 12890 | 138K| 68 (0)| 00:00:01 |

|* 5 | VIEW | | 1000 | 26000 | 2 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6653_4B0C1F7 | 1000 | 11000 | 2 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - MAIN

2 - CUST_TOTALS

4 - CUST_TOTALS / ORD2@CUST_TOTALS

5 - SEL$03D1C9D1 / INLINE_VIEW@MAIN

6 - SEL$03D1C9D1 / T1@SEL$03D1C9D1

Predicate Information (identified by operation id):

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

5 - filter("TOTAL_ORDERS">1400000)

This makes it clear that if the optimizer can merge the subquery factor in to the main query it will. The materialize hint will keep the subquery factor as a separate entity. Bottom line is that if you want a subquery factor kept as a separate entity, and you notice that in the plan it’s being merged in, then use the materialize hint.

Which brings up another point. Some folks will use the NO_MERGE hint to keep the subquery factor from being merged in. This works, sort of. Here is the same query using the NO_MERGE hint.

SQL> -- no merge hint

SQL> WITH inline_view AS

2 (

3 SELECT /*+ no_merge qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders

4 FROM ord2

5 GROUP BY CUST_NO

6 )

7 SELECT /*+ qb_name(main) */ *

8 FROM inline_view where total_orders > 1400000;

CUST_NO TOTAL_ORDERS

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

9379 1409495.95

…

And now here is the plan. Notice that the subquery factor isn’t completely merged in, but it does become basically an inline view instead. This of course might be just fine and works the way you want, however it’s not a materialized version of the subquery factor, but an inline view.

SQL_ID 6mf7u56n8bad6, child number 0

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

WITH inline_view AS ( SELECT /*+ no_merge qb_name(cust_totals)*/

CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders FROM ord2 GROUP BY

CUST_NO ) SELECT /*+ qb_name(main) */ * FROM inline_view where

total_orders > 1400000

Plan hash value: 2169976290

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

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

| 0 | SELECT STATEMENT | | | | 69 (100)| |

| 1 | VIEW | | 1 | 26 | 69 (2)| 00:00:01 |

|* 2 | FILTER | | | | | |

| 3 | HASH GROUP BY | | 1 | 11 | 69 (2)| 00:00:01 |

| 4 | TABLE ACCESS FULL| ORD2 | 12890 | 138K| 68 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - CUST_TOTALS / INLINE_VIEW@MAIN

2 - CUST_TOTALS

4 - CUST_TOTALS / ORD2@CUST_TOTALS

Predicate Information (identified by operation id):

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

2 - filter(SUM("TOTAL_ORDER_PRICE")>1400000)

Monday, December 3, 2018

Stale Statistics in the cloud or not

Some folks believe that the optimizer will not use stale statistics. This is simply not true.

The stats being stale or not is only used by the stats collection job to know which tables should have stats collected on them. Below is a script you can use to see this is true. I’ll go thru the key points in the script to show what is going on. I was using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 on a windows laptop for this test. In this post I'm only showing the core information form the run for brevity. Run the script to see all the details.

At the top of the script a table is created as a copy of the good old EMPLOYEES table from the sample schema. And then collect stats on it, and run a query. You can see that the stats are being used, the estimated rows is 107 which is the number for rows in the table.

SQL> SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB;

COUNT(*)
----------

107

Here is the plan:

SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB

Plan hash value: 3396615767

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

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

| 0 | SELECT STATEMENT | | | 3 (100)| |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| STALE_TAB | 107 | 3 (0)| 00:00:01 |

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

14 rows selected.

A look into the 10053 trace for this query shows this for the base statistics. These are the stats on the table just created and used.

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: STALE_TAB Alias: STALE_TAB

#Rows: 107 SSZ: 0 LGR: 0 #Blks: 5 AvgRowLen: 69.00 NEB: 0 ChainCnt: 0.00 ScanRate: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1

#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000

Then in the script is an update that updates all rows in the table. The stats are now stale.

SQL> UPDATE STALE_TAB SET SALARY = SALARY *.15;

107 rows updated.

SQL> commit;

Commit complete.

SQL> -- show the staleness of the stats

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'STALE_TAB';

STARS STALE_STATS

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

**************** YES

1 row selected.

The script then runs a new select on the table. This is using the stale stats. If it were not using the stale stats it would have to do one of two things. Either use dynamic sampling or use the default statistics. It is not doing either of these. If it was doing dynamic sampling there would be a note about that in the plan, there isn’t. If it were using the default statistics the base number of rows for the table would be something like 3186 or 8168, which are the default number of rows in an 8K block size database, which what this database has. (Side note: the 3186 number appears to be used for a real table which has not been analyzed, and the 8168 number used for a casted table.)

Here is the plan:

SQL_ID g63r4gfua0wtq, child number 0

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

SELECT /*+ QB_NAME(MAIN) */ DEPARTMENT_ID, SUM(SALARY) FROM STALE_TAB

GROUP BY DEPARTMENT_ID

Plan hash value: 1068443980

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

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

| 0 | SELECT STATEMENT | | | | 4 (100)| |

| 1 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| STALE_TAB | 107 | 749 | 3 (0)| 00:00:01 |

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

15 rows selected.

And here are the stats from the 10053 trace of this above query, notice this time it pulled the stats on the DEPARTMENT_ID column. This is because that column is used in the group by clause. And remember this is when the stats are stale.

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: STALE_TAB Alias: STALE_TAB

#Rows: 107 SSZ: 0 LGR: 0 #Blks: 5 AvgRowLen: 69.00 NEB: 0 ChainCnt: 0.00 ScanRate: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1

#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000

Column (#11): DEPARTMENT_ID(NUMBER)

AvgLen: 3 NDV: 11 Nulls: 1 Density: 0.090909 Min: 0.000000 Max: 10.000000

Notice there is nothing saying that the stats are stale. The optimizer uses stats as long as they are there, stale or not.

Below this in the script the stats are dropped and another query is run. Now the optimizer has to do dynamic sampling since there are no stats. However the select that ran the very first time is still in the library cache and still runs without a new parse. Dropping stats will not invalidate plans that are in the cache.

SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS ('OP', 'STALE_TAB');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'STALE_TAB';

STARS STALE_STATS

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

**************** NULL

1 row selected.

. . .

SELECT /*+ QB_NAME(MAIN) */ DEPARTMENT_ID, COUNT(*) FROM STALE_TAB

GROUP BY DEPARTMENT_ID

Plan hash value: 1068443980

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

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

| 0 | SELECT STATEMENT | | | | 4 (100)| |

| 1 | HASH GROUP BY | | 107 | 1391 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| STALE_TAB | 107 | 1391 | 3 (0)| 00:00:01 |

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

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

And here is the first query being run again after the stats are dropped:

SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB

Plan hash value: 3396615767

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

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

| 0 | SELECT STATEMENT | | | 3 (100)| |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| STALE_TAB | 107 | 3 (0)| 00:00:01 |

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

14 rows selected.

* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Here is the full script if you’d like to try it yourself:

rem demo to show stale stats are used by the optimizer

rem November 2018 RVD

rem file name stale_test.sql

rem

set NULL NULL

set long 255

set lines 255

set serveroutput off

set echo on feedback on termout on heading on

COLUMN STALE_STATS FORMAT A15

-- create the test table

-- this uses the standard EMPLOYEES table as a base from the sample schema

DROP TABLE STALE_TAB PURGE;

CREATE TABLE STALE_TAB AS SELECT * FROM EMPLOYEES;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OP',TABNAME =>'STALE_TAB',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-- run a query on it and show the plan

SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB;

-- capture the SQL_ID

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

SELECT PREV_SQL_ID, PREV_CHILD_NUMBER FROM V$SESSION

WHERE AUDSID = USERENV('SESSIONID');

-- Showing the plan

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR ('&PSQLID','&PCHILDNO','TYPICAL NOTE'));

-- create a 10053 trace file from the sql

EXECUTE DBMS_SQLDIAG.DUMP_TRACE(P_SQL_ID=>'&PSQLID', P_CHILD_NUMBER=>'&PCHILDNO', -

P_COMPONENT=>'Optimizer', P_FILE_ID=>'STALE01_TEST_'||TO_CHAR(SYSDATE, 'HH24MISS'));

-- show the staleness of the stats

-- stars are there just to make it eaiser to see in the output

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'STALE_TAB';

-- update every row in the table

UPDATE STALE_TAB SET SALARY = SALARY *.15;

COMMIT;

-- show the staleness of the stats

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'STALE_TAB';

-- run another query and show the plan

-- notice dynamic sampling is not done, it's using the stale stats

SELECT /*+ QB_NAME(MAIN) */ DEPARTMENT_ID, SUM(SALARY) FROM STALE_TAB GROUP BY DEPARTMENT_ID;

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

SELECT PREV_SQL_ID, PREV_CHILD_NUMBER FROM V$SESSION

WHERE AUDSID = USERENV('SESSIONID');

-- Showing the plan

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR ('&PSQLID','&PCHILDNO','TYPICAL NOTE'));

-- create a 10053 trace file from the sql

-- look for BASE STATISTICAL INFORMATION to see the stats used

-- the table stat are stale but are still being used

EXECUTE DBMS_SQLDIAG.DUMP_TRACE(P_SQL_ID=>'&PSQLID', P_CHILD_NUMBER=>'&PCHILDNO', -

P_COMPONENT=>'Optimizer', P_FILE_ID=>'STALE02_TEST_'||TO_CHAR(SYSDATE, 'HH24MISS'));

-- run the first query on it and show the plan

-- this is using the plan that is in the library cache

SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL NOTE'));

-- delete the stats and run another the query

-- show the staleness notice they it is NULL because there are no stats

EXEC DBMS_STATS.DELETE_TABLE_STATS ('OP', 'STALE_TAB');

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'STALE_TAB';

-- run another query and show the plan

-- notice dynamic sampling is done because the stats are gone in this case

SELECT /*+ QB_NAME(MAIN) */ DEPARTMENT_ID, COUNT(*) FROM STALE_TAB GROUP BY DEPARTMENT_ID;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL NOTE'));

-- run the first query on it and show the plan

-- this is using the plan that is in the library cache

-- hence no dynamic sampling on this one

SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL NOTE'));