As an Oracle resource taking part in development projects I’ve had the change to see how PL/SQL can actually increase the quality of both the application development and in application maintenance. In this article I’ll try to share this experience, and show how using PL/SQL will increase the quality of your application and your Oracle databases.
I used to work as a DBA in the operational part of EVRY. Most of my time I spent working with performance issues and problems. As so many others DBAs have experienced before me, I quickly learned that most performance issues are closely related to the application side. Because of this I got transferred to a job in development when a opportunity arose. My first experience was two folded. I quickly realized that performance tuning is very different from performance troubleshooting. I really needed another kind of knowledge and methods to approach my work. My other experience was how hard is was to get through with my knowledge in the organization. The developers in my department didn’t realize the knowledge I where having, and what I could contribute in the projects. This was really a big disappointment. Instead of contributing to the development projects I was set to writing batch jobs (in bash!!!), and called upon every time a performance issue arose. I was back to working the same way as before.
A couple of years ago I got a new role in Financial Service, EVRY – “Leader of Center of Excellence – Databases”. This was basically the same as “CoE – Oracle”, since our strategic choice of database is Oracle. Now I was working as an architect, being an early part of our development projects. Now I finally had the chance to contribute to the database model – I thought. But once again I got disappointed. A second time I realized that I was not eligible to do a proper job. I lacked the functional knowledge, the knowledge tied to the financial reality. And the time I was given to solve the problem was not sufficient to obtain the necessary functional knowledge. The project excepted me to just built a efficient data model through my Oracle knowledge. The task was moved to the chief Java developer and architect, and I was set to ensure quality when the data model was established. But not being able to work daily with the functional re1uirements I felt my contribution was limited. So basically this was business as usual.
This is where PL/SQL comes in as an excellent way to contribute toward the quality in your project. I recently worked in a project where the data access layer was handled by PL/SQL. We used PLSQL for both inserting, changing, deleting and reading data. The Java code was accessing the Oracle database by calling PL/SQL procedures. Now the chief PLSQL developer, a resource with excellent Oracle knowledge, was forced to participate actively in the very early part of the project, automatically getting the functional requirements needed to understand and develop the data model design. Introducing PLSQL actually forced Oracle competence to contribute more to the development project. The Java developers could concentrate on what they know best – coding the application layer, and the PLSQL developers could work on what they know best – the data layer. Introducing PLSQL into the application design forced the project to include the necessary (but often neglected) Oracle resources.
Another aspect is that I believe the data model should be a living project, and not something developed in the beginning of the project and never changed. As anything else I believe that the data model should go through the same evolution as any other component. Not being able to change the data model is going to show up as a technical depth in the long run. I believe agile techniques and refactoring should also be a part of your data model design. Using PLSQL as the access layer my experience is that it makes it easier to do changes to the underlying data model throughout the project.
We also believe we have accomplished a more efficient way of handling data. Handling financial data often involve parsing big XML or Json documents inserting many records throughout the data model. Using PLSQL we handle this doing batch inserts, and very close to the data layer. Really making the application more scalable. In earlier projects we have seen how – for instance – using Hibernate do not scale when handling similar batch-like tasks.
Yes. PLSQL does increase the need for Oracle competence in a project. But projects often neglect the need for these resource in the first place. My experience is that using PLSQL in the access layer will increase the quality in your database layer worth many times the cost of the PLSQL developer. This is especially the case in enterprise applications where you really need scalability.
Our project in EVRY just went into production, and I’m really looking forward to see how this is going to work out when the use of the application scales up. We already have PLSQL knowledge in our management team, so the delivery into production has been pretty straight forward. For some organizations using PLSQL could lead to a need for another kind of knowledge in parts of the organization. My experience is that these parts usually have a lack of adequate Oracle knowledge anyway. In many organization I’ve been visiting through my career, Oracle knowledge is something that is left alone to the Oracle DBA, doing the regular database maintenance. These DBAs are often maintaining many Oracle databases, having very little functional knowledge of the business requirements. Such organizations are missing an very important part of the Oracle database daily life and quality assurance. Quality is generated in test and development, not in production. And quality is generated closely related to the business requirements, not the database systems. Using PLSQL in your application might force your organization to invest in this kind of knowledge, and will probably increase the quality in development and maintenance a order of magnitude.
Post a Comment