Reputation: 582
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')
Upvotes: 1
Views: 1408
Reputation: 36922
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:
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.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.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.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.Upvotes: 2
Reputation: 1587
You can do at least two things:
cache result of function replace_word, using RESULT_CACHE IS (http://www.oracle-developer.net/display.php?id=504)
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.
Upvotes: 0
Reputation: 6486
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,
Upvotes: 0