Application tuning with SQL tuning requires to be done before deployment in LIVE and during the development process.
Many performance problems are caused by bad SQL. Make it a point to integrate tuning into your application development life cycle. Tuning your code is necessary at least at two distinct stages: during and after a program unit is developed, and during the final integration, prior to deployment.
During development, the developer writes the code in a manner he or she presumes is functionally correct. As long as the code looks and feels right, and as long as it retrieves or performs a DML operation in the correct number of rows, it is acceptable. Few developers make the extra effort to really perform any kind of performance check. And even fewer make the effort to analyze whether the code is prone to breakage and failure and whether any steps can be taken to prevent, detect, and repair problems in code. Repairing code breakdown at runtime requires a detailed understanding of the environment in which it will be run so that anticipated and unanticipated runtime errors can be repaired ad hoc in a manner that prevents or minimizes downtime.
If the development efforts are targeted toward a custom application for a specific environment, it is always easier to take into account possible areas of failure specific to that environment and try to deal with them. However, developing a generic application that would run at many sites, some of which may require high availability access, is always more difficult. Even then, the best efforts need to be taken to determine as many areas of failure as possible along with ways of circumnavigating them. Integrating such preventive actions into the code development process may increase the time taken to develop each module. While developing for 24*7 site, however, it is generally well worth the extra time in the medium to long run.
Tuning of individual program units can be accomplished by using Oracle utilities like explain plan, SQL trace, and tkprof. It is generally better to have a peer review of code where someone other than the original programmer is designated to be the reviewer. Too often when programmers are asked to review their own code they don’t, as they are too busy or they know they write good code.
Every single program unit may be tuned and run reasonably fast. When all the modules are integrated, however, a few unpleasant surprises may be revealed in terms of the shoddy performance of the application as a whole. This is frequently the result of enthusiastically deploying the application and then running the application only if the performance is unacceptable. Why fix something if it is not broken? This maxim is the main reason given by such optimistic developers. They feel, rather than make the development and deployment process more complicated or time-consuming, it is easier to just deploy and run. Unfortunately, however, things are seldom that easy on a high-availability database site.
Tuning the application after deployment may, in turn, increase the complexity and downtime; and, at times, it may not even be possible due to users actually using the application, and the chunks of downtime required to tune and repair it may not be available. In other words, trying to fix the problem after the pain is felt may be too late. A proactive approach is almost mandatory to anticipate, understand, and resolve performance and availability issues. Accordingly, a pre-development integrated tuning approach is always recommended.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
