-
Slow database
SELECT RTRIM(ORD_MM.DEAL_NUMBER,' '),ORD_MM.AREA, ORD_MM.TRADE_DATE,
DB2_DRCRLD.ACTIVITY_DATE,
DB2_DRCRLD.CURRENCY,DB2_DRCRLD.ENTRY_AMOUNT,DB2_DRCRLD.DR_CR_FLAG,ORD_NOSTRO
.SHORT_CODE,RTRIM(ORD_SI.ADD1,' '),RTRIM(ORD_SI.ADD2,'
'),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
('DEP','MML','DEPR','MMLR','CLD','CLL','MTND','FFD','FFL') AND
ORD_MM.WSS_GDP_SITE=ORDMM_ALIAS.WSS_GDP_SITE(+) AND
ORD_MM.ROLLOVER_TID=ORDMM_ALIAS.WSS_TID(+) AND
DB2_DRCRLD.AREA_UNIT=ORD_AREA.AREA_UNIT AND
DB2_DRCRLD.CUST_TID=ORD_CS.WSS_TID AND
DB2_DRCRLD.SUB_LEDGER_ACCOUNT='NOSTRO' AND
ORD_NOSTRO.ACCOUNT=DB2_DRCRLD.SUB_LEDGER_ACCOUNT AND
ORD_NOSTRO.AREA_UNIT=DB2_DRCRLD.AREA_UNIT AND
ORD_NOSTRO.QUALIFIER=DB2_DRCRLD.QUALIFIER AND
ORD_NOSTRO.CURRENCY=DB2_DRCRLD.CURRENCY AND
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
will
-
You mean only the first time it takes 3 hrs and from next every 15 minutes its normal?
Never Ever Give Up!
-
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.
-
explain plan?
Also how much data it has to process ?
OCP 8.0, 8i, 9i
-
launchpad,
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?
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
well database needs sort of warm up after a reboot that´s why hot backups are recommended these days
DB2_DRCRLD,
ORD_AREA,
ORD_MM,
ORD_SI,
ORD_CS,
ORD_NOSTRO,
ORD_MM ORDMM_ALIAS
and post the explain plan
-
Is there any analyze scripts in the schema that runs after every shutdown and startup.
Never Ever Give Up!
-
Owing to it's frequency, I presume you have "kept" related objects in memory, right?
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Hint=RULE
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
NESTED LOOPS OUTER
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORD_NOSTRO
SORT JOIN
TABLE ACCESS FULL DB2_DRCRLD
SORT JOIN
TABLE ACCESS FULL ORD_MM
TABLE ACCESS BY INDEX ROWID ORD_MM
INDEX UNIQUE SCAN ORD_MM_KEY
SORT JOIN
TABLE ACCESS FULL ORD_AREA
SORT JOIN
TABLE ACCESS FULL ORD_CS
SORT JOIN
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|