What does Oracle do best

This is how performance tuning works in Oracle

Oracle performance tuning is an important step in ensuring fast application functionality and fast data retrieval. Here are some facts you should know to improve database performance.

All database administrators (DBAs) are well aware of the chore of increasing the performance of the database. To speed up application functions, database administrators need to reduce query response time, which means that admins need to have a clear understanding of how the database is organized and how it serves its purpose. In other words, DBAs should not only know the actual database well, but also the dedicated computer language that accesses the database for data retrieval, data manipulation, or data deletion.

What is Oracle Performance Tuning?

Are you wondering how exactly should performance tuning be done in Oracle? Oracle is a relational database management system (RDBMS) and uses Structured Query Language (SQL) for communication between the applications and the database. Performance tuning is the process of optimizing Oracle performance by streamlining the execution of SQL statements. In other words, performance tuning simplifies the process of accessing and modifying data in the database to improve query response times and application operations.

To explain this further, let's first analyze the different components of the database management system, starting with the database. As mentioned, Oracle uses the relational database model.

What is a relational database?

  • A relational database is an information system. It is a computerized method of storing and using information.
  • The purpose of a relational database is to consolidate, store and, if necessary, access information through the applications. The applications then use this data for specific purposes. For example, a company can use a database to store its customers' personal data. A segmentation app can search the database for email addresses of customers who made a purchase in the last month - this gives the company personal information that can be used to target specific customer segments via email broadcast to address.
  • A relational database stores data in tables. These tables are called "relations" and are configured in a similar way to a spreadsheet program: the columns are "fields" with different attributes and the rows contain specific entries. A customer base data table could contain rows 1 through 27, each row corresponding to a different customer record and each column assigned an attribute of personal data (name, email address, date of last purchase, etc.). Line 1 could read: "John", [email protected], "1/1/2019" etc.

This database model is the foundation of the management system. The management system itself is software that creates relational databases and manages their organization and interaction with other data-using applications. As defined by Oracle itself, the main functions of a database management system (DBMS) are:

  • Kernel code - defines settings and allocates memory and storage space to the system.
  • A data dictionary - a collection of metadata. This repository provides a read-only overview of the data in the database and displays tables and views and reference information about the database itself and its users.
  • A specified query language - enables applications to access the database information.

As mentioned, Oracle uses SQL as its database language. SQL is characterized by the ability to enter, manipulate, retrieve, and delete data from a database. SQL also gives administrators the ability to grant and revoke access and create their own views and functions. SQL does not prescribe any procedures, i.e. SQL statements inform the database management system, Whatis to be done, but do not prescribe a procedure. Basically, the software in the database management system recognizes how to execute instructions by analyzing the options available and choosing the best sequence of actions. Not only is SQL a quick language to master, users can embed SQL in other host languages ​​as well.

How does Oracle database performance tuning work?

Performance tuning takes into account the many elements in a relational database management system to correct the source of performance problems. Database administrators often face a difficult task: users of the network report delays in the application or slow loading pages, but administrators cannot identify the source of these database bottlenecks. Is it because of the optimization program? Is the coding of the query statements the weak point? Or is the problem with the computer?

In order to be able to identify the causes, administrators must examine the many elements of a relational database management system during performance tuning in order to discover the delays in the database operation. Not only can performance tuning be time-consuming, it can be difficult knowing where to start. An intensive performance tuning process must start at the system level and run through RDBMS components from top to bottom.

Many database experts point out that fine-tuning individual SQL statements - one at a time - will do little if the administrator has not first fine-tuned them at the system level, that is, on the server, in the instances and objects. We recommend evaluating the input and output measures (I / O measures), the optimization parameters and statistics, and the instance settings before setting up individual SQL statements. Otherwise, the careful SQL tuning will later be undone by the optimization, as it detects an execution log that deviates from the execution plans developed.

10 steps to effective Oracle performance tuning

