Tuesday, August 4, 2020
dbametrix
More
    Home Oracle Tuning SQL Tuning is Essential prior to deployment in Live Database

    SQL Tuning is Essential prior to deployment in Live Database

    Blog post explains how Application tuning with SQL tuning requires to be done before deployment in LIVE and during the development process

    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.

    - Advertisement -
    dbametrix

    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 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 a 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 seldom are that easy in 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, 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.

    2+
    - Advertisment -
    dbametrix

    Most Popular

    MySQL as PHP database

    The article illustrates the relationship between data and semantics of data stored in a database with reference to the semantic WEB

    Supply Chain Management in ERP

    The article provides detail of what is supply chain management system and what is the importance.

    Business Intelligence Market

    The blog explains how business intelligence is growing in Global market and what is the future of BI.

    Peer to Peer Technology

    The article explains what is the peer to peer p2p technology and how it works and helps to improve your knowledge.

    Recent Comments