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.php?p=6265757#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.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.|||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

:)|||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.|||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.

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|||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.|||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...

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...

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|||Garr. I would have expected analyze to help you somewhat, but I guess not, eh?|||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...:D

Regards,

hmscott|||The pay for an experienced Oracle DBA on the other hand...:D

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 :)|||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?|||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 :)