Data Warehouse Design Mistakes 1: Lack of Foreign Key Constraints

This post is part of a series that discusses some common issues in data warehouses.

What is the Purpose of Foreign Keys?

• In the context of relational databases, a foreign key is a column, or group of columns, on one table that uniquely identifies a row in another table.  So, a foreign key on a child record uniquely identifies a row on the parent table.
• If a foreign key constraint is enforced (which they are when the foreign key constraint is enabled, which they are by default) then you cannot insert a child for which you cannot find a parent, and you cannot delete a parent for which children exist.  This guarantees that data is, and remains, referentially integral.

A foreign key constraint can also permit the Oracle optimizer to perform certain optimisations in executing a SQL query.  

• If you are querying a child and parent table (or if you prefer a fact and dimension table) without referencing any attribute on the parent (or dimension), then the optimizer can rely on the foreign key constraint to know that the parent row will always be present and so omit the parent table from the query.  This is called 'foreign key join elimination', and I will discuss this in more detail below.

I think that documenting relationships between tables through foreign keys helps developers build sensible SQL code that follows the data model, performs better, and is easier to understand and therefore easier to maintain.

If you have foreign key constraints defined in an OLTP system, it is typical to see them enabled exactly because they enforce referential integrity.
In a data warehouse there is less emphasis on revalidating data, but saving effort though join elimination is important.  Unfortunately, it is all too common to see downstream data warehouses without foreign key constraints at all.  It seems to be very easy to find an excuse not to build them. While researching this blog I came across this blog posting that I thought had the ring of truth of about it.

"9 Reasons Why There Are No Foreign Keys in Your Database" (Piotr Kononow)

1. Performance: degrades DML performance as foreign keys are validated
2. Legacy data is not referentially integral in the first place.
3. Full Table Reload.  Should disable, reload, and then re-enable and revalidate constraints.
4. High-Level Framework doesn't create foreign keys.
5. Cross-Database relations
6. Database platform agnosticism (eg. PeopleSoft)
7. Open for Change
8. Lazy Architect
9. Table relationships are not clear/revealed.

I'll add one more unacceptable excuse this list.

• Extracting referentially integral data from an OLTP system into the data warehouse, so there is no need for more foreign keys to revalidate it there again.

Foreign Key Join Elimination

If you are querying a child and parent table, without referencing any attribute on the parent, then the optimizer can rely on the foreign key constraint to know that the parent row will always be present and so omit the parent table from the query.  

Let's start with a very simple demonstration, on the Sales History demo schema, of a query of the fact table (SALES), and three dimension tables (PRODUCTS, TIMES, CUSTOMERS).  I am referencing attribute columns on the PRODUCT and TIMES tables, but nothing on the CUSTOMERS table other than the primary key column CUST_ID in a join predicate.

Without Foreign Key Constraints

I have disabled the foreign key constraints between these tables to model the situation without foreign key constraints.

ALTER TABLE sales MODIFY CONSTRAINT sales_channel_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_product_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_promo_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk DISABLE NOVALIDATE;

This is a query for certain products (Electronics) in a single year (1999).

SELECT  p.prod_category
,  t.fiscal_year
,  COUNT(*)
FROM  sales s
, products p
,  times t
, customers c
WHERE  s.time_id = t.time_id
AND  s.prod_id = p.prod_id
AND   t.fiscal_year = 2001
AND c.cust_id = s.cust_id
AND p.prod_category = 'Electronics'
AND p.prod_category = 'Software/Other'
GROUP BY p.prod_category
,  t.fiscal_year
ORDER BY 1
/

Throughout these tests, having set STATISTICS_LEVEL=ALL in order to collect additional runtime statistics, I have will extract execution plans, as follows

select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED +ADAPTIVE PROJECTION +ALLSTATS LAST, IOSTATS'));

