Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48

Last May, I wrote about Changes to DDL Models in PeopleTools 8.48. DDL models 4 and 5 are used by the %UpdateStats PeopleCode macro. Previously, PeopleSoft had delivered these models with ANALYZE TABLE commands. Now, in line with long standing Oracle RDBMS guidance, they call DBMS_STATS (see $PS_HOME/script/ddlora.dms). I certainly welcome that change.

However, I have recently noticed that the DDL models have been swapped over. I have commented on this elsewhere, but I felt it needed a separate posting.

I am certain that this is a mistake, but it is at least one that can be easily corrected by PeopleSoft customers. The problem is not obvious because the full compute DDL model actually only uses a 1% sample, and the automatic sample size calculated by Oracle is usually within an order of magnitude of this value, though it is often greater than 1%.

So let me be absolutely clear here that:

Model 4 is used by %UpdateStats([table],LOW);

Model 5 is used bt %UpdateStats([table],HIGH);

This can be easily verified. I wrote a simple Application Engine that collected statistics on two tables via the %UpdateStats macro. I implemented the delivery DDL models as specified in ddlora.dms. This is the Application Engine trace file.

...
-- 17.35.40 .(DMK.MAIN.Step01) (SQL)
RECSTATS PSLOCK LOW
/
-- Row(s) affected: 1
/
/
Restart Data CheckPointed
/
COMMIT
/
...
-- 17.35.41 .(DMK.MAIN.Step02) (SQL)
RECSTATS PSVERSION HIGH
/
-- Row(s) affected: 1
/
/
Restart Data CheckPointed
/
COMMIT
/
...

Unfortunately the %UpdateStats macro is not fully traced in the PeopleTools trace either (it also reports the same information as the AE Trace file). The only way I know to find out what is being submitted to the database is to enable Oracle SQL Trace, and look in the trace file.

...
=====================
PARSING IN CURSOR #2 len=155 dep=0 uid=39 oct=47 lid=39 tim=259825298401 hv=1993983003 ad='6c1382b4'
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=> 'PSLOCK', estimate_percent=> 1, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE); END;
END OF STMT
PARSE #2:c=31250,e=386098,p=25,cr=105,cu=0,mis=1,r=0,dep=0,og=1,tim=259825298396
...
=====================
PARSING IN CURSOR #2 len=193 dep=0 uid=39 oct=47 lid=39 tim=259826057420 hv=2784637395 ad='6c138098'
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=>'PSVERSION', estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=> TRUE); END;
END OF STMT
PARSE #2:c=0,e=2195,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=259826057415
...

So, you can see that RECSTATS HIGH corresponds to the default estimate, but RECSTATS LOW corresponds to the 1% sample size.
If you look in ddlora.dms you can see that model 4 is the 1% sample and model 5 is the default estimate.

...
4,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=> , method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE);
//
5,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=> TRUE);
//
...

So you can also see how the DDL models have been confused, which as I have commented I consider to be a typographical error, and that should really have been 100% indicating a full compute. I think it would make sense to change the ddlora.dms script to read as follows:

......
INSERT INTO PSDDLMODEL (
STATEMENT_TYPE,
PLATFORMID,
SIZING_SET,
PARMCOUNT,
MODEL_STATEMENT)
VALUES(
:1,
:2,
:3,
:4,
:5)
\
$DATATYPES NUMERIC,NUMERIC,NUMERIC,NUMERIC,CHARACTER
...
4,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=>dbms_stats.auto_sample_size, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=> TRUE);
//
5,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=>, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=> TRUE);
//
/
...

Or, if you use the wrapper SQL that I proposed in %UpdateStats() -v- Optimizer Dynamic Sampling

...
4,2,0,0,$long
wrapper.ps_stats(p_ownname=> [DBNAME], p_tabname=> [TBNAME], p_estimate_percent=> 0);
//
5,2,0,0,$long
wrapper.ps_stats(p_ownname=> [DBNAME], p_tabname=> [TBNAME], p_estimate_percent=> 1);
//
...

I have kept the 1% sample size for the compute model, but there is no reason why you could not choose a larger value. If you wanted %UpdateStats([table],HIGH) to continue to mean a full compute, then the value really should be 100%. It is really a matter of how long you want to spend analysing statistics on tables during batch programs. However, a higher sample size will not necessarily produce statistics that will lead to a better execution plan!

