Tune Performance of Relational Database Queries

Many customers experience performance issues with data warehouse applications so here are some high-level guidelines on how you can analyze and improve the performance of a report in Oracle Analytics. In some cases, the SQL queries generated by Oracle Analytics are complex to analyze. This topic describes how to analyze and minimize performance issues that might originate from the SQL queries generated by Oracle Analytics.

This topic doesn’t cover performance issues due to problems with your network, browser, or report presentation.

Analyze the query log for Oracle Analytics (log level 3 required)

For information on how to find this log or understand the content, see Gather and Analyze Query Logs.

1. Compare the amount of time your query spends in Oracle Analytics with the time spent in the database, that is, response time versus physical query duration. Normally, the time spent in Oracle Analytics doesn’t exceed a few seconds.
2. If the response time is more than a few seconds, analyze the individual steps taken in Oracle Analytics to find the cause (log level 5 required).

Analyze the physical SQL

1. Check whether all the tables included in the query are necessary. Look for any tables that are joined but not included in the SELECT clause and don’t have any filters applied (real filters, not join conditions).
2. Identify how many physical queries and sub-queries are generated. More precisely, how many times does the query read a fact table. In a perfect world, the query reads a single fact table and only once. When there’s more than one fact table, find out why and see if you can remove some.
3. Check for excluded columns, non-additive aggregation rules (REPORT_AGGREGATE, count(distinct)...), selection steps, sub-query in the report, set operators (UNION), totals, sub-totals, multiple views, and so on.
4. Check for any outer joins. Discover where they originate from and whether you can remove some by changing the design.

Analyze the execution plan

If optimizing the SQL is not enough, analyze the execution plan to find the root cause of your performance issue. Ask your DBA to help you. There are four main ways to improve performance at this point:

1. Reduce the volume of IOs by improving data access paths (indexes).
2. Reduce the volume of IOs by reducing the volume of data read. For example, you can review the filters applied or the data model structure (see next section).
3. Increase parallelism (number of threads used to read big tables).
4. Improve the IO speed (infrastructure improvements, in-memory database, and so on).

Review and improve the data model to reduce the volume of data read

1. Create aggregate tables.
2. Use fragmentation.

   For example, if users mostly select data from the current Year, Quarter, or Month, you could split the fact into two tables: current and archive. On the Oracle database, you can also use partitioning.

3. Use denormalization (to reduce the number of joins).
4. Split up tables to reduce the number of columns.

   The volume of data read doesn’t just depend on the number of rows in each table. Table volume also depends on the number and size of its columns. For example, you could split a big table with 500 columns into two tables; one table with the 50 most frequently used columns and the other with the remaining 450 columns that are rarely used.

Many performance issues in Oracle Analytics originate from poor design as this causes sub-optimal SQL queries to be generated. By modifying the design, you can improve the SQL queries that Oracle Analytics generates. This can have a huge impact, not only on the performance of your reports but also on the amount of resources used in the database.