Thursday, July 19, 2012

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

Recently, I have been looking into how to gather cost-based optimizer statistics on composite partitioned objects. 

Database partitioning is not used by default in PeopleSoft because it is database specific.  Not all databases support partitioning, and where they do it is done in a platform specific manner.  Application Designer has no capability to create partitioned objects (although from PeopleTools 8.51 it will preserve partitioning in tables and index that are already partitioned in an Oracle database). It is therefore no surprise that it is not used widely by PeopleSoft customers.  However, partitioning is essential in Global Payroll systems that use 'streamed'  processing (many concurrent payroll processes).  By definition, these are the payroll systems that have large volumes of data where collecting statistics on the results tables can quickly become a challenge.  Partitioning can also have application in General Ledger reporting in Financials, particularly where nVision is in use.

It is important that statistics on partitioned tables are both up to date and accurate.  However, collecting global statistics on a large partitioned object can be a time-consuming and resource intensive business as Oracle samples all the physical partitions or sub-partitions. Briefly, if you do not collect global statistics on a partitioned table, Oracle will aggegrate the statistics on the physical partitons or sub-partitions to calculate statistics on the logical table and partition segments.

Oracle 10g makes a number of mistakes in its calculation of these aggregated statistics.  In particular the number of distinct values on columns by which the table is partitioned have impossibly low values.  This is can affect cardinality calculations and so lead the optimizer to choose the wrong execution plan.

I have now published the second of two documents on my website that examine aspects of statistics on partitioned, and in particular composite partitioned tables. The first document examines the problems in 10g, and proposes a procedure to 'correct' the aggregated statistics to at least minimum possible values.
The second document looks at the same issue in 11g and shows that while most of the issues are fixed, one problem remains.
Posted by at No comments :

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

Recently, I have been looking into how to gather cost-based optimizer statistics on composite partitioned objects.  Collecting global statistics on a large partitioned object can be a time-consuming and resource intensive business as Oracle samples all the physical partitions or sub-partitions. Briefly, if you do not collect global statistics on a partitioned table, Oracle will aggegrate the statistics on the physical partitons or sub-partitions to calculate statistics on the logical table and partition segments.

Oracle 10g makes a number of mistakes in its calculation of these aggregated statistics.  In particular, the number of distinct values on columns by which the table is partitioned have impossibly low values.  This is can affect cardinality calculations and so lead the optimizer to choose the wrong execution plan.

I have now published the second of two documents on my website that examine aspects of statistics on partitioned, and in particular composite partitioned tables. The first document examines the problems in 10g, and proposes a procedure to 'correct' the aggregated statistics to at least minimum possible values.
The second document looks at the same issue in 11g and shows that while most of the issues are fixed, one problem remains.







Posted by at No comments :
Labels:

Friday, March 02, 2012

Editing Hints in Stored Outlines

Introduction

This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.  
CAVEAT: This is a hack, so this is more of an oddity than something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines?

I am using outlines because I am working with Oracle 10g, so I don't have SQL Plan Management until Oracle 11g. I am not using SQL Profiles because I am working with PeopleSoft Global Payroll. I want complete stability of execution plans rather than having the optimizer produce 'better plans' with adjusted costing. Otherwise, a single employee payroll calculation can put a different plan into the library cache which can then be used for a company-wide payroll calculation. I want to guarantee just one execution plan.

Preparation

I'll start by creating a table to use.
DROP TABLE t PURGE
/
CREATE TABLE t
(a NUMBER NOT NULL
,b NUMBER NOT NULL
,c VARCHAR2(100)
)
/
TRUNCATE TABLE t
/
INSERT INTO t (a,b,c)
SELECT row_number() over (order by level)
,      row_number() over (order by level desc)
,      TO_CHAR(TO_DATE(rownum,'J'),'Jsp')
FROM DUAL
CONNECT BY LEVEL <= 10000
/
BEGIN
 dbms_stats.gather_table_stats
 (ownname=>user
 ,tabname=>'T'
 ,cascade=>TRUE
 );
end;
/
Table T has 10000 rows. Column A is numbered 1 to 10000, B is numbered 10000 to 1, and there is a third column to provide some padding.
         A          B C
---------- ---------- ----------------------------------------
         1      10000 One
         2       9999 Two
         3       9998 Three
