Performance Tuning in SQL Oracle- Performance Tuning Features

On the execution of an SQL statement on an Oracle database, the query optimizer concludes the most efficient execution plan after many factors related to the objects referenced and the conditions given in the query. Determining this is a crucial move in the processing of any SQL statement and can hugely impact execution time.

If not done well, it can reduce the performance of the database and then you will need Oracle database performance tuning to improve its performance. During the evaluation process, the query optimizer reviews statistics collected on the system to find the best path for data access and other factors. You can override the execution plan of the query optimizer with hints inserted in the SQL statement.

Effective data collection and its analysis is crucial for identifying and improving the problems related to database performance. Oracle database offers various tool that let a performance engineer to collect information related to the database performance. Besides collecting data, the Oracle database offers tools for monitoring performance, tuning applications, and diagnosing problems.

The Oracle Database monitoring and collecting attributes are mainly automatic and managed by Oracle background processes. In order to enable automatic performance features and automatic statistics collection, the STATISTICS_LEVEL initialization parameter must be set to ALL or TYPICAL. You can manage and display the outside of the tuning tools and clustering with Oracle Enterprise Manager Cloud Control (Cloud Control), or with views and APIs. For ease of use and for availing benefits of its various diagnostic and automated monitoring tools, Cloud Control is recommended.

In this article, we are mainly going to discuss the automatic performance tuning features of the Oracle database. So, let’s get started with the same-

1. The first thing you must know is that AWR, known as Automatic Workload Repository collects, maintains, and processes performance statistics for self-tuning and problem detection purposes.

2. Automatic Database Diagnostic Monitor (ADDM) interprets the data collected by AWR for possible performance intricacies with the Oracle database.

3. SQL Access Advisor enables a quick and efficient technique for optimizing the SQL statements without the need of modifying any statements.

4. SQL Access Advisor gives recommendations on materialized views, materialized view logs, and indexes.

5. End to End Application tracing determines whether there’s an excessive workload on the system by a specific service, user, or application component.

6. Server-generated notification automatically sends notifications when approaching problems are encountered. You can discover how to control the database operation with server-generated alerts.

7. Additional advisors that can be launched with Oracle Enterprise Manager Cloud Control (Cloud Control) like memory advisors for optimizing memory for an example. The memory advisors are generally used when automatic memory management isn’t set up for the database. Other advisors can be employed for improving mean time to recovery (MTTR), undo tablespace settings, and shrinking of segments.

8. The Database Performance page in Cloud Control displays instance service time, host, and throughput information for diagnosis and real-time monitoring. You can set the page for refreshing automatically in selected intervals or manually.

With this, we hope you are contented with the automatic performance tuning features of the Oracle database. In case you are looking for SQL performance tuning for Oracle, you can get some amazing tools based on artificial intelligence such as the one provided by Tosska Technologies that lets you perform several databases’ functions simultaneously.