Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: does an increase in records cause a slowdown (with shared tablespaces)

    All,
    High level question about 9i.

    Lets say I have two tables sharing the same tablespace.

    To start with:

    Table1: 10,000 records
    Table2: 40,000 records

    Query on Table1 takes 5 seconds to execute (does not touch table2).

    Something occurs and now the stats are as follows:

    Table1: 10,000 records
    Table2: 3,240,000 records

    Query on Table1 takes 32 seconds to execute (does not touch table2).


    My feeling is that since these tables share a tablespace, the increase of table2 will have a negative impact on the query times for table1.

    Is this correct?

    Will splitting the two tables into separate tables spaces give me the original performance back?

    Thanks!

    --james

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >My feeling is that since these tables share a tablespace, the increase of table2 will have a negative impact on the query times for table1.
    Please explain/elaborate on how/why table2 change in size would impact query against table1.
    >Is this correct?
    No.

    >Will splitting the two tables into separate tables spaces give me the original performance back?
    No if both tablespaces reside on same/original volume.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by anacedent
    >My feeling is that since these tables share a tablespace, the increase of table2 will have a negative impact on the query times for table1.
    Please explain/elaborate on how/why table2 change in size would impact query against table1.
    >Is this correct?
    No.

    >Will splitting the two tables into separate tables spaces give me the original performance back?
    No if both tablespaces reside on same/original volume.
    It was a guess (about shared table space affecting query speed (hence the question in the subject)).

    We had a fast query all of a sudden take a _lot_ longer to run, and the only changes were to the size of one of the tables. The query doesn't use that table, so I was hoping maybe thats what caused the issue. Some of the index's for that large table are also in that tablespace, no idea if that would affect it or not.

    Basically, I'm trying to figure out why something that ran in just a few seconds is taking closer to 40 seconds, and the only signifigant changes were to an unrelated table. Grasping at straws here...


    --james

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Operating system name & version?
    Oracle version to 4 decimal places?
    > the only signifigant changes were to an unrelated table
    I suspect the statement above is not correct.
    A change which you might not consider "significant" may be the cause.
    "The ONLY" - that you are aware of.
    Please post SQL & EXPLAIN_PLAN for "slow" query using CUT & PASTE from SQL*Plus.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by anacedent
    Operating system name & version?
    Oracle version to 4 decimal places?
    > the only signifigant changes were to an unrelated table
    I suspect the statement above is not correct.
    A change which you might not consider "significant" may be the cause.
    "The ONLY" - that you are aware of.
    Please post SQL & EXPLAIN_PLAN for "slow" query using CUT & PASTE from SQL*Plus.

    DB: Oracle 9.2.0.7.0 64bit Production
    OS: SunOS 5.9

    I hate to hamstring anyone trying to help me, but I can't post the full query, but here is a sanitized version:

    select * from ( SELECT series, ROW_NUMBER() OVER (ORDER BY series) as rn from ps where pc='A' and status='A' and EXISTS( SELECT 1 from pi where pi.status='A' AND pi.series=ps.series ) )a where rn IN (1,10,11,20,21,30,31,40,41,50,51,60,61,70,71,80,81 ,82)


    Let me describe what happened. table pi has about 80k records in it, ps has around 3400. I was making that query and getting a full answer back in about a second or so (including network time to and from the db).

    Well, someone decided to alter another table on the same instance (not pi or ps, though both ps and pi contain have fields that are FK's on the table that was altered), this added about 60k records, (and since we track those changes our audit table also increased by approximately the same amount). Anyway, those changes were made, and as soon as it was done, I saw my sub-second query increase to approximately 22 seconds. We tried getting new statistics, and a few more things, but none of which helped.

    I appreciate you trying to help me, but I can't really be any more specific than that.

    Basically, I was not aware that changes to the size of a table would affect other, unrelated tables. (Which appears to be the case here). When I say "The Only", as you pointed out, I mean, prior to the slowdown no system settings were changed, and the insert's were pretty straightforward.

    So basically,

    1.) Query < 1 second
    2.) Co-worker of doom runs script, adds about 120k records to two tables in the db (neither of the tables are pi or ps)
    3.)Query ~22 seconds
    4.) the beatings begin

    Anyway, thanks again.

    --james

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Did you run explain plan to check if your indexes are used properly?
    What does SQL*Plus with set autotrace give you?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2006
    Posts
    47
    Anacedent,
    I'm quite aware of that fallacy of logic. But let me put it to you another way.

    You're in your office (with other employees), and you step out to get a cup of coffee. When you return to your office the lights are out. There are several possibilities of what may have caused that:

    1.) you turned the lights out
    2.) someone else turned the lights out
    3.) power is off
    4.) bulb is blown
    (and im sure a bunch of others)

    However, as you were getting coffee, you noticed a co-worker walking around randomly throwing switches.

    Now, you are correct to state that you cannot be certain that his action caused your lights to be out.

    However, its pretty easy to assign a "likelyhood" value to each one of those occurences. Can you guess which one immediately jumps to the top of the list? In case you can't, my money is on the idiot walking around throwing switches.

    You've been abrasive since the first time you responded to my request for assistance, but I let it slide because the fact of the matter is I thought you were actually trying to help. It has since become obvious that you suck at life. So kindly stop responding to this thread. Thank you.

    --james

  9. #9
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by shammat
    Did you run explain plan to check if your indexes are used properly?
    What does SQL*Plus with set autotrace give you?
    Everything looked like I expected it to with autotrace. the thing that gets me, is that my query didn't change, and the tables I'm querying didn't change. Only one event occured between the query running quickly, and the query running smoothly. In a nod to anacedents point, that doesnt mean that the increase in records caused the slow down, per se, but perhaps something stemming from that did. I'm just trying to come up with some kind of answer.

    (Of course, after looking at the trace files, I need to speed up some other aspects of this query too) But it's performance was acceptable prior to the slowdown.

    --james

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK if your execution plan did NOT change after this script then could your database load have increased due to queries against the table which grew thus indirectly slowing down all queries?

    Alan

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    By any chance is PI the large table and if it is, is series indexed in both pi and ps? if not, you are experencing full table scans everytime you hit your exists clause.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by AlanP
    OK if your execution plan did NOT change after this script then could your database load have increased due to queries against the table which grew thus indirectly slowing down all queries?

    Alan
    Possible, but unlikely, this is dev dB, should be at most 2 users on it.

    Ran the sun equivalent of top on the db, and it looked fine.

    Thanks for the suggestion though.

    --james

  13. #13
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by beilstwh
    By any chance is PI the large table and if it is, is series indexed in both pi and ps? if not, you are experencing full table scans everytime you hit your exists clause.
    PI is decently sized (100k or so), but its not the one that increased. Series is indexed in both PI and PS. Whats really odd, is I query pretty much the same way on just PI, and even though some of the results have more records, it's perfomance didn't change.

    I think the dB gods just have it in for me

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Its probably worthwhile confirming that this other table which grew is the problem. Why dont you export the table and then delete the extra records and see if performance returns to normal ( remembering to reanalyze it aswell)

    Alan

Posting Permissions

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