2007/07/14

A vs B, the grand final

Sorry for the delayed conclusion folks, been incredibly busy with a Peopletools 8.48 upgrade. Did you know that DESC indexes are actually FBIs? Create one and then check out the USER_IND_EXPRESSIONS view!


Was quite surprised when suddenly nearly 8000 indexes sprouted an entry there with only the column name string as the expression: didn't even know that was possible. But yes, that is a DESC index: a special case of a FBI. Live and learn.


And boy: do they EVER break the 10.2.0.2 optimizer! Let's just say the warning is red letters, in the 8.48 release notes. Near the end, well away from the obvious place: "database dependencies". But still there, nevertheless.


One cumulative patch and one special patch later and I'm still fixing custom views that completely broke the optimizer when using DESC indexes!


And then Oracle's marketing gets stroppy when I call their latest software "incredibly fragile and buggy!". Seriously folks: I still have to worry about optimizer bugs, with FBIs and DESC indexes losing query rows in 10.2.0.2? You gotta be joking!...




Anyways: on to bigger and better things. Hopefully.



What else did SSr9 impress me with?



Well, it's hopeless at handling online reconfiguration of disk space - why oh why, Microsoft, did you stick with the "drive letter C:, D:, etc" nonsense? It sucks big time: it stops anyone dynamically reconfiguring a SAN, it stops one from effectively using a NAS for dbs, it frustrates and irritates dbas and sysadmins who have to constantly fight "drive letter starvation", etcetc. So much for HA right there, guys!


It also sucks - BIG TIME! - at space management inside the db. Again, sorry SS folks: but you got no clue about what one can do in Oracle - and db2! - to efficiently manage and handle large volumes of data that are hit by large volumes of updates. SSr9 and all prior releases are hopeless in this field. And yes: "rebuilding indexes" is a VERY POOR solution for anyone pretending to have a HA solution... I won't even go into the incredibly efficient and flexible partitioning in Oracle!


On the other hand: it EXCELLS at simplicity of administration, at ease of management of mixed development/test environments, at setting up multiple environments on each server, at integrating with a large range of other Wintel services. Mostly due to the incredibly good toolset included in the base price. But also due in great part to the very well thought out and balanced feature set.


It integrates so well with the Wintel architecture there is simply no hope in heck of anyone else catching up in this field. Seriously folks: you MUST give SQL Server Integration Services - SSIS - a look. It is so good, it hurts to even think about it: kid you not!


It is also extremely well priced: the base feature set of its various configurations is very well balanced and the EE version is not brain-damaged like Oracle's: it includes EVERY bell and whistle one can think of. Not like Oracle's EE, which will need the partition option - separate cost. Then the OEM/Grid ASM/ADDM/etc tuning paraphernalia - you guessed it: at additional cost. Etcetc: you get the picture...




So, what else is SSr9 good at? Well, I reckon it's a strong candidate for the title of small to medium size businesse - aka SMB - database of choice.


Joel Garry then asks the obvious and very pertinent question in the last post: "what is the definition of SMB?".



Yeah... You suck, Joel! :-)



I've been thinking about it and the naked simple truth is: I don't know! You see, in the universe of db users I don't think there is such a thing as a well defined concept of SMB.


Why?


Well, I'm reminded of the last site I was a remote dba for: three Peoplesoft/Oracle dbs. About 10 SSr8 and MSExchange databases. About 100 users online at any time. Is this a SMB? Peoplesoft is the base of their business and is quite large, their online user base is incredibly small. Their IT budget is HUGE: without the Peoplesoft/Oracle/San/Unix servers, they would be dead as a business.


Then where I work now: a large JDE AS400 db, a couple of small(ish) Peoplesoft HCM and EPM databases, a huge DW in Oracle. Then the rest is all either dev/test Oracle, MSDE or SSr8+9 small production databases and a little bit of Notes sprinkled around. About 4000 users possibly hitting everything. Heaps of cross-db traffic with staging databases all over the place for a multitude of departmental applications. Yet we are a small concern in terms of IT, certainly our yearly budget is. Heck, we have 2 dbas who manage to take care of everything between Lotus Notes to AS400 db2, Oracle 7 and 10gr2, SSr8 and SSr9 and MSDE!


