AEM Blue

Use our experience to learn an advanced tool for tuning SQL statements and databases.

Tuning SQL statements and databases can be intimidating to Database Developers and DBAs alike. If you take too long, say something is “good enough,” or declare that something cannot be fixed, it may hurt your reputation or even have you looking for a new employer! But if done well, the benefits of resolving a performance issue range from helping your coworker to improving the lives of the operational community.

When tuning SQL statements and databases, utilize your experience and research skills. Avoid caving to pressure by simplifying problems to identify solutions. Knowing a few reliable strategies can help ensure success when addressing database-wide performance issues or troubleshooting a delete statement taking much longer than expected.

To help tune your skills, we have drawn from an example from our work. In this scenario (with some modification), a developer or DBA is resolving a legacy process that moves data from one database to another hosted on two separate servers. We walk you through the process we followed.

The Tuning Scenario

In this scenario, the process requires all data from the target partition to be deleted prior to refreshing the data. While that sounds simple, over one hundred partitioned tables are involved, and the process must be executed ON-DEMAND while the partitioned tables are accessed. What’s more, constraints and triggers cannot be disabled to avoid data integrity issues.

The tuning process must be executed in the background without any hang-ups to minimize operational impact. This process must be completed in minutes, not hours. After some detailed analysis and trying several fixes from the toolbox, we want to reduce the deletion time from 13 hours to seven minutes. Let’s look at how we can accomplish this feat.

Tuning the DELETE:

The first step is to identify the parent-child relationship for each of the tables, allowing you to construct a referentially correct DELETE order. Below is the script developed to determine the relationship where you will be prompted for the table and schema (owner) information:

SQL-Tuning-1

The above script will produce the output below:

SQL Tuning 2

In this example, we have based the table partitions on the two-letter state abbreviation codes (for example, CA = California). With the parent-child relationship in hand, the referentially correct delete order is constructed as follows:

SQL Tuning 3-1

Understanding the Tuning Scenario:

Question: Why did it take 13 hours to delete the CUSTOMER table?

Theoretically, it should be a quick DELETE because we emptied all of the child tables. After trying several relatively simple solutions, such as TRUNCATE, PARALLEL DML, gathering stats, rebuilding indexes, moving tables to a different tablespace, and moving to a different partition, nothing seemed to improve the performance. It’s good to rule out the more obvious solutions with troubleshooting performance issues before exploring more complex alternatives. Once the “low hanging fruit” was out of the way, it became clear that the dependent tables also have partitions, so perhaps they were contributing to the performance impact.

Answer: During the DELETE process, the DELETE will hop to the child tables and check for dependent data across all of the state partitions. The DELETE process is effectively doing a full table scan across all dependent tables across state partitions because there are no indexes on these associated columns. This process is a considerable performance hit.

Adding an index to the child tables prevents full table scans from happening, using the script below. You will need to change the “lender” (schema) in the script to the schema/owner along with providing the parent table requiring the delete (command prompts you for this with &&theparenttable):

SQL Tuning 4

The script generates the necessary CREATE INDEX:

SQL Tuning 5

Indexing the child partitioned tables greatly improves the DELETE performance, as the timing data below shows:

Before creating the indexes

SQL Tuning 6

After creating the indexes

SQL Tuning 7

Conclusion:

When performing mass partitioned DELETEs, do not lose sight of dependent tables with partitions and associated referential integrity. You will need to create the associated indexes to maximize performance because these constraints do not inherently come with indexes. Following these steps can help solve your performance issues and improve your approach to performance tuning.

RECOMMENDED BLOG POSTS

Changing Oracle Database Passwords with No Downtime

Some customers want to change all passwords every 60 days, which is a more secure security practice yet is a burden to the administrative staff needing to work off hours due to the system impact.

How to Resolve “Audit Tablespace Full” on a Pluggable Database

Most Database Administrators are used to the occasional crisis—it comes with the job. The level of stress involved can vary greatly depending on the frequency of the issue, how well you remember the fix, and/or how quickly can you find a resolution using a Google search. Hopefully, this wasn’t too hard to find on Google.

6 Steps to Ease Your Silent Upgrade of OEM to 13.4

Oracle Enterprise Manager (OEM) is a vital tool for any Oracle DBA to properly manage and monitor the operational environment. OEM helps identify issues and measure database performance in an accessible format providing an enterprise-wide view of their databases with the ability to zoom in for detailed information, as needed.

Like any piece of software, it is important to keep OEM up to date through patches and version releases. In this blog post, we will tackle one of the more challenging scenarios when it comes to upgrading OEM – Upgrading from 13.2 to 13.4 using the silent upgrade option.