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
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 16 2020 - 19:00:34 CEST