You can see that all 4 tables (or indexes on them) referenced in the SQL appear in the execution plan (in bold).

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |      1 |        |       |  1167 (100)|          |       |       |      1 |00:00:00.21 |    3386 |    472 |       |       |          |
|   1 |  SORT GROUP BY NOSORT             |                      |      1 |      1 |    44 |  1167   (3)| 00:00:01 |       |       |      1 |00:00:00.21 |    3386 |    472 |       |       |          |
|   2 |   NESTED LOOPS                    |                      |      1 |   2840 |   122K|  1167   (3)| 00:00:01 |       |       |   2840 |00:00:00.21 |    3386 |    472 |       |       |          |
|   3 |    VIEW                           | VW_GBF_35            |      1 |   2840 |   108K|  1167   (3)| 00:00:01 |       |       |   2840 |00:00:00.19 |     544 |    472 |       |       |          |
|   4 |     HASH GROUP BY                 |                      |      1 |   2840 |   138K|  1167   (3)| 00:00:01 |       |       |   2840 |00:00:00.19 |     544 |    472 |  1137K|  1137K| 1403K (0)|
|*  5 |      HASH JOIN                    |                      |      1 |  41362 |  2019K|  1163   (3)| 00:00:01 |       |       |  23678 |00:00:00.17 |     544 |    472 |  1695K|  1695K| 1571K (0)|
|   6 |       PART JOIN FILTER CREATE     | :BF0000              |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|*  7 |        TABLE ACCESS FULL          | TIMES                |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|*  8 |       HASH JOIN                   |                      |      1 |    165K|  6156K|  1146   (3)| 00:00:01 |       |       |  26637 |00:00:00.17 |     488 |    472 |  1572K|  1572K| 1390K (0)|
|*  9 |        VIEW                       | index$_join$_002     |      1 |     13 |   273 |     2   (0)| 00:00:01 |       |       |     13 |00:00:00.01 |       5 |      0 |       |       |          |
|* 10 |         HASH JOIN                 |                      |      1 |        |       |            |          |       |       |     13 |00:00:00.01 |       5 |      0 |  1355K|  1355K| 1376K (0)|
|* 11 |          INDEX RANGE SCAN         | PRODUCTS_PROD_CAT_IX |      1 |     13 |   273 |     1   (0)| 00:00:01 |       |       |     13 |00:00:00.01 |       1 |      0 |       |       |          |
|  12 |          INDEX FAST FULL SCAN     | PRODUCTS_PK          |      1 |     13 |   273 |     1   (0)| 00:00:01 |       |       |     72 |00:00:00.01 |       4 |      0 |       |       |          |
|  13 |        PARTITION RANGE JOIN-FILTER|                      |      1 |    918K|    14M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.13 |     482 |    472 |       |       |          |
|  14 |         TABLE ACCESS FULL         | SALES                |      5 |    918K|    14M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.13 |     482 |    472 |       |       |          |
|* 15 |    INDEX UNIQUE SCAN              | CUSTOMERS_PK         |   2840 |      1 |     5 |     0   (0)|          |       |       |   2840 |00:00:00.01 |    2842 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

With Foreign Key Constraints

I have re-enabled the foreign key constraints

ALTER TABLE sales MODIFY CONSTRAINT sales_channel_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_product_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_promo_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk enable validate;

Now the CUSTOMERS table is no longer referenced due to foreign key join elimination.  The nested loop operation (at line 2 of the previous plan) to join the CUSTOMERS table is no longer required because the query does not need anything from that dimension table.  The foreign key constraint tells the optimizer that there will always be a corresponding row for every SALES record.  Thus not joining to it does not change the result of the query.  There is one less operation requiring PGA memory, and the number of buffers required, and the optimizer cost has also dropped.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |       |  1163 (100)|          |       |       |      1 |00:00:00.87 |     544 |    472 |       |       |          |
|   1 |  SORT GROUP BY NOSORT          |                      |      1 |      1 |    45 |  1163   (3)| 00:00:01 |       |       |      1 |00:00:00.87 |     544 |    472 |       |       |          |
|*  2 |   HASH JOIN                    |                      |      1 |  41362 |  1817K|  1163   (3)| 00:00:01 |       |       |  23678 |00:00:00.85 |     544 |    472 |  1695K|  1695K| 1669K (0)|
|   3 |    PART JOIN FILTER CREATE     | :BF0000              |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | TIMES                |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|*  5 |    HASH JOIN                   |                      |      1 |    165K|  5346K|  1146   (3)| 00:00:01 |       |       |  26637 |00:00:00.79 |     488 |    472 |  1572K|  1572K| 1331K (0)|
|*  6 |     VIEW                       | index$_join$_002     |      1 |     13 |   273 |     2   (0)| 00:00:01 |       |       |     13 |00:00:00.01 |       5 |      0 |       |       |          |
|*  7 |      HASH JOIN                 |                      |      1 |        |       |            |          |       |       |     13 |00:00:00.01 |       5 |      0 |  1355K|  1355K| 1377K (0)|
|*  8 |       INDEX RANGE SCAN         | PRODUCTS_PROD_CAT_IX |      1 |     13 |   273 |     1   (0)| 00:00:01 |       |       |     13 |00:00:00.01 |       1 |      0 |       |       |          |
|   9 |       INDEX FAST FULL SCAN     | PRODUCTS_PK          |      1 |     13 |   273 |     1   (0)| 00:00:01 |       |       |     72 |00:00:00.01 |       4 |      0 |       |       |          |
|  10 |     PARTITION RANGE JOIN-FILTER|                      |      1 |    918K|    10M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.43 |     482 |    472 |       |       |          |
|  11 |      TABLE ACCESS FULL         | SALES                |      5 |    918K|    10M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.25 |     482 |    472 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…
Note
-----
   - rely constraint used for this statement

