Optimising Journal Line Queries: 2. Exadata System Statistics

This is the second of a series of five articles where I look at the challenges presented by typical queries on the journal line table (PS_JRNL_LN).

1. Problem Statement
2. Exadata System Statistics
3. Partitioning
4. Compression
5. Conclusion

Exadata System Statistics 

Many other people have written notes about how Oracle's optimizer costs a full table scan.  This is a selection:

• Neil Chandler:  Oracle Optimizer System Statistics
• John Brady: Oracle Optimizer Plan Costing - Full Table Scans
• Randolf Geist: Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple block sizes - part 4
• Martin Widlake: Cost of Full Table Scans

Roughly speaking, the cost calculated by the optimizer that we see in an execution plan is an estimate of the time taken to perform an operation, where the unit of time is the duration of a single block read.  Although that statement is an oversimplification.  There are various guesses and assumptions built into the optimizer's calculation.  The cost-based optimizer looks for the cheapest plan, that ought to be the fastest to execute.  However, in many cases, cost does not correspond to execution time.

• See also Jonathan Lewis: Cost is Time

Full Scan Cost

The cost of a full table scan is made up of an I/O cost (the time taken to read the blocks from disk) and a CPU cost (the time taken to process the rows).  The I/O cost is the number of multi-block read operations, multiplied by the ratio of the duration of a multi-block read to a single-block read.

• IO Cost = (HWM / MBRC) . (MREADTIM / SREADTIM)

Where

• HWM = the high water mark of the segment expressed as a number of blocks
• MBRC = average multi-block read count for sequential read, in blocks (see parameter DB_FILE_MULTIBLOCK_READ_COUNT).
• MREADTIME = average time to perform a multi-block read at once (sequential read), in milliseconds
• SREADTIME = average time to read a single block (random read), in milliseconds

See PL/SQL Packages and Types Reference: DBMS_STATS

The single and multi-block read times are derived from two system statistics, the block size and the multi-block read count.

• SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED
• MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

Where

• IOSEEKTIM = Seek time + latency time + operating system overhead time, in milliseconds (default 10ms).
• IOTFRSPEED = I/O transfer speed in bytes per millisecond (or if you prefer KBytes/second)
• DB_BLOCK_SIZE = block size of the segment (usually 8Kb)

System statistics can be gathered based on actual system behaviour using DBMS_STATS, or set to pre-defined values using DBMS_STATS.GATHER_SYSTEM_STATS.  Over the years many blogs, forums and presentations have discussed the merits or otherwise of collecting or setting system statistics.  

Oracle's position is set out in the Oracle Optimizer Blog: Should You Gather System Statistics?  It can be summarised as: 

• Do not gather your own system statistics.
• Use the Oracle-provided defaults.
• Except on Exadata, where you can consider using the Exadata defaults, and perhaps not even then on a mixed workload. You will have to test this for yourself.

On any Oracle system, the default system statistics can be reset with 

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

This sets the system statistics as follows:

• MBRC=8
• IOSEEKTIM=10
• IOTFRSPEED=10

Thus:

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED

         = 10 + 8192 / 4096

         = 12 (ms)

MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

         = 10 + (8192 * 8 ) / 4096

         = 10 + 16 

         = 26 (ms)

However, on Exadata, you can set 'system statistics take into account the unique capabilities of Oracle Exadata, such as large I/O size and high I/O throughput' 

exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');

Some system statistics are then set differently:

• MBRC=128
• IOSEEKTIM=10
• IOTFRSPEED=204800

Thus

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED

         = 10 + 8192 / 204800

         = 10.04 (ms)

MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

         = 10 + (8192 . 128 ) / 204800

         = 10 + 5.1200 

         = 15.12000 (ms)

Now, I can put these numbers back into the formula Oracle uses to calculate the I/O cost of a full scan.

• IO Cost = (HWM / MBRC) . (MREADTIM / SREADTIM)

Let us suppose that we are going to read 100M blocks.  The I/O cost of that scan will be very different with Exadata system statistics rather than the normal default system statistics.

Normal IO Cost  = (100000000/8) . (26/12)

                = 27,083,333.3

 

Exadata IO Cost = (100000000/128) . (15.12/10.04)

                = 1,176,543.8

Thus, introducing Exadata system statistics significantly reduces the cost of the full scan, making the database more likely to use a full scan than index lookups.  That may or may not be desirable.

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |   428K|    93M|       |  2834K  (8)| 00:01:51 |       |       |
|   1 |  HASH GROUP BY                 |                |   428K|    93M|   111M|  2834K  (8)| 00:01:51 |       |       |
|*  2 |   HASH JOIN                    |                |   428K|    93M|    46M|  2819K  (8)| 00:01:51 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0001        |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   4 |     PART JOIN FILTER CREATE    | :BF0000        |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS STORAGE FULL | PS_JRNL_HEADER |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   6 |    JOIN FILTER USE             | :BF0001        |  1120K|   136M|       |  2817K  (8)| 00:01:51 |       |       |
|   7 |     PARTITION RANGE JOIN-FILTER|                |  1120K|   136M|       |  2817K  (8)| 00:01:51 |:BF0000|:BF0000|
|*  8 |      TABLE ACCESS STORAGE FULL | PS_JRNL_LN     |  1120K|   136M|       |  2817K  (8)| 00:01:51 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------------------

