Global Temporary Tables Share Statistics Across Sessions

In another blog posting, I asserted that statistics collected by one session on a Global Temporary table (GTT) would be used by other sessions that reference that table, even though each session has their own physical instance of the table. I thought I should demonstrate that behaviour, so here is a simple test.

We will need two database sessions. I will create a test Global Temporary table with a unique index.

DROP TABLE t PURGE;
TRUNCATE TABLE t;

CREATE GLOBAL TEMPORARY TABLE t
(a NUMBER,b VARCHAR2(1000))
ON COMMIT PRESERVE ROWS;

CREATE UNIQUE INDEX t ON t(a);

In my first session, I’ll populate the table with 100 rows. The values in column A have the range 1 to 100.

INSERT INTO t SELECT rownum, RPAD(TO_CHAR(TO_DATE(rownum,'J'),'Jsp') ,500,'.')
FROM dba_objects
WHERE rownum <= 100;

COMMIT;

And I’ll collect statistics on it.

begin sys.dbms_stats.gather_table_stats(ownname=>user,tabname=>'T'); end;
/

The following settings are just to make the queries easy to read, and so I can use dbms_xplan to generate a plan.

alter session set nls_date_format = 'hh24:mi:ss dd.mm.yyyy';
alter session set statistics_level = ALL;
set autotrace off pages 40 lines 100
column table_name  format a1
column column_name format a1
column low_value   format a32
column high_value  format a32
column a           format 999
column b           format a30

So now let’s check the contents of the table. There are 100 rows in the range 1 to 100.

select count(*), min(a), max(a) from t;

  COUNT(*)     MIN(A)     MAX(A)
---------- ---------- ----------
       100          1        100

And the statistics agree with this.

select table_name, num_rows, last_analyzed
from user_tables 
where table_name = 'T';

T   NUM_ROWS LAST_ANALYZED
- ---------- -------------------
T        100 18:13:17 13.10.2009

select table_name, column_name, low_value, high_value
from user_tab_columns
where table_name = 'T';

T C LOW_VALUE                        HIGH_VALUE
- - -------------------------------- --------------------------------
T A C102                             C202
T B 45696768742E2E2E2E2E2E2E2E2E2E2E 54776F2E2E2E2E2E2E2E2E2E2E2E2E2E
    2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E 2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E

So now let’s try a test query. The database returns 42 rows using a full scan. The statistics in the execution plan¹ also predict that there will be 42 rows. Perfectly reasonable.

select * from t where a<=42;

   A B
---- ------------------------------
   1 One
…
  42 Forty-Two

42 rows selected.

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  6g743z6c2m2ap, child number 0
-------------------------------------
select * from t where a<=42

Plan hash value: 1601196873
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |     42 |     4   (0)|     42 |00:00:00.01 |      15 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"<=42)

Now, let’s start a second session, and insert some slightly different data into the same GTT. There are still 100 rows, but this time column A is in the range 43 to 142.

INSERT INTO t SELECT rownum+42
, RPAD(TO_CHAR(TO_DATE(rownum+42,'J'),'Jsp') ,500,'.')
FROM dba_objects
WHERE rownum <= 100;

COMMIT;

I’ll collect statistics in the same way. First we will check that the data in the table is correct.

select count(*), min(a), max(a) from t;

  COUNT(*)     MIN(A)     MAX(A)
---------- ---------- ----------
       100         43        142

And I can also see that the statistics have changed.

select table_name, num_rows, last_analyzed
from user_tables 
where table_name = 'T';

T   NUM_ROWS LAST_ANALYZED
- ---------- -------------------
T        100 18:18:22 13.10.2009

select table_name, column_name, low_value, high_value
from user_tab_columns
where table_name = 'T';

T C LOW_VALUE                        HIGH_VALUE
- - -------------------------------- --------------------------------
T A C12C                             C2022B
T B 4569676874792D45696768742E2E2E2E 53697874792E2E2E2E2E2E2E2E2E2E2E
    2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E 2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E2E