From Oracle 12.2, when a constraint has been used to remove a table from an execution plan, you get a note in the execution plan "rely constraint used for this statement".  Though it doesn't tell you which table has been eliminated, nor due to which constraint.
Admittedly, the actual run time of the query has gone up because there is more work for the optimizer to do.  However, this is an unusually small test, so it has swamped the savings in not visiting the CUSTOMERS table.  Foreign key join elimination should generally deliver a performance improvement because the query processes less data.

With Disabled Reliable Constraints

If you don't want the overhead and complexity of enforced foreign key constraints, you can still get the benefit of foreign key join elimination.  It is possible to disable the constraint from SALES to CUSTOMER so that it is not enforced, but it can still tell the optimizer that it can RELY upon the referential integrity of the data as if the constraint were enforced.

ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY DISABLE NOVALIDATE;

This behaviour has been available since constraints were introduced in Oracle 8 (c. 1998), but there has been a change in Oracle 12c.  Foreign key join elimination on disabled RELY constraints does not occur if QUERY_REWRITE_INTEGRITY is set to its default value of ENFORCED.  It must be set to either TRUSTED or STALE_TOLERATED.  However, bear in mind that setting it to STALE_TOLERATED also affects how materialized views can be used by query rewrite.

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

And now we are back to the same plan as before without the CUSTOMERS table, and with the "rely constraint used for this statement" note.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |       |  1164 (100)|          |       |       |      1 |00:00:01.55 |     544 |    472 |       |       |          |
|   1 |  SORT GROUP BY NOSORT          |                      |      1 |      1 |    45 |  1164   (3)| 00:00:01 |       |       |      1 |00:00:01.55 |     544 |    472 |       |       |          |
|*  2 |   HASH JOIN                    |                      |      1 |  81133 |  3565K|  1164   (3)| 00:00:01 |       |       |    110K|00:00:01.46 |     544 |    472 |  1476K|  1476K| 1528K (0)|
|*  3 |    VIEW                        | index$_join$_002     |      1 |     26 |   546 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |       5 |      0 |       |       |          |
|*  4 |     HASH JOIN                  |                      |      1 |        |       |            |          |       |       |     26 |00:00:00.01 |       5 |      0 |  1298K|  1298K| 1612K (0)|
|*  5 |      INDEX RANGE SCAN          | PRODUCTS_PROD_CAT_IX |      1 |     26 |   546 |     1   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |       1 |      0 |       |       |          |
|   6 |      INDEX FAST FULL SCAN      | PRODUCTS_PK          |      1 |     26 |   546 |     1   (0)| 00:00:01 |       |       |     72 |00:00:00.01 |       4 |      0 |       |       |          |
|*  7 |    HASH JOIN                   |                      |      1 |    229K|  5369K|  1160   (3)| 00:00:01 |       |       |    246K|00:00:01.00 |     538 |    472 |  1695K|  1695K| 1683K (0)|
|   8 |     PART JOIN FILTER CREATE    | :BF0000              |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|*  9 |      TABLE ACCESS FULL         | TIMES                |      1 |    364 |  4368 |    16   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      55 |      0 |       |       |          |
|  10 |     PARTITION RANGE JOIN-FILTER|                      |      1 |    918K|    10M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.47 |     482 |    472 |       |       |          |
|  11 |      TABLE ACCESS FULL         | SALES                |      5 |    918K|    10M|  1136   (2)| 00:00:01 |:BF0000|:BF0000|    296K|00:00:00.28 |     482 |    472 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
…
Note
-----
   - rely constraint used for this statement

Multi-Column Foreign Key Join Elimination 