If I look at the same example query that I used earlier, then with the Exadata default system statistics, the cost has come down significantly (from 66M to 2817K).  It is a significant improvement, but it is still greater than the cost of the nested loop (1730K).  Therefore, for this query, I still only get this execution plan if I hint the statement to force it.  I still need to make the full scan cheaper.

Different queries will have different costs and will flip between the nested loop and Full scan/Bloom filter/hash join at different points.

Non-Exadata System Statistics 

If you are not running on Exadata, then the advice from Oracle is clear and unambiguous: use the default system statistics that can be reset with 

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

You will have to consider other techniques to reduce the cost of the full table scan.

Optimising Journal Line Queries: 1. Problem Statement

In each PeopleSoft product, certain tables usually grow to become the largest in the implementations at most customers. The challenges they present and the options for dealing with them also are common to most systems.  Most PeopleSoft Financials systems use General Ledger.  In General Ledger, the ledger, summary ledger and journal line tables are usually the largest tables, and present the biggest challenges.

This is the first of five articles where I look at the challenges typically presented by queries on the journal line table (PS_JRNL_LN).

1. Problem Statement
2. Exadata System Statistics
3. Partitioning
4. Compression
5. Conclusion

Problem Statement

In General Ledger, we typically see many queries on the ledger (or summary ledger) tables and also queries in the application, drill-down queries in nVision reporting, and ad-hoc PS/Queries that query details of journals posted to the ledger. Below is part of a typical query. The statement and execution plans below were taken from a PeopleSoft Financials system.  It is running on Oracle 19c on Exadata.  Making use of Exadata features will also be a topic.

SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT
, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER
, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR

• The journal line table (PS_JRNL_LN) is joined to its parent, the journal header table (PS_JRNL_HEADER), by the 4 key columns on the journal header (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ).
• There are criteria on both the journal header and line tables.
• The number of journal line rows per header is usually highly variable, and it also varies from customer to customer depending on the shape of their data.  It is not unusual to see thousands of journal line rows per header row.  Filtering it by FISCAL_YEAR and perhaps also ACCOUNTING_PERIOD could be very effective.  However, these columns are on PS_JRNL_HEADER, and not on PS_JRNL_LN. 
• Queries often include criteria on other attribute columns on PS_JRNL_LN.  However, these columns are not indexed by default, though many customers add such indexes.
  

Below is the execution plan that Oracle produced for this query.  The optimizer chose to full scan the PS_JRNL_HEADER table, expecting to find 428,000 rows, and then do an index look-up on PS_JRNL_LN for each header row.  The optimizer predicts that the query will run for about 68s.  In practice, this query runs for over an hour, spending most of its time on the index lookup of PS_JRNL_LN by its unique index of the same name.

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |   428K|    93M|       |  1730K  (1)| 00:01:08 |       |       |
|   1 |  HASH GROUP BY                      |                |   428K|    93M|   111M|  1730K  (1)| 00:01:08 |       |       |
|   2 |   NESTED LOOPS                      |                |   428K|    93M|       |  1715K  (1)| 00:01:07 |       |       |
|   3 |    NESTED LOOPS                     |                |   428K|    93M|       |  1715K  (1)| 00:01:07 |       |       |
|*  4 |     TABLE ACCESS STORAGE FULL       | PS_JRNL_HEADER |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE ITERATOR        |                |     1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |      INDEX RANGE SCAN               | PS_JRNL_LN     |     1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID| PS_JRNL_LN     |     1 |   128 |       |     4   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------------

If I add hints to the SQL statement forcing it to full scan PS_JRNL_HEADER and generate a Bloom filter that it will apply during a full scan of PS_JRNL_LN, then the cost of the execution plan goes up (from 1730K to 66M), and the estimated run time goes up to 43 minutes, due to the cost of the full scan.  However, the actual execution time comes down to under 3 minutes.

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |   428K|    93M|       |    66M  (1)| 00:43:30 |       |       |
|   1 |  HASH GROUP BY                 |                |   428K|    93M|   111M|    66M  (1)| 00:43:30 |       |       |
|*  2 |   HASH JOIN                    |                |   428K|    93M|    46M|    66M  (1)| 00:43:30 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0001        |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|   4 |     PART JOIN FILTER CREATE    | :BF0000        |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|*  5 |      TABLE ACCESS STORAGE FULL | PS_JRNL_HEADER |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|   6 |    JOIN FILTER USE             | :BF0001        |  1132K|   137M|       |    66M  (1)| 00:43:28 |       |       |
|   7 |     PARTITION RANGE JOIN-FILTER|                |  1132K|   137M|       |    66M  (1)| 00:43:28 |:BF0000|:BF0000|
|*  8 |      TABLE ACCESS STORAGE FULL | PS_JRNL_LN     |  1132K|   137M|       |    66M  (1)| 00:43:28 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------------------

I could solve the problem by adding hints either directly to the statement or with a SQL profile or SQL patch.  However, this is one of many statements, and the users will continuously produce more with the ad-hoc PS/Query tool.  I need a generic solution.

The Cost-Based Optimizer chose the nested loop plan because it calculated that it was cheaper.  However, that does not correspond to the change in the actual execution time.  The plan that was executed more quickly cost more, indicating a problem with the cost model.

• Why is the full scan so expensive?  
• How can I make it cheaper?

Now read on...