(Updated 11.7.2014) Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temporary Records. Earlier this year, I encountered a situation where Optimizer Dynamic Sampling was not sufficient, and I needed properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper that can be called by the %UpdateStats PeopleCode macro via a customised DDL model to collects statistics.
I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema-wide or database-wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).
However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.
When an Application Engine program completes successfully, or when the process request is cancelled, specific instances of temporary records that were allocated when the program began are deallocated by deleting the row from PS_AETEMPTBLMGR. Therefore, I propose the following trigger that will delete the statistics for that record when that row is deleted.
I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema-wide or database-wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).
However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.
When an Application Engine program completes successfully, or when the process request is cancelled, specific instances of temporary records that were allocated when the program began are deallocated by deleting the row from PS_AETEMPTBLMGR. Therefore, I propose the following trigger that will delete the statistics for that record when that row is deleted.
CREATE OR REPLACE TRIGGER sysadm.gfc_deletetemptablestats
AFTER INSERT ON sysadm.ps_aetemptblmgr
FOR EACH ROW
WHEN (new.curtempinstance > 0)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
l_table_name VARCHAR2(30) := '';
l_last_analyzed DATE := '';
l_stattype_locked VARCHAR2(5) := '';
table_doesnt_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_doesnt_exist,-20001);
BEGIN
SELECT r.table_name, t.last_analyzed
INTO l_table_name, l_last_analyzed
FROM (
SELECT r.recname
, DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||:new.curtempinstance table_name
FROM psrecdefn r
) r
LEFT OUTER JOIN user_tables t
ON t.table_name = r.table_name
AND t.temporary = 'N'
WHERE r.recname = :new.recname;
SELECT s.stattype_locked
INTO l_stattype_locked
FROM user_tab_statistics s
WHERE s.table_name = l_table_name
AND s.object_type = 'TABLE';
IF l_last_analyzed IS NOT NULL THEN --only delete statistics if they exist
dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>l_table_name,force=>TRUE);
END IF;
IF l_stattype_locked IS NULL THEN --stats need to be locked, 21,11,2009
dbms_stats.lock_table_stats(ownname=>user,tabname=>l_table_name);
END IF;
EXCEPTION
WHEN no_data_found THEN NULL;
WHEN table_doesnt_exist THEN NULL;
END;
/
show errorss
NB: The trigger must use an autonomous transaction because dbms_stats also commits its updates.
You can test the trigger like this: First I will populate the control table with a dummy record, and collect statistics
INSERT INTO ps_aetemptblmgr (PROCESS_INSTANCE, RECNAME, CURTEMPINSTANCE, OPRID, RUN_CNTL_ID, AE_APPLID ,RUN_DTTM, AE_DISABLE_RESTART, AE_DEDICATED, AE_TRUNCATED) VALUES (0,'TL_EXCEPT_WRK',24,'PS','Wibble','TL_TIMEADMIN',sysdate,' ', 1,0) / execute dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'PS_TL_EXCEPT_WRK24',force=>TRUE); column table_name format a18 SELECT table_name, num_rows, last_analyzed FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24' / TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------ ---------- ------------------- PS_TL_EXCEPT_WRK24 0 14:36:12 06/04/2009
Now I will delete the row, and the trigger will delete the statistics for me.
DELETE FROM ps_aetemptblmgr WHERE process_instance = 0 and curtempinstance = 24 and recname = 'TL_EXCEPT_WRK' / SELECT table_name, num_rows, last_analyzed FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24' / TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------ ---------- ------------------- PS_TL_EXCEPT_WRK24
2 comments :
I have one doubt, is there a way to set particular timeframe to purge temporary tables ?
Actually I was going thru interview questions related to PeopleSoft at following website - www.itwisesolutions.com/PsftQuiz.html and found lot of tricky questions.
-LArry
I think that temporary tables are automatically purged by AE when they are allocated to an application engine instance. It is not done when the program ends, I think, because then the data is available for debug.
Post a Comment