It has always been possible to create foreign keys on multiple columns, sometimes called composite foreign keys.  From Oracle 12.2, it is possible to get join elimination on composite foreign keys. Jonathan Lewis has published a blog with demonstration scripts.

ALTER TABLE child 
add constraint child_fk_parent foreign key (id_g, id_p) 
references parent (id_g, id);

However, he has also found a case where join elimination depends on the order of tables in the from clause – (currently unpublished) bug 22228669.  The workaround is to list parents in the from clause before their children.
The need for single column keys inevitably leads to meaningless keys, often generated from a sequence.  Another of Jonathan's blogs and its comments discusses the pros and cons. 
Updated 19th November: It depends how important foreign key join elimination is to you.  How much use a system makes of this feature will depend upon how it is written.  If you think that you may need to use this feature then, at least until this bug is resolved, I would stick with single column keys in Oracle 12.2.

Summary of Good Practice

• Primary keys (with unique indexes) on all tables.
• Define foreign key constraints on all dimension columns of fact tables referencing the primary or unique keys on the dimension tables through equality joins only. 
• I think that even from Oracle 12.2 I would still avoid creating multi-column primary and foreign keys because there appears to be a bug with this feature.
• If foreign key constraints are to be enforced, then the key columns should also be indexed avoid TM locking during DML operations.
• Otherwise, in order to achieve join elimination mark the constraints as reliable:

ALTER TABLE … MODIFY CONSTRAINT … RELY NOVALIDATE DISABLE

• From Oracle 12 you must also set QUERY_REWRITE_INTEGRITY to TRUSTED or STALE_TOLERATED.

How Not to Build A(n Autonomous) Data Warehouse

My day job involves investigating and resolving performance problems, so I get to see a lot of bad stuff.  Often, these problems have their roots in poor design.  It is not surprising. but is nonetheless disappointing, that when I point this out I am told that the system is either delivered this way by the vendor, or it has already been built and it is too late to change.
In the last couple of years, I have worked on several data warehouse applications that have provided the inspiration for a new presentation that I am giving at the DOAG and UKOUG conferences this year.
The presentation and this series of related blogs have several objectives:

• Partly, it is an attempt to get some advice out in the hope that some of these poor design decisions are not made in the first place. 
• I get to have a bit of a rant about how to design a data warehouse properly, and I will feel better at the end it.
• I get to attend two really good conferences where I will learn (or be reminded of) much.

This particular blog post is an index to various topics within the presentation, that I have turned into separate blog postings.

Oracle Sales History (SH) Sample Schema

I cannot illustrate issues with actual examples from client systems, no matter how well anonymised they are. So, I have recreated some of the most egregious mistakes using the Oracle Sales History example schema, SH.  Everything I am going to show you does have an origin in the real world!  The SH sample schema is a simple and very typical star schema.  It is well designed and implemented, and represents good practice. I encourage you to install and play with it for yourself.

My examples use a single fact table (SALES), that has five dimensions around it (CUSTOMERS, CHANNELS, PRODUCTS, PROMOTIONS and TIMES), although I will only use three. COUNTRIES is a dimension on the CUSTOMERS dimension, sometimes called a 'snowflake'.

• The schema described in the Oracle documentation.
• Installation scripts are available from Github.
• It is also available by default in the Autonomous Data Warehouse Cloud but in a slightly different form.

In some of my examples, I have deliberately broken the data model in exactly the same way that I have seen it broken in real life. So, if you find yourself saying "nobody would actually do that!", let me assure you that they did!

Common Mistakes in Data Warehouse Design and Build

As each blog post is published, I will add a link to them in this section.

1. Lack of Foreign Keys
2. Effective Dated Dimensions
3. Dates that are not Dates

Other Topics

• To Index or Not: Star Transformation -v- Full Scan-Bloom Filter
• Snowflakes and Lost Skew
• Engineered System Considerations
• A Brief Look Inside Oracle's Autonomous Data Warehouse Cloud

How Not to Collect Optimizer Statistics in an Application Engine Program

I was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.

ORA-06533: Subscript beyond count 
ORA-06512: at "SYS.DBMS_STATS"…

It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.
This blog is not about the bug, but how to manage the workaround.

DO NOT TRY THIS AT HOME!

I think that there are a number of problems with this approach