…
      9998          3 Nine Thousand Nine Hundred Ninety-Eight
      9999          2 Nine Thousand Nine Hundred Ninety-Nine
     10000          1 Ten Thousand

Collecting Outlines

So, the table doesn't have any indexes. I will disable autotrace in SQL*Plus, and dynamic sampling to prevent the collection of additional SQL outlines.
DROP INDEX t1;
DROP INDEX t2;

SET AUTOTRACE OFF
CLEAR SCREEN
ALTER SESSION SET optimizer_dynamic_sampling=0;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C1;

SELECT *
FROM t
WHERE a=42
OR    b=42
/

ALTER SESSION SET create_stored_outlines=FALSE;
ALTER SESSION SET use_stored_outlines=FALSE;
Without any indexes, Oracle can only do a full scan on the table.

Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    86 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |    86 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"=42 OR "B"=42)
I will now add the indexes and create another stored outline for the same statement but in a different category.
CREATE INDEX t1 ON t (a,b);
CREATE INDEX t2 ON t (b,a);
SET AUTOTRACE OFF 
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C2;

SELECT *
FROM t
WHERE a=42
OR    b=42
/
ALTER SESSION SET create_stored_outlines=FALSE;
ALTER SESSION SET use_stored_outlines=FALSE;
With the indexes, the execution plan includes a concatenation of queries on each index.
Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |    86 |     6   (0)| 00:00:01 |
|   1 |  CONCATENATION               |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2   |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1   |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=42)
   5 - access("A"=42)
       filter(LNNVL("B"=42))
I will create a third stored outline in a third category so that I have two copies of the outline for comparison.
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C3;

SELECT *
FROM   t
WHERE  a=42
OR     b=42
/

ALTER SESSION SET create_stored_outlines=FALSE;
I am going to rename the outlines to match the category to make it easier to work in the rest of this example.
BEGIN
 FOR i IN (
  SELECT DISTINCT c1.category, c1.name name
  FROM user_outlines c1
  , user_outlines c2
  WHERE c1.category != c2.category 
  AND c1.signature = c2.signature
  AND c1.category != c1.name
 ) LOOP
  EXECUTE IMMEDIATE 'ALTER OUTLINE '||i.name||' RENAME TO '||i.category;
 END LOOP;
END;
/

Looking at Outlines

We now have three outlines
SET AUTOTRACE OFF PAGES 100 LINES 120
BREAK ON name SKIP 1
SELECT * FROM user_outlines ORDER BY 1
/

NAME       CATEGORY   USED   TIMESTAMP           VERSION
---------- ---------- ------ ------------------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE                        COMPATIBLE ENABLED  FORMAT     MIGRATED
-------------------------------- ---------- -------- ---------- ------------
C1         C1         UNUSED 14:49:10 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED


C2         C2         UNUSED 14:49:10 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED


C3         C3         UNUSED 14:48:33 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED
And these are the hints in the outlines
SELECT  *
FROM user_outline_hints
ORDER BY 1,2,3
/

