Database Design + Development
Oracle Schema design
Designing the structure of a database is as much a science as it is an art. Beyond the third normal form, you can go as deep in normalizing as you want or de-normalize entirely. The typical decision making points are ease of updating the data, ease of querying and performance. Achieving a balance between these key points is the objective to aim at.
Elevata Incorporated provides 10+ years of experience designing databases for massive applications developed for major corporations in the US and Europe.
Oracle provides a set of tools to optimize the storage and retrieval of data. Should your table be defined as an Index Organized Table? Should you create a surrogate primary key? Should you use bitmap indices? How do you keep your data consistent? Will you use triggers or will you guarantee the integrity of your data in your PL/SQL API?
All these questions are legitimate and there is no perfect answer. Fortunately, you can rely on our experience in this area to guide you through the process so we can design a structure that meets your requirements while offering a good mix of performance and ease of querying. Oracle performance optimization
If you are using Oracle as your database engine, it means that you probably have some serious amounts of data in your database(s). This means you have met the "wall" where the performance is not meeting your needs. Do you have an application that works fine on the development and test servers, where the volume of data is light but deteriorates in Production where the data is voluminous?
There are many reasons that could explain why the performance crumbles when the volume of data in the database is large, but most performance problems can be boiled down to one or many of the following:
- I/O contention: The disks are a typical bottleneck of any database system because they are way slower that modern CPU's. Of course, one can get better performance by using modern arrays of disks that leverage some flavor of RAID aggregation. Maybe Oracle Automatic Storage Management (ASM) could help, but this is a serious endeavor that needs planning and testing. A much less intrusive approach is usually to analyze what the server is doing that is generating the contention. Are we seeing physical I/O's that are out of the ordinary, is it logical I/O's, is the buffer cache too small? Or it could simply be that there is a "hot table" that is consistently used by many queries that is the culprit.
- Tables and Indices definition: If the table definition doesn't allow easy querying for the most used queries in the system, there may be a need to reengineer the structure (if this is a custom application whose source code is available) or to review the definition of indices and their relevance. Oracle implements the Cost Based Optimizer (CBO) to make a decision about the best execution plan to use. To do so, it relies heavily on statistics that are used to generate weights to ponder the potential cost of an execution plan. Could it be a trigger that is very expensive somewhere and is called all the time?
- SQL queries: Writing high performance SQL queries is both an art and a science. Oracle provides a lot of extension to the SQL92 standard, like aggregate functions or recursive queries. SQL Queries are usually the place where we can have a big return on the time invested to rewrite the most expensive ones.
- PL/SQL code: PL/SQL is a very powerful tool to fully utilize the features that Oracle provides. There have been a lot of enhancements made to it in version 10 and 11. However, a lot of code is still written like it used to be in version 7. Also, if we can determine that PL/SQL execution time is CPU based (and not limited by the performance of the SQL queries) it could potentially gain a lot by using native compilations instead of running it in a virtual machine.
There are other reasons why an Oracle database is slow and Elevata Incorporated can help you determine where the bottleneck is and how to remove it.
Oracle PL/SQL development
PL/SQL is Oracle proprietary language specifically designed to leverage Oracle features. It usually runs in a "Virtual Machine" implemented by the database engine or, it can be translated into C and then compiled into a library that will be loaded when the code needs to run if the performance is limited by the CPU.
Elevata Incorporated has extensive knowledge and experience developing PL/SQL packages, procedures and functions that can be used to implement integration processes to load data or export data. Another area where PL/SQL shines is in the implementation of an API that external systems can use to interact with your data (for instance, an application server or a reporting server) while guaranteeing that nobody will be able to manipulate your tables directly. This allows you to be certain that your database will not get corrupted because different systems are trying to access your data directly.
Elevata Incorporated's philosophy is to always protect your data against potential corruption. Your data is valuable and will always be there, no matter what database engine or application programming language you use. Who knows what the next major technology shift will be in five or ten years from now? Building a PL/SQL API to enforce the integrity of your data allows you to be sure that your business will be able to accommodate any technical change coming in the future.
We always recommend our clients that their Oracle database be built to enforce data integrity rules via a public API and not rely on any external system to guarantee the integrity of the data. Systems come and go, are modified or upgraded and what is working today may not work tomorrow. Also, no external system knows better than the people developing your database how to best access your data and how to write the best performing queries.
With data security compliance needed in many industries, shielding your data from direct access allows you to implement transparent encryption/decryption of sensitive data. You can thus centralize the security enforcement and all external systems using your public API are automatically able to follow your rules, after successful authentication.
We can assist you with defining, developing and testing a PL/SQL API that will allow you to safeguard the integrity of your data, guarantee the best performance for your database (no external system is allowed to run its "own queries" and they all must use the public API) and enforce the data security policy of your choice.
Oracle Data Cartridge development
The Oracle Data Cartridge is a mechanism provided by Oracle to implement your own functions that provide services to the system beyond what Oracle provides natively. It is also a very powerful way of providing high performance implementations of a very specific algorithm that you need your database to have access to. You can also use this mechanism to define new data types to use in your database.
The code implementing your data cartridge is usually written in C and is compiled natively into a library (a .dll file on Windows or a .so or .a file on UNIX.) This library is then loaded into an Oracle process and can be called from PL/SQL to run your specific algorithm.
A very good example of a data cartridge is the implementation of an algorithm that uses the Fast Fourier Transform. You can choose to implement all of this code yourself or (as we would recommend in such a case) you can use a library that is available to leverage the tremendous computing power available in today's CPU's. For instance, Intel Corporation provides highly optimized Math libraries that leverage the multi-core architecture of Intel CPU's. See for instance http://www.intel.com/cd/software/products/asmo-na/eng/307757.htm
You could use the library written by Intel engineers to handle your FFT needs, since it has been already optimized and validated, and focus on implementing your proprietary algorithm in a data cartridge. Then, you could get access to your algorithm directly from within Oracle.
Elevata Incorporated can help you with the architecture and development of your Data Cartridge so that it can be used inside Oracle. There is no need for you to give us the details of your proprietary algorithm as we can work with a public API to call your code.
|
