Mohamed -- Thanks for the question regarding "Main Frame applications versus oracle applications", version 8.1.7
Submitted on 17-Feb-2008 13:28 Central time zone
Last updated 18-Feb-2008 12:05
You Asked
Hi Tom,
Since more than 6 years I am working in a bank migrating main frame applications (cobol/unisys) to oracle/sun environment.
All the applications we have migrated are instable, less performant and hard to use. We have done such a miserable work that people from non oracle teams, those working on mainframe applications, are very disapointed by the oracle data base
Could you please say few words about this situation or give me examples of successful applications built using oracle data base.
Thanks
and we said...
well, you are using one right now.
Or the software the many people use to do payroll, HR, financials, CRM, whatever. We have hundreds of thousands of customers using applications in a big way..
I will pick up on one sentence you wrote:
... We have done such a miserable work that people from non oracle teams, those working on mainframe applications, are very disapointed ...
I was surprised how that ended. Why would they be disappointed in Oracle?
I can write very bad, unstable, slow mainframe applications.
I can write very bad, unstable, slow applications on Oracle/sun.
I can write excellent, stable, scalable, performant mainframe applications.
I can write excellent, stable, scalable, performant applications on Oracle/Sun.
It is curious that you are blaming software - when you programmed it.
I'm curious
On this mainframe migration - did you just attempt to "port" (move) code from one platform to the other, without looking at it (eg: SUN != MAINFRAME, ORACLE != VSAM). I'd guess 'yes, we used microfocus cobol on unix and created tables for vsam files'
Did you undergo training - would you call yourselves novice Unix/Oracle people or expert. Did you hire an expert?
Did you use the same mentality on open systems as you did on the mainframe (change control, code reviews, DESIGN to be stable, available, scalable). Or, because it was "open systems" and flexible, you just whipped it together.
I often (as in 100% of the time) find that when people say:
... All the applications we have migrated are instable, less performant and hard to use. We have done such a miserable work that people from non oracle teams, those working on mainframe applications, are very disapointed by the oracle data base ...
the root causes go back to - well - the implementation.
I read "migrated" and envision "move code, change as little as you can"
I read "less performant" and think "we didn't really size our system, we just bought what we could afford AND we did the prior bullet point" - meaning you'll never scale - you just did "database X" processing in database Y
I read "instable" and think "we didn't really know our environment really well, not like the mainframe where we have been living for 20 years, and didn't know how to do things the best way there"
I read "hard to use" and think "we did the mainframe on open systems, and it is ugly"
This is chapter 1 of my last book - it describes what I think you need to do in order to have a stable, performant and so on...
http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D109970032738
5695287&p_cat=5300Ch01_prefinal-td.pdf&p_company=822925097021874 There are thousands of successful Oracle applications.
very few words ...
February 18, 2008 - 1pm Central time zone
Reviewer: A reader
Abstract words like "mainframe", "Oracle", "Sun" with whatever technology "X", "Y", "Z".
What's left concludes to ... leadership,management failure.
Don't blame Oracle....
February 18, 2008 - 1pm Central time zone
Reviewer: Harry from GA, USA
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.
What a coincidence!
February 18, 2008 - 5pm Central time zone
Reviewer: Loïc from Paris
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
I blame my self and not the data base
February 19, 2008 - 8am Central time zone
Reviewer: Mohamed Houri
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
An eye for an eye, a tooth for a tooth
February 19, 2008 - 5pm Central time zone
Reviewer: Loïc from Paris
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