NAME        NODE  STAGE  JOIN_POS HINT
---------- ----- ------ --------- ----------------------------------------
C1             1      1         1 FULL(@"SEL$1" "T"@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

C2             1      1         1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
               1      1         1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
               1      1         0 OUTLINE(@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1_2")
               1      1         0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
               1      1         0 OUTLINE_LEAF(@"SEL$1_1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

C3             1      1         1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
               1      1         1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
               1      1         0 OUTLINE(@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1_2")
               1      1         0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
               1      1         0 OUTLINE_LEAF(@"SEL$1_1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS
Or, I queried from OUTLN.OL$HINTS
SET LINES 110 PAGES 999
BREAK ON ol_name SKIP 1
SELECT ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen
FROM outln.ol$hints
WHERE category IN('C1','C2')
ORDER BY 1,2,3,4,5
/

OL_NAME    HINT# CATEGORY  HINT_TYPE HINT_TEXT                                                    STAGE# NODE#
---------- ----- -------- ---------- ------------------------------------------------------------ ------ -----
TABLE_NAME  TABLE_TIN  TABLE_POS     REF_ID USER_TABLE       COST CARDINALITY      BYTES HINT_TEXTOFF HINT_TEXTLEN
---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ------------ ------------
C1             1 C1                2 FULL(@"SEL$1" "T"@"SEL$1")                                        1     1
T                   1          1          0 SYSADM.T   22.1083368           2         86           15            1

               2 C1             1011 OUTLINE_LEAF(@"SEL$1")                                            1     1
                    0          0          0                     0           0          0            0        0

               3 C1             1013 ALL_ROWS                                                          1     1
                    0          0          0                     0           0          0            0        0

               4 C1             1012 OPT_PARAM('optimizer_dynamic_sampling' 0)                         1     1
                    0          0          0                     0           0          0            0        0

               5 C1               54 DB_VERSION('11.2.0.2')                                            1     1
                    0          0          0                     0           0          0            0        0

               6 C1             1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                             1     1
                    0          0          0                     0           0          0            0        0

               7 C1             1008 IGNORE_OPTIM_EMBEDDED_HINTS                                       1     1
                    0          0          0                     0           0          0            0        0

C2             1 C2               51 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))          1     1
T                   1          1          0 SYSADM.T   3.00073364           1         43           15            1

               2 C2               51 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))            1     1
                    1          1          0 SYSADM.T   3.00073196           1         43           15            1

               3 C2             1010 OUTLINE(@"SEL$1")                                                 1     1
                    0          0          0                     0           0          0            0        0

               4 C2             1011 OUTLINE_LEAF(@"SEL$1_2")                                          1     1
                    0          0          0                     0           0          0            0        0

               5 C2               38 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))                           1     1
                    0          0          0                     0           0          0            0        0

               6 C2             1011 OUTLINE_LEAF(@"SEL$1_1")                                          1     1
                    0          0          0                     0           0          0            0        0

               7 C2             1011 OUTLINE_LEAF(@"SEL$1")                                            1     1
                    0          0          0                     0           0          0            0        0

               8 C2             1013 ALL_ROWS                                                          1     1
                    0          0          0                     0           0          0            0        0

               9 C2             1012 OPT_PARAM('optimizer_dynamic_sampling' 0)                         1     1
                    0          0          0                     0           0          0            0        0

              10 C2               54 DB_VERSION('11.2.0.2')                                            1     1
                    0          0          0                     0           0          0            0        0

              11 C2             1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                             1     1
                    0          0          0                     0           0          0            0        0

              12 C2             1008 IGNORE_OPTIM_EMBEDDED_HINTS                                       1     1
                    0          0          0                     0           0          0            0        0

Editing Outlines 

Adding a Hint 

I can add a new hint, thus
REM Insert hint at positition 0
INSERT INTO outln.ol$hints
(ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen)
VALUES
('C2',0,'C2',42,'NO_EXPAND'
,1,1,NULL,0,0
,0,NULL,0,0,0
,0,0)
/
REM increment hint numbers if there is a hint at position 0
UPDATE outln.ol$hints x
SET hint#=hint#+1
WHERE EXISTS (select 'x'
 FROM outln.ol$hints y
 WHERE y.ol_name = x.ol_name
 AND y.hint#=0)
AND ol_name = 'C2'
/
REM update the hint count on the parent record
UPDATE outln.ol$ x
set hintcount = (
 SELECT count(*)
 FROM outln.ol$hints h
 where h.ol_name = h.ol_name)
where ol_name = 'C3'
/

Changing a Hint 

However, in this case, I want to change an existing hint from USE_CONCAT to NO_EXPAND.
UPDATE outln.ol$hints
SET    hint_text = 'NO EXPAND'
WHERE  ol_name = 'C3'
AND    hint_text like 'USE_CONCAT(%)'
/

Testing Outlines 

Original Outline 

CLEAR SCREEN
SET AUTOTRACE OFF
ALTER SESSION SET statistics_level = ALL;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET use_stored_outlines=C2;
SELECT *
FROM t
WHERE a=42
OR    b=42
/
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED'))
/
The unchanged outline C2 still produces the concatenation
Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |    86 |     6   (0)| 00:00:01 |
|   1 |  CONCATENATION               |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2   |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1   |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=42)
   5 - access("A"=42)
       filter(LNNVL("B"=42))

Edited Outline 

Now, let's try the outline that I updated directly.
ALTER SESSION SET use_stored_outlines=C3;
SELECT *
FROM t
WHERE a=42
OR    b=42
/
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED'))
/
ALTER SESSION SET use_stored_outlines=FALSE;
I get a different execution plan that doesn't do CONCATENATION. Note that the outline still doesn't contain the NO_EXPAND hint that I put into the outline.
Plan hash value: 4269684720
-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |     2 |    86 |     6  (34)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   3 |    BITMAP OR                     |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|   5 |      SORT ORDER BY               |      |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | T1   |       |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."A" "T"."B")))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("A"=42)
       filter("A"=42)
