Oracle Tuning Approach
Maximizing Availability – Oracle tuning approach
Granting 24/7 access to enterprise applications needs protection from unplanned downtime. This usually requires architecture with redundant components. Oracle offers an exhaustive blueprint known as Maximum Availability Architecture, which includes all the necessary components to eliminate the complexity in datacenters. The following figure illustrates Oracle’s Maximum Availability Architecture:
A big advantage of Oracle’s Maximum Availability Architecture is that it consists of redundant components to improve the efficiency of production systems.
Oracle Real Application Clusters provide protection against server failures and offer more scalability for enterprise applications.
You can use built-in Data Guard technology for disaster recovery. The Active Data Guard allows reporting and backup operations to be offloaded to standby systems.
In an Oracle Data Guard environment, users can be switched between production and standby databases. Version differences of the database and the operating system are always supported. That is, standby database can be upgraded to the next version and users on the older versions can be switched over to the latest versions.
SQL Execution Efficiency
In the designing phase of any system development, you need to make sure that all the developers understand SQL execution efficiency.
As every database connection is an expensive operation, you are supposed to reduce the number of concurrent connections to the database.
As far as multi-tiered applications are concerned, you must ensure that the database connections are pooled.
Maintaining user connections is also important in reducing the parsing activity. Parsing is the process of analyzing SQL statements and developing an execution plan. This process involves syntax checking, loading shared structures and security checking.
Hard parses perform all the operations involved in a parse. They are considered as non-scalable and resource intensive. Soft parses are not ideal. They still require security checking that may consume system resources.
As parsing should be reduced to a great extent, developers must design their applications that can parse SQL statements once and execute them many times. This could be achieved through cursors. Experienced developers should be aware of opening and re-executing cursors. Make sure that SQL statements are shared within the shared pool. To achieve this, you can use bind variables to represent the parts of the query.