Execute Count in AWR Report
Date: Thu, 16 Jul 2020 17:00:34 +0000
Message-ID: <BYAPR07MB51607E56C9467D71F6F6B66CE27F0_at_BYAPR07MB5160.namprd07.prod.outlook.com>
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
Ian A. MacGregor
SLAC National Accelerator Laboratory
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 16 2020 - 19:00:34 CEST