Posted by at No comments :
Labels:

Sunday, June 26, 2011

Deferred Segment Creation in PeopleSoft

This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation by Chistian Antognini.
They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home
Deferred segment creation is available from Oracle 11gR1. From Oracle 11.2.0.2 this becomes the default, so there is no need to reconfigure anything in PeopleSoft in order use this Oracle feature. You may want to drop empty segments created under previous versions of the database.
Posted by at No comments :
Labels:

Thursday, March 03, 2011

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.

More Priority Levels

By default, PeopleSoft process can be given priority 1 (low), 5 (medium), and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference to the lower priority.

That’s fine, but sometimes in a very complex batch environment 3 process levels are not enough, and it would be useful to have more priority levels so that it is possible to define the hierarchy of processing to a finer level.

There does not appear to be anything special about the priorities delivered by PeopleSoft. They do not appear to be hard-coded anywhere within the SQL submitted Process Scheduler. As you might expect, some of the queries are sorted in descending order of priority.

The priority of a process in the Process Scheduler is defined by the single character field PRCSPRIORITY on the record PRCSDEFN. All you have to do is define addition translate values

The new priorities can be assigned via the delivered components.

I started with the intention of applying the new priority level to processes through the delivered component. However, I encountered some quirky behaviour from the Process Scheduler and Master Process Scheduler. There is something happening inside the programs rather than the SQL where they do not always take the process with the highest priority if that priority is not one of the delivered values. Therefore, I do not recommend using the new priorities on process definitions.

However, Server Categories also have priorities. Testing has shown that the priority of the Process Category take precedence over the priority on the Process Definition.

The new server categories should be defined in the Process Category Administration page of the Process Scheduler System Settings component


Then priorities and maximum concurrencies can be assigned to the categories in each Process Scheduler Server definition.


Testing the Additional Priorities
I have (with some assistance) constructed a simple test harness. I have an Application Engine program, AE_SLEEP, that does nothing, but sleeps for a period of time by calling the Java Sleep() method in a PeopleCode step.

Local JavaObject &Obj;Local ProcessRequest &RQST;
MessageBox(0, "", 0, 0, "Sleep Begin for " | GFC_SLEEP_AET.DURATION.Value | " seconds ");

&Obj = CreateJavaObject("java.lang.Thread");&Obj.start();
&Obj.sleep(GFC_SLEEP_AET.DURATION.Value * 1000);

MessageBox(0, "", 0, 0, "Sleep END");

I have a number of other Application engine programs that have different priorities, or that are in categories with different priorities, that call AE_SLEEP.

Finally, there is a process AE_SLEEP_RUN which submits requests for the other processes based on a control table. In this test I will be setting up some test data to demonstrate how the Process Scheduler behaves, in the next article, I will be modeling the behaviour of a real batch load from a real system

TRUNCATE TABLE ps_gfc_sleep_rc
/
INSERT INTO ps_gfc_sleep_rc
(run_cntl_id, prcstype, prcsname, offset_amount, duration, rundttm, last_run_cntl_id, next_run_cntl_id)
SELECT level, 'Application Engine', 'AE_SLEEP'||level, 5*level, 175, NULL, ' ', ' '
FROM dual CONNECT BY level <= 9
/
COMMIT
/ 
ALTER SESSION SET NLS_DATE_FORMAT='hh24:mi:ss';
set lines 120
COLUMN run_cntl_id FORMAT a8 heading 'Run|Control'
COLUMN last_run_cntl_id FORMAT a8 heading 'Last|Run|Control'
COLUMN next_run_cntl_id FORMAT a8 heading 'Next|Run|Control'
COLUMN prcsinstance FORMAT 99999 HEADING 'PRCS|INSTANCE'
COLUMN offset_amount FORMAT 9999 HEADING 'Offset|Amount'
COLUMN rownum FORMAT 99 HEADING 'Row|#'
COLUMN prcstype FORMAT a18
COLUMN prcsname FORMAT a12
COLUMN prcsprty FORMAT 9 HEADING 'PRCS|PRTY'
COLUMN prcscategory FORMAT a11 HEADING 'PRCS|CATEGORY'
COLUMN serverassign FORMAT a6 HEADING 'SERVER|ASSIGN'
COLUMN runstatus FORMAT a6 HEADING 'RUN|STATUS' 
 
