Performance Tuning in SQL Oracle- Performance Tuning Features
by Tosska Technologies sql server performance tuning- Tosska TechnologiesOn 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.
Sponsor Ads
Created on Mar 18th 2020 05:18. Viewed 456 times.