The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

Oracle - How Quaint!

Quaint

I have to do some work on Oracle DB, coming from a MS background.

Does anyone else find the Oracle.com site a) confusing with dozens of links over every page not to mention a bit drab and corporate. b) Very slow.

Oracle seems to have command prompt tools and requires little config files and updates to PATH variables – all a bit 1990’s.

The customer DB I need to investigate is really slow, however not very big < 3gb but the performance is pathetic. I can find any equivalent of query analyser, to view execution plans etc.

I believe Oracle can be viewed using SQL Management Studio, anyone have any experience or pointers to setting this up?

I seem to need linked servers configuring, which seems to need an oracle client, which seems to be and ODBC client, which transports me back to 1990 and PATH variable configs, I expect to see .BAT files soon?
Anton Send private email
Wednesday, June 04, 2008
 
 
I agree. I don't have much Oracle experience (only one of our clients use it) but I can tell you that it is quite painful compared to MySQL, Firebird, DB2, and MSSQL. The web-based tools are a pitiful excuse for an administrative application. The database often doesn't even start when Windows restarts (forcing users to send a start command manually or through a script). And the whole "empty strings are considered null" fiasco has caused us a lot of headaches.

I'm convinced that Oracle has made its products very hard to support in order to increase their revenue on services and to create a need for highly trained DBA's to administer it. That strategy probably worked back in the 1900's but doesn't work that well today.
dood mcdoogle
Wednesday, June 04, 2008
 
 
For analysing individual statements, you can use SQL Developer (http://www.oracle.com/technology/software/products/sql/index.html).

For looking at the overall performance and workload, the web-based enterprise manager is the easiest tool to use. Your DBA can supply the URL.
Alistair Wall Send private email
Wednesday, June 04, 2008
 
 
You might want to look into TOAD for Oracle, a feature rich GUI and packages some of the more common DB tasks to make it easier to get upo to speed.
Chris Hulan Send private email
Wednesday, June 04, 2008
 
 
1995 called, it wants it's Oracle client back.
sensible
Wednesday, June 04, 2008
 
 
oracle.com is the marketing site

otn.oracle.com has the technical documents.
go to documents and download the oracle docs.
Contractor
Wednesday, June 04, 2008
 
 
oracle has their own free gui tool for using oracle called sql developer. its built in java. you can download it from otn.oracle.com
Contractor
Wednesday, June 04, 2008
 
 
I find most web sites for large corporations to be confounding. It is almost as if there is an entire class of companies whose main concern is to make it impossible to find any useful information about the companies products or services, much less actually purchase said products of services. My best guess is that it has something to do with companies that only interact with their customers through a highly paid sales force: maybe the sales division, paid on commission, influences the marketing/PR division to make the website as unhelpful as possible in order to force all contact with the company to go through the sales force, in the hope of generating more commissions.
Jeffrey Dutky Send private email
Wednesday, June 04, 2008
 
 
otn.oracle.com is a very good technical site. You have to get used to it. As I said there are links to documentation by version and product. So you can download all the docs. The based Oracle DB has about 20,000 pages of documentation. Its well organized. There is a tabbed web page you can use to find the docs you need to read.

The oracle docs are very good.
Contractor
Wednesday, June 04, 2008
 
 
Real programmers use the command line (:=).

Seriously, if you have to use Oracle, just accept it and let it go. From my perspective of having used it for 10+ years, it straddles the line between mainframe and more "modern" type of apps, which can be useful.

I find it to be a Swiss Army knife kind of tool. The lexical focus of the toolset did make dynamic programming much easier, which was invaluable for me at the time. YMMV.

Funny, when I did some work with MSSQL, the nice GUI made it seem like a toy to me. Of course I know it isn't, but your perspective depends on where you're viewing from.
Steve Hirsch Send private email
Wednesday, June 04, 2008
 
 
Um.

Oracle is not a joke.  Configuring and tuning it properly requires non-trivial skills and expertise.  If you're seriously considering using SQL Management Studio for these tasks, I'd take that as an indication you're in way over your head.  Do yourself and your client a favor and recuse yourself now.
curious yellow
Wednesday, June 04, 2008
 
 
Perhaps I should add, in the spirit of offering more practical advice: google "Tom Kyte" and read everything the man has written.
curious yellow
Wednesday, June 04, 2008
 
 
1. Have a look at SQLDeveloper, which you should be able to download from Oracle's web site.

2. Once you get past disliking the differences, have a look at _Optimizing Oracle Performance_, Millsap & Holt.

Finally, as Steve Hirsch says, a lot of it is about what you're used to. I could rant on and on about the deficiences of Transact SQL as compared to PL/SQL, but mostly I sigh, grind my teeth, and write the code.
George Jansen Send private email
Wednesday, June 04, 2008
 
 
SQLDeveloper is very nice and reminds me a lot about SQL management studio.
Martin Schultz Send private email
Wednesday, June 04, 2008
 
 
>> entire class of companies whose main concern is to make it impossible to find any useful information about the companies products or services, much less actually purchase said products of services

It's that whole 90s (and older) mentality.  Why give out the info for free when you can sell $5,000 training classes, or training manuals.

Why give out sales info online when they can have you "call one of our representatives to get a custom quote".

Both practices I despise, and the second one usually means I won't even consider your product.  The last thing a techie wants to do is get fed a bunch of kitchen sinks by some lughead sales guy.
TravisO Send private email
Wednesday, June 04, 2008
 
 
Well, on the topic of websites, if I want to find tech docs on Microsoft stuff, and I'm not jumping to it through the help in Visual Studio, I can find that a bit tricky sometimes too.

I've administered (installed, configured, run, built, stoppped, started) Oracle on a few different versions.  I always found it OK on Unix, but I think that's a Unix thing.  As in, I don't think it transplanted very well to Windows, which is what I guess you're using in this case.  It's by no means unique in that sense though.

But serious systems require serious Oracle administration skills, far more depth than I ever needed to keep little dev environments going.

And Tom Kite is the man, use asktom.oracle.com for reading lots of his stuff.  If your DB already sucks at 3Gb, a couple of suggestions from my experience (again, on Unix I'm afraid).

1.  If you had a generic build script, you might well have all your indexes in the same tablespace as your data .  Get them in a separate one, which should be on a separate disk.  Having index and data on the same physical disk really sucked even on a powerful Solaris box, moving them to seperate disks was a revelation. (ALTER INDEX REBUILD)

2.  If you're using CBO (Cost Based Optimiser), which you almost certainly will be if your system is based on Oracle 10 or newer (Rule Based Optimiser long since deprecated, possibly even gone by now), make sure your statistics are up to date, and are kept up to date. (DBMS_STATS.GATHER_SCHEMA_STATS I think)

I haven't actually done this for a few years so any Oracle guys reading this are probably laughing their asses off now.
Fru.T.Bunn the programmer
Wednesday, June 04, 2008
 
 
"If you're seriously considering using SQL Management Studio for these tasks, I'd take that as an indication you're in way over your head. "

Spoken like a true Oracle DBA trying to justify his high billing rate. Seriously, there is no reason why the command line should be required to configure Oracle just like there is no reason why the command line should be required to configure Linux. It is an indication of a company and people who are trying to raise the barrier to entry in order to hold on to revenue streams from support and training. There is no excuse and trying to give one simply gives away your position.
uggh
Wednesday, June 04, 2008
 
 
" If you're seriously considering using SQL Management Studio for these tasks, I'd take that as an indication you're in way over your head.  Do yourself and your client a favor and recuse yourself now."

So we should not use these modern IDE's to develop software and such, which increase productivity, among other improvements and drop back to vi. WTF kind of logic is that.
Jeff Hawkins
Wednesday, June 04, 2008
 
 
"So we should not use these modern IDE's to develop software and such, which increase productivity, among other improvements and drop back to vi. WTF kind of logic is that. "

Oracle is immune to productivity.  Oracle or productivity, choose 1.  Also, if it's merely amazingly slow, there's problably not any improvement to be had.  If it's unbeliveably slow, taking multiple seconds on simple queries on a small DB, you might be able to squeeze a few percent out of it.

But, hey, "it scales well", or so the true believers say.
Skorj
Wednesday, June 04, 2008
 
 
Skorj - are you really suggesting that Oracle just can't do it ?  I don't argue against the suggestion that getting it to fly can need the right training and some real DBAs, but it can definitely rock.  I've seen it working on a 5 terabyte system and the performance was spot on - as it was in much smaller systems after production DBAs had been let loose on it.

This was in a top 5 i-bank though, where they thought nothing of paying top wack to get the best people.  Which is what it can need, but if you have them, there's no question it can whip along.
I promise you it works.....
Wednesday, June 04, 2008
 
 
In the days of 486 and Pentium Pro (200 MHz) computers we were able to scale a traditional client server app with Oracle on NT (quad processors, 4 gigs ram, 10 disk drives in an array) which could handle 1500 simultaneous users in a TPC-C like application.  (OLTP, but more data rich than TPC-C)  We didn't need to hire any expensive consultants etc.  We just read the manuals and followed the recommendations there. (use bind variables, reuse cursors, etc.)  In those days you had to buy the manuals and they weren't cheap. 

Soon after that Oracle put the manuals on the installation disk as PDF and HTML files.  Now you can down load the very same docs from Oracle (otn.oracle.com) for free.

We could have gotten more users on the machine but NT had a thread limit and so about 1,500 users was the limit since each user got their own thread. (we were not using Oracle's MTS)

Oracle scales quite well even on MS Windows platforms.  Yes, you have to read the manuals and understand what you are doing.  It is different in that way. 

There is a youtube video somewhere showing someone installing Oracle on a Unix platform with their hands behind their back, using just their nose.
Jim Send private email
Wednesday, June 04, 2008
 
 
There is something seriously wrong if a 3GB oracle database is slow. We have a 1TB oracle DB running on sunfire with excellent performance. Of course we have experienced DBAs for admin. More importantly we have a developer DBA who sits right with us (the .Net devs) for DB design and writing efficient stored procs.

As for tools get the free SQL developer. Being a Java tool the fonts look awful on a high res lcd with cleartype. But if you have money get Toad.
dot netter
Wednesday, June 04, 2008
 
 
For the record: when I questioned the OP's idea of using SQL Management Studio, it wasn't because it's a GUI tool.  It's because it's a Microsoft tool designed for use with Microsoft's SQL server.  As a further clarification, I have nothing against either MS or SQLServer.  I'd have thought that these are different environments best served by different tools seems so obvious that it didn't need stating.  I was wrong, apparently.

There are tools (both for-pay and free) that are far superior for working *with Oracle*.  The fact that the OP hadn't demonstrated that he'd even considered investigating the Oracle ecosystem on his own, and instead asked about mixing apples and oranges, indicated to me that he was in over his head.
curious yellow
Wednesday, June 04, 2008
 
 
<time to tick off some people>
Waaa, the pedals on my new car don't work the same as the pedals on my mountain bike.  They are both pedals, why don't they work the same??!!!
</time to tick off some people>
It is NOT the same thing as Sql Server,UDB, etc.  Stop expecting it to be.  Oracle is not something you can pick up in an afternoon.  It has become very easy to install but to run a production system on it does take an experienced DBA.  They charge a lot because, and i know this will come as a shock to some, the market will pay that.  If anybody could do it then the price would reflect that. It really does take a lot of training to become good at the job of a DBA. (not just oracle)  There are built in tools to examine the performance of a query.  There are even built in tools to determine which query is taking up the most resources.  The formated output tells you a lot but the unformated output tells you even more IF you take the time to learn how to read it.  (does the LIO count exceed the PIO count?  Is that good or bad?) 

Do you know who designed the database?  Was it a developer or an architect?  (and boy, can you tell the difference quickly.)  Why do you think it is the platform and not the design or the code?  I can write crappy code that will get the job done eventually but the code can also be written to be much more efficient and speedier.  If the statistics aren't up to date on all tables in the query then things will be slow, as stated above.  (I disagree with the move the index's to a different tablespace for PERFORMANCE.  Yes, they should, IMHO, be in a separate tablespace for managablity but since an index is accessed BEFORE the data in the table and not at the same time it does not add a performance load.  And unless you are the only one using the database, the disk is going to rotate anyway.

Running oracle well is a skill and like all skills it needs to be learned.  There are plenty of resources on the web that will help but you can no more just install it and expect ultimate performance than you could expect an off the lot car to outperform a tweaked and tuned roadster.
Me2 the Sql Send private email
Wednesday, June 04, 2008
 
 
I found Embarcadero's DBArtisan an invaluable tool when I had to deal with Oracle databases. It's a pricey little bugger, but I dare say it was worth it.
The Original Henry
Wednesday, June 04, 2008
 
 
To get an insight in your database, I would use ModelRight (http://www.modelright.com). You can quickly reverse engineer any Oracle database and see how tables related, constraints, indexes, any physical properties, etc. I find ModelRight's Oracle support to be excellent.
oracle user
Wednesday, June 04, 2008
 
 
"than you could expect an off the lot car to outperform a tweaked and tuned roadster."

But 99% of us don't need a tuned roadster. We just want a car off of the lot which is usually more than adequate. So it should come as no surprise that most people would scoff at a car that was aimed/marketed at 99% of the population but required you to take hundreds of hours of lessons just to learn how to drive it reasonably well.
uggh
Wednesday, June 04, 2008
 
 
"Top Ten Mistakes in Oracle": http://download-uk.oracle.com/docs/cd/A97630_01/server.920/a96532/ch2.htm#13670

Failure to use bind variables is a real big one. Undersizing the redo logs is another -- pretty common.

Run the Automatic Workload Repository reports and find out what's happening in the system. The level or instrumentation in the Oracle kernel puts every other RDBMS to shame, and AWR will tell you exactly where the system time is being spent.

And stop whining -- the Oracle community is full of people very willing to help those who will just go to the forums at Oracle Technet and ask for guidance.
David Aldridge Send private email
Wednesday, June 04, 2008
 
 
1) If you can find a better solution than Oracle go for it!  But I doubt for large serious scalable apps you will.

2) If you can't find one and you still don't want to go with Oracle then create your own solution and make millions.  Or sell it to Oracle and make millions.

Remember, if you encounter an obstacle it is very likely others have too.  That means you have a potential market needing a better mouse trap.
Paperclip
Thursday, June 05, 2008
 
 
Guys if Oracle sucks so bad then go makes something better.  This is an incredible money making opportunity!
zoloft
Thursday, June 05, 2008
 
 
Interesting to note how Microsoft is stressing the benefits of command line interfaces for administration in Windows 2008 and SQL Server 2008.
Arethuza
Thursday, June 05, 2008
 
 
All I can say is that you are doing something very strange with Oracle. I have 16 installations, databases up to 22 GB. Smallest installation has 30 users, largest around 350.
Nothing special about them. I just installed default database, allocated tablespace for data, create tables for my app and run.
First few months I checked ADDM once a week and tweeked parameters according to suggestions.
All installations are on 32bit W2K3, 2-4GB RAM, servers with single P4 or XEON. Really nothing special.
I even have RAID5 on each instalation, which is considered a nono for large databases (slow writes).
moronica
Thursday, June 05, 2008
 
 
Programmers are the most conservative people in the world.

If you're having difficulty, repeat this mantra, it will soothe your soul:

I don't care which billionaire I make richer, I don't care which billionaire I make richer...
Steve Hirsch Send private email
Thursday, June 05, 2008
 
 
You are getting some good advice in the other responses.  Without disparaging any of that advice,  I'd like to look at the problem you state from a slightly different perspective.  Before trying to analyze the behavior of the database under load,  I suggest finding out more about the database itself.  Here are some questions to start with:

What version of Oracle are you using?

What tool do you use to access the database?  Even if it's just SQL,  there's plenty you can do with SQL queries on the Data Dictionary tables, if you know how they work.

How many schemas are there in the database? 
How many user tables are there in each of these schemas?
How many columns are there in each of these tables?
How many rows are there in each of these tables?
How many indexes are there on each of these tables?

Can you construct a database diagram by reverse engineering the database itself?  There are some spiffy tools for doing this out there,  but you can do this with an office tool like MS Access,  if you can set up a datbase connection and  some table links,  and are willing to do a little work setting up the relationship diagram.

The reason I focus on these items is that original database design sometimes results in performance nightmares.  This is especially true when the designer was ignorant of fundamental database design principles,  and was guided by false mythology about what kinds of designs would slow down the RDBMS server.  I'm not saying this was the case here, but as other responders have said,  "something is wrong"  when a 3Gb database runs too slow.  It's worth checking out the fundamental design before investing a lot of effort at finding and fixing bottlenecks.

By the way,  how slow is too slow?
Walter Mitty Send private email
Thursday, June 05, 2008
 
 
"Seriously, there is no reason why the command line should be required to configure Oracle just like there is no reason why the command line should be required to configure Linux. It is an indication of a company and people who are trying to raise the barrier to entry in order to hold on to revenue streams from support and training."

You're so right. I mean, all those wonderful GUI tools for Linux administration out there that Linux Inc. won't let you use.

Oh wait. That's right. There's no single company deciding what you can and can't use.

Okay, maybe it's the fact that the people who *could* write tools for Linux system administration *already know* how to administer Linux systems, so they don't need GUI tools. Yeah, that sounds a bit more likely.

Maybe with Oracle you have point. I'm sure they balance the lost support revenue from better tools against the lost sales revenue from more people wanting to buy their product because of the tools.
Drew Kime Send private email
Thursday, June 05, 2008
 
 
I don't follow this "no gui's so support costs increase", when applied to a company that gives out Application Express and SQL Developer. Not trivial products at all.
David Aldridge Send private email
Thursday, June 05, 2008
 
 
I remember back when the people wanted to make computers and computing more reliable, more robust, and more friendly.  Somewhere along the road over the last 10+ years all of this went out the window.

Kind of coincides with the death of serious computer science programs and their replacement with watered down CS for anyone programs.  It's difficult to make things reliable, robust, and usable.  I don't think the hordes of "me too" programmers have it in them today so they stick with the old school stuff.
reign
Friday, June 06, 2008
 
 
"Yes, you have to read the manuals and understand what you are doing.  It is different in that way."

Isn't that asking a bit much, to understand what one is doing?

...and yes, I am joking - I just found that sentence to be totally hilarious :-)
El Dorko
Friday, June 06, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz