Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Explain plan control - hash join running long

Sturla ThorApr 9 2015 — edited Apr 10 2015

Hi all,

Wanted to see if anybody could give me pointers for a sql I'm trying to "tune". This is actually code written by Oracle (EBS standard) so I don't have too much control over the sql.

History first, this sql is part of a batch job that runs every day and is inserting records into xla_trial_balances table.

The sql is usually inserting around 5000-10.000 records in each batch job and the records are selected from 10 tables, some of them very large (will provide stats).

This query is taking about 10 hours or more to run which seems a lot of time for 5000-10000 rows.

Attached is the sql itself (fkzwp9dwy8ng0.txt) and the sql-monitor overview of the execution. Here is the explain plan from the latest execution:

SQL>  SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fkzwp9dwy8ng0',1));
SQL_ID  fkzwp9dwy8ng0, child number 1
-------------------------------------
    INSERT INTO xla_trial_balances (           record_type_code
 ,source_entity_id          ,event_class_code
,source_application_id          ,applied_to_entity_id
,applied_to_application_id          ,gl_date
,trx_currency_code          ,entered_rounded_dr
,entered_rounded_cr          ,entered_unrounded_dr
,entered_unrounded_cr          ,acctd_rounded_dr
,acctd_rounded_cr          ,acctd_unrounded_dr
,acctd_unrounded_cr          ,code_combination_id
,balancing_segment_value          ,natural_account_segment_value
  ,cost_center_segment_value          ,intercompany_segment_value
   ,management_segment_value          ,ledger_id
,definition_code          ,party_id          ,party_site_id
,party_type_code          ,ae_header_id          ,generated_by_code
     ,creation_date          ,created_by          ,last_update_date
     ,last_updated_by          ,last_update_login


Plan hash value: 853743902


---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                                |                           |       |       |  3050 (100)|          |       |       |
|   1 |  LOAD TABLE CONVENTIONAL                        |                           |       |       |            |          |       |       |
|   2 |   HASH GROUP BY                                 |                           |     1 |   412 |  3050   (2)| 00:00:37 |       |       |
|*  3 |    FILTER                                       |                           |       |       |            |          |       |       |
|*  4 |     HASH JOIN                                   |                           |     1 |   412 |  3049   (2)| 00:00:37 |       |       |
|   5 |      NESTED LOOPS                               |                           |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                              |                           |     1 |   307 |    18   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS OUTER                       |                           |     1 |   271 |    17   (0)| 00:00:01 |       |       |
|   8 |         MERGE JOIN CARTESIAN                    |                           |     1 |   251 |    16   (0)| 00:00:01 |       |       |
|   9 |          NESTED LOOPS                           |                           |       |       |            |          |       |       |
|  10 |           NESTED LOOPS                          |                           |     1 |   236 |    15   (0)| 00:00:01 |       |       |
|  11 |            NESTED LOOPS OUTER                   |                           |     1 |   183 |    13   (0)| 00:00:01 |       |       |
|  12 |             NESTED LOOPS                        |                           |     1 |   126 |     8   (0)| 00:00:01 |       |       |
|  13 |              NESTED LOOPS                       |                           |     1 |    74 |     6   (0)| 00:00:01 |       |       |
|  14 |               NESTED LOOPS                      |                           |     1 |    28 |     2   (0)| 00:00:01 |       |       |
|  15 |                TABLE ACCESS BY INDEX ROWID      | GL_LEDGERS                |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|* 16 |                 INDEX UNIQUE SCAN               | GL_LEDGERS_U2             |     1 |       |     0   (0)|          |       |       |
|  17 |                TABLE ACCESS BY INDEX ROWID      | FND_CURRENCIES            |   249 |  5229 |     1   (0)| 00:00:01 |       |       |
|* 18 |                 INDEX UNIQUE SCAN               | FND_CURRENCIES_U1         |     1 |       |     0   (0)|          |       |       |
|  19 |               TABLE ACCESS BY GLOBAL INDEX ROWID| XLA_AE_LINES              |     1 |    46 |     4   (0)| 00:00:01 | ROWID | ROWID |
|* 20 |                INDEX RANGE SCAN                 | XLA_AE_LINES_C2_9529961   |     1 |       |     3   (0)| 00:00:01 |       |       |
|  21 |              PARTITION LIST ITERATOR            |                           |     1 |    52 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 22 |               TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS            |     1 |    52 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 23 |                INDEX UNIQUE SCAN                | XLA_AE_HEADERS_U1         |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  24 |             PARTITION LIST ITERATOR             |                           |     1 |    57 |     5   (0)| 00:00:01 |   KEY |   KEY |
|* 25 |              TABLE ACCESS BY LOCAL INDEX ROWID  | XLA_DISTRIBUTION_LINKS    |     1 |    57 |     5   (0)| 00:00:01 |   KEY |   KEY |
|* 26 |               INDEX RANGE SCAN                  | XLA_DISTRIBUTION_LINKS_N3 |    11 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 27 |            INDEX UNIQUE SCAN                    | GL_CODE_COMBINATIONS_U1   |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 28 |           TABLE ACCESS BY INDEX ROWID           | GL_CODE_COMBINATIONS      |     1 |    53 |     2   (0)| 00:00:01 |       |       |
|  29 |          BUFFER SORT                            |                           |     1 |    15 |    14   (0)| 00:00:01 |       |       |
|* 30 |           TABLE ACCESS BY INDEX ROWID           | XLA_TB_DEFINITIONS_B      |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|* 31 |            INDEX RANGE SCAN                     | XLA_TB_DEFINITIONS_B_N1   |     1 |       |     0   (0)|          |       |       |
|  32 |         TABLE ACCESS BY INDEX ROWID             | XLA_LEDGER_OPTIONS        |     2 |    40 |     1   (0)| 00:00:01 |       |       |
|* 33 |          INDEX UNIQUE SCAN                      | XLA_LEDGER_OPTIONS_U1     |     1 |       |     0   (0)|          |       |       |
|* 34 |        INDEX UNIQUE SCAN                        | XLA_EVENT_TYPES_B_U2      |     1 |       |     0   (0)|          |       |       |
|  35 |       TABLE ACCESS BY INDEX ROWID               | XLA_EVENT_TYPES_B         |     1 |    36 |     1   (0)| 00:00:01 |       |       |
|  36 |      TABLE ACCESS STORAGE FULL                  | XLA_TB_DEF_SEG_RANGES     |   754K|    75M|  3025   (2)| 00:00:37 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter(:7<=:8)
   4 - access("XTD"."DEFINITION_CODE"="XSR"."DEFINITION_CODE")
       filter((NVL("GCC"."SEGMENT1",'0')>=NVL(NVL("XSR"."SEGMENT1_FROM","GCC"."SEGMENT1"),'0') AND
              NVL("GCC"."SEGMENT1",'0')<=NVL(NVL("XSR"."SEGMENT1_TO","GCC"."SEGMENT1"),'0') AND
              NVL("GCC"."SEGMENT2",'0')>=NVL(NVL("XSR"."SEGMENT2_FROM","GCC"."SEGMENT2"),'0') AND
              NVL("GCC"."SEGMENT2",'0')<=NVL(NVL("XSR"."SEGMENT2_TO","GCC"."SEGMENT2"),'0') AND
              NVL("GCC"."SEGMENT3",'0')>=NVL(NVL("XSR"."SEGMENT3_FROM","GCC"."SEGMENT3"),'0') AND
              NVL("GCC"."SEGMENT3",'0')<=NVL(NVL("XSR"."SEGMENT3_TO","GCC"."SEGMENT3"),'0') AND
              NVL("GCC"."SEGMENT4",'0')>=NVL(NVL("XSR"."SEGMENT4_FROM","GCC"."SEGMENT4"),'0') AND
              NVL("GCC"."SEGMENT4",'0')<=NVL(NVL("XSR"."SEGMENT4_TO","GCC"."SEGMENT4"),'0') AND
              NVL("GCC"."SEGMENT5",'0')>=NVL(NVL("XSR"."SEGMENT5_FROM","GCC"."SEGMENT5"),'0') AND
              NVL("GCC"."SEGMENT5",'0')<=NVL(NVL("XSR"."SEGMENT5_TO","GCC"."SEGMENT5"),'0') AND
              NVL("GCC"."SEGMENT6",'0')>=NVL(NVL("XSR"."SEGMENT6_FROM","GCC"."SEGMENT6"),'0') AND
              NVL("GCC"."SEGMENT6",'0')<=NVL(NVL("XSR"."SEGMENT6_TO","GCC"."SEGMENT6"),'0') AND
              NVL("GCC"."SEGMENT7",'0')>=NVL(NVL("XSR"."SEGMENT7_FROM","GCC"."SEGMENT7"),'0') AND
              NVL("GCC"."SEGMENT7",'0')<=NVL(NVL("XSR"."SEGMENT7_TO","GCC"."SEGMENT7"),'0') AND
              NVL("GCC"."SEGMENT8",'0')>=NVL(NVL("XSR"."SEGMENT8_FROM","GCC"."SEGMENT8"),'0') AND
              NVL("GCC"."SEGMENT8",'0')<=NVL(NVL("XSR"."SEGMENT8_TO","GCC"."SEGMENT8"),'0') AND
              NVL("GCC"."SEGMENT9",'0')>=NVL(NVL("XSR"."SEGMENT9_FROM","GCC"."SEGMENT9"),'0') AND
              NVL("GCC"."SEGMENT9",'0')<=NVL(NVL("XSR"."SEGMENT9_TO","GCC"."SEGMENT9"),'0')))
  16 - access("GL"."LEDGER_ID"=:9)
  18 - access("FDC"."CURRENCY_CODE"="GL"."CURRENCY_CODE")
  20 - access("XAL"."AE_HEADER_ID">=:7 AND "XAL"."ACCOUNTING_CLASS_CODE"='LIABILITY' AND "XAL"."AE_HEADER_ID"<=:8)
       filter("XAL"."ACCOUNTING_CLASS_CODE"='LIABILITY')
  22 - filter(("XAH"."UPG_BATCH_ID" IS NULL AND "XAH"."GROUP_ID"=:GROUP_ID AND "XAH"."LEDGER_ID"=:9 AND
              "XAH"."EVENT_TYPE_CODE"<>'MANUAL' AND INTERNAL_FUNCTION("XAH"."GL_TRANSFER_STATUS_CODE")))
  23 - access("XAH"."AE_HEADER_ID"="XAL"."AE_HEADER_ID" AND "XAH"."APPLICATION_ID"="XAL"."APPLICATION_ID")
       filter(("XAH"."AE_HEADER_ID">=:7 AND "XAH"."AE_HEADER_ID"<=:8))
  25 - filter("XAL"."APPLICATION_ID"="XDL"."APPLICATION_ID")
  26 - access("XAL"."AE_HEADER_ID"="XDL"."AE_HEADER_ID" AND "XAL"."AE_LINE_NUM"="XDL"."AE_LINE_NUM")
       filter(("XDL"."AE_HEADER_ID">=:7 AND "XDL"."AE_HEADER_ID"<=:8))
  27 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")
  28 - filter("GCC"."CHART_OF_ACCOUNTS_ID"=:COA_ID)
  30 - filter("XTD"."ENABLED_FLAG"='Y')
  31 - access("XTD"."LEDGER_ID"=:9)
  33 - access("XLO"."APPLICATION_ID"="XAH"."APPLICATION_ID" AND "XLO"."LEDGER_ID"=:9)
  34 - access("XAH"."APPLICATION_ID"="XET"."APPLICATION_ID" AND "XAH"."EVENT_TYPE_CODE"="XET"."EVENT_TYPE_CODE")
       filter("XET"."EVENT_TYPE_CODE"<>'MANUAL')




105 rows selected.


SQL>

From the sql-monitor html I can see that there is a data-skew where the optimizer thinks it will get 1 row from xla_ae_lines but in fact gets 7347 rows.

99% of the execution is happening in the hash join of the query (nr 4) where it is joining 13k rows with the table in nr 36.

I have tried to use some hints (no_use_hash, index) but have been unable to get better results.

Does anyone have any hint for me how to tackle this?

60437

Thanks for reporting this. I'll be looking into it. In the meantime, please tell us your first name and update your forum handle and profile with it to help us. Thanks.

Scott

634962

Done.

Let me know what you find.

Thanks.

Gregg

60437

Gregg,

First, I'm not seeing the behavior you described when uploading duplicate file names, using 3.1.1. I get a validation error when I try that. So maybe that problem is fixed.

Can someone please provide some kind of overview of APEX_APPLICATION_FILES, APEX_WORKSPACE_FILES, WWV_FLOW_FILE_OBJECTS$, and any APIs that interact with them? Some questions: What is the difference between APEX_APPLICATION_FILES and APEX_WORKSPACE_FILES? How do they then relate to WWV_FLOW_FILE_OBJECTS$?

APEX_APPLICATION_FILES is a view on wwv_flow_file_objects$ constrained by your current workspace ID. APEX_WORKSPACE_FILES has more information than APEX_APPLICATION_FILES as it joins more tables. It also appears to have a bug, showing more rows than are actually there.

Why is there 'context security' on APEX_APPLICATION_FILES but not APEX_WORKSPACE_FILES?

I do not see that this is the case. Select the view definition from dba_views to see for yourself.

Can you just work with those views directly?

For selecting, yes.

How can/should I remove the 'orphaned' records?

You can delete from wwv_flow_file_objects$ as long as you are sure about what you are doing. This table is owned by FLOWS_FILES.

Scott

634962

Scott,

Thanks for your help with this.

If you are receiving a formal validation error, then I would agree that the issue was probably fixed in the newer version. I guess I will work to get our system updated. And in the meantime, I will just avoid the problem, now knowing what it is. (I think I stumbled into this problem by simply trying to upload a file again after having updated it offline, without deleting the version that was already in ApEx.)

If I understand you correctly, APEX_APPLICATION_FILES and APEX_WORKSPACE_FILES should have the same number of rows for a given workspace ID, correct? In my environment, APPLICATION has more records than WORKSPACE, which seems backwards to what you mentioned. Please clarify/confirm.

OK -- I see the security on WORKSPACE now. It obviously just works a little differently than the security on APPLICATION.

Unfortunately, I do not have access to WWV_FLOW_FILE_OBJECTS$ or the FLOWS_FILES user. But if I got access, I would just need to delete out the problematic records? Will there only be one record for each orphan? Any other cleanup work needed? Any other tables?

Obviously, I can just use another filename, but, well...that's annoying.

Thanks again.

60437

Gregg,

To access wwv_flow_file_objects$, you can connect as SYS and then

alter session set current_schema=flows_files;

Then query for the rows of interest and delete those you don't want. Pay particular attention to the security_group_id value (this is the workspace ID). As far as identifying "orphan" rows or knowing whether there are multiple rows that meet that criteria for a given value of filename+security_group_id, you'll just have to query the data to find out.

Be aware that any changes you make to this table can affect the operation of the entire Application Express instance (all workspaces), so be very careful.

Scott

P.S. If you'll put your first name in your OTN profile, responders will have ready access to it.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on May 8 2015

Added on Apr 9 2015

#general-database-discussions

24 comments

14,026 views

Oracle Database Discussions

Explain plan control - hash join running long

Comments

Post Details