Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance problems sometime query takes about 6-7 seonds.

I used explain plan to know where performance bottle neck was i saw some nested loops and after googling i found to use ORDERED USE_HASH but after using the performance has only increased .I am pasting below my explain plan output with and without use_hash.Please let me hwo best I can handle this.

I am using usign oracle 11g,Below is the query

SELECT DISTINCT HH_ID, HH_NM,HH_PATTERN_MAP_ID,TEST_NAME,CT_NAME,TEST_NAME, TASC1,TASC2,      LISTAGG(DATA_ANS, ', ') within group(order by DATA_ANS) over (partition BY HH_ID,HH_PATTERN_MAP_ID) DATA_ANS from(select DISTINCT TM.HH_ID,TM.HH_NM,KPM.HH_PATTERN_MAP_ID,(select R.TTEST_NM from TTEST R,TTEST_VALUE E WHERE R.TTEST_ID=E.TTEST_ID and E.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID) TTEST_NAME, (select CN.COUNTRY_NAME from TCNTRY CN, TCNTRY_VALUE CV where CN.CNTRY_ID=CV.CNTRY_ID and CV.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID) CNTRY_NAME, (select ST.STATE_NM from TTEA ST,TTEA_VALUE SV where ST.STATE_ID =SV.STATE_ID and SV.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID) STATE_NAME,(select PC.PRODUCT_TASC_NM from TPROD_TASC PC,TPROD_TASC_VALUE PV where PC.PRODUCT_TASC_ID =PV.PRODUCT_TASC_ID and PV.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID) TASC1,(select SC.PRODUCT_SUB_TASC_NM from TPROD_SUB_TASC SC,TPROD_SUB_TASC_VALUE SCV where SC.PRODUCT_SUB_TASC_ID = SCV.PRODUCT_SUB_TASC_ID and SCV.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID) TASC2,KFV.HH_FINAL_VALUE  SHORT_ANSWER from THH_DATA TM,THH_PATTERN_MAP KPM,THH_DATA_MAP KOM,THH_DATA_MAP KFV,tHH_field_map_definition KFMD where TM.HH_ID=KOM.HH_ID and KPM.HH_ID=KOM.HH_ID and KFV.HH_PATTERN_MAP_ID=KPM.HH_PATTERN_MAP_ID and kfv.field_id = kfmd.field_id and kfmd.answer_type = 'SA'  AND KOM.DEPT_ID=6  )




Before usign ORDERED USE_HASH

----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                       
| Id  | Operation                    | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                       
|   0 | SELECT STATEMENT             |                               |   109K|    30M|       | 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|   1 |  NESTED LOOPS                |                               |     1 |    17 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|   2 |   TABLE ACCESS BY INDEX ROWID| TTEST_VALUE                 |     1 |     7 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|*  3 |    INDEX UNIQUE SCAN         | REGION_VALUE_TTEST_ID   |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|   4 |   TABLE ACCESS BY INDEX ROWID| TTEST                       |     3 |    30 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|*  5 |    INDEX UNIQUE SCAN         | TTEST_PK                    |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|   6 |  NESTED LOOPS                |                               |     1 |    23 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|   7 |   TABLE ACCESS FULL          | TCOUNTRY                      |   260 |  3900 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|*  8 |   INDEX UNIQUE SCAN          | INDEX_CNTRY_HH_TTEST    |     1 |     8 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|   9 |  NESTED LOOPS                |                               |     1 |    43 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  10 |   TABLE ACCESS BY INDEX ROWID| TTEA_VALUE                  |     1 |     9 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 11 |    INDEX UNIQUE SCAN         | STATE_VALUE_TTEST_ID    |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|  12 |   TABLE ACCESS BY INDEX ROWID| TTEA                        |   115 |  3910 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|* 13 |    INDEX UNIQUE SCAN         | TTEA_PK                     |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|  14 |  NESTED LOOPS                |                               |     1 |    21 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  15 |   TABLE ACCESS BY INDEX ROWID| TPROD_TASC_VALUE          |     1 |     7 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 16 |    INDEX UNIQUE SCAN         | TASC_VALUE_TTEST_ID    |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|  17 |   TABLE ACCESS BY INDEX ROWID| TPROD_TASC                |     5 |    70 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|* 18 |    INDEX UNIQUE SCAN         | TMAJOR_LINE_PK                |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|  19 |  NESTED LOOPS                |                               |     1 |    31 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  20 |   TABLE ACCESS BY INDEX ROWID| TPROD_SUB_TASC_VALUE      |     1 |     7 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 21 |    INDEX UNIQUE SCAN         | SUBTASC_VALUE_TTEST_ID |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|  22 |   TABLE ACCESS BY INDEX ROWID| TPROD_SUB_TASC            |    42 |  1008 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|* 23 |    INDEX UNIQUE SCAN         | TMINOR_LINE_PK                |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|  24 |  HASH UNIQUE                 |                               |   109K|    30M|    34M| 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|  25 |   WINDOW SORT                |                               |   109K|    30M|    34M| 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|  26 |    VIEW                      |                               |   109K|    30M|       |  2173   (1)| 00:00:27 |                                                                                                                                                                                       
|  27 |     HASH UNIQUE              |                               |   109K|  8848K|  9936K|  2173   (1)| 00:00:27 |                                                                                                                                                                                       
|* 28 |      HASH JOIN               |                               |   109K|  8848K|       |    55   (8)| 00:00:01 |                                                                                                                                                                                       
|* 29 |       TABLE ACCESS FULL      | THH_FIELD_MAP_DEFINITION      |    75 |   525 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                       
|* 30 |       HASH JOIN              |                               |  3068 |   227K|       |    48   (5)| 00:00:01 |                                                                                                                                                                                       
|* 31 |        HASH JOIN             |                               |  2691 |   134K|       |    14   (8)| 00:00:01 |                                                                                                                                                                                       
|* 32 |         HASH JOIN            |                               |     8 |   352 |       |     6  (17)| 00:00:01 |                                                                                                                                                                                       
|* 33 |          TABLE ACCESS FULL   | THH_DATA_MAP                 |     8 |    56 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|  34 |          TABLE ACCESS FULL   | THH_DATA                    |    74 |  2738 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  35 |         INDEX FAST FULL SCAN | INDEX_HH_TTEST_ID       |  6837 | 47859 |       |     8   (0)| 00:00:01 |                                                                                                                                                                                       
|  36 |        TABLE ACCESS FULL     | THH_TEST_VALUE               |  7581 |   185K|       |    33   (0)| 00:00:01 |                                                                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                       


