CODEWITHSUNDEEP
oracle-databaseplsqlquery-optimizationsql-execution-plannvl
Glauco Cucchiar
Glauco Cucchiar

Reputation: 774

Oracle Optimization: which execution plan is better?

I've to improve this query:

    SELECT sub.SDDOCO
    FROM( 
      SELECT SDSHAN, SDDOCO, SDMCU, SDLITM, SDIR03, SDVR01
      FROM PRODDTA.F4211 

      UNION ALL

      SELECT SDSHAN, SDDOCO, SDMCU, SDLITM, SDIR03, SDVR01
      FROM PRODDTA.F42119 
    )sub 
    INNER JOIN PRODDTA.F0101 tab 
         ON sub.SDSHAN = tab.ABAN8
    WHERE 
        sub.SDMCU LIKE  CONCAT(CONCAT('%',nvl(:pMCU,SDMCU)),'%') 
    AND sub.SDLITM LIKE  CONCAT(CONCAT('%',nvl(:pLITM,SDLITM)),'%') 
    AND sub.SDIR03 LIKE  CONCAT(CONCAT('%',nvl(:pIR03,SDIR03)),'%')
    AND tab.ABALKY LIKE  CONCAT(CONCAT('%',nvl(:pALKY,ABALKY)),'%')
    AND sub.SDVR01 LIKE  CONCAT(CONCAT('%',nvl(:pVR01,SDVR01)),'%')

I've modified WHERE conditions in this manner:

    ((:pMCU IS NOT NULL AND sub.SDMCU LIKE '%' || :pMCU || '%') OR :pMCU IS NULL)
AND ((:pLITM IS NOT NULL AND sub.SDLITM LIKE '%' || :pLITM || '%') OR :pLITM IS NULL)
AND ((:pIR03 IS NOT NULL AND sub.SDIR03 LIKE '%' || :pIR03 || '%') OR :pIR03 IS NULL)
AND ((:pVR01 IS NOT NULL AND sub.SDVR01 LIKE '%' || :pVR01 || '%') OR :pVR01 IS NULL)
AND ((:pALKY IS NOT NULL AND tab.ABALKY LIKE '%' || :pALKY || '%') OR :pALKY IS NULL)

At this link you can read the execution plan for both query. Which one is better? Any suggestion to improve query? I need to have all records of result

EDIT: Where "F4211" table has 71,758 records and F42119 has 4,606,154 records

EDIT: and F0101 table has 634,187 records. Whith All parameters sets to NULL, the result is 4,677,925 records in an unreasonable time (some minutes). The query without where clauses takes 4,677,925 records in 58 seconds

