Tuning of complex query

Question

I have a following query which is taking ages to execute. I tried using hint and created indexes on the columns in the where clause but didn't help. As per explain plan the aggregate function is taking more cost and for me it's absolutely necessary to have them. Is there anyway I can tune it?

 INSERT
  INTO tab3
  (CDE,
  SOURCE,
  SCENARIO,
  ID_COUNT,
  AMOUNT)
SELECT /*+ parallel(t,8) */ 'BENEFICIARY' AS CDE, 'MTS' AS SOURCE,'Match on Value' AS SCENARIO,COUNT(T.BA1) AS ID_COUNT,SUM(T.AMT) AS AMOUNT
FROM tab1 E
JOIN tab2 T
ON E.AA1 = T.BA1
WHERE (CASE WHEN E.AF1 = 'Y'
        THEN replace_word(E.AF2)
        ELSE replace_word(E.AF3)
    END) = UPPER(TRIM(T.BF1))
AND E.AF5 = '001'
AND E.AF6 = 'Y'
AND T.BF2 = '001';

If I made this query as part of procedure and select query as cursor and then use bulk collect to insert into tab3, will that help? Thanks in advance for your time. Our DB is Oracle 11g.

Edit: Adding explain plan of the query above:

-------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT               |                                |     1 |   127 |       |  1458K  (1)| 04:51:40 |

|   1 |  LOAD TABLE CONVENTIONAL       | tab3                           |       |       |       |            |          |

|   2 |   SORT AGGREGATE               |                                |     1 |   127 |       |            |          |

|*  3 |    HASH JOIN                   |                                | 10005 |  1240K|    76M|  1458K  (1)| 04:51:40 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| tab1                           |  1000K|    64M|       |   432K  (1)| 01:26:33 |

|*  5 |      INDEX RANGE SCAN          | IDX_AF5                        |  2000K|       |       |  4483   (1)| 00:00:54 |

|*  6 |     TABLE ACCESS FULL          | AF5                            |  3538K|   199M|       |  1009K  (1)| 03:22:00 |

-------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   3 - access(CASE "E"."AF1" WHEN 'Y' THEN "REPLACE_WORD"("E"."AF2") ELSE 

              "REPLACE_WORD"("E"."AF3") END =UPPER(TRIM("T"."BF1")) AND 

              "E"."AA1"="T"."BA1")

   4 - filter("E"."AF6"='Y')

   5 - access("E"."AF5"='001')

   6 - filter("T"."BF2"='001')

Answer 1

Performance tuning requires a lot of information. This question contains the full query and execution plan, which is much more than most performance questions. But as Rob van Wijk mentioned, it is important to know the actual times and rows returned, not just the estimates. It's surprising how many people only ever make guesses based on estimates when the real numbers are so close. Rob's method will work, although I prefer to use select dbms_sqltune.report_sql_monitor(sql_id => 'your sql_id') from dual;

Here are some tips based on the information provided:

1. Statement-level parallelism. Replace the hint SELECT /*+ parallel(t,8) */ ... with SELECT /*+ parallel(8) */ .... The current query uses object-level hints, which only direct the optimizer to consider using parallelism for one full table scan. A statement level hints tells the optimizer to use parallelism for the entire statement. This may enable parallel index access.
2. Parallel DML. If there are a lot of rows inserted, the data does not need to be instantly recoverable, there are no locking concerns, etc, a hint like INSERT /*+ APPEND PARALLEL(8) */ ... may help significantly. Changing LOAD TABLE CONVENTIONAL to a direct-path insert, LOAD AS SELECT, can sometimes increase performance by orders of magnitude. Getting this to work can be tricky, the session will also need alter session enable parallel dml;, the table may need to be in NOLOGGING, foreign keys may need to be disabled, etc.
3. replace_word. Custom functions can cause many problems in SQL statements. Context switching may decrease performance if it is called many times. Based on your other question, the function will prevent parallelism because it is not defined with PARALLEL_ENABLE. And the optimizer cannot estimate the selectivity of a function, although this can sometimes be helped with associate statistics. If possible it might be better to replace the query with an inline view or some other declarative method. Also, you definitely want to remove the exception handler, it's doing nothing but obscuring the line number of the error.
4. Gather statistics. As others have said, optimizer statistics needs to be accurate for Oracle to build an accurate plan. Hopefully the database still has the default statistics gathering task enabled. Otherwise it is usually sufficient to call a procedure like this: exec dbms_stats.gather_table_stats('schema_name', 'E');. Even i this changes nothing, the question should always state "statistics are up-to-date" because it's usually the first thing to look at.

Answer 2

You can do at least two things:

1. cache result of function replace_word, using RESULT_CACHE IS (http://www.oracle-developer.net/display.php?id=504)

2. make function index on UPPER(TRIM(T.BF1))

According to (http://www.dba-oracle.com/oracle_tips_null_idx.htm) to index null column you could make it like that:

create index
func_idx on
tab2 T
(UPPER(TRIM(T.BF1)), 1);

using pl/sql will not help, things will get probably worst than before.

3. You can try APPEND hint in your insert. I recommend this tutorial: http://www.akadia.com/services/ora_insert_append.html

Answer 3

Get rid of invoking of the function from your query:

create or replace function fun1(
  p_var in varchar2
)
return varchar2
is
begin
  return replace(p_var, 'a', 'aaa');
end fun1;

declare
  l_start number := dbms_utility.get_cpu_time;
begin
  for i in (select fun1(object_name) from all_objects where rownum < 10000)
  loop
    null;
  end loop;
  DBMS_OUTPUT.put_line((dbms_utility.get_cpu_time - l_start) || ' hsec');
end;

302 hsec


declare
  l_start number := dbms_utility.get_cpu_time;
begin
  for i in (select replace(object_name, 'a', 'aaa') from all_objects where rownum < 10000)
  loop
    null;
  end loop;
  DBMS_OUTPUT.put_line((dbms_utility.get_cpu_time - l_start) || ' asdf');
end;

268 hsec

As you can see I have about 0.3 sec profit and this is for 10K records. I ran several times each to worm up the buffer cache,