1. Using a fixed sample size rather than AUTO_SAMPLE_SIZE should only be considered as a temporary workaround. The new hash-based number-of-distinct-values (NDV) algorithm in Oracle 12c only works with AUTO_SAMPLE_SIZE, and it produces more accurate statistics and runs faster because saves a large sort operation. Coding a fixed sample size into an Application Engine requires a managed change to be made, tested and released into production, and then when the underlying problem is resolved the customisation needs to be removed by the same managed process. 
2. DBMS_STATS.GATHER_TABLE_STATS implies a commit. That can lead to problems that PeopleSoft avoids by only calling statistics via the %UpdateStats macro and controlling when that macro does and does not execute. 

Committing and Restart Checkpointing in Application Engine

If a restartable Application Engine program fails it rolls back to the last commit point and it can then be restarted from that point. Committing is managed by Application Engine at section and step levels where the program state record is updated accordingly. If an error occurs in a step after the implicit commit in DBMS_STATS, it can result in the data in the application tables being different to where the state record indicates the program can be restarted. The program may not restart, or it could conceivably execute but produce erroneous results.
Committing inside a do while loop, including any other Application Engine program called from inside the loop is suppressed at Application Engine section/step level and therefore the execution of %UpdateStats macro is also suppressed. Otherwise, you could get rogue ORA-01555 Snapshot Too Old errors. Suppression of %UpdateStats is reported in the Application Engine step trace.

-- 19:07:37.199 .(AEMINITEST.MAIN.LOOP) (Do Select)
%Select(recname) SELECT recname FROM psrecdefn WHERE recname = 'JOB'
/
-- Buffers:
--   1) JOB
-- 19:07:37.204 Iteration 1 of .(AEMINITEST.MAIN.LOOP) (Do Select) loop

-- 19:07:37.208 .(AEMINITEST.MAIN.LOOP) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:37.211 UpdateStats ignored - COMMIT required

-- 19:07:37.212 .(AEMINITEST.MAIN.LOOP) (Do Fetch)

Even a previously uncommitted SQL step can lead to %UpdateStats being suppressed.

-- 19:07:35.205 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0

-- 19:07:35.213 .(AEMINITEST.MAIN.Step02) (PeopleCode)

-- 19:07:35.220 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:35.227 UpdateStats ignored - COMMIT required

If you code DBMS_STATS.GATHER_TABLE_STATS explicitly, Application Engine will not recognise the step as having committed. In the following example, you can see the %UpdateStats on the last step has been suppressed because it Application Engine does not recognise that the update in the first step has been committed by the call to DBMS_STATS.

-- 19:12:06.690 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0
-- 19:12:06.696 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:12:06.700 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1

-- 19:12:09.871 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:12:09.877 UpdateStats ignored - COMMIT required
/

Perhaps, the safest form of this workaround would be to have the step with DBMS_STATS and the immediately preceding step explicitly commit as in the following example.  I have also made the program restartable.  Now restart data is checkpointed, and the %UpdateStats macro executes at step US1.

Restart Data CheckPointed
/
COMMIT
/

-- 19:20:24.792 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1
/ 
/
Restart Data CheckPointed
/
COMMIT
/

-- 19:20:29.153 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- Row(s) affected: 1
/

However, you have to consider the state the application data after an error, whether you wish to restart or cancel the Application Engine because you can no longer rollback.

Doing the Right Thing

I recommend that:

• You should only ever collect stats in Application Engine with the %UpdateStats macro that in turn executes the command in the DDL model.
• From Oracle 11g both PeopleSoft statistics gathering DDL models should be the same and should ultimately call DBMS_STATS without any parameters other than the table name. The default value of ESTIMATE_PERCENT is AUTO_SAMPLE_SIZE.

