Slow database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Slow database

  1. #1
    Join Date
    Feb 2001
    Posts
    107

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16
    You mean only the first time it takes 3 hrs and from next every 15 minutes its normal?
    Never Ever Give Up!

  3. #3
    Join Date
    Feb 2001
    Posts
    107
    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.

  4. #4
    Join Date
    Jul 2000
    Posts
    119
    explain plan?
    Also how much data it has to process ?
    OCP 8.0, 8i, 9i

  5. #5
    Join Date
    Feb 2001
    Posts
    107
    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

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  8. #8
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16
    Is there any analyze scripts in the schema that runs after every shutdown and startup.
    Never Ever Give Up!

  9. #9
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Owing to it's frequency, I presume you have "kept" related objects in memory, right?
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  10. #10
    Join Date
    Feb 2001
    Posts
    107
    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
  •  



Click Here to Expand Forum to Full Width