Once the database administrators have checked the system, they can begin tuning the SQL queries. In general, SQL tuning is about minimizing the number of steps - "database contacts" - following a query, which reduces duration, costs and waiting time. Many small SQL peculiarities and best practices need to be considered. While this list is by no means exhaustive or universal, the following guidelines have proven to be extremely helpful in most cases.

Our top 10 tips for tuning SQL query performance:

  1. Start by identifying the most expensive queries so you can rank your tuning efforts accordingly. In reality, SQL query performance tuning is an ongoing process because there is always room for improvement and there is always more optimizing code and more monitoring and maintenance that makes it feel like it may never end. For this reason, it is important to isolate the high impact SQL statements - that is, those that run most frequently and require the most database and I / O activity. These statements are the most promising when it comes to improving database performance, and so it is worth exploring them, as the improvement you make is the one that most justifies the effort.
  2. Always minimize the amount of data to be scanned in one operation. Lots of query statements cause the database to scan the entire table, which causes much more I / O and degrades performance by slowing down operations and performing unnecessarily large searches.
    To streamline data retrieval:
    • Index tables when you need access to less than 5% of the data, unless they are relatively small tables (which are more efficiently searched in full, regardless of whether you need little or a lot of data).
    • Avoid * in your SELECT statement queries, unless it is essential for data retrieval, because the symbol is a burden on the system.
    • Use filters in WHERE clauses to limit the size of the data set.
    • In a columnar system, only select the columns that you need for the query.
    • Remove unnecessary tables from query statements. Sometimes developers forget to remove JOINS that don't add anything to the query. This may not matter in the test phase, but once the system is up and running, JOINS to tables that are not part of the retrieved data can increase processing time enormously.
    • Use EXISTS in subqueries. This tells Oracle that if it finds a match, it can stop searching instead of continuing to scan the entire table by default.
  1. Avoid indexes on tables that are undergoing more UPDATE or INSERT operations, as indexes can slow down data entry. Similarly, it can be useful to omit the indexes altogether when planning batch updates or inserts. In this case, it may be optimal to recreate the indexes after each individual batch event or to omit indexes altogether if batch data loads occur frequently in the tables from the outset.
  2. Do not mix data types and do not convert numbers to characters. The comparison can slow operation and performance.
  3. In certain cases it may be easier to create a new field instead of doing the calculation using the JOIN or WHERE clause. In that case, the new field would contain the calculated value that the SELECT statement would use instead of calculating it itself. To do this, of course, the person optimizing the code needs permission to change the records, which shouldn't be a problem for a DBA or other IT administrator.
  4. Make your SQL statements and records more general. To do this, search the SQL syntax to make sure you have written statements that match the data structure and are easy to access.
  5. Enforce a protocol using procedures instead of individual statements. A procedure is a collection of instructions that reduces the cost of running a repetitive query. For example, if you are using an app that calls data once a week, that query can consume a significant amount of database activity. You could establish a mechanism to ensure that the query runs quickly and on schedule because database engines run procedures without optimizing them.
  6. Use global temporary tables (GTT) whenever possible to simplify complication summation queries. By breaking down the labor-intensive subqueries, GTTs have been shown to significantly improve database performance.
  7. Work with “hints” or notices. To assist application developers and database administrators, Oracle provides a list of “hints” online. Its purpose, as explained by the database itself, is to allow administrators and developers to "modify execution plans" and "enforce different approaches". This allows developers fine-tuning their SQL statements to take control of the optimizer in certain cases when people know more about the data than the optimizer does. In those cases, they can ensure that the execution plans are followed and not overridden if a data access path is chosen that does not optimize speed and performance.
  8. Finally, make performance tuning routine. Even if you do not want to set redundant work processes to the constant tuning of the same queries, performance tuning in SQL requires routine maintenance in order to prevent a temporal decrease in performance in the database, which is caused by the further development of the data sets such as the RDBMS software. With this in mind, do the routine normalization and defragmentation of the database.

Recommendations from Oracle Performance Tuning Software and Oracle Performance Tuning Tools