SELECT run_cntl_id, prcsname, offset_amount, duration, rundttm 
FROM ps_gfc_sleep_rc
/

So the AE_SLEEP_RUN will schedule one process every 5 seconds. The lowest priority processes are scheduled first.

Run                            Offset
Control  PRCSNAME     RUNDTTM  Amount   DURATION
-------- ------------ -------- ------ ----------
1        AE_SLEEP1                  5        175
2        AE_SLEEP2                 10        175
3        AE_SLEEP3                 15        175
4        AE_SLEEP4                 20        175
5        AE_SLEEP5                 25        175
6        AE_SLEEP6                 30        175
7        AE_SLEEP7                 35        175
8        AE_SLEEP8                 40        175
9        AE_SLEEP9                 45        175

I can query what actually happened with the following SQL.

SELECT rownum, a.* 
FROM (
   SELECT SYSDATE, q.prcsinstance, q.prcsname, q.prcsprty, q.rundttm
   , r.begindttm, r.enddttm, q.serverassign, q.runstatus
   --, r.prcscategory
   FROM psprcsrqst r, psprcsque q
   WHERE r.prcsinstance = q.prcsinstanceAND r.prcsname like 'AE_SLEEP_'
   AND r.prcsinstance > (
      SELECT MAX(prcsinstance)
      FROM psprcsrqst
      WHERE prcsname = 'AE_SLEEP_RUN' AND runstatus = '9')
ORDER BY r.begindttm, q.prcsprty DESC, q.rundttm, r.prcsinstance) a
/

In all of the examples below, the first three low-priority processes run first because they are scheduled to run and therefore start before the higher priority processes are scheduled. The Process Scheduler is then blocked until all the other processes are due to have run, and so priority determines while processes the scheduler starts next.

However, due to a quirk in Process Scheduler, the processes are not assigned to the Process Scheduler in time order. In the following example, AE_SLEEP5 was executed before AE_SLEEP6.

Row           PRCS
#   SYSDATE   INSTANCE PRCSNAME     RUNDTTM  BEGINDTTM ENDDTTM
--- --------- -------- ------------ -------- --------- --------
  1 13:14:17      4583 AE_SLEEP1    13:03:27 13:03:55  13:07:08
  2 13:14:17      4584 AE_SLEEP2    13:03:32 13:03:55  13:07:08
  3 13:14:17      4585 AE_SLEEP3    13:03:37 13:03:55  13:07:08
  4 13:14:17      4591 AE_SLEEP9    13:04:07 13:07:12  13:10:27
  5 13:14:17      4587 AE_SLEEP5    13:03:47 13:07:13  13:10:27
  6 13:14:17      4588 AE_SLEEP6    13:03:52 13:07:13  13:10:27
  7 13:14:17      4586 AE_SLEEP4    13:03:42 13:10:31  13:13:46
  8 13:14:17      4589 AE_SLEEP7    13:03:57 13:10:47  13:14:01
  9 13:14:17      4590 AE_SLEEP8    13:04:02 13:10:47  13:14:01

But if the non-standard priority jobs are put in a different category, they are executed in the right order. It would appear that there is some logic in the Master Process Scheduler (and not in the SQL queries it submits) that works category by category.

Row              PRCS              PRCS                            SERVER RU
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN ST PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ -- ------------
  1 17:44:21     4681 AE_SLEEP5       5 17:31:10 17:31:24 17:34:39 PSNT   9  Default
  2 17:44:21     4682 AE_SLEEP5       5 17:31:13 17:31:24 17:34:39 PSNT   9  Default
  3 17:44:21     4683 AE_SLEEP5       5 17:31:16 17:31:40 17:34:56 PSNT   9  Default
  4 17:44:21     4690 AE_SLEEP7       7 17:32:43 17:34:43 17:37:57 PSNT   9  Priority 9
  5 17:44:21     4689 AE_SLEEP7       7 17:32:44 17:34:43 17:37:57 PSNT   9  Priority 9
  6 17:44:21     4688 AE_SLEEP7       7 17:32:45 17:34:59 17:38:14 PSNT   9  Priority 9
  7 17:44:21     4687 AE_SLEEP7       7 17:32:46 17:38:01 17:41:16 PSNT   9  Priority 9
  8 17:44:21     4684 AE_SLEEP5       5 17:31:19 17:38:01 17:41:16 PSNT   9  Default
  9 17:44:21     4685 AE_SLEEP5       5 17:31:22 17:38:17 17:41:33 PSNT   9  Default 
 10 17:44:21     4686 AE_SLEEP5       5 17:31:25 17:41:20 17:44:20 PSNT   9  Default