After usign ORDERED USE_HASH

----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                       
| Id  | Operation                    | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                       
|   0 | SELECT STATEMENT             |                               |   109K|    30M|       | 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|*  1 |  HASH JOIN                   |                               |     1 |    17 |       |     4  (25)| 00:00:01 |                                                                                                                                                                                       
|   2 |   TABLE ACCESS FULL          | TTEST                       |     3 |    30 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|   3 |   TABLE ACCESS BY INDEX ROWID| TTEST_VALUE                 |     1 |     7 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|*  4 |    INDEX UNIQUE SCAN         | REGION_VALUE_TTEST_ID   |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                       
|*  5 |  HASH JOIN                   |                               |     1 |    23 |       |    13   (8)| 00:00:01 |                                                                                                                                                                                       
|   6 |   TABLE ACCESS FULL          | TCOUNTRY                      |   260 |  3900 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|*  7 |   INDEX FAST FULL SCAN       | INDEX_CNTRY_HH_TTEST    |     1 |     8 |       |     9   (0)| 00:00:01 |                                                                                                                                                                                       
|*  8 |  HASH JOIN                   |                               |     1 |    43 |       |     6  (17)| 00:00:01 |                                                                                                                                                                                       
|   9 |   TABLE ACCESS FULL          | TTEA                        |   115 |  3910 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  10 |   TABLE ACCESS BY INDEX ROWID| TTEA_VALUE                  |     1 |     9 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 11 |    INDEX UNIQUE SCAN         | STATE_VALUE_TTEST_ID    |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|* 12 |  HASH JOIN                   |                               |     1 |    21 |       |     5  (20)| 00:00:01 |                                                                                                                                                                                       
|  13 |   TABLE ACCESS FULL          | TPROD_TASC                |     5 |    70 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|  14 |   TABLE ACCESS BY INDEX ROWID| TPROD_TASC_VALUE          |     1 |     7 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 15 |    INDEX UNIQUE SCAN         | TASC_VALUE_TTEST_ID    |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|* 16 |  HASH JOIN                   |                               |     1 |    31 |       |     5  (20)| 00:00:01 |                                                                                                                                                                                       
|  17 |   TABLE ACCESS FULL          | TPROD_SUB_TASC            |    42 |  1008 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|  18 |   TABLE ACCESS BY INDEX ROWID| TPROD_SUB_TASC_VALUE      |     1 |     7 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|* 19 |    INDEX UNIQUE SCAN         | SUBTASC_VALUE_TTEST_ID |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                       
|  20 |  HASH UNIQUE                 |                               |   109K|    30M|    34M| 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|  21 |   WINDOW SORT                |                               |   109K|    30M|    34M| 15990   (1)| 00:03:12 |                                                                                                                                                                                       
|  22 |    VIEW                      |                               |   109K|    30M|       |  2173   (1)| 00:00:27 |                                                                                                                                                                                       
|  23 |     HASH UNIQUE              |                               |   109K|  8848K|  9936K|  2173   (1)| 00:00:27 |                                                                                                                                                                                       
|* 24 |      HASH JOIN               |                               |   109K|  8848K|       |    55   (8)| 00:00:01 |                                                                                                                                                                                       
|* 25 |       TABLE ACCESS FULL      | THH_FIELD_MAP_DEFINITION      |    75 |   525 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                       
|* 26 |       HASH JOIN              |                               |  3068 |   227K|       |    48   (5)| 00:00:01 |                                                                                                                                                                                       
|* 27 |        HASH JOIN             |                               |  2691 |   134K|       |    14   (8)| 00:00:01 |                                                                                                                                                                                       
|* 28 |         HASH JOIN            |                               |     8 |   352 |       |     6  (17)| 00:00:01 |                                                                                                                                                                                       
|* 29 |          TABLE ACCESS FULL   | THH_DATA_MAP                 |     8 |    56 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                       
|  30 |          TABLE ACCESS FULL   | THH_DATA                    |    74 |  2738 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                       
|  31 |         INDEX FAST FULL SCAN | INDEX_HH_TTEST_ID       |  6837 | 47859 |       |     8   (0)| 00:00:01 |                                                                                                                                                                                       
|  32 |        TABLE ACCESS FULL     | THH_TEST_VALUE               |  7581 |   185K|       |    33   (0)| 00:00:01 |                                                                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------                                    
share|improve this question
    
Do you really need to return 109K rows to the application? This does not sound right. –  mustaccio Apr 9 at 15:05
    
That will be filtered , i will have to join to many tables –  user3514542 Apr 11 at 5:54
    
actuall rows fetched at last is 745 –  user3514542 Apr 11 at 11:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.