Of course, it is almost impossible to do all of this work of Oracle performance monitoring and evaluation manually on an ad hoc basis. There are many software packages available that are useful and inexpensive that can automate tracking of database performance and help database administrators determine the source of performance problems. This saves IT departments a lot of time. Instead of having to search the code and database for the origin after detecting a delay, administrators can check the software for queries that have hit performance the hardest.

These are the Oracle database tools that I have valued most:

  1. Database Performance Analyzer

SolarWinds® Database Performance Analyzer® (DPA) is one of the most powerful analytics solutions that can be used to test SQL query performance in Oracle. The dashboard has been designed to be user-oriented and offers comprehensive, informative visualizations that break down SQL statement activity data that can be condensed and difficult to analyze on other platforms.

 

The dashboard shows a time analysis chart that shows the day with the total response time to queries (the time between a query and the response, i.e. the cumulative processing time and the waiting time for the resources to be available). The respective day bar is color-coded according to the query instruction, which enables DBA to quickly gain insight into the measured values ​​of each individual query by simply clicking on them.

The Database Performance Analyzer® collects data in real time. The information is therefore up-to-date from one moment to the next. The tool also displays warning messages in the dashboard (or sends them via email or as a mobile notification) when it detects system-critical problems. This helps DBAs to hierarchize their chaotic workload and address potential issues and query latency that are having the greatest impact on system performance first before moving on to other things.

This function of the Oracle performance monitoring and tuning tool from SolarWinds® is particularly useful because it puts the focus of performance tuning work, which, if handled incorrectly, leads to high investments without noticeable success, on one aspect: the waiting time. Many tools, and even IT departments, make the mistake of prioritizing resource usage when tuning performance, but doing so can obscure the root causeof the performance problem. It is primarily about guesswork - which is not the case in database performance analysis, because it compares specific queries, waiting time events, I / O and memory statistics and database activity with one another in order to determine causalities and find performance solutions.

SolarWinds® DPA is unparalleled in the way it consolidates and analyzes data to make it truly actionable. Its graph of SQL statements with worst performance results enables users to identify specific types of waits and events that are causing latency in applications. If the tool detects that there is a waiting process, for example, on the CPU, DBAs have a starting point at which the performance tuning can begin: the actualroot cause. Correcting this problem results in an immediate increase in performance with measurable benefits to network users and applications. DPA also improves group communication and institutional memory. DPA not only retains historical data, but also issues custom reports, alerts and metrics for database administrators that they can share internally with their colleagues or externally with other departments or even customers.

  1. Idera SQL Diagnostic Manager

Another great Oracle performance monitoring and tuning tool for anyone who wants to know how to check Oracle database performance is the Idera SQL Diagnostic Manager. It contains features comparable to the SolarWinds® DPA tool, such as predictive alerts and customizable monitoring settings for multiple servers.

In our opinion, its greatest strength lies in its prescriptive recommendations. So if there is a performance problem, the tool will highlight that problem and recommend a solution like an executable script. This is especially useful for IT staff who are still tuning the performance of SQL queries, although they obviously need their own expertise when it comes to fixing complex bottlenecks.

The interface seems a little cluttered, but the tool is certainly a good option for Windows users. The only other issue worth mentioning is the configurable reporting and monitoring, both of which don't seem as robust as they should be.

  1. SolarWinds® Database Performance Analyzer Free

Also noteworthy is the free or free version of SolarWinds® DPA. It doesn't have all of the features of the paid version, but it stands out as the only time-keeping tool for databases.

DPA Free includes some great features: the tool monitors response time and displays both wait events and wait types, which gives database administrators a data-driven approach and useful visualization. Just like the paid version of DPA, it works without agents, supports unlimited users, and has less than 1% load on the server.

Given the non-existent cost and specifications, DPA Free is great value and is an excellent place to start for those new to this work. Database administrators who are looking for more in-depth functions to investigate the causes of a decrease in performance in the database based on response times are undoubtedly better off with the premium version of DPA.

Categories Reviews of tools