Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27

    Unanswered: Multiple db on single server - Performance

    We have 2 databases running on one server. Queries against one db (say database A) are sometimes, running very slow. The dba thinks that queries running against database B is Slowing the query performance against database A. There is a particular query in database B which has 5 UNION ALL in it. The DBA says that this query is generating lot of I/O against a disk (U01) which has temp table space that is being shared by both Database A and Database B. Could this be the reason for poor performance of Queries in Database A ? How can the disk I/O affect the query performance ? How can we reduce disk I/O for a query that has UNION ALL ?

    Any help is appreciated

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Things you can try:
    1) move the temp tablespace of one the databases to another disk. Then see if you notice a difference. You can do this while online.
    2) rewrite the query such that it doesnt use the temp tablespace, usually if you can avoid a sort.
    3) Increase your pga so the sort is done in memory.

    Alan

  3. #3
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27
    Thanks Alan,

    Is it possible that if two database reside on same server, sharing same temp space, queries run against one database will impact the query performance on other db becuasue on I/O ?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by chrisP
    Thanks Alan,

    Is it possible that if two database reside on same server, sharing same temp space, queries run against one database will impact the query performance on other db becuasue on I/O ?
    If the two databases are two separate databases, they will NOT use the same disk space or memory. However, the two instances will be sharing the same servers IO channels and processors. A server should only run one instance, if the instance is used for production. If the instances are test or training instances, go for it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    assuming this is a dev/QA environment:
    it all depends on the server now doesn't it?
    we know nothing about this server. if it is bare-bones with low RAM and a worthless CPU then you won't be able to run many instances on it.

    With that being said, we have more than 50 QA instances running on one server.

    Also, the UNION ALL query should be rewritten in my opinion. FIVE unions will not be very efficient if running against tables with large amounts of data.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree with Duck... I too run multiple databases on servers without any problem. Planning and configuration up front are the key ... Also, not knowing much else about the situation, 5 unions in a statement are alot !!!

    What version of Oracle are you running. If you are in Oracle8.xx then you can also look at the configuration parameter sort_area_size and possibly increase it. Also ensure that whatever this is set to, make the default next size on the temp tablespace equal to this value.

    I am also curious about a comment in the original post ...
    " The DBA says that this query is generating lot of I/O against a disk (U01) which has temp table space that is being shared by both Database A and Database B. "

    Need to look at the tuning the query that is generation this excessive IO

    Gregg

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I wouldn't say the union all is necessarily a bad thing, for example...
    Code:
    select 1 from dual
    union all
    select 2 from dual
    etc...
    While not particularly elegant, it isn't going to stress any database.

    Quote Originally Posted by gbrabham
    Need to look at the tuning the query that is generation this excessive IO
    Absolutely.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree, not all unions are bad, but in this case
    " There is a particular query in database B which has 5 UNION ALL in it. The DBA says that this query is generating lot of I/O against a disk "

    I would have to look at this query and see what it's doing and try to
    knock out some of the IO...

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27
    Thanks for all the replies. I am trying to re-write this query. I appreciate if you can provide any suggestions about writing queries that generate less I/O . The db version is 9.2

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The first thing is to know the data ...
    You want to try and have the "Driving" table be the one that will
    return the smallest result set first (or that requires the least IO's either
    buffer gets or disk reads). Know indexes and their selectivity

    The best thing to do is to know what the final result is you are needing,
    take the union query and run it thru sqlplus with autotrace on to see the
    execution plan. Turn each statement individually.

    Do you really need 5 unions to get the final result. Would materialized views
    provide you with a better design? Ensure you have statistics updated on
    the objects. Does the parallel query option suit you better?. If the tables
    are large and there is a well defined "break", does partitioning fit? How is
    the database configured (init parameters) ...

    Bottom line is that there are many different things that come into play, but
    the #1 thing is knowing the data (ie, avg length of a row..how many rows can I fit in a block buffer), and knowing the indexes .. be able to predict how you think the query analyzer should behave and then adjust as you need to.

    Also, what version of 9.2 ??? 9.2.0.4 had alot of problems with the optimizer and bugs (especially with partition tables). There were numerous work arounds but the best that I found was to take the optimizer compatibility back to 8.1.7. 9.2.0.5 and .6 seem to be fairly solid (still some issues, but nothing as serious as 9.2.0.4)

    HTH
    Gregg

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27
    Thank you and appreciate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •