Oracle Performance Tuning Training in Bangalore

Effective Oracle Performance Tuning

Wondering how to do performance tuning in Oracle, specifically?

Oracle is a relational database management system (RDBMS), and it utilizes Structured Query Language (SQL) to enable communication between 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 altering information contained by the database with the intention of improving query response times and application operations.

Once DBAs have done a systems-level check, they can proceed to SQL query tuning. Generally speaking, SQL tuning seeks to minimize the number of steps—“database touches”—a query entails, thus decreasing time cost and wait time. There are a lot of little SQL quirks and best practices to keep in mind, and while this list is by no means comprehensive or universally applicable, 

The following guidelines are helpful pointers in most situations.

Begin by identifying the most cost-intensive queries to appropriately allocate your tuning efforts. The truth is that SQL query performance tuning is an ongoing process; there’s always room for improvement, there’s always more code to optimize, and there’s always monitoring and upkeep to be done—which can make it feel like it never ends. For this reason, it’s important to isolate the high-impact SQL statements—those that are executed most frequently and require the most database and I/O activity. These statements offer the biggest returns in terms of database performance improvement, so targeting them will optimize the amount of tuning work put in relative to performance improvements.

Always minimize the amount of data that must be scanned in an operation. Many query statements will prompt the database to perform full-table scans, which incur much more I/O and can degrade performance by slowing down operations and carrying out unnecessarily broad searches. To streamline data retrieval:

• Add indexes to tables if you need to access under 5% of their data, except in the case of fairly small tables (which are more expediently searched in full whether or not you need much data).
• Do not include * in your SELECT statement queries unless necessary to fetch data, as this symbol will load the system.
• Use filters in WHERE clauses to restrict the size of the data set.
• Conversely, in a column-oriented system, only select the columns you need for the query.
• Cull unnecessary tables from query statements. Occasionally developers may forget to remove JOINS that do not serve the query. While this can be innocuous in the testing stage, once the system goes into effect, JOINS to tables that do not contribute to the retrieved data can greatly increase processing time.
• Utilize EXISTS in subqueries. This communicates to Oracle that it can stop the search, rather than complete a full-table scan by default, when it finds the match.

Do not use indexes to tables that undergo more UPDATE or INSERT operations, as indexes can slow data input. In that same vein, you might consider dropping your indexes when striving for batch updates or insertions. In this case, it might be best to recreate the indexes after a single batch event, or simply avoid indexes on tables frequently experiencing batch data loads to begin with.

Don’t mix data types, and do not convert numbers to characters. Their comparison can slow operations and impact performance.

In some scenarios, it may be easier to create a new field than to perform a calculation function on a JOIN or WHERE clause. In this situation, the new field would contain the calculated value, which the statement would SELECT rather than calculate itself. To accomplish this, the person tuning the code would have to have permission to alter the datasets, of course—but this should be no problem for a DBA or other IT administrator.

More broadly, align your SQL statements and datasets. Basically, comb through SQL syntax to ensure that you’ve written your statements in ways that match up with the data structure and allow easy access.

Institute protocol by using procedures instead of individual statements. A procedure is a collection of statements, and they downsize the cost of executing a recurring query. For example, if you use an app that requires you to pull data weekly, this query may account for a significant amount of database activity. You could use a procedure to certify that the query executes quickly, and according to your execution plan, because database engines carry out procedures without optimizing them.

Use global temporary tables (GTT) whenever possible to simplify complication summarization queries. By breaking up the work of work-intensive subqueries, GTT have been shown to improve database performance significantly.

Use hints. Oracle makes a list of hints available onlineto help application designers and DBAs. The purpose, as explained by the database itself, is to allow administrators and developers to “alter execution plans” and “force various approaches.” This allows designers who are tuning their SQL statements to take the reins from the optimizer in specific scenarios where the humans know more about the data than the optimizer. In these situations, they can ensure their execution plan gets carried out rather than overwritten by the optimizer, which might choose a data access path that does not optimize speed and performance.

Finally, make tuning a routine. While you don’t want to have to allot redundant labor to tuning the same queries over and over, performance tuning in SQL does require habitual maintenance to prevent database performance deterioration over time as both datasets and RDBMS software evolve. With this in mind, commit to normalizing and defragmenting the database routinely.

Oracle Performance Tuning Software Recommendations

Of course, all of this Oracle performance monitoring and assessment work is nearly impossible to do manually on an ad hoc basis. Plenty of useful and affordable software exists that can automate database performance tracking and help DBAs identify the source of performance problems.

This saves IT departments a good deal of time. Rather than noting a lag and having to root through their code and database set up to find the root cause, they can check their software to drill down into the queries with the highest performance impact.

TOP ORACLE PERFORMANCE TUNING TIPS

• Importance of Baseline
• Utilization of Automatic Workload Repository (AWR)
• Checking Stats
• Ensure Maximum CPU Utilization
• Monitoring Oracle via SQL Tuning