There are two DDL models in PeopleSoft because %UpdateStats can be invoked with a second parameter to collect the statistics HIGH or LOW. This dates back to Oracle's ANALYZE command that could either compute or estimate statistics (and other database platforms had similar options). Collecting optimizer statistics with ANALYZE has been deprecated for many years, but the command still has other valid uses.  It was superceded by DBMS_STATS in Oracle 8i (released in 1998).
Automatic sample size was introduced in Oracle 9i.  In Oracle 9i and 10g, it was usual to use automatic sample size in the high statistics gathering model and a small fixed sample size in the low model for use on very large tables.  The LOW parameter was specified on %Updatestats in Application Engine programs as necessary.
This approach became redundant from Oracle 11g with the introduction of table preferences. If you need to collect statistics with a specific rather than the automatic sample size or specify any other parameters, then a table preference should be created. Preferences apply wherever statistics are gathered on that table and not overridden in the call of DBMS_STATS., including schema and database-wide operations such as the maintenance window.  If there are multiple places where statistics are collected on a table, a preference assures that the statistics will always be collected will be consistently.
From Oracle 12c, as the new NDV algorithm only works with AUTO_SAMPLE_SIZE, you should always use the default unless you have an overarching reason to the contrary, and then you should use a table preference. This approach does not require any application code change because the preference is an attribute of a table in the database.
I recommend using GFCPSSTATS package, calling it from the DDL model (see previous blog Managing Cost-Based Optimizer Statistics for PeopleSoft). The package also includes a mechanism to specify table preferences in metadata, so that they are automatically instantiated when a table is created or altered by Application Designer, and are applied to every instance of a temporary record.

gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);

Alternatively, just call DBMS_STATS with no additional parameters (other than FORCE, in case you lock statistics on temporary tables) but then you must manage table preferences manually.

DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);

Further Reading

• Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?
• Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 11g
• Oracle Optimizer Blog: Understanding DBMS_STATS.SET_*_PREFS procedures  
• PeopleSoft DBA Blog: Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48 
• PeopleBooks PT8.56: Development Tools -> Application Engine -> Using Application Engine MetaSQL -> %UpdateStats

Parallel Execution of PL/SQL

A recent experience with text searching showed up some limitations on parallel execution of PL/SQL functions, and how to work around them.

Text Searching can be CPU Intensive?

It started with a requirement to find names that roughly matched a search string. The roughness of the match was determined by calculating the Levenshtein distance between each customer name and the search string. Informally, the Levenshtein distance is a mathematical function that calculates the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other. Oracle has implemented it as a function in a delivered package UTL_MATCH.EDIT_DISTANCE().  This function isn't directly relevant to the rest of the discussion about parallelism, except that it is an example of CPU intensive processing that doesn't alter the database where parallel execution may be a legitimate tactic for improving performance.
The examples in this article use the SH schema in the Oracle sample schemas (available on Github).

The following query searches case-insensitively for customer names within a Levenshtein distance of 3 from 'Stephen'. It finds 'Steven' and 'Staphany'.

set autotrace on timi on pages 99 lines 200
with x as (
select c.cust_first_name
,      utl_match.edit_distance(upper(cust_first_name),'STEPHEN') edit_distance
from customers c
)
select * from x
where edit_distance <= 3
/

CUST_FIRST_NAME      EDIT_DISTANCE
-------------------- -------------
Staphany                         3
Steven                           2
Steven                           2

However, to do so Oracle had to full scan the table and execute the function for every row in the table.

Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  2775 | 19425 |   429   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |  2775 | 19425 |   429   (4)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("UTL_MATCH"."EDIT_DISTANCE"(UPPER("CUST_FIRST_NAME"),'STEPHEN')<=3)

Oracle implemented Levenshtein as a C function that is then called from PL/SQL, so it just consumes CPU and doesn't do anything else to the database. You can start to see why the user complained that the query with UTL_MATCH was slow.
However, the first question is how much time do we spend on the full scan and how much time do we spend executing this function?

Follow the Time with Instrumentation 

For test purposes, I am going to build my own packaged functions with session instrumentation. Then I can use Active Session History (ASH) to work out where the time went.
NB: ASH requires a licence for the Diagnostics Pack

• One function levenshtein() calls to UTL_MATCH.EDIT_DISTANCE().
• The other dolittle() is a control function that does nothing except instrumentation. It is used to measure the intrusion effect of the instrumentation.