If there are two new priorities in the new category, then they do not run in the right order.

Row              PRCS              PRCS                            SERVER RUN
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------
  1 19:01:58     4716 AE_SLEEP5       5 18:06:58 18:07:26 18:10:40 PSNT   9      Default
  2 19:01:58     4718 AE_SLEEP5       5 18:07:04 18:07:26 18:10:40 PSNT   9      Default
  3 19:01:58     4719 AE_SLEEP5       5 18:07:07 18:07:26 18:10:40 PSNT   9      Default
  4 19:01:58     4723 AE_SLEEP6       6 18:08:25 18:10:44 18:13:43 PSNT   9      Priority 9
  5 19:01:58     4722 AE_SLEEP6       6 18:08:30 18:10:44 18:13:43 PSNT   9      Priority 9
  6 19:01:58     4717 AE_SLEEP5       5 18:07:01 18:10:44 18:13:43 PSNT   9      Default
  7 19:01:58     4724 AE_SLEEP7       7 18:08:40 18:14:02 18:17:00 PSNT   9      Priority 9
  8 19:01:58     4725 AE_SLEEP7       7 18:08:45 18:14:02 18:17:00 PSNT   9      Priority 9
  9 19:01:58     4720 AE_SLEEP5       5 18:07:10 18:14:02 18:17:00 PSNT   9      Default
 10 19:01:58     4721 AE_SLEEP5       5 18:07:13 18:17:18 18:20:17 PSNT   9      Default

However, if I create a separate category for each priority, and assign the priority to that category in the server definition, then the processes run in the desired order.

Row              PRCS              PRCS                            SERVER RUN
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------
  1 19:38:00     4740 AE_SLEEP5       5 19:25:10 19:25:23 19:28:23 PSNT   9      Default
  2 19:38:00     4741 AE_SLEEP5       5 19:25:13 19:25:39 19:28:38 PSNT   9      Default
  3 19:38:00     4742 AE_SLEEP5       5 19:25:16 19:25:39 19:28:38 PSNT   9      Default
  4 19:38:00     4748 AE_SLEEP7       7 19:26:52 19:28:41 19:31:41 PSNT   9      Priority 7
  5 19:38:00     4749 AE_SLEEP7       7 19:26:57 19:28:58 19:31:56 PSNT   9      Priority 7
  6 19:38:00     4747 AE_SLEEP6       6 19:26:37 19:28:58 19:31:56 PSNT   9      Priority 6
  7 19:38:00     4746 AE_SLEEP6       6 19:26:42 19:32:00 19:34:59 PSNT   9      Priority 6
  8 19:38:00     4743 AE_SLEEP5       5 19:25:19 19:32:16 19:35:15 PSNT   9      Default
  9 19:38:00     4744 AE_SLEEP5       5 19:25:22 19:32:16 19:35:16 PSNT   9      Default
 10 19:38:00     4745 AE_SLEEP5       5 19:25:25 19:35:02 19:37:59 PSNT   9      Default

Conclusion
The Process Scheduler does not reliably take process priorities into account when scheduling processes,  so I do not recommend using the additional priorities on processes, just the three delivered priorities (1, 5, and 9).

However, all the priorities do work properly on process categories. The priority of the server class does not appear to have any effect (I have not produced results for those tests here).

The priority of the category to which a process belongs takes precedence over the priority of the process. Thus a low priority process in a high priority category will be executed by the Process Scheduler in preference to a high priority process in a low priority category. Naturally if two processes are in the same category, or categories of equal priority, the higher priority process will be scheduled first.

Acknowledgements
My thanks to Andy Mason of Business Integrations for contributing to the initial concept and the Java code to make Application Engine sleep and assistance with the test harness.

Further Reading
I have published a paper on my website that describes how to extend this technique to model a real production batch, and predict the impact of Process Scheduler configuration changes.
Posted by at No comments :
Labels: ,
Subscribe to: Posts ( Atom )