Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Challenge: Make SQL Server run as bad as Oracle

    NEVER let anyone tell you that Oracle is faster than SQL Server:
    http://www.dbforums.com/showthread.p...57#post6265757
    So here is my challenge. What would you have to do to SQL Server to make it take 10 minutes to return a count from an empty table? Other than coding a loop that waits 10 minutes, of course.
    I'm not sure it could be done.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apparently the issue I have encountered is due to something called the High Water Mark or HWM in Oracle. Naturally, this concept is not even mentioned in any of the Oracle programming and administration books that I have.
    Oracle retains and reserves the maximum number of pages ever used by a table, even after the records in those pages have been deleted. After that, even when performing a simple COUNT of records on the table, Oracl scans all the previously used pages as if they still had data in them.
    Amazing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on, pal, don't hold it in, let it out, you'll feel a lot better

    you've just given reason #421 for why i am ~so~ not a DBA

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I have heard of the HWM in reference to attempting to resize (shrink) files, but not on tables. I would expect analyzing the table will shorten the response, as well. Remember, Oracle does not have auto-update stats. And there have been some cases in SQL Server, where I have thought seriously about disabling it on a few tables.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    Apparently the issue I have encountered is due to something called the High Water Mark or HWM in Oracle. Naturally, this concept is not even mentioned in any of the Oracle programming and administration books that I have.
    Oracle retains and reserves the maximum number of pages ever used by a table, even after the records in those pages have been deleted. After that, even when performing a simple COUNT of records on the table, Oracl scans all the previously used pages as if they still had data in them.
    Amazing.
    Code:
    ALTER TABLE [table_name] DEALLOCATE UNUSED;
    Go on, ask me how I know. Go on ask...please!

    'Cause I just (today) passed 1Z0-033 (9i Tuning and Performance). That was actually one of the test questions. And you thought those tests never had any practical value!

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Congrats on the test, hmscott. But won't the DEALLOCATE UNUSED merely deallocate extents above the HWM? I have no good test system to try that on.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by MCrowley
    Congrats on the test, hmscott. But won't the DEALLOCATE UNUSED merely deallocate extents above the HWM? I have no good test system to try that on.
    MCrowley,

    From my study guide...

    Quote Originally Posted by SYBEX 9i Tuning/Performance Study Guide
    The HWM is significant because a User's Server Process reads all the segment block's up to the HWM when performing a full table scan...

    ...Since the HWM does not move when rows are deleted from a segment, many empty blocks may end up being scanned...

    ...This unused space can be released back to the tablespace using the ALTER TABLE [tablename] DEALLOCATE UNUSED; command.
    So far so good (maybe). This stuff gets me confused and I did not pass the test by a wide margin...

    ...reading just a page further on in the guide...
    Quote Originally Posted by SYBEX 9i Tuning/Performance Study Guide
    There are two techniques for moving a table's HWM to the appropriate level:
    - Export; then drop or truncate the original and re-import
    - Use the ALTER TABLE ... MOVE and specify a new TABLESPACE to store the table.
    Like I said, I was marginal on the test. I thought I was better prepared, but I need to hit the books yet once again. It looks like my original statement was wrong and that you are correct. DEALLOCATE UNUSED will only clear the stuff above the HWM. You'll need to use one of the latter two techniques to fix things properly. From experience on 8i, I know that if you use the ALTER TABLE ... MOVE command, you will also have to remember to rebuild associated indexes.


    Regards,

    hmscott
    Last edited by hmscott; 04-05-07 at 17:11.
    Have you hugged your backup today?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Garr. I would have expected analyze to help you somewhat, but I guess not, eh?

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by MCrowley
    Garr. I would have expected analyze to help you somewhat, but I guess not, eh?
    Mayhap it would. Here's the thing that kills me about Oracle...let me try to run down my thoughts on this particular scenario...

    1. The server just executed the query, so the blocks are in the db buffer, no?

    2. Maybe not, it was a full table scan and therefore the blocks went immediately to the LRU (least recently used) end of the LRU "conveyor" which means they got flushed out of cache almost immediately, right?

    3. Maybe not, was there other activity affecting the db cache at the time?

    4. Was the execution plan cached in the shared pool? Should be. But would that make a difference (if the execution plan still says that he has to scan every block up to the HWM)?

    Like I said, this is what kills me about Oracle; the more I learn about it, the less confident/comfortable I feel about it. There are hundreds of undocumented startup parameters (in addition to the 200 or so documented ones). The stack of publications that I have for Oracle (on file and on paper) and the list of web sites that I have bookmarked for help trouble shooting Oracle issues absolutely dwarfs the stuff that I have for SQL.

    For SQL, I know that if I have an issue, I can come here and get an answer quick!

    The pay for an experienced Oracle DBA on the other hand....


    Regards,

    hmscott
    Have you hugged your backup today?

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by hmscott
    The pay for an experienced Oracle DBA on the other hand....
    but is it worth it?

    my 2 cents: nothing could be more boring in my mind, to become one of the world's experts on 200 undocumented startup parameters. It's like the SAP devs that become experts on some table named ZJSJ__3338dj. So what if they make more. That kind of knowledge is so arbitrary, and has nothing to do with real logic puzzles and problem solving which is what makes programming fun (to me anyway). Just learning a bunch of arbitrary configuration so you know which knob to tweak when things go south? Yuck. Let someone else do it and they can have the fatter paycheck.

    I guess that's why I'm not a dba

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's probably why, as a rule, all Oracle documentation sucks. If it took you years to find out about an obscure command, and that knowledge was that source of your salary, would you post it somewhere where the next person could google it in five minutes?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think the causal chain goes the other way: salaries are higher for oracle dbas because they have a skill that's harder to acquire than the same skill for sql server. and that skill is harder to acquire because the docs are poor, and because the product is more complicated to boot.

    If oracle wanted to sell more licenses, they should make their product easier to use and administer. this would be good for the people that buy their licenses, and bad for the average oracle dba -- because their guild would be easier to join, causing their salaries to go down.

    I don't see any real incentive for oracle to keep their docs poor, unless they are getting kickbacks from the dba guild

Posting Permissions

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