EDIT: Inserting code snippents I exceed max num of char :( Any suggestion to post execution plan?

EDIT: This is the execution plan:

"PLAN_TABLE_OUTPUT"
"Plan hash value: 3925457500"
" "
"-----------------------------------------------------------------------------------------------------------"
"| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |"
"-----------------------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT                       |                  |     2 |  1224 |   614K  (1)| 00:00:24 |"
"|   1 |  NESTED LOOPS                          |                  |     2 |  1224 |   614K  (1)| 00:00:24 |"
"|   2 |   VIEW                                 | index$_join$_004 | 31721 |  1455K| 11379   (1)| 00:00:01 |"
"|*  3 |    HASH JOIN                           |                  |       |       |            |          |"
"|   4 |     INDEX FAST FULL SCAN               | F0101_0          | 31721 |  1455K|  2414   (1)| 00:00:01 |"
"|*  5 |     INDEX FAST FULL SCAN               | F0101_3          | 31721 |  1455K|  8390   (1)| 00:00:01 |"
"|   6 |   VIEW                                 |                  |     1 |   565 |    19   (0)| 00:00:01 |"
"|   7 |    UNION ALL PUSHED PREDICATE          |                  |       |       |            |          |"
"|*  8 |     TABLE ACCESS BY INDEX ROWID BATCHED| F4211            |     1 |   417 |     9   (0)| 00:00:01 |"
"|*  9 |      INDEX RANGE SCAN                  | F4211_4          |     6 |       |     3   (0)| 00:00:01 |"
"|* 10 |     TABLE ACCESS BY INDEX ROWID BATCHED| F42119           |     1 |   422 |    10   (0)| 00:00:01 |"
"|* 11 |      INDEX RANGE SCAN                  | F42119_9         |    10 |       |     3   (0)| 00:00:01 |"
"-----------------------------------------------------------------------------------------------------------"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
"   3 - access(ROWID=ROWID)"
"   5 - filter(""GGG"".""ABALKY"" LIKE U'%'||NVL(:PALKY,""GGG"".""ABALKY"")||U'%')"
"   8 - filter(""SDMCU"" LIKE U'%'||NVL(:PMCU,""SDMCU"")||U'%' AND ""SDLITM"" LIKE "
"              U'%'||NVL(:PLITM,""SDLITM"")||U'%' AND ""SDIR03"" LIKE U'%'||NVL(:PIR03,""SDIR03"")||U'%' AND ""SDVR01"" "
"              LIKE U'%'||NVL(:PVR01,""SDVR01"")||U'%')"
"   9 - access(""SDSHAN""=""GGG"".""ABAN8"")"
"  10 - filter(""SDMCU"" LIKE U'%'||NVL(:PMCU,""SDMCU"")||U'%' AND ""SDLITM"" LIKE "
"              U'%'||NVL(:PLITM,""SDLITM"")||U'%' AND ""SDIR03"" LIKE U'%'||NVL(:PIR03,""SDIR03"")||U'%' AND ""SDVR01"" "
"              LIKE U'%'||NVL(:PVR01,""SDVR01"")||U'%')"
"  11 - access(""SDSHAN""=""GGG"".""ABAN8"")"

Upvotes: 0

Views: 131

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

1 Oracle have good facilities for improve query performance for example DBMS_SQLTUNE https://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm#TGSQL586 At the begin you may create tuning task

declare
  l_task_name varchar2(4000);
begin
 l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
      sql_text => q'!SELECT sub.SDDOCO
FROM( 
  SELECT SDSHAN, SDDOCO, SDMCU, SDLITM, SDIR03, SDVR01
  FROM PRODDTA.F4211 
  UNION ALL
  SELECT SDSHAN, SDDOCO, SDMCU, SDLITM, SDIR03, SDVR01
  FROM PRODDTA.F42119 
)sub 
INNER JOIN PRODDTA.F0101 tab 
     ON sub.SDSHAN = tab.ABAN8
WHERE 
    sub.SDMCU LIKE  CONCAT(CONCAT('%',nvl(:pMCU,SDMCU)),'%') 
AND sub.SDLITM LIKE  CONCAT(CONCAT('%',nvl(:pLITM,SDLITM)),'%') 
AND sub.SDIR03 LIKE  CONCAT(CONCAT('%',nvl(:pIR03,SDIR03)),'%')
AND tab.ABALKY LIKE  CONCAT(CONCAT('%',nvl(:pALKY,ABALKY)),'%')
AND sub.SDVR01 LIKE  CONCAT(CONCAT('%',nvl(:pVR01,SDVR01)),'%')!'
, bind_list   => sql_binds( anydata.ConvertVarchar2('ABC'), 
            anydata.ConvertVarchar2('BCD'),
            anydata.ConvertVarchar2('CDE'),
            anydata.ConvertVarchar2('DEF'),
            anydata.ConvertVarchar2('EFG'))
, task_name   => 'TEST_TUNING_SQL_SO' 
, description => 'Task to tune a query from StackOverflow');
end;
/

After you may execute it 

begin
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK (
   task_name => 'TEST_TUNING_SQL_SO');
end;
/

And retrieve report and perform advices 

select DBMS_SQLTUNE.REPORT_TUNING_TASK(
   task_name => 'TEST_TUNING_SQL_SO') from dual;

2 How to publicate a plan

You may generate it in text format(not in HTML). And try to add only plan table with information about predicates. You may exclude any additional information if it was not required.

Upvotes: 1

Related Questions