Only one person can use the db at a time



  • Another team is running a new report that they're testing. Naturally, part of it asks the db to grind through lots of data.

    Apparently, Oracle copies some portion of the relevant tables to temp space (no clue why) in order to keep that snapshot sane.

    Unfortunately, if anyone else is doing a lot of inserts into those same tables, Oracle eventually craps out with a snapshot-out-of-date error. Turns out that this is caused by insufficient temp space.

    Ok, this isn't the production db, so let's ramp up the temp space to match the configuration of the production db. Same problem.

    Ok, let's give it MORE temp space than production. Same problem.

    Ok, let's give it a LOT more temp space than production. The report works.

    The decree? When this report is running, everyone else needs to be suspended until the report finishes.

    Um, what about previously submitted batch jobs? What about requests submitted by our actual customers - in real time? Excuse me Mr/s. Customer, but you can't press SUBMIT until ThisPerson finishes running her report; please wait <hourglass cursor>

    Maybe the report needs to be rewritten to leverage indices? Maybe it can be done in stages so as to consume fewer resources?

    So here I sit, reading TDWTF instead of working....

     



  • That is a well-known issue and I'm sure a well-paid Oracle consultant can stop by to review your system's performance. I can't remember all the details, but just increasing temp space isn't enough. You've also got to have more space for the redo log, and perhaps something else. There are also things you can optimize by partitioning the data, because (if I remember properly) Oracle doesn't have to isolate the data from other processes when the partition the data is in isn't written to: separation by date did the trick for us, since nobody queried the latest data, at least not for heavy reporting (bonus question: what kind of data could that be?). You could also read up a bit about isolation levels.



  • I'm gonna assume you're talking about "ORA-01555: snapshot too old". What's going on here is that your rollback segments / undo aren't big enough. Here's the basic design.

    When a user ("Alex") does an insert/update/delete, the database saves a before copy of the data in a rollback or undo segment. (Undo segments are the same as rollback segments except they're automatically managed instead of manually managed by the DBA.) If Alex rolls back his transaction instead of committing, then the database gets the information out of the rollback segment. And until Alex rolls back or commits his transaction, the database will keep the data in the rollback segment just in case he rolls back.

    So. Suppose that at 15:00, Alex starts an "update" on table FOO. At 15:01, "Betty" starts running MonsterQuery. MonsterQuery uses the rows Alex is updating in FOO. Since Alex hasn't committed his transaction yet, Betty's query needs to use the "before" image. It will get the "before" data from Alex's rollback segment.

    At 15:02, Alex commits his transaction. Now he no longer needs the rollback data. So the database is now allowed to overwrite his "before" image if it needs the rollback segment space. But there's one problem. Betty's MonsterQuery is still running. Since she started it before Alex committed, she needs to keep using the "before" image.

    Rollback segments are circular buffers. After Alex's transaction is done, the next transaction will use the next space forward in the buffer and so on. So Alex's "before" image will stick around for quite a while. But eventually, another transaction will need the rollback segment space holding Alex's "before" image. At that point, if Betty's query is still going, Betty will get the "ORA-1555: Snapshot too old".

    It's very nicely explained here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:1441804355350

    Solution: Bigger rollback segments, and/or a faster query. Temp space has nothing to do with it, except maybe to speed up the query if it's doing a massive amount of on-disk sorts. I really hope that's just the DBAs badly explaining rollback segments as a kind of temp space, not actually thinking they can fix the problem by adding more space to the temporary tablespace.

    ... as you noted, the solution is NOT "Oh, tell all the other users to stop using the database." WTF indeed.

    -aliquot


  • ♿ (Parody)

    I've had queries where the parameters changed and so it needs to use a vastly different plan than whatever it used at one point. Oracle had been doing a cartesian join (only a handful of matching records) in one scenario and thousands in another. BOOM! We got a different error about actually running out of temp space, so this may be something completely different.

    But it's still likely (as others said) that there's something that can be done to the actual report to make it less horrible. Not that you can presumably do anything about it, so I guess you'll just enjoy more paid downtime.



  • @aliquot said:

    useful info about how Oracle works
    It was definitely 01555.

    Very interesting. I learned something today (thanks). Maybe our DBAs can learn something too (I forwarded it on to them).

    As for the report, it turns out that it can be run offline, but this individual was just too lazy to use the other database because "this is where they always logged in".

     



  • Wot aliquot said, apart from:

    @aliquot said:

    Solution: Bigger rollback segments, and/or a faster query. Temp space has nothing to do with it, except maybe to speed up the query if it's doing a massive amount of on-disk sorts
     

    Larger UNDO TABLESPACE, since using Ora10 I presume you're using AUM and not rollback segs anymore.

    His second bit about faster queries is also valid: there are some optimisations that can speed up data retrieval (caches, indexes, etc).



  • @snoofle said:

    Maybe our DBAs can learn something too (I forwarded it on to them).
     

    Your DBAs should KNOW THIS SHIT.

    This is one of the reasons you have full-time DBAs, to detect and address these issues!

    This should NOT be anything new to them!

    Oh, $deity... it's snoofle and his Untouchable Fuckwitted Database-room staff. My apologies, I forgot: you're not describing proper DBAs.



  • @Cassidy said:

    Your DBAs should KNOW THIS SHIT.

    This is one of the reasons you have full-time DBAs, to detect and address these issues!

     

    Yes, you would be right, but what can you expect from such grossly underpaid workers? Last time I looked, they earned less than the CEO.


  • Then get that CEO on the database, stat! Since he earns more than any of your DBA's, clearly he knows more than they do!



  • @Cassidy said:

    Oh, $deity deity$

    much better like this



  • @Speakerphone Dude said:

    much better like this

    What if deity$ is null?



  • Good point. Best implement a check.

    In fact, implement two: to be sure, to be sure.



  • @barfoo said:

    @Speakerphone Dude said:
    much better like this

    What if deity$ is null?

    BURN PAGAN BURN


  • BINNED

    @Speakerphone Dude said:

    @barfoo said:
    @Speakerphone Dude said:
    much better like this

    What if deity$ is null?

    BURN PAGAN ATHEIST BURN

    FTFY. For pagans, deity$ >= 1.



  • @PedanticCurmudgeon said:

    @Speakerphone Dude said:
    @barfoo said:
    @Speakerphone Dude said:
    much better like this

    What if deity$ is null?

    BURN PAGAN ATHEIST AGNOSTIC BURN

    FTFY. For pagans, deity$ >= 1.

    FTFTFY. Technically he said: "what if deity$ is null" which implies that we may not be able to know.



  • @Speakerphone Dude said:

    Technically he said: "what if deity$ is null" which implies that we may not be able to know.

    Heretic!! You worship at the alter of SQL!!


  • :belt_onion:

    @Xyro said:

    @Speakerphone Dude said:
    Technically he said: "what if deity$ is null" which implies that we may not be able to know.
    Heretic!! You worship at the alter of SQL!!
    I usually write my SQL queries in uppercase, so that would be ALTER



  • @bjolling said:

    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy



  • @bjolling said:

    I usually write my SQL queries in uppercase, so that would be ALTER

    As long as you don't uppercase table names etc.

     



  • @dhromed said:

    @bjolling said:

    I usually write my SQL queries in uppercase, so that would be ALTER

    As long as you don't uppercase table names etc.

     

    SeLeCt PaSsWoRd FrOm UsErS wHeRe UsErNaMe = 'dhromed'; drop table users;--'



  • @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase
    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.


  • BINNED

    Yes, but the typical practice is to only shout keywords.



  • @PedanticCurmudgeon said:

    Yes, but the typical practice is to only shout keywords.

    Well, of course. You don't want the data to be shouted back at you.



  • @Cassidy said:

    @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.

    It has been demonstrated that it is the exact opposite: screaming can impact disk latency



  • @Speakerphone Dude said:

    @Cassidy said:

    @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.

    It has been demonstrated that it is the exact opposite: screaming can impact disk latency

    With that much background noise, I'm surprised his fake throwing-up sounds made a difference.



  • @Ben L. said:

    @Speakerphone Dude said:
    @Cassidy said:

    @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.

    It has been demonstrated that it is the exact opposite: screaming can impact disk latency

    With that much background noise, I'm surprised his fake throwing-up sounds made a difference.

    Sound level is not intuitive



  • @Speakerphone Dude said:

    @Ben L. said:
    @Speakerphone Dude said:
    @Cassidy said:

    @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.

    It has been demonstrated that it is the exact opposite: screaming can impact disk latency

    With that much background noise, I'm surprised his fake throwing-up sounds made a difference.

    Sound level is not intuitive


    seems intuitive to me. It is a logarithmic function.



  • @Helix said:

    @Speakerphone Dude said:
    @Ben L. said:
    @Speakerphone Dude said:
    @Cassidy said:

    @Xyro said:

    @bjolling said:
    I usually write my SQL queries in uppercase

    wwhhyyyyyyyyyyyyyyyy
     

    BECAUSE EVERYBODY KNOWS DATA IS RETURNED QUICKER WHEN YOU SHOUT AT DATABASES.

    YOU'VE GOT TO LET THEM KNOW WHO'S BOSS.

    It has been demonstrated that it is the exact opposite: screaming can impact disk latency

    With that much background noise, I'm surprised his fake throwing-up sounds made a difference.

    Sound level is not intuitive


    seems intuitive to me. It is a logarithmic function.

    You have to scroll to the bottom to get the Cliff's note version: "Doubling the loudness feeling is obtained by an increase of the (loudness) level of about 10dB."


Log in to reply