Re: Capital Projects on Oracle performance tip

We found that creating a SQL profile for one heavily used EPFM Capital Projects query reduced our response time by 40 hours per week. Here’s the relevant section of the Oracle ADDM report:

Recommendation 1: SQL Tuning

Estimated benefit is .31 active sessions, 27.24% of total activity.

——————————————————————-

Action

Run SQL Tuning Advisor on the SELECT statement with SQL_ID

“98qk5sc95vbg9”.

Related Object

SQL statement with SQL_ID 98qk5sc95vbg9.

select all dmr_content.r_object_id, dmr_content.full_format,

dm_repeating.page_modifier from dmr_content_sp dmr_content,

dmr_content_rp dm_repeating where ((dmr_content.r_object_id in

(select r_object_id from dmr_content_rv dm_repeating where

(dm_repeating.parent_id=:”SYS_B_0″) and

(dm_repeating.page=:”SYS_B_1″))) ) and

dm_repeating.r_object_id=dmr_content.r_object_id order by :”SYS_B_2″

Rationale

The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

This part of database time may be improved by the SQL Tuning Advisor.

Rationale

Database time for this SQL was divided as follows: 100% for SQL

execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

execution.

Rationale

SQL statement with SQL_ID “98qk5sc95vbg9” was executed 6478 times and

had an average elapsed time of 13 seconds.

When we followed this advice to run the SQL Tuning Advisor on this query, it created a SQL profile that greatly improved the performance of this query, and greatly improved the response time for Capital Projects overall. A SQL profile is “an object containing information that helps the query optimizer find an efficient execution plan for a specific SQL statement.”

Not every Oracle DBA will be conversant with SQL profiles, or think they’re a good idea. But if you’re running Capital Projects on Oracle 11, you can’t afford not to implement a SQL profile on this query. This may be a bug in the Oracle 11 query optimizer that will get fixed with Oracle 12.

Related:

Leave a Reply