Abstract words like "mainframe", "Oracle", "Sun" with whatever technology "X", "Y", "Z".

What's left concludes to ... leadership,management failure.

Oracle is the best Database engine available. Please blame yourself not the software you are using. The perfomance of some of our applications (still using 8.1.7) is great. As Tom said you can write a good or a bad application. It is you not the database to be blamed.

Hi Mohamed,
Like you I'm working (since 3.5 years) in a bank migrating a main frame application (cobol/db2) to sun/oracle environment (the project started in 2001).

When we brought the application in production (3 years ago), we started to migrate 2% of the volumes (i.e. number of transactions) and we painfully succeeded to migrate 5% of transactions during the 6 next months. Since 2.5 years we were trying to migrate 30% more of transactions. And because of a lot of previous decisions, we failed until one week ago.

Yes it took us 2.5 years to realize that we were misusing the tools and frameworks that architects (gone since a long time) selected for the developers. The grand idea was: mask as much as you can (technology, database design, components integration, EAI...) from developers so that we can take juniors and they will *easily* code what you want.

You can imagine the result: a poor application slowed done by a "slow" component: the Oracle database.

You can not imagine how wrong those architects were: in 2.5 years we've almost rewritten a new application by taking into account what was the bottleneck: the database.

As the DBA leader I can tell you that the developers didn't know how to use an Oracle database and more precisely how it worked. I guess the books Thomas Kyte has written helped me and us, so I may thank you Tom for all the good sens you put into what you write -- your work is just amazing and when I talk of you to my bosses, they are astonished that you help the DBA community on asktom.oracle.com for free ^_^;

By the way, the key points were:
- to understand how the Oracle database works (indexes to retrieve a little amount of data, a full table scan for a lot of data, explain plan and also understand how to write efficient queries (data-set query when needed), learn that external tables exist and learn how it works, learn about stored procedures, learn the impacts of giga bytes of data sent over the network between the database and the application servers (Lobs of 1Mb to 10Mb have just been eradicated, we limit there size to less than 4Kb), learn the power of bind variables and soft parse vs hard parse, learn how to get performance indicators using statspack, learn how to treat bottlenecks: I/O bound => fast disks or bigger buffer cache, CPU bound => tune queries to decrease logical I/Os...)
- and more generally we learned to think before to code, to challenge users needs (no need for you user to retrieve 2,000,000 rows on your client application to perform aggregates for reporting, just ask us to develop a new screen where we'll display data aggregated by the database)

As Tom mentioned earlier, don't blame the tool, blame the person misusing the tool!

Don't blame Oracle, blame the way you use it!

Oh and know that the migration of 30% more transactions just surprised everybody (users and big bosses). This one was such a success that we must now migrate the other 65% of transactions until end of may... and we will thanks to Oracle!

Loïc

Since I have bought the two Tom's book and the two Jonathan Lewis books, I realised that I have finally find the light that will show me the correct way towards successful oracle applications. I have two examples to show in this context. The first one is about the ora-01555 rollback segment snapshot too old error. When I first read Tom's book ,'Oracle Expert' that a commit inside the loop is the main reason of this error, a simple look in our plsql code showed that there was absolutely no commit outside the loop. They were all inside loops!!! It's then easy to explain why our migrated applications are encountering the ora-01555 error in our overnight UNIX jobs.

The second example is related to the advice given by our technical superiors in order to avoid the ora-01555. We have been told to alter the session and use an explicit big rollback segment. This advice was given to all developers that were committing across fetch. This explicit set made the big rollback segment largely overused so that one day all our overnight jobs failed with the error ORA-1650: unable to extend rollback segment. Why have we been given this advice? Did they know that the ora-01555 is occurring during a select process while the explicit set of big rollback segment is for update/delete/insert operation?

If today, unfortunately too late after the migration, I can easily avoid the ora-01555 without instructing my transaction to use a big rollback segment, then it is mainly due to the books mentioned above.

Of course, I blame my self and not the data base. But you can not imagine how it is to hard to implement what you have correctly learned when you have architects and superiors ignoring your recommendations because 6 years before you have been hired as a simple developer.

Mohamed

Mohamed,
I know how you may feel: disappointed by what statspack tells you and which decisions may be taken by your boss or achitects; to see how wrong the project is going and being alone to see the thruth.

However, what I've learned is -- when dealing with a precise problem being related to performance, stability... -- to digg as much as you can, to get knowledge by reading books then to get enough information (through statspack, vmstat...) to expose the problem in a simple chart ala Excel.

In fact I've been amazed of the power a simple chart extracted from a tool like SAR (that gets CPU related information for a Unix server) can change the project. Hence, I'm now pretty good at extracting data that I need from statspack tables in order to show (for example) how many logical reads are performed per hour and translate these into a simple understandable unit: a blue ray DVD (50Gb). "Oh our main -- refactored entirely 3 times -- process has required 5 blue ray DVD of logical I/Os to finish today!" or "During our last one-hour-test, we've generated as much deadlocks as in production... during 5 months." ;)

As an example, one incredible meeting just changed our approach to load files into tables when I just mentionned that statspack said: regarding our 7Gb database, we needed 7,000,000+ queries, 10Gb of redo logs... in order to load a 40Mb file into the database. (You may read this very helpful article: http://tkyte.blogspot.com/2006/10/slow-by-slow.html , and that was me who posted the last comment)

Finally, be patient, 2 years were necessary for me to make boss and developers realize we were taking the wrong way. This was achieved through SQL and Oracle internal mechanisms trainings. And of course, one success after the other proved that the database was a component that if properly used was very robust and can be trusted for critical projects.

Cheers!
Loïc