Long Story : Performance issue occuies when DB sharing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Long Story : Performance issue occuies when DB sharing

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Long Story : Performance issue occuies when DB sharing

    BackGround
    The story starts almost one year ago ,Customer is building their WMS and ERP application . Customer IT insist WMS and ERP should share the same Oracle Database ( it is a concern to save the development cost for Interface and license cost to buy another oracle ) . I caution that the potential performance issue would be occuried because of this kind of db sharing . It is hard for both vendors ( I am developing ERP and another vendor develop WMS) to distinguish the reason of performance problem.

    Now , one year passed , the situation is :
    A. Our ERP application is running just perfect.
    B. WMS application could run just so so when ERP is offline
    C. WMS application became extremely slow if ERP is working .

    In ERP application , since it is same database , we don't interface any data but just directly visiting the inventory table under WMS schema ( wms.tb_inventory).

    Customer regret and now they are thinking whether to separate those 2 system on 2 different database , it is a kind of lesson to customer for they making such kind of decision one year ago.

    From technical point of view , If ERP application is ported on another database , There are 2 options:
    1. Still re-use the current code , which impact on development cost is little , just change wms.tb_inventory to wms.tb_inventory@wmsdblink. But I think the performance would not be resolved ( and it may slow down ERP too)
    2. Change the technical architecure , Use ASCII file , Middle table to interface information between both systems. Well the development cost is ++.
    --------------------------------------------------------------------------

    Question
    I am asking my developer to check V$session_id , and it appears the hit radio of WMS application session is extremely low , the ratio of some session shows 10% or less . Is that a clue to tell actually WMS itself , the poor SQL coding is the final reason of the performance issue?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    select Username,

    OSUSER,

    Consistent_Gets,

    Block_Gets,

    Physical_Reads,

    100*( Consistent_Gets + Block_Gets - Physical_Reads)/

    ( Consistent_Gets + Block_Gets ) "Hit Ratio %"

    from V$SESSION,V$SESS_IO

    where V$SESSION.SID = V$SESS_IO.SID

    and ( Consistent_Gets + Block_Gets )>0

    and username is not null

    order by Username,"Hit Ratio %";


    -->This is the utility I used for Ratio check
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I don't see anything wrong with both WMS and ERP running on the same database.

    The hit-ratio is for the entire database you cannot calculate it per application wise at least not the way i know of :-).

    If i were you i would take statspack reports when there is a performance problem and then look at all the high impact sq l's and if they belong to wms ask the wms vendor to tune their sql's

    regards
    Hrishy
    Last edited by hrishy; 01-01-2008 at 03:52 AM.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well I have customer who move from 8 databases to a single one just for the sake to save license costs, reduce network traffic. So running several application in a single database is not really a problem but obviously if one is badly designed it can impact the others if they run in same database.

    It looks me your customer needs to start checking the SQL codes before making any decisions

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Quote Originally Posted by pando
    Well I have customer who move from 8 databases to a single one just for the sake to save license costs, reduce network traffic. So running several application in a single database is not really a problem but obviously if one is badly designed it can impact the others if they run in same database.

    It looks me your customer needs to start checking the SQL codes before making any decisions

    Thx , Pando and Hrishy , 【BTW , Long time no see , I was visiting DBASUPPORT since year 2000 , and was quite fond of it for 2~3 years 】.

    The SQL Tuning seems the biggest issue , and I will ask Customer to push another vendor to study their SQL code ( and internally , our ERP code need to be reviewd tol ) . Maybe SQL Trace is the easy way to do it
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Quote Originally Posted by ligang
    Thx , Pando and Hrishy , 【BTW , Long time no see , I was visiting DBASUPPORT since year 2000 , and was quite fond of it for 2~3 years 】.
    Are you referring to Pando or me well i was here all the time even during holidays as far Pando i rather have a sneaky suspicion that he was lurking around on one of those sybase forums :-) i think i saw him there :-)


    regards
    Hrishy

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    cant be me, I have not touched Sybase in my life!

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Quote Originally Posted by hrishy
    Are you referring to Pando or me well i was here all the time even during holidays as far Pando i rather have a sneaky suspicion that he was lurking around on one of those sybase forums :-) i think i saw him there :-)


    regards
    Hrishy
    I am referring both of U, as well as mario89
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    as far as i see.. hrishy and pando are very regular to this forum.. i guess you were the one who rarely came here?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Quote Originally Posted by abhaysk
    as far as i see.. hrishy and pando are very regular to this forum.. i guess you were the one who rarely came here?
    Yah , Didn't come here almost for 5 years . 5 years ago , I am the regular visitor too
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

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