One of our PeopleSoft Developers sent me the following information
SL_TRU_DBSYNC:
Frist run (full sync):
FSTST(07/15 12:04) took 3 mins retrieve
rows: 1870248
Second run (incremental):
FSTST(07/15 12:13) took 1mins
58 sec. retrieve rows 0.
SL_TRU_DBSYNC refers to the following statement
INSERT INTO PS_SL_PROJ_RES SELECT BUSINESS_UNIT , PROJECT_ID ,
ACTIVITY_ID , RESOURCE_ID , RESOURCE_ID_FROM , BUSINESS_UNIT_GL ,
JOURNAL_ID , JOURNAL_DATE , UNPOST_SEQ , JOURNAL_LINE , 2020 ,
ACCOUNTING_PERIOD , ACCOUNT , ALTACCT , DEPTID , OPERATING_UNIT ,
PRODUCT , FUND_CODE , CLASS_FLD , PROGRAM_CODE , BUDGET_REF , AFFILIATE
, AFFILIATE_INTRA1 , AFFILIATE_INTRA2 , CHARTFIELD1 , CHARTFIELD2 ,
CHARTFIELD3 , BUS_UNIT_GL_FROM , CURRENCY_CD , STATISTICS_CODE ,
LEDGER_GROUP , ANALYSIS_TYPE , RESOURCE_TYPE , RESOURCE_CATEGORY ,
RESOURCE_SUB_CAT , RES_USER1 , RES_USER2 , RES_USER3 , RES_USER4 ,
RES_USER5 , TRANS_DT , ACCOUNTING_DT , OPRID , DTTM_STAMP , JRNL_LN_REF
, OPEN_ITEM_STATUS , LINE_DESCR , JRNL_LINE_STATUS , JOURNAL_LINE_DATE
, FOREIGN_CURRENCY , RT_TYPE , FOREIGN_AMOUNT , RATE_MULT , RATE_DIV ,
CUR_EFFDT , PROCESS_INSTANCE , PC_DISTRIB_STATUS , GL_DISTRIB_STATUS ,
PROJ_TRANS_TYPE , PROJ_TRANS_CODE , RESOURCE_STATUS , DESCR ,
SYSTEM_SOURCE , UNIT_OF_MEASURE , EMPLID , EMPL_RCD , SEQ_NBR ,
TIME_RPTG_CD , JOBCODE , COMPANY , BUSINESS_UNIT_AP , VENDOR_ID ,
VOUCHER_ID , VOUCHER_LINE_NUM , APPL_JRNL_ID , PYMNT_CNT ,
DST_ACCT_TYPE , PO_DISTRIB_STATUS , BUSINESS_UNIT_PO , REQ_ID ,
REQ_LINE_NBR , REQ_SCHED_NBR , REQ_DISTRIB_NBR , PO_ID , DUE_DATE ,
LINE_NBR , SCHED_NBR , DISTRIB_LINE_NUM , AM_DISTRIB_STATUS ,
BUSINESS_UNIT_AM , ASSET_ID , PROFILE_ID , COST_TYPE , BOOK ,
INCENTIVE_ID , MSTONE_SEQ , CONTRACT_NUM , CONTRACT_LINE_NUM ,
CONTRACT_PPD_SEQ , BI_DISTRIB_STATUS , BUSINESS_UNIT_BI , BILLING_DATE
, INVOICE , REV_DISTRIB_STATUS , BUSINESS_UNIT_AR , CUST_ID , ITEM ,
ITEM_LINE , ITEM_SEQ_NUM , DST_SEQ_NUM , BUSINESS_UNIT_IN ,
SCHED_LINE_NO , DEMAND_LINE_NO , INV_ITEM_ID , PAY_END_DT ,
BUSINESS_UNIT_OM , ORDER_NO , ORDER_INT_LINE_NO , EX_DOC_ID ,
EX_DOC_TYPE , RESOURCE_QUANTITY , RESOURCE_AMOUNT , BUDGET_HDR_STATUS ,
KK_AMOUNT_TYPE , KK_TRAN_OVER_FLAG , KK_TRAN_OVER_OPRID ,
KK_TRAN_OVER_DTTM , BUDGET_OVER_ALLOW , BUDGET_LINE_STATUS , BUDGET_DT
, LEDGER , BD_DISTRIB_STATUS , BUSINESS_UNIT_BD , FA_STATUS ,
TIME_SHEET_ID , SHEET_ID , DT_TIMESTAMP , VCHR_DIST_LINE_NUM ,
PM_REVIEWED , PRICED_RATE , ACTIVITY_ID_DETAIL , CST_DISTRIB_STATUS ,
TXN_LMT_TRANS_ID , EVENT_NUM , CA_FEE_STATUS , BUSINESS_UNIT_WO , WO_ID
, WO_TASK_ID , RSRC_TYPE , RES_LN_NBR , COMPRESS_ID , AMOUNT_IN_EXCESS
, RECLAIMED_FROM_OL , FND_DIST_STATUS , SEQ_TRANS_ID , DIST_TRANS_ID ,
ADJ_LINE_TYPE , FEEDER_SUM_ID , PRICE_SUM_ID , DEPOSIT_BU , DEPOSIT_ID
, PAYMENT_SEQ_NUM FROM PS_PROJ_RESOURCE A WHERE A.ACCOUNTING_DT BETWEEN
TO_DATE('2019-10-01','YYYY-MM-DD') AND
TO_DATE('2020-09-30','YYYY-MM-DD') AND NOT EXISTS ( SELECT 'X' FROM
PS_SL_PROJ_RES B WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND
B.PROJECT_ID = A.PROJECT_ID AND B.ACTIVITY_ID = A.ACTIVITY_ID AND
B.RESOURCE_ID = A.RESOURCE_ID)
/
I was able to retrieve the SQL_ID for the statement and ran the following
Column first_time format a30
Column last_time format a30
select sql_id, sql_plan_hash_value, min(sample_time) as first_time,
max(sample_time) as last_time
from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id ='9aa87dfufsr8r'
Group by sql_id, sql_plan_hash_value
order by 3
/
SQL_ID SQL_PLAN FIRST_TIME LAST_TIME
------------------- ---------------- -------------------------------------
--------------------------------------
9aa87dfufsr8r 913658610 15-JUL-20 12.04.51.402 PM 15-JUL-20 12.07.31.570 PM
9aa87dfufsr8r 3515813421 15-JUL-20 12.13.21.966 PM 15-JUL-20 12.14.52.116 PM
When I look at the AWR report for the hour from Noon to 1pm on July 15th it
reports
only one execution with an elapsed time of 101.05 seconds
This seems top correspond with the
Second run (incremental):
FSTST(07/15 12:13) took 1mins
58 sec. retrieve rows 0.
My first question is why isn't the first run counted in the AWR?
My second question is whether the other reported valuers for the statement such
as
User I/O wait time , buffer gets, and disk reads are also only for the second
run?
Ian A. MacGregor
SLAC National Accelerator Laboratory