If I run the same query, it correctly returns no rows, and uses an index scan to so. The statistics predict one row, but Oracle actually doesn’t find any. Again perfectly reasonable.

select * from t where a<=42;

no rows selected

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  6g743z6c2m2ap, child number 0
-------------------------------------
select * from t where a<=42

Plan hash value: 2795797496

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |     2   (0)|      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN          | T    |      1 |      1 |     1   (0)|      0 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"<=42)

But now let’s go back to the first session and run the query again (having flushed the shared pool). Last time we ran it we got 42 rows with a full scan. We still get 42 rows, but now it is using the index range scan, the same execution plan as the other session. In fact, the costs in the execution plan are the same as in the other session. Oracle expected 1 row from the index, but this time it actually got 42. So changing the statistics in the other session has changed the plan in this session, and possibly not for the better. The two sessions are using the same execution plan for different sets of data.

A B
---- ------------------------------
…
  42 Forty-Two

42 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  6g743z6c2m2ap, child number 0
-------------------------------------
select * from t where a<=42

Plan hash value: 2795797496
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |     2   (0)|     42 |00:00:00.01 |      10 |
|*  2 |   INDEX RANGE SCAN          | T    |      1 |      1 |     1   (0)|     42 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"<=42)

If I now delete stats, I will use optimizer dynamic sampling.

begin sys.dbms_stats.delete_table_stats(ownname=>user,tabname=>'T'); end;
/

And the plan changes back to a full scan.

(correct version reposted 1.11.2009)

A B
---- ------------------------------
…
  42 Forty-Two

42 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  6g743z6c2m2ap, child number 0
-------------------------------------
select * from t where a>=42

Plan hash value: 1601196873

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |     42 |     4   (0)|     42 |00:00:00.01 |      15 |
-------------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A">=42)

Note
-----
   - dynamic sampling used for this statement

Conclusion 

Different instances of the same GTT do share statistics because there is only one location in the data dictionary to store statistics for each table. Therefore, collecting statistics on a GTT in one session will affect other sessions using the same table name, possibly adversely!

(Added 1.11.2009) Optimizer Dynamic Sampling may be a better option for GTTs, and is enabled by default from Oracle 9i, but the behaviour changes slightly in 10g.  However, as Cokan points out in his comments below, if a query on a GTT from one session is still in the shared pool when it is used in a different session, then Oracle will not re-parse the statement, and will not choose a different execution plan.

Footnote 1: The execution plans in this posting have been obtained using dbms_xplan thus:

select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS +COST'));

I have also flushed the shared pool between each statement; otherwise the numbers are aggregated across multiple executions of the same statement.

Aggregating & Purging Batch Timings

Application Engine can collect timing information for the programs being execution. These 'batch timings' can be written to log file and/or tables in the database. I always recommend that this is enabled in all environments. The runtime overhead is very low, and this data is extremely valuable to determine the performance of a system over a period of time, and to identify the pieces of SQL or PeopleCode code that account for the most time. The timing data collected for individual processes can be viewed directly within the Process Monitor component.

The Process Scheduler purge process does not delete batch timings, so this data remains in the database indefinitely, although it can no longer be accessed via the Process Monitor. Over time, on a busy system, a large volume of data can accumulate. In some ways this is a good thing. There are good reasons to purge the Process Scheduler as aggressively as the business will permit.  The batch timings can still be analysed by direct SQL query. However, the sheer volume of data is likely to result in queries that can take quite a while to execute. After a while, you are less likely to be interested in the performance of individual processes, but are more likely to want to aggregate the data. So, it makes sense to hold the data at least partly aggregated.

I have produced a very simple Application Engine program (GFC_TIM_ARCH) to address this problem. This program is available for download from the Go-Faster website as a PeopleTools Application Designer Project. Please note that this process has been written using Oracle RDBMS specific SQL syntax.