And the place I worked at before: search engine marketing company with three main prod Oracle dbs, around 100 employees around the world, yet pumping, responding and collecting search click traffic - that's right: google, yahoo, altavista, inktomi, etc - at the rate of 20GB in less than 6 hours. All done with commodity servers, Linux 32-bit and SE Oracle! Is that SMB? Well: it resoundly beats very large telco dbs I was doing 6 years ago with VERY BIG iron...


Yeah: hard nut to crack, Joel. But I'll give it a go.



To me, a SMB is a company that can't afford the large - in-house or outsourced - development teams we see in larger concerns. Their database size is really not relevant, apart from the odd abnormal case.


These companies have small development groups mostly concerned with customizing off the shelf applications. And can't afford the incredibly expensive and grandiose "fusion architectures" that suddenly become "frameworks" - the lesser said about those, the better...


Nor can they afford the economies of scale to outsource/offshore everything: their volume of development hardly justifies the small teams they have.


What are the db solutions for these SMB companies then?


Well there is the "proprietary" route, with Wintel as the main solution and of course SSr9 as the main database environment. Any company with a large and established dependency on Wintel desktops will be looking at SSr9 primarily, and with very good reasons for doing so. Sorry mr Oracle but you had your chance in the Wintel arena and you blew it: the integration of SSr9 with the rest of Wintel is so well done and established that your "web architecture" just simply looks ridiculous by comparison. As for "grid", forget it with Wintel: no one wants that sort of complexity in that environment!



Then we have the "open-source" solution. This is the MySQL/Postgres territory. With a large base of development solutions to draw on, from the astonishingly efficient script+Perl+C solutions to the more sophisticated Python/php/ruby/web server. These companies will have small but very technically competent and experienced development groups. They might give Oracle a second look but odds are they'll pick MySQL or Postgres for obvious cost reasons.


And then there is the special case. Like the search engine marketing company above. Quite frankly for that sort of performance requirement there is only one solution: Oracle, backed by a large SAN or NFS/NAS storage solution. They will not have a large development group, so no j2ee there for sure: more than likely the team will be very competent in a specialized field like Perl/Apache/C integration. The results will show incredibly cost-efficient applications delivering amazing performance. But this kind of company is rare and as they say in fishing circles: "there is a lot of water in between them"...



I may be wrong but it'll be interesting to watch the constant "hassle" of Microsoft in the db arena - larger market share growth rate than db2 and Oracle COMBINED, for the last 5 years...


And see how Oracle will respond.


IBM is basically out of this race: db2 is simply a non-event nowadays outside of their own locked-in user base. It's finally showing very clearly in the market share figures but it's been bleeding obvious to anyone with half a brain for quite a while.


Remains to be seen how Oracle will react, but react they MUST! I've got a feeling Microsoft hasn't rested on their achievements and SSr10 - aka SQL Server 2008 - will have a LOT of improvements in obvious places compared to Oracle. It won't take much to start competing with the big O in the serious size/performance arena and looking at what they achieved so far, it's gonna be fun to watch!


Finally: some serious competition. Who knows, the big O might actually wake up and jetison all the "fusion" flights of fancy and start delivering quality and solid software?


Well, that's it! Apart of course from any comments. Hopefully, I'll be able to go back to my usual light mix of photos and technical stuff very soon. Got a stack of new material to show. As well as a lot to revisit, mainly to do with Kevin's incredibly interesting series of posts on SANs, NFS and NAS.



Catchyalata, folks!

8 Comments:

Blogger PaweÅ‚ Barut said...

The problem with Oracle is that if someone uses to many features at once it will not work good. Unfortunately...

Sunday, July 15, 2007 4:46:00 am  
Blogger SydOracle said...

An SMB is easy to define. It's any company that isn't paying for Enterprise Edition with all the options. Like the old adage, if you have to ask the price, you can't afford it, if Oracle's prices are too high, you are not rich enough.

