SELECT RTRIM(ORD_MM.DEAL_NUMBER,' '),ORD_MM.AREA, ORD_MM.TRADE_DATE,
'),RTRIM(ORD_CS.CUST_NUMBER,' '),RTRIM(ORD_CS.NAME_FULL,' '),
DB2_DRCRLD.WSS_GDP_SITE, DB2_DRCRLD.WSS_COA_DATE, DB2_DRCRLD.WSS_TID,
DB2_DRCRLD.STATUS, DB2_DRCRLD.SEQUENCE_NO, DB2_DRCRLD.KEY_AMOUNT,
ORD_MM.WSS_TID, ORD_MM.EXT_TRAN_NO, ORD_MM.ORIG_NO, ORD_MM.REMARK_THREE,
ORDMM_ALIAS.DEAL_NUMBER FROM DB2_DRCRLD,ORD_AREA,ORD_MM,ORD_SI,ORD_CS,
ORD_NOSTRO, ORD_MM ORDMM_ALIAS WHERE DB2_DRCRLD.PRODUCT_CAT='MM' AND
ORD_AREA.AREA_CODE='DBGL' AND DB2_DRCRLD.DEAL_NUMBER = ORD_MM.DEAL_NUMBER
AND ORD_MM.AREA_INT_NO = DB2_DRCRLD.BOOKING_UNIT AND
ORD_MM.FSI_TID=ORD_SI.WSS_TID AND ORD_MM.PRODUCT IN
DB2_DRCRLD.WSS_COA_DATE=ORD_AREA.AREA_DATE AND FILE_NAME IS NULL
The script above runs every 15mins in our production enviroment. This works OK for what it is intended for. Every morning about 7:30am the database is shutdown for backup. After the database has been brought up this script is takes about 3 hours to return any data. After that thing are OK until the next shutdown.
I am hoping you db gurus will point me in the right direction to sort this out. Any suggestions welcomed
You mean only the first time it takes 3 hrs and from next every 15 minutes its normal?
No, I mean that the script is scheduled to run every 15 mins. The first time that the script runs is at 8.10am after a cold backup of the database has being taken. The fisrt run is taking a very long time to return any data. This morning it took 3hrs. After the first run, all the other runs return data very quickly ie between 3-10mins.
Also how much data it has to process ?
I have already looked at that. My question is why it does take so long after the database has been re-started?. It seems to be OK after some time.
I am thinking that it is taking too long for the database to come to optimum performance hence maybe some init.ora parameter needs tuning
Are there any other scripts run at the same time in the morning but not during the day that could have an affect on this query?
Is the data content similar at each runtime?
well database needs sort of warm up after a reboot thatīs why hot backups are recommended these days
and post the explain plan
Is there any analyze scripts in the schema that runs after every shutdown and startup.
Owing to it's frequency, I presume you have "kept" related objects in memory, right?
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Hint=RULE
NESTED LOOPS OUTER
TABLE ACCESS FULL ORD_NOSTRO
TABLE ACCESS FULL DB2_DRCRLD
TABLE ACCESS FULL ORD_MM
TABLE ACCESS BY INDEX ROWID ORD_MM
INDEX UNIQUE SCAN ORD_MM_KEY
TABLE ACCESS FULL ORD_AREA
TABLE ACCESS FULL ORD_CS
TABLE ACCESS FULL ORD_SI
Explain plan. Hope it makes sense.
To answer a few questions at the same time:
The data content is similar at each runtime. More scripts are eun during the evening. There are no analyze scripts run after each shutdown.
I am thinking of add pre_page_sga= TRUE in the init.ora. Will this have any impact on db preformance?