[Solved] Optimizing SELECT query performance

EverSQL Database Performance Knowledge Base

Optimizing SELECT query performance

I have a SELECT statement that runs really slow, it's holding back our night process.

The query is: (Please don't comment about the implicit join syntax, this is automatically generated by Informatica that runs this code) :

SELECT *
  FROM STG_DIM_CRM_CASES,V_CRM_CASE_ID_EXISTS_IN_DWH,stg_scd_customers_key
 WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   and STG_DIM_CRM_CASES.Case_Create_Date between  stg_scd_customers_key.start_date(+) and  stg_scd_customers_key.end_date(+)

edit: The actual query selects only account_number,start_date,end_date and one other column which is not indexed.

Tables info :

STG_DIM_CRM_CASES

Index - (Account_Number,Case_Create_Date)
size - 270k records.

stg_scd_customers_key

Index - Account_Number,Start_Date,End_Date
Partitioned - End_Date
Size - 500 million records.

V_CRM_CASE_ID_EXISTS_IN_DWH(View) -

select  t.case_id
from crm_ps_rc_case t, dim_crm_cases x
where t.case_id=x.crm_case_id;

dim_crm_cases -

Indexed - (crm_case_id)
Size - 100 million .

crm_ps_rc_case -

Size - 270k records

Edit - If it wasn't clear, the view returns 270k records .

The query without the join to stg_scd is taking seconds, looks like it is the part that causing the performance issues, the view runs in seconds too although it is being joined to a 100 Million records table. Right now the query is taking somewhere between 12 to 30 minutes, depends how busy our sources are.

Here is the EXECUTION PLAN :

6   |   0 | SELECT STATEMENT                |                             |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |        |      |            |
7   |   1 |  PX COORDINATOR                 |                             |       |       |            |          |       |       |        |      |            |
8   |   2 |   PX SEND QC (RANDOM)           | :TQ10003                    |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |  Q1,03 | P->S | QC (RAND)  |
9   |*  3 |    HASH JOIN OUTER              |                             |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |  Q1,03 | PCWP |            |
10  |   4 |     PX RECEIVE                  |                             | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,03 | PCWP |            |
11  |   5 |      PX SEND HASH               | :TQ10002                    | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,02 | P->P | HASH       |
12  |*  6 |       HASH JOIN RIGHT OUTER     |                             | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,02 | PCWP |            |
13  |   7 |        BUFFER SORT              |                             |       |       |            |          |       |       |  Q1,02 | PCWC |            |
14  |   8 |         PX RECEIVE              |                             | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |  Q1,02 | PCWP |            |
15  |   9 |          PX SEND BROADCAST      | :TQ10000                    | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |        | S->P | BROADCAST  |
16  |  10 |           VIEW                  | V_CRM_CASE_ID_EXISTS_IN_DWH | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |        |      |            |
17  |* 11 |            HASH JOIN            |                             | 29188 |   399K| 50575   (5)| 00:11:49 |       |       |        |      |            |
18  |  12 |             TABLE ACCESS FULL   | CRM_PS_RC_CASE              | 29188 |   199K|   570   (1)| 00:00:08 |       |       |        |      |            |
19  |  13 |             INDEX FAST FULL SCAN| DIM_CRM_CASES$1PK           |   103M|   692M| 48894   (3)| 00:11:25 |       |       |        |      |            |
20  |  14 |        PX BLOCK ITERATOR        |                             | 29188 |    10M|    87   (2)| 00:00:02 |       |       |  Q1,02 | PCWC |            |
21  |  15 |         TABLE ACCESS FULL       | STG_DIM_CRM_CASES           | 29188 |    10M|    87   (2)| 00:00:02 |       |       |  Q1,02 | PCWP |            |
22  |  16 |     BUFFER SORT                 |                             |       |       |            |          |       |       |  Q1,03 | PCWC |            |
23  |  17 |      PX RECEIVE                 |                             |   515M|    14G|   507K  (3)| 01:58:28 |       |       |  Q1,03 | PCWP |            |
24  |  18 |       PX SEND HASH              | :TQ10001                    |   515M|    14G|   507K  (3)| 01:58:28 |       |       |        | S->P | HASH       |
25  |  19 |        PARTITION RANGE ALL      |                             |   515M|    14G|   507K  (3)| 01:58:28 |     1 |  2982 |        |      |            |
26  |  20 |         TABLE ACCESS FULL       | STG_SCD_CUSTOMERS_KEY       |   515M|    14G|   507K  (3)| 01:58:28 |     1 |  2982 |        |      |            |
27  ------------------------------------------------------------------------------------------------------------------------------------------------------------
28   
29  Predicate Information (identified by operation id):
30  ---------------------------------------------------
31   
32     3 - access("STG_DIM_CRM_CASES"."ACCOUNT_NUMBER"="STG_SCD_CUSTOMERS_KEY"."ACCOUNT_NUMBER"(+))
33         filter("STG_DIM_CRM_CASES"."CASE_CREATE_DATE">="STG_SCD_CUSTOMERS_KEY"."START_DATE"(+) AND 
34                "STG_DIM_CRM_CASES"."CASE_CREATE_DATE"<="STG_SCD_CUSTOMERS_KEY"."END_DATE"(+))
35     6 - access("STG_DIM_CRM_CASES"."CRM_CASE_ID"="V_CRM_CASE_ID_EXISTS_IN_DWH"."CASE_ID"(+))
36    11 - access("T"."CASE_ID"="X"."CRM_CASE_ID")

Notes: Adding indexes may be an issue, depends on the index. This is not the only place this tables are being used, so indexes may interfere with other commands(Inserts mostly) on these tables.

I've also tried adding a filter on stg_scd and excluding all the dates smaller than the minimum date in Table_Cases, but that didn't help because it filtered only 1 year of records.

Thanks in advance.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `crm_ps_rc_case` ADD INDEX `crm_rc_idx_case_id` (`case_id`);
ALTER TABLE `dim_crm_cases` ADD INDEX `dim_cases_idx_crm_id` (`crm_case_id`);
The optimized query:
SELECT
        t.case_id 
    FROM
        crm_ps_rc_case t,
        dim_crm_cases x 
    WHERE
        t.case_id = x.crm_case_id

Related Articles



* original question posted on StackOverflow here.