• Application Engine stores batch timing data in three table PS_BAT_TIMINGS_LOG, PS_BAT_TIMINGS_FN and PS_BAT_TIMINGS_DTL.
• I have created three new tables PS_GFC_TIMINGS_LOG, PS_GFC_TIMINGS_FN and PS_GFC_TIMINGS_DTL.
• GFC_TIM_ARCH aggregates the data in each of these tables by the day on which the process begin, by the process name, and (where applicable) by the 'detail_id' column. The aggregated data is put into the GFC_TIMINGS% tables, the original data is removed from the BAT_TIMINGS% tables.
• It finds the earliest three days for which timing data exists that is older than the longest Process Scheduler retention limit. The idea is that the process should be run daily (the delivered 'Daily Purge' recurrence is suitable), but if it doesn't run for some reason it will catch up the next day.
• Only one instance of WMS_TIM_ARCH is permitted to run concurrently.

On one system, where I tested this process, BAT_TIMINGS_DTL was growing by over 1 million rows per day. This became around 5000 rows per day in GFC_TIMINGS_DTL.

If you have been running with batch timings for a while, then when you first introduce this process you will probably have a large backlog of data to be aggregated and purged. The easiest option is to run this process repeatedly until the data has been processed (possibly using the recurrence that causes a program to run every minute). After the backlog has been cleared the BAT_TIMINGS% tables should be rebuilt or shrunk in order to release the space left in the tables by the deleted rows. This will help queries that scan the BAT_TIMINGS_DTL record, otherwise these scans still need to include the empty rows because they are below the tables High Water Mark.

Once the backlog has been cleared, the GFC_TIM_ARCH process can run daily and, not withstanding variations in the load on the system, the rolling volume of data retained in the BAT_TIMINGS% tables should be fairly constant, and it should not be necessary to rebuild them frequently. Space freed by the daily delete should be used by new rows that are inserted into the table as AE processes run.

Identifying Application Engine Source Code

One of the recurring challenges of performance tuning PeopleSoft systems is that having identified a problem SQL statement with the aid of a database utility, such as Oracle Enterprise Manager or session trace, you often need to identify the source so that you can implement a change.
Stored Outlines and SQL Profiles do not work well with PeopleSoft. Bind variables in Application Engines often become literals before the SQL statement is submitted to the database, unless the ReUse Statement feature has been enabled, which it is not by default.
I wrote about this in PeopleSoft for the Oracle DBA, it contains a script called stmtid.sql (listing 11-23, page 280) that adds an identifying comment to each of the stored statements used by PeopleSoft COBOL programs.
Now, I have produced a similar script for Application Engine programs. The script aeid.sql adds identification comments containing the name of object. It directly modifies the PeopleTools tables for Application Engine steps and PeopleSoft SQL objects. These comments will be seen in database tools and traces. So now when your database tools find a problem statement, it is easy to find the source.
Below, is part of a screen shot from Oracle Enterprise Manager. You can see the identifying comment in the SQL, which was added by this script
The comment identifies the name of the Application Engine program, section, step and step type. In this example, the SQL is from an Application Engine ID GPGB_EDI_PRC, in section called EDI_PRC, in a SQL type step called Step01.
For example, if you need adjust this SQL statement, perhaps add a hint to it, you don't have to spend time working out where it came from. It is not uncommon to find many similar SQL statements in a program. Also, where dynamic code is used to generate the SQL statement, it can be very tricky to find the exact source. 

Using aeid.sql

• You will need to edit the script each time, to specify which Application Engine programs and SQL objects are to be commented. The script is not perfect. It does not handle some very long statements, so you cannot simply instrument the entire system in one go.
• The script is designed to run in SQL*Plus.
• It produces a report of statements that it has altered.
• The script does not commit. You should either commit or rollback the update depending upon whether you are satisfied with the output.
• I suggest that the script only be run in pre-production environments. The comments will of course be migrated along with any other changes.