CREATE OR REPLACE package dmk AS
function levenshtein(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER;
function dolittle(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER;
END;
/

CREATE OR REPLACE package body dmk AS
function levenshtein(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER IS
  l_distance INTEGER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
BEGIN
  dbms_application_info.read_module(l_module,l_action); /*save current module/action*/
  dbms_application_info.set_action('levenshtein()'); /*set action for function*/
  l_distance := utl_match.edit_distance(UPPER(p1),UPPER(p2));
  dbms_application_info.set_action(l_action); /*restore previous action*/
  RETURN l_distance;
END levenshtein;

function dolittle(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER IS
  l_distance INTEGER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
BEGIN
  dbms_application_info.read_module(l_module,l_action);
  dbms_application_info.set_action('dolittle()');
  l_distance := 1;
  dbms_application_info.set_action(l_action);
  RETURN l_distance;
END dolittle;
END dmk;
/

Now, I will run a test query that executes UTL_MATCH for each of the 55500 rows in the CUSTOMERS table and executes that all 10 times inside a PL/SQL block.

set timi on autotrace on lines 500 serveroutput on
DECLARE
  l_counter INTEGER := 0;
BEGIN
  dbms_application_info.set_module('DMK LEVENSHTEIN TEST',null);
  FOR j IN 1..10 LOOP
    FOR i IN (
      select sum(dmk.levenshtein(cust_first_name,'STEPHEN')) a
      ,      sum(dmk.dolittle(cust_first_name,'STEPHEN')) b
      from   customers c
    ) LOOP
      l_counter := l_counter+i.b;
    END LOOP;
  END LOOP;
  dbms_application_info.set_module(null,null);
  dbms_output.put_line('Executions: '||l_counter);
END;
/
Executions: 555000

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.08

We can see that the Levenshtein function was executed 555000 times.  Now I will query the ASH for this test and group it by ACTION.

set timi on autotrace off lines 500
column module format a25
column action format a25
select module, action, sql_id, sum(1) ash_Secs
from v$active_Session_History
where module = 'DMK LEVENSHTEIN TEST'
and sample_time >= SYSDATE-1/1440
group by module, action, sql_id
/

MODULE                    ACTION                    SQL_ID          ASH_SECS
------------------------- ------------------------- ------------- ----------
DMK LEVENSHTEIN TEST      dolittle()                7gp0w6qdvxrd2          2
DMK LEVENSHTEIN TEST      levenshtein()             7gp0w6qdvxrd2          3
DMK LEVENSHTEIN TEST                                7gp0w6qdvxrd2         20

The runtime of the Levenshtein function took 3 seconds, and the function that does nothing except instrumentation is 2 seconds, so the overhead of UTL_MATCH is only about 1 second, and there are 20 seconds in the SQL. In this test, the overhead of Levenshtein is low, but it would still be worth doing the full scan in parallel.

I Can't Get No Parallelism 

But there is a problem! I specified parallelism with a hint, but I don't get a parallel plan.

select /*+PARALLEL(C 4)*/ avg(utl_match.edit_distance(UPPER(cust_first_name),'STEPHEN'))
from customers c
/

Plan hash value: 1978308596
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |     7 |   115   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |           |     1 |     7 |            |          |
|   2 |   SORT AGGREGATE    |           |     1 |     7 |            |          |
|   3 |    TABLE ACCESS FULL| CUSTOMERS | 55500 |   379K|   115   (0)| 00:00:01 |
---------------------------------------------------------------------------------

However, if I use a simple SQL function, then I do get parallelism.

select /*+PARALLEL(C 4)*/ max(cust_first_name)
from customers c
/

Plan hash value: 1221513835
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     7 |   115   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |           |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |           |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |           |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |           | 55500 |   379K|   115   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| CUSTOMERS | 55500 |   379K|   115   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of table property

So, UTL_MATCH.EDIT_FUNCTION must be preventing parallelism in some way. There is a closed Oracle bug 169587070 that details exactly this problem with UTL_MATCH.

PARALLEL_ENABLE and PRAGMA RESTRICT_REFERENCES 

This is also documented default behaviour in PL/SQL. Database VLDB and Partitioning Guide: About Parallel Execution of Functions, Functions in Parallel Queries:
"A user-written function may be executed in parallel in any of the following cases: 

• If it has been declared with the PARALLEL_ENABLE keyword
• If it is declared in a package or type and has a PRAGMA RESTRICT_REFERENCES clause that indicates all of WNDS, RNPS, and WNPS 
• If it is declared with CREATE FUNCTION and the system can analyze the body of the PL/SQL code and determine that the code neither writes to the database nor reads or modifies package variables" 

Oracle only wraps the bodies of their delivered package, but not the package specifications. So, you can see for yourself that the delivered UTL_MATCH package does not contain these specifications.

Workarounds 

There are various workarounds for this.

• I could add the PARALLEL_ENABLE declarations to the functions in the package specification of the delivered UTL_MATCH package. Although it does work, I would certainly not be happy to alter any delivered Oracle package in any serious database, without approval from Oracle support.

• Or, I could add the RESTRICT_REFERENCES pragma to the package specification. Again, although this works, it involves altering a delivered package.

• However, I can wrap the delivered package in my own packaged function with either PARALLEL_ENABLE (my personal preferrance).

CREATE OR REPLACE package dmk AS
FUNCTION levenshtein(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER PARALLEL_ENABLE;
FUNCTION dolittle(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER PARALLEL_ENABLE;
END;
/

• Or you can use RESTRICT_REFERENCES in the package specification, but you must include the TRUST pragma to over-ride the lack of a pragma definition in the called packaged function.

CREATE OR REPLACE package dmk AS
FUNCTION levenshtein(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER;
PRAGMA RESTRICT_REFERENCES (levenshtein,WNDS,RNDS,WNPS,RNPS,TRUST);
FUNCTION dolittle(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER;
PRAGMA RESTRICT_REFERENCES (do_little,WNDS,RNDS,WNPS,RNPS,TRUST);
END;
/

Now, I get parallel execution of the unaltered delivered UTL_MATCH.EDIT_DISTANCE() function.

select /*+PARALLEL(C 4) FULL(C)*/ sum(dmk.levenshtein(cust_first_name,'STEPHEN')) a
,      sum(dmk.dolittle(cust_first_name,'STEPHEN')) b
from   customers c
/

Plan hash value: 1221513835
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     7 |   115   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |           |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |           |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |           |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |           | 55500 |   379K|   115   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| CUSTOMERS | 55500 |   379K|   115   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of table property

Conclusion: Parallel PL/SQL 

I can now repeat the earlier test, but with a parallel hint, the runtime goes down from 25 to 9 seconds, although about the same amount of database time is recorded by ASH. So parallelism can improve the performance for the end user, but will not reduce the total CPU overhead. If anything is likely to increase overall CPU time.

set timi on lines 500
DECLARE
  l_counter INTEGER := 0;
BEGIN
  dbms_application_info.set_module('DMK LEVENSHTEIN TEST',null);
  FOR j IN 1..10 LOOP
    FOR i IN (
      select /*+PARALLEL(C 4)*/
             sum(dmk.levenshtein(cust_first_name,'STEPHEN')) a
      ,      sum(dmk.dolittle(cust_first_name,'STEPHEN')) b
      from   customers c
    ) LOOP
      l_counter := l_counter+i.b;
    END LOOP;
  END LOOP;
  dbms_application_info.set_module(null,null);
  dbms_output.put_line('Executions: '||l_counter);
END;
/
Executions: 555000

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.34

set timi on autotrace off lines 500
column module format a32
column action format a32
select module, action, sql_id, sum(1) ash_Secs
from v$active_Session_History
where module = 'DMK LEVENSHTEIN TEST'
and sample_time >= SYSDATE-1/1440
group by module, action, sql_id
/

MODULE                           ACTION                           SQL_ID          ASH_SECS
-------------------------------- -------------------------------- ------------- ----------
DMK LEVENSHTEIN TEST                                              3391gqpzu5g2k         21
DMK LEVENSHTEIN TEST             levenshtein()                    3391gqpzu5g2k          5
DMK LEVENSHTEIN TEST             dolittle()                       3391gqpzu5g2k          4

NB: I have not been able to get parallelism to work in a PL/SQL function defined in a WITH clause because you cannot specify PARALLEL_ENABLE, and a pragma can only be specified in a package specification.

WITH
  FUNCTION levenshtein(p1 VARCHAR2, p2 VARCHAR2) RETURN INTEGER PARALLEL_ENABLE IS
  BEGIN
    RETURN utl_match.edit_distance(UPPER(p1),UPPER(p2));
  END;
select /*+PARALLEL(C 4)*/
       sum(levenshtein(cust_first_name,'STEPHEN')) a
from   customers c
/
ORA-06553: PLS-712: illegal option for subprogram LEVENSHTEIN

Managing Cost-Based Optimizer Statistics for PeopleSoft

I gave this presentation to UKOUG PeopleSoft Roadshow 2018

PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.

I have previously written and blogged on this subject.  This presentation focuses exclusively on the Oracle database and draws together the various concepts into a single consistent picture.  It makes clear recommendations for Oracle 12c that will help you work with the cost-based optimizer, rather than continually fight against it.

It looks at collecting statistics for permanent and temporary working storage tables and considers some other factors that can affect optimizer statistics.

This presentation also discusses PSCBO_STATS, that is going to be shipped with PeopleTools, and compares and contrasts it with GFCPSSTATS11.