Funny, I realised about DESC indexes and FBIs a week or two back. I ended up pulling the DBMS_METADATA.GET_DDL to work it out (but there is a column somewhere to say asc/desc). I guess under the sheets it is 'inverting' the column to get the reverse ordering (like multiplying by -1 for a number column or switching AD/BC for a date).

PS. The A:/B:... drive lettering is getting bad enough at home. New PC has a built in card-reader. So does my external drive. That's 8 drive letters assigned straight off. Plus A:/B: are still semi-associated with non-existent floppy drives.

Sunday, July 15, 2007 10:56:00 am  
Blogger Noons said...

Pawel:

Oracle has known about the optimizer problems when using new features for the last umpteen years! Since release 8ir3, in fact.

A LOT of users who tried to use the new stuff complained loud and clear since then, about the shaky reliability.

Oracle did preciously nothing about it: instead of alerting senior management to the paltry state of stability, no one had the guts to let them know what was going on. They just pressed on with the new releases.

Now that they are trying to use these same features in their OWN software, it's breaking left right and centre.

Would have been a lot more intelligent if they had LISTENED to those who alerted them, ages ago.

Unfortunately their middle management is too stupid and gullible to understand the problems and the top management is too gutless to take action to correct the problem.

Gary:

Good catch! I only realised it during this upgrade. It was the increase in contents of dba_ind_expressions that alerted me.

I always like to snoop around the dictionary after a major upgrade in Peoplesoft, it's the easiest way of gauging how many potential problems there will be with performance.

Things like comparing: number of columns per index on average, number of indexes, types of indexes, etc.

It's amazing how few new features Oracle's own application software really uses. With good reason, it turns out!

But you listen to Oracle's advocates in the online world and you're a "criminaly incompetent dba" if you're not using ALL the new stuff!

IOW: "we need you to debug this crap for us, so start using it!"...

DBMS_METADATA.GET_DDL is one of the tools I love: I've found it incredibly useful, particularly in trying to extricate details about handling of LOBs and how they are setup.

Sunday, July 15, 2007 5:24:00 pm  
Blogger Joel Garry said...

Ain't I just the little stinker! :-)

Thanks Gary, that's a pretty good SMB definition, succinctly describes a lot of my experience.

Moving out to left field, here's something I'd never do. Somehow it reminds me of Oracle.

Monday, July 16, 2007 8:15:00 am  
Blogger Ranger Rawr! said...

Hey thanks for the camera advice! I really apprecitate it!

Tuesday, July 17, 2007 1:52:00 am  
Anonymous Anonymous said...

Nuno,

Useful series of posts, thanks.

Interesting to hear of the "missing data" queries - do you have any simple recreatable examples or do they depend on large data sets and complex queries ?

Could I interest you and Gary in a copy of Practical Oracle 8i, by the way? Page 115: descending indexes are function-based indexes using one's-complement with a trailing 0xFF.

Regards
Jonathan Lewis

Tuesday, July 17, 2007 7:07:00 am  
Blogger Noons said...

Jonathan:
Glad to know you found it useful.
As for the missing data: it's highly dependent on load, parameter settings, etc.
In Metalink, have a look at cumulative patch 5381980 and the problem description for the bugs it covers. bug 5092688 is the one but this cumulative patch addresses a number of bugs dealing with the various manifestations of the problem.

Yeah, I know about Practical Oracle 8i: you signed my copy many years ago! Haven't read it in years, maybe I need to do it again.

ranger:
pleasure, buddy. hope you find something suitable within your budget.

Tuesday, July 17, 2007 11:07:00 am  
Blogger SydOracle said...

Jonathan,
Well I was saving my money for your next Cost-Based Oracle volume which I assume you are busy working on ?

Also found a reference for a (the?) undocumented function SYS_OP_DESCEND for the descending function:
http://psoug.org/reference/undocumented.html#uods
There's also a SYS_OP_UNDESCEND

Thursday, July 19, 2007 9:20:00 am  

Post a Comment

<< Home