Update 1st December 2014: However, this strategy is pretty much obsolete from PeopleTools 8.52 because Application Engine is instrumented. That is to say that the Application Engine name, section and step are set in the session's Module and Action attributes (see PeopleTools 8.52 Application Engine sets MODULE and ACTION), although there are some problems with DO SELECT and DO WHILE loops.

Automatic Segment Space -v- Freelist Management for PeopleSoft Temporary Records

Earlier this year, I wrote about some research into the effects of concurrent TRUNCATE TABLE operations in concurrent PeopleSoft Application Engine process. Since then I have been prompted to look at the effect of Automatic Segment Space -v- Freelist (or Manual) Management.

ASSM was introduced in Oracle 9i for Locally Managed Tablespaces, and from Oracle 10g it is default option, It was designed for high concurrency systems, and avoids some of the problems, such as contention on header blocks, that can occur with Freelist Management. It uses tracking blocks to indicate utilisation of data blocks. The ASSM structure is similar to a B-tree index (see Tanel Poder's presentation: Freelists -v- ASSM in Oracle 9i).

Normally, using ASSM is preferable in multi-user OLTP systems. The reduction in contention on the freelists should outweigh the additional I/O for the tracking blocks.

However, non-shared instances of PeopleSoft Temporary Records are only used by a single Application Engine process at any one time. Therefore, there would be no contention on freelists for these objects, and so DML operations on these objects will not derive much benefit from ASSM. In fact, the contrary could easily be true. The additional tracking blocks are also maintained during DML and are cleared out by local writes during a truncate operation. Truncate operations are serialised in Oracle on the RO enqueue. If you run multiple concurrent Application Engine programs you can get contention between the truncate operations. The additional I/O on the ASSM tracking blocks causes the truncate to take longer and in extreme cases can aggravate truncate contention.

The local write operations that occur during truncate operations cannot be deferred. Significant truncate contention can be a symptom of disk contention. However, switching back to Freelist Management saves the I/O to the tracking blocks, and so improves the performance of truncate.

I ran a test where I truncated a table with 5000 rows. I examined an Oracle trace with waits of the truncate. I tested it in an ASSM tablespace, and Freelist Managed tablespace. I tested truncating just the table on its own, and sometimes with an index.

Number of Wait Events	Table only, without any indexes			With a single primary key index
Tablespace Type	db file sequential read	enq: RO - fast object reuse	local writes	db file sequential read	enq: RO - fast object reuse	local writes
Automatic Segment Space Management	8	1	4	16	2	9
Freelist Management	3	1	1	6	2	3

You can see from this table that the truncates in the ASSM tablespace required more single block reads (db_file_sequential_read) and more local writes.

Recommendation

In addition to my previous recommendation to move the all (non-shared instances) of temporary working storage tables, and their indexes, to tablespaces with a larger block size, I also recommend that those tablespaces should be specified with MANUAL segment space management.

PeopleSoft and the Oracle Recycle Bin

If you are running PeopleSoft on Oracle 10g, what do you do about the Recycle Bin? It is a new feature in Oracle 10g, and it is enabled by default. So you are using it, unless you have taken a decision to the contrary.

It works just like the Windows recycle bin. You can drop a table and then flash it back (they didn't call it UNDROP because Oracle marketing now calls everything Flashback). So when you drop a table, Oracle marks it as dropped, and renames it with a system generated name beginning with BIN$. You can look at the contents of the Recycle Bin through a catalogue view.

>create table t (a number);
>drop table t;
>select * from user_recyclebin

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME                        CREATETIME          DROPTIME               DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T                                DROP      TABLE
PSDEFAULT                      2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
                          YES YES     776642      776642       776642          4

If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.

PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.

My Opinion

Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.

The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.

I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.