Exactly how Oracle calculates the default sample size is not published. Values in the range 0.5% to 10% are typical. In a perverse sense, a 1% sample size will usually be smaller sample than the Oracle default sample size, so in the delivered DDL models, %UpdateStats([table],HIGH) will usually use a larger sample size that %UpdateStats([table],LOW)! However, I simply cannot believe that this is what was intended.

You can see the value that Oracle calculates for auto_sample_size by tracing the dbms_stats call, and looking for the sample clause in the recursive SQL. Eg.

... from "SYSADM"."PSPCMNAME" sample ( .9490170771) t

Changes in Calculation of Predicate Selectivity in Oracle 10g

Stuff changes! The very first entry I wrote for this blog back in April 2006 (http://blog.go-faster.co.uk/2006/04/currentdatein-metasql-prevents-oracle.html) discussed how the Oracle 9i optimizer calculated the selectivity of expressions, such as the expansion of the %CurrentDateIn macro in PeopleSoft. Recently, I had cause to repeat the test script in this entry on Oracle 10gR2 (10.2.0.1.0 on Windows, 10.2.0.3.0 on HP-UX).
For the simple TRUNC(SYSDATE) predicate, Oracle correctly calculated the cardinality as 4. So no change here since Oracle 9i.

EXPLAIN PLAN FOR
SELECT * FROM t1 WHERE c > TRUNC(SYSDATE);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |  4096 |    41   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |  4096 |    41   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C">TRUNC(SYSDATE@!))

But when I use the expansion of the PeopleSoft %CurrentDateIn macro, Oracle 10g now also correctly calculates the cardinality as 4. Oracle 9i didn't calculate this, and instead used a hard-coded assumption of 5% selectivity (50 rows in this example).

EXPLAIN PLAN FOR
SELECT * FROM t1 WHERE c > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |  4096 |    41   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |  4096 |    41   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C">TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))

I have tried other more complex expressions, but it would appear that, where possible, Oracle will calculate the value of the expression and then use that result to calculate the selectivity of that value.
This statement will generate an error when it is executed because the divisor is zero. However, if I just produce an execution plan, Oracle cannot calculate the selectivity, and so has gone back to the 5% assumption.

EXPLAIN PLAN FOR
SELECT * FROM t1 WHERE c > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 | 51200 |    41   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    50 | 51200 |    41   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C">SYSDATE@!+1/0)

While it is wonderful that Oracle optimizer has got a bit cleverer in calculating the cardinality of predicates, this is going to cause execution plans to change on upgrading to 10g. Research into this is on-going, but I am particularly concerned by the effect on the costing of effective-dated sub-queries.

Advantages of Row Source Aliases inside Views

Here is a simple idea that could make it easier to read the execution plan of queries that have multiple references to the same table, possibly via views: make the row source aliases sufficiently distinctive that they would identify the view. Perhaps even have the alias include the name of view.
This is not exclusive to PeopleSoft, except that PeopleSoft uses views very heavily.

First, here is a very simple test example. I'll create a table and a view on the same table.

CREATE TABLE t
(a NUMBER NOT NULL CONSTRAINT t_pk PRIMARY KEY
,b NUMBER NOT NULL);

INSERT INTO t
SELECT rownum, 2*rownum
FROM   dba_objects
WHERE  rownum <= 100; 

CREATE OR REPLACE VIEW v AS 
SELECT v.a, v.b FROM t v 
WHERE  v.a <= 42;

Now let's look at an execution plan of a query that joins the views.

EXPLAIN PLAN FOR
SELECT t.a, v.b
FROM   t, v
WHERE  t.a = v.b
AND    t.b >= 24;

SELECT * FROM TABLE(dbms_xplan.display);

From just the execution plan, it is impossible to tell which INDEX SCAN is from which view.

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    42 |   420 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |      |    42 |   420 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    42 |   210 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |    42 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |     5 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

But the predicate information helps us. The nested loop is driven from the reference to T in the view V to the table T.

Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("V"."A"<=42)
  4 - filter("T"."B">=24)
  5 - access("T"."A"="V"."B")

The row source alias of the object appears in the predicate, but only if it is specified on that column in either the select list of the criteria. But now we can determine which operation relates to which reference.

Here is part of a simple SQL statement generated by nVIsion.

SELECT ...
FROM ps_pr_jrnl_hdr_vw a, ps_pr_jrnl_ln_vw b, 
     ps_xx_rt_rate_qvw c, ps_xx_rt_rate_q_vw d …;

Both PS_XX_RT_RATE_QVW and PS_XX_RT_RATE_Q_VW are views on PS_RT_RATE_TBL. We can see from the execution plan that Oracle has chosen to use one index in one view and another index in another. But which in which?

----------------------------------------------------------------------------------
| Id  | Operation                      |  Name           | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |   160 |   549 |
|   1 |  SORT GROUP BY                 |                 |     1 |   160 |   549 |
|   2 |   NESTED LOOPS                 |                 |     1 |   160 |   547 |
|   3 |    NESTED LOOPS                |                 |     1 |   132 |   545 |
|*  4 |     HASH JOIN                  |                 |     1 |    66 |   541 |
|*  5 |      INDEX RANGE SCAN          | PSJJRNL_HEADER  |  4066 |   150K|    87 |
|*  6 |      INDEX FAST FULL SCAN      | PSFRT_RATE_TBL  |     1 |    28 |   452 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| PS_JRNL_LN      |     1 |    66 |     4 |
|*  8 |      INDEX RANGE SCAN          | PSFJRNL_LN      |     1 |       |     3 |
|*  9 |    INDEX RANGE SCAN            | PSERT_RATE_TBL  |     1 |    28 |     2 |
----------------------------------------------------------------------------------

But I have used the name of the query as the row source alias for the table PS_RT_RATE_TBL in these views, and that alias now appears in the Predicate Information.

Predicate Information (identified by operation id):
---------------------------------------------------
…
  6 - filter(TO_NUMBER(TO_CHAR("XX_RT_RATE_QVW"."EFFDT",'YYYY'))=2007 AND ...
…
  9 - access("JL"."CURRENCY_CD"="XX_RT_RATE_Q_VW"."FROM_CUR" AND ...

It is now very obvious which view in the FROM clause is responsible for which access of the rate table at operations 6 and 9 in this plan.

TM locking: Checking for Missing Indexes on Foreign Key Constraints

Recently, I was working on a packaged application purchased from a third-party vendor. It is one of those platform agnostic systems that started life on Microsoft SQL Server, and has been ported to Oracle. I spend a lot of my time working with PeopleSoft, so I had a certain sense of déjà vu. However, this application uses referential integrity.

The application was upgraded, and simultaneously Oracle was upgraded to 10g and then exhibited TM contention. It had probably been suffering from TM contention while running on Oracle 9i, but we hadn't realised because Oracle9i only reports 'enqueue'.

From 10g, there are no less that 208 different enqueue wait events, that show the type of lock that the process is waiting for, and sometimes additional information. In my case it was event 175. Events can be listed from v$event_name.

SELECT event#, name FROM v$event_name
WHERE UPPER(name) LIKE 'ENQ: TM%'
/
EVENT#     NAME
---------- --------------------
175 enq: TM - contention

With a little help from my friends I came to realise that the cause of this contention was that the system had foreign key constraints on columns that were not indexed. Having found one example of this, I realised that I needed a way to check the entire data model. The result was the following SQL and PL/SQL script.

REM fk_index_check.sql
REM 19.10.2007

Uncommenting the following section will produce a test case that should build two indexes.

/*--------------------------------------------------------------
ALTER TABLE EMP_TAB DROP CONSTRAINT MGR_FKEY;
ALTER TABLE EMP_TAB DROP CONSTRAINT DEPT_FKEY;
DROP TABLE Emp_tab;
DROP TABLE DEPT_TAB;

CREATE TABLE Dept_tab (
setid   NUMBER(3),
deptno  NUMBER(3),
dname   VARCHAR2(15),
loc     VARCHAR2(15)
--CONSTRAINT dname_ukey UNIQUE (Dname, Loc),
--CONSTRAINT loc_check1
--CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
,CONSTRAINT Dept_pkey PRIMARY KEY (setid,deptno)
)
/
CREATE TABLE Emp_tab (
empno    NUMBER(5) CONSTRAINT emp_pkey PRIMARY KEY,
ename    VARCHAR2(15) NOT NULL,
job      VARCHAR2(10),
mgr      NUMBER(5) CONSTRAINT mgr_fkey REFERENCES emp_tab,
hiredate DATE,
sal      NUMBER(7,2),
comm     NUMBER(5,2),
setid    NUMBER(3),
deptno   NUMBER(3) NOT NULL,
CONSTRAINT dept_fkey FOREIGN KEY (setid,deptno)
REFERENCES dept_tab (setid,deptno) ON DELETE CASCADE
)
/
/*------------------------------------------------------------*/
set serveroutput on buffer 1000000000

GFC_FK_INDEX_CHECK is a working storage script that is to hold results of the tests on each foreign key.

DROP TABLE gfc_fk_index_check
/
CREATE TABLE gfc_fk_index_check
(owner             VARCHAR2(30) NOT NULL
,table_name        VARCHAR2(30) NOT NULL
,constraint_name   VARCHAR2(30) NOT NULL
,r_owner           VARCHAR2(30) NOT NULL
,r_table_name      VARCHAR2(30) NOT NULL
,r_constraint_name VARCHAR2(30) NOT NULL
,i_index_owner     VARCHAR2(30)
,i_index_name      VARCHAR2(30)
,i_status          VARCHAR2(30) DEFAULT 'UNKNOWN'
,i_column_list     VARCHAR2(300)
,CONSTRAINT gfc_fk_index_check_pk
PRIMARY KEY(table_name, constraint_name)
)
/
TRUNCATE TABLE gfc_fk_index_check
/

First the script populates the working storage table with all the referential integrity constraints that reference a primary key constraint.

INSERT INTO gfc_fk_index_check
(owner, table_name, constraint_name
,r_owner, r_constraint_name, r_table_name)
SELECT c.owner, c.table_name, c.constraint_name
,      c.r_owner, c.r_constraint_name
,      r.table_name r_table_name
FROM   all_constraints c
,      all_constraints r
WHERE  c.constraint_Type = 'R'
AND    r.owner = c.r_owner
AND    r.constraint_name = c.r_constraint_name
AND    r.constraint_Type = 'P'
AND    r.owner = user
/

This PL/SQL routine checks each foreign key constraint in the table for each constraint it looks up the referring columns in all_cons_columns and builds a dynamic query that SELECTs the owner and name of an index with the same columns in the same position. The name of that index and the column list is stored on the working storage table. Depending upon how many rows that query returns, a status string is written to the table: No Index/Index Found/Multiple Indexes

DECLARE
l_counter     NUMBER;
l_column_list VARCHAR2(200);
l_sql1        VARCHAR2(4000);
l_sql2        VARCHAR2(4000);
l_tmp1        VARCHAR2(20);
l_tmp2        VARCHAR2(20);
l_alias       VARCHAR2(3);
l_oldalias    VARCHAR2(3);
l_index_owner VARCHAR2(30);
l_index_name  VARCHAR2(30);
l_status      VARCHAR2(30);
BEGIN
FOR a IN (SELECT * FROM gfc_fk_index_check) LOOP
 l_counter := 0;
 l_column_list := '';
 l_sql1 := 'SELECT i1.index_owner, i1.index_name';
 l_sql2 := '';
 FOR b IN (SELECT *
        FROM  all_cons_columns c
        WHERE c.owner = a.owner
        AND   c.constraint_name = a.constraint_name
        AND   c.table_name = a.table_name
        ORDER BY position) LOOP
  l_counter := l_counter + 1;
  l_oldalias := l_alias;
  l_alias := ' i'||TO_CHAR(l_counter);
  IF l_counter > 1 THEN
   l_sql1 := l_sql1||', '; 
   l_sql2 := l_sql2
         ||' AND '||l_oldalias||'.index_owner='
                  ||l_alias   ||'.index_owner'
         ||' AND '||l_oldalias||'.index_name='
                  ||l_alias   ||'.index_name'
         ||' AND ';
   l_column_list := l_column_list||',';
  ELSE
   l_sql1 := l_sql1||' FROM ';
   l_sql2 := l_sql2||' WHERE';
  END IF;
  l_sql1 := l_sql1||'all_ind_columns'||l_alias;
  l_sql2 := l_sql2
            ||l_alias||'.TABLE_OWNER='''||b.owner||''''
   ||' AND '||l_alias||'.TABLE_NAME='''||b.table_name||''''
   ||' AND '||l_alias||'.COLUMN_NAME='''||b.column_name||''''
   ||' AND '||l_alias||'.COLUMN_POSITION='''||b.position||'''';
  l_column_list := l_column_list||b.column_name;
 END LOOP;
--   dbms_output.put_line(l_sql1);
--   dbms_output.put_line(l_sql2);
--   dbms_output.put_line(l_column_list);
 l_status := a.i_status;
 l_index_owner := '';
 l_index_name := '';
 BEGIN
  EXECUTE IMMEDIATE l_sql1||l_sql2
               INTO l_index_owner, l_index_name;
  l_status := 'Index Found';
 EXCEPTION
  WHEN NO_DATA_FOUND THEN l_status := 'No Index';
  WHEN TOO_MANY_ROWS THEN l_status := 'Multiple Indexes';
 END;
 UPDATE gfc_fk_index_check
 SET    i_status = l_status
 ,      i_index_owner = l_index_owner
 ,      i_index_name  = l_index_name
 ,      i_column_list = l_column_list
 WHERE  owner = a.owner
 AND    table_name = a.table_name
 AND    constraint_name = a.constraint_name;
END LOOP;
COMMIT;
END;
/

This query produces a simple report on each foreign key constraint.

set lines 90 head on feedback on echo on
column owner             format a20
column table_name        format a30
column constraint_name   format a30
column r_owner           format a20
column r_constraint_name format a30
column r_table_name      format a30
column i_index_owner     format a20
column i_index_name      format a30
column i_status          format a30
column i_column_list     format a80
spool fk_index_check
SELECT g.owner, g.table_name, g.constraint_name
,      g.r_owner, g.r_table_name, g.r_constraint_name
,      g.i_index_owner, g.i_index_name, g.i_status
,      g.i_column_list
FROM   gfc_fk_index_check g
/
spool off

This query is similar to the last, but it produces a report of just largest tables that lack indexes on FK constraints. It show tables more than 10000 rows (according to the CBO statistics), or at least the top 20. These are likely to be most severe offenders.

spool fk_index_by_size
SELECT * from (
SELECT g.owner, g.table_name, g.constraint_name
,      g.r_owner, g.r_table_name, g.r_constraint_name
,      g.i_index_owner, g.i_index_name, g.i_status
,      /*t.temporary, t.partitioned, */ t.num_rows
,      g.i_column_list
FROM   gfc_fk_index_check g, all_tables t
WHERE  t.table_name = g.table_name
AND    t.owner = g.owner
AND    g.i_status = 'No Index'
ORDER BY num_rows desc
) WHERE rownum <= 20 or num_rows >= 10000
/
spool off

This query generates a script constraint create index DDL statements that will build the missing indexes. The index will have the same name as the foreign key constraint to which it relates.

set head off trimout on trimspool on feedback off verify off timi off echo off lines 200
spool fk_index_build.sql
SELECT 'CREATE INDEX '||g.owner||'.'||g.constraint_name
      ||' ON '||g.owner||'.'||g.table_name
      ||' ('||g.i_column_list||');' build_indexes
FROM   gfc_fk_index_check g, all_tables t
WHERE  t.table_name = g.table_name
AND    t.owner = g.owner
AND    g.i_status = 'No Index'
ORDER BY t.num_rows
/
spool off
set lines 90 head on feedback on echo on

The test script correctly reports (in fk_index_check.LST) that there are two foreign keys that require supporting indexes

OWNER           TABLE_NAME           CONSTRAINT_NAME
--------------- -------------------- --------------------
R_OWNER         R_TABLE_NAME         R_CONSTRAINT_NAME
--------------- -------------------- --------------------
I_INDEX_OWNER   I_INDEX_NAME         I_STATUS
--------------- -------------------- --------------------
I_COLUMN_LIST
---------------------------------------------------------
SYSADM          EMP_TAB              MGR_FKEY
SYSADM          EMP_TAB              EMP_PKEY
                                    No Index
MGR

SYSADM          EMP_TAB              DEPT_FKEY
SYSADM          DEPT_TAB             DEPT_PKEY
                                    No Index
SETID,DEPTNO

It produces another script fk_index_build.sql that will build the missing indexes.

CREATE INDEX SYSADM.MGR_FKEY ON SYSADM.EMP_TAB (MGR);
CREATE INDEX SYSADM.DEPT_FKEY ON SYSADM.EMP_TAB (SETID,DEPTNO);

When I ran this test script on my problem application, it identified over 200 missing indexes on 900 foreign key constraints, and since building the indexes on tables where I have seen TM locking, I haven't seen any TM locking contention.

The script can be downloaded from the Go-Faster website at http://www2.go-faster.co.uk/scripts.htm#fk_index_check.sql

Caveat: Just because you can index a foreign key, doesn't mean that you should. See
http://www.jlcomp.demon.co.uk/faq/fk_ind.htmlThis query produces a simple report on each foreign key constraint.

Record Locator Dialogue Behaviour

A little while ago I was asked whether Business Units need to be 5 characters for good performance. PeopleSoft used to recommend that SETIDs and Business Units should be defined as five characters.

I blithely responded that it was because if you put five characters into the search criteria of a five character field on a Record Locator Dialogue, then the resulting SQL would result in an equality condition rather than a LIKE.

If I search for a string shorter than the field, it generates a LIKE:

SELECT DISTINCT SETID ... WHERE SETID LIKE 'ABCD' || '%' ESCAPE '\' ...
ORDER BY SETID ...

If you search for a string equal in length to the field you get an equality (unless you specify something else in advanced search).

SELECT DISTINCT SETID ... WHERE SETID = 'ABCDE' ... ORDER BY SETID ...

That is how it was in PeopleTools 8.44, or at least that is what I wrote in my book (page 94).

But things change. I checking the behaviour in PeopleTools 8.48, and this is what I found:

Lets start with the Basic Search mode in the Record Locator Dialogue

The default operation in the basic search is 'begins with'. The search string is shorter than the field (11 characters in this case) and so we get a LIKE criteria, but there is a change here. The % has been put into the search string.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '1%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

However, if I fill the search field with 11 characters I still get a LIKE, and most significantly I no longer get an equality condition.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

However, in Advanced Search, I get exactly what I ask for.
'Begins with' always results in a LIKE condition, irrespective of the length of the search string.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

'Contains' always produces a LIKE condition, with % symbols at both ends, again irrespective of the length of the search string.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '%11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

If I specify '=', then I get an equality condition in the SQL, but the search criteria also becomes a bind variable in the SQL. This is different from all other cases, where the search criteria appears in the SQL as a literal value.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID=:2
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS
Bind-2 type=2 length=1 value=1

Only on a mixed case field, does PeopleSoft append the % in a separate string, because both the column and the search string are forced into upper case.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1
AND UPPER(NAME) LIKE UPPER('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') || '%' ESCAPE '\'
ORDER BY NAME, EMPLID
Bind-1 type=2 length=2 value=PS

Implications

So does it matter that PeopleTools uses a LIKE instead of an equality? Sometimes it could, but not always. Here is a very simple test where I search for a single employee by the unique key columns, first with an equality

SELECT emplid FROM ps_personal_data WHERE emplid = 'SFCH00034'
/

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PS_PERSONAL_DATA |     1 |     8 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("EMPLID"='SFCH00034')

Statistics
----------------------------------------------------------
         2  consistent gets

And here is the same search with a LIKE criteria

SELECT emplid FROM ps_personal_data WHERE emplid LIKE 'SFCH00034%'
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     8 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PS_PERSONAL_DATA |     1 |     8 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("EMPLID" LIKE 'SFCH00034%')
      filter("EMPLID" LIKE 'SFCH00034%')

Statistics
----------------------------------------------------------
         3  consistent gets

In this case, although both queries return the same result, it made the difference between a index unique scan and a single fetch operation, and an index range scan and a second fetch, requiring another consistent read. Although, I can also produce tests which produce the same execution plan and number of consistent reads for = and LIKE.
The LIKE operation does cause an additional filter operation, but this is only a small CPU overhead.

However, this does mean that the advice about having 5 character SETIDs and Business Units is no longer valid.