Database Performance Tuning

What are Some Common Things that Make the Database Performance Tuning Slower?

Database performance tuning is the process of optimizing the performance of a database. The goal of performance tuning is to minimize the response time of the database. This can be done by optimizing the database design, optimizing the SQL queries, and optimizing the database server configuration.

In spite of its prevalence, database performance tuning remains one of the most crucial tasks that any DBA must conduct on a regular basis. Databases should be fine-tuned on a frequent basis, as recommended by specialists, to ensure optimal performance.

Tuning Steps

Finding the source of the issue is the first step toward fixing it. The OS, the database, memory constraints, etc., are all places that could be the source of the issue. Once an issue has been found, it can be fine-tuned for optimal performance. The topic of performance tweaking is the exclusive emphasis of this blog.

Oracle has provided DBAs with a set of tools to aid in problem diagnosis and Database performance tuning troubleshooting. Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR) reports are two such tools; they can be generated for the problematic time period and used for investigation. Look for skewed or adjustable parts using the tools provided.

Top Timed Events in AWR reports

With AWR, you may produce a report that provides a statistical overview of the instance’s memory consumption. To generate an AWR report, run the following file in a sqlplus prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

It uses a saved template to produce an HTML AWR report. Once the AWR report has been prepared, the trouble spot can be seen immediately in the Top Timed Events section.

ADDM tuning Session

After receiving a report of an issue, a DBA’s usual course of action may be to narrowly focus on fixing that issue. However, the issue could grow into a much larger one if it is not properly diagnosed and managed. There is a risk of tunnel vision for the DBA. Oracle has developed another tool for database performance tuning administrators (DBAs) called Advanced Deployment and Management (ADDM) to aid them in gaining a more holistic understanding of database issues. A tuning session using ADDM is conducted in a manner analogous to that of a manual tuning session.

Bad SQL and execution plans

After the AWR or ADDM report identifies sections of bad SQL/SQL_ID, you can use DBMS_XPLAN to gather more information about them. The DBMS_XPLAN package supplies the following table functions that you can use to retrieve and display the execution (exec) plan:

DISPLAY

DISPLAY_AWR

 select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR('fs22b3fgfh8xc'));

DISPLAY_CURSOR

You can learn from the ADDM reports if adding new indexes would improve database performance tuning. When you need to optimize your queries and maybe switch to a better execution plan, you can use OEM’s SQL Tuning Advisor to do so. An improved design for the troublesome SQL usually fixes the major performance problem.

Expertise in SQL Tuning

With the help of SQL authorization ID (SQLID), SQL Tuning Advisor may evaluate SQL statements and provide performance recommendations. Among the data types analyzed by SQL database performance tuning Advisor are:

Among its many features is one labeled “top activity,” which examines the most frequently executed SQL statements.

• Analyzes a set of SQL statements you submit using the SQL tuning sets feature
• The SQL statements gathered in AWR snapshots can be analyzed with Historical SQL (AWR)

Indefinitely-Delayed Requests

When a small number of concurrent queries in an Oracle eBusiness Suite database continue to run, the resulting database performance tuning bottleneck is known as a “long-running request.” You need to learn more about the database session linked to the simultaneous requests in order to fix this.

Database Memory Settings in Oracle

By comparing the cache hit ratio to that of other caches, a DBA can readily determine which cache needs fine-tuning after analyzing the AWR reports.

Top 10 database performance tuning issues

The following are the top ten database performance tuning problems that customers usually encounter:

1.  Lackluster handling of connections: Developers craft the code that establishes a connection to the database and executes queries to retrieve data from the database performance tuning within the application. The function should terminate the database connection once all data has been retrieved. However, this rarely occurs, which results in a swell in the number of dormant sessions within the DB. As a result, other active connections may have to be put on hold while these sessions consume scarce resources.

2.  Poor management of the shared pool and cursors Developers have a lot of tools at their disposal, but they rarely employ the cursor. If you don’t use cursors, Oracle will have to perform a manual parsing on your code every time it’s executed. This has a significant effect on the speed at which SQL queries are executed. The portion of the AWR report titled “Instance Efficiency Percentages – Execute to Parse%” is where a DBA can spot this issue.

3.  Inefficient SQL: An SQL query’s performance after execution is profoundly impacted by the way in which the query was designed (including the join criteria used to retrieve the data). Full table scans are inefficient for large tables and should be avoided. Developers and DBAs should run an explained plan on the SQL once it has been created to get a sense of how much it will cost to execute the SQL in the database performance tuning. Increase productivity with the help of cursors, bind variables, and indexes.

4.  DBAs should always use standard or suggested initialization parameters, however, sometimes they stray from them. Never change the default initialization parameters unless instructed to do so by Oracle Support (SR).

5.  For example, a DBA who is in charge of selecting database hardware should strive to split the database across numerous drives and consult with the network team to determine the best way to ensure that data is sent quickly between the database and the end users. If a DBA wants to prevent slowdowns or database performance tuning difficulties, they should think about how fast the network’s switches and routers are.

6.  Setup issues with the redo log: Oracle’s ability to roll back transactions in the event of a crash depends on redo logs, which must be used to permanently record the contents of the redo buffer. Multiple switches in the database could be a database performance tuning issue if the redo log size is too small. The archival storage system is also put under more stress because of this.

7.  When there are not enough available undo segments or list groups, data blocks in the buffer cache begin to serialize. For databases that rely heavily on inserts but have a high volume of concurrent users but a small number of undo segments, this circumstance might cause significant performance concerns.

8.  In order to identify queries that use full table scans, you can check for them with an explanation plan. Full table scans are a symptom of poor SQL design and can be avoided with proper indexing and data reduction. Complete table scans are rarely useful, even for small tables, but there are exceptions.

9.  Recursive SQL: If used properly, recursive SQL may be a huge help to developers, but it also has the potential to cause problems. When it’s done correctly, it yields the desired results quickly and easily. As a result, the database’s efficiency suffers greatly.

10.   In-disk sorting: It’s quite costly for databases to perform in-disk sorting. It’s a symptom of poorly optimized SQL and inefficient design. The AWR report’s Instance Activity Stats – Sorts (disk) part will reveal the cause of the problem.

Conclusion

Although there are many aspects of a database that need to be optimized for speed, performance tuning actually begins with the creation of the database and any applications that will be using it. Scalability is greatly improved in databases and applications that are developed with performance tweaking in mind.

The database performance tuning optimization recommendations in this blog post are only the tip of the iceberg. If you care about how well your database functions as a whole, I urge your fellow DBAs to continue learning about this area.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.