Home » RDBMS Server » Performance Tuning » Query does not error, but does not come back with a result set (as if it's in an endless loop) (Oracle 11G running on UNIX AIX 6.1 O/S)
Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642508] Sun, 13 September 2015 17:15 Go to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
Hi, I've searched for answers on this forum, as well as google and I've tried a few suggestions, but nothing has worked so far. I'm running what I think is a simple nested subquery.

Issue is that the query never stops. I let it run for more than 15 minutes, but it still doesn't come back. When I run everything except the outermost query, I get a result set in less than 2 seconds (I have a hardcoded grup in the innermost query to limit the data). But adding the outermost query causes it to never stop!


Starting with the innermost query...
The innermost query uses D_ESTABLISH which is the most recent established date, where the D_UPDT is greater than 8/31/13.
The 2nd subquery gives me the specific group where the D_RELEASE date is not null.
The 3rd subquery gives me the I_CUST with the lowest number for that I_GRUP (1 to many -- I_GRUP to I_CUST)
Up to here, it's fine and runs fast
The 4th outermost query simply takes the I_CUST from previous query and gives me

SELECT DISTINCT ACCT.I_CUST FROM SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT
WHERE ACCT.I_CUST IN 
   (SELECT DISTINCT MIN(CUST.I_CUST)   
     FROM SFDC_STAGING.STG_IND_SALI_CUST CUST,
          SFDC_STAGING.STG_IND_SALI_ADDR ADDR,
          SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
          SFDC_STAGING.STG_IND_SALI_GRUP GRUP
     WHERE ACCT.I_CUST = CUST.I_CUST
       AND CUST.I_CURR_GRUP = GRUP.I_GRUP
       AND ADDR.I_GRUP = GRUP.I_GRUP
       AND CUST.I_CUST = EMAL.I_CUST(+)
       AND  CUST.C_STAT = 'A'                                                     --ONLY GET ACTIVE CUSTOMERS
       AND  ADDR.C_STAT = 'A'                                                     --WITH A VALID ADDRESS
       AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL)                             --AND ONLY IF THE EMAIL STATUS NOT INACTIVE
       AND  GRUP.I_GRUP IN
             (SELECT DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (SELECT DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,                  
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'         --WHERE DATE GREATER THAN THIS DATE
                        AND GRAG.I_GRUP = 8722127                                 --FOR THIS SPECIFIC GROUP
                     GROUP BY GRAG.I_GRUP
                    )
                 AND GRAG.I_GRUP = GRUP.I_GRUP
                 AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'                --USE DATE HERE TOO OR ELSE IT WON'T WORK
   )
 GROUP BY GRUP.I_GRUP
)


Table definitions are here:
SFDC_STAGING.STG_IND_SALI_CUST CUST
i_cust INTEGER 10
i_curr_grup INTEGER 10
i_ssn VARCHAR2(9)
d_crea TIMESTAMP(6)
d_updt TIMESTAMP(6)
c_stat VARCHAR2(1)
n_prefix VARCHAR2(4)
n_first VARCHAR2(15)
n_mi VARCHAR2(1)
n_last VARCHAR2(24)

SFDC_STAGING.STG_IND_SALI_ADDR ADDR
i_addr INTEGER 10
i_grup INTEGER 10
d_crea DATE
d_updt DATE
c_stat VARCHAR2(1)
t_type VARCHAR2(10)
t_addr_1 VARCHAR2(30)
t_addr_2 VARCHAR2(30)
t_city VARCHAR2(24)
t_state VARCHAR2(2)
i_zipc VARCHAR2(11)
c_county VARCHAR2(30)
c_county_code VARCHAR2(5)

SFDC_STAGING.STG_IND_SALI_EMAIL EMAL
i_email INTEGER 10
i_cust INTEGER 10
t_e_mail VARCHAR 120
c_stat VARCHAR2(1)
d_crea DATE
d_updt TIMESTAMP(6)

SFDC_STAGING.STG_IND_SALI_GRUP GRUP
i_grup INTEGER 10
d_crea DATE
d_updt TIMESTAMP(6)
c_stat VARCHAR2(1)
c_ownr_type VARCHAR2(1)
c_market_org VARCHAR2(12)
t_suppress VARCHAR2(10)
t_language VARCHAR2(2)

SFDC_STAGING.STG_IND_SALI_GRAG GRAG
i_grup INTEGER 10
i_agnt VARCHAR2(12)
d_crea DATE
d_updt DATE
d_establish DATE
d_release DATE
d_sked_release DATE

SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT
I_CUST	INTEGER		
N_PREFIX VARCHAR2(10)
N_FIRST	VARCHAR2(20)
N_LAST	VARCHAR2(30)
T_EVENING_PHONE	VARCHAR2(20)
T_DAY_PHONE VARCHAR2(20)
D_BIRTH DATE
C_GEND	CHAR (1)
P_ADDR_1 VARCHAR2(30)
P_ADDR_2 VARCHAR2(30)
P_CITY VARCHAR2(30)
P_STATE	VARCHAR2(2)
P_ZIPC VARCHAR2 (15)

Please help. I think I have provided everything that is requested.

*BlackSwan added {code} tags. Please do so yourself in the future.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[Updated on: Sun, 13 September 2015 17:23] by Moderator

Report message to a moderator

Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642509 is a reply to message #642508] Sun, 13 September 2015 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
Since we have no data rows, our performance may be different from your results

Please post EXPLAIN PLAN for your query
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642510 is a reply to message #642509] Sun, 13 September 2015 17:41 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
Here is the explain plan. Please let me know if this is not what your are looking for:

Plan
SELECT STATEMENT ALL_ROWSCost: 18,524,592,650 Bytes: 19 Cardinality: 1
31 HASH UNIQUE Cost: 18,524,592,650 Bytes: 19 Cardinality: 1
30 HASH JOIN Cost: 18,524,592,649 Bytes: 19 Cardinality: 1
28 VIEW VIEW SYS.VW_NSO_1 Cost: 18,524,557,795 Bytes: 13 Cardinality: 1
27 HASH GROUP BY Cost: 18,524,557,795 Bytes: 81 Cardinality: 1
26 FILTER
20 HASH JOIN Cost: 223,169 Bytes: 60,864,372 Cardinality: 751,412
18 HASH JOIN Cost: 183,212 Bytes: 49,726,578 Cardinality: 681,186
16 FILTER
15 HASH JOIN OUTER Cost: 136,165 Bytes: 45,592,227 Cardinality: 680,481
13 HASH JOIN Cost: 121,127 Bytes: 42,362,472 Cardinality: 718,008
10 JOIN FILTER CREATE SYS.:BF0000 Cost: 60,466 Bytes: 27,924,750 Cardinality: 620,550
9 HASH JOIN Cost: 60,466 Bytes: 27,924,750 Cardinality: 620,550
6 JOIN FILTER CREATE SYS.:BF0001 Cost: 36,460 Bytes: 23,960,625 Cardinality: 614,375
5 HASH JOIN Cost: 36,460 Bytes: 23,960,625 Cardinality: 614,375
2 JOIN FILTER CREATE SYS.:BF0002 Cost: 14,591 Bytes: 8,026,618 Cardinality: 472,154
1 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_GRUP Cost: 14,591 Bytes: 8,026,618 Cardinality: 472,154
4 JOIN FILTER USE SYS.:BF0002 Cost: 10,452 Bytes: 146,868,392 Cardinality: 6,675,836
3 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_GRAG Cost: 10,452 Bytes: 146,868,392 Cardinality: 6,675,836
8 JOIN FILTER USE SYS.:BF0001 Cost: 14,435 Bytes: 56,658,528 Cardinality: 9,443,088
7 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_GRUP Cost: 14,435 Bytes: 56,658,528 Cardinality: 9,443,088
12 JOIN FILTER USE SYS.:BF0000 Cost: 45,632 Bytes: 151,443,782 Cardinality: 10,817,413
11 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_CUST Cost: 45,632 Bytes: 151,443,782 Cardinality: 10,817,413
14 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_EMAIL Cost: 8,068 Bytes: 38,333,432 Cardinality: 4,791,679
17 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT Cost: 34,823 Bytes: 67,807,746 Cardinality: 11,301,291
19 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_ADDR Cost: 27,410 Bytes: 82,503,528 Cardinality: 10,312,941
25 FILTER
24 SORT GROUP BY NOSORT Cost: 24,894 Bytes: 31 Cardinality: 1
23 HASH JOIN Cost: 24,894 Bytes: 31 Cardinality: 1
21 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_GRUP Cost: 14,459 Bytes: 17 Cardinality: 1
22 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_GRAG Cost: 10,435 Bytes: 14 Cardinality: 1
29 TABLE ACCESS STORAGE FULL TABLE SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT Cost: 34,823 Bytes: 67,807,746 Cardinality: 11,301,291
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642511 is a reply to message #642510] Sun, 13 September 2015 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>Please let me know if this is not what your are looking for:

It is/was not what I was looking for.

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof


I see MANY Full Table Scans; which can indicate that INDEXES are missing or statistics are not current.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642512 is a reply to message #642511] Sun, 13 September 2015 19:48 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
Here is the output of the explain plan. I think I was able to get it this time. Please let me know if this is still not what you are looking for.
Plan hash value: 3210202793
 
 -------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                           |     3 |   630 |       |  1631K  (1)| 05:26:18 |
|   1 |  HASH UNIQUE                                    |                           |     3 |   630 |       |  1631K  (1)| 05:26:18 |
|*  2 |   HASH JOIN                                     |                           |     3 |   630 |       |  1631K  (1)| 05:26:18 |
|   3 |    NESTED LOOPS                                 |                           |     1 |   188 |       |  1631K  (1)| 05:26:17 |
|   4 |     NESTED LOOPS                                |                           |     1 |   188 |       |  1631K  (1)| 05:26:17 |
|   5 |      NESTED LOOPS                               |                           |     1 |   175 |       |  1631K  (1)| 05:26:17 |
|   6 |       NESTED LOOPS                              |                           |     1 |   169 |       |  1631K  (1)| 05:26:17 |
|   7 |        NESTED LOOPS OUTER                       |                           |     1 |   157 |       |  1631K  (1)| 05:26:17 |
|   8 |         NESTED LOOPS OUTER                      |                           |     1 |   129 |       |  1631K  (1)| 05:26:17 |
|*  9 |          HASH JOIN                              |                           |     1 |   121 |       |  1631K  (1)| 05:26:17 |
|  10 |           VIEW                                  | VW_NSO_1                  |     1 |    13 |       |  1596K  (1)| 05:19:17 |
|  11 |            HASH GROUP BY                        |                           |     1 |    75 |    60M|  1596K  (1)| 05:19:17 |
|* 12 |             FILTER                              |                           |       |       |       |            |          |
|  13 |              NESTED LOOPS                       |                           |   750K|    53M|       | 97353   (1)| 00:19:29 |
|  14 |               NESTED LOOPS                      |                           |   750K|    53M|       | 97353   (1)| 00:19:29 |
|* 15 |                FILTER                           |                           |       |       |       |            |          |
|* 16 |                 HASH JOIN OUTER                 |                           |   680K|    43M|    48M| 76934   (1)| 00:15:24 |
|  17 |                  NESTED LOOPS                   |                           |   718K|    40M|       | 61896   (1)| 00:12:23 |
|  18 |                   NESTED LOOPS                  |                           |   718K|    40M|       | 61896   (1)| 00:12:23 |
|* 19 |                    HASH JOIN                    |                           |   620K|    26M|    29M| 43275   (1)| 00:08:40 |
|  20 |                     NESTED LOOPS                |                           |   614K|    22M|       | 33481   (1)| 00:06:42 |
|  21 |                      NESTED LOOPS               |                           |   614K|    22M|       | 33481   (1)| 00:06:42 |
|* 22 |                       TABLE ACCESS STORAGE FULL | STG_IND_SALI_GRUP         |   472K|  7838K|       | 14591   (2)| 00:02:56 |
|* 23 |                       INDEX RANGE SCAN          | STG_IND_SALI_GRAG_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|* 24 |                      TABLE ACCESS BY INDEX ROWID| STG_IND_SALI_GRAG         |     1 |    22 |       |     1   (0)| 00:00:01 |
|  25 |                     INDEX FULL SCAN             | STG_IND_SALI_GRUP_PK      |  9443K|    54M|       |   223   (1)| 00:00:03 |
|* 26 |                    INDEX RANGE SCAN             | STG_IND_SALI_CUST_PK2     |     1 |       |       |     1   (0)| 00:00:01 |
|* 27 |                   TABLE ACCESS BY INDEX ROWID   | STG_IND_SALI_CUST         |     1 |    14 |       |     1   (0)| 00:00:01 |
|  28 |                  TABLE ACCESS STORAGE FULL      | STG_IND_SALI_EMAIL        |  4791K|    36M|       |  8068   (1)| 00:01:37 |
|* 29 |                INDEX RANGE SCAN                 | STG_IND_SALI_ADDR_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|* 30 |               TABLE ACCESS BY INDEX ROWID       | STG_IND_SALI_ADDR         |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 31 |              FILTER                             |                           |       |       |       |            |          |
|  32 |               SORT GROUP BY NOSORT              |                           |     1 |    31 |       |     2   (0)| 00:00:01 |
|  33 |                MERGE JOIN CARTESIAN             |                           |     1 |    31 |       |     2   (0)| 00:00:01 |
|* 34 |                 TABLE ACCESS BY INDEX ROWID     | STG_IND_SALI_GRUP         |     1 |    17 |       |     1   (0)| 00:00:01 |
|* 35 |                  INDEX RANGE SCAN               | STG_IND_SALI_GRUP_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|  36 |                 BUFFER SORT                     |                           |     1 |    14 |       |     1   (0)| 00:00:01 |
|  37 |                  TABLE ACCESS BY INDEX ROWID    | STG_IND_SALI_GRAG         |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 38 |                   INDEX RANGE SCAN              | STG_IND_SALI_GRAG_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|  39 |           TABLE ACCESS STORAGE FULL             | STG_IND_SALI_PERSON_ACCNT |    11M|  1163M|       | 34987   (1)| 00:07:00 |
|  40 |          TABLE ACCESS BY INDEX ROWID            | STG_IND_SALI_CUS2         |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 41 |           INDEX UNIQUE SCAN                     | STG_IND_SALI_CUS2_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|  42 |         TABLE ACCESS BY INDEX ROWID             | STG_IND_SALI_EMAIL        |     1 |    28 |       |     1   (0)| 00:00:01 |
|* 43 |          INDEX RANGE SCAN                       | STG_IND_SALI_EMAIL_PK     |     1 |       |       |     1   (0)| 00:00:01 |
|* 44 |        INDEX RANGE SCAN                         | STG_IND_SALI_CUST_PK3     |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 45 |       INDEX RANGE SCAN                          | STG_IND_SALI_GRUP_PK      |     1 |     6 |       |     1   (0)| 00:00:01 |
|* 46 |      INDEX RANGE SCAN                           | STG_IND_SALI_GRAG_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|  47 |     TABLE ACCESS BY INDEX ROWID                 | STG_IND_SALI_GRAG         |     1 |    13 |       |     1   (0)| 00:00:01 |
|  48 |    TABLE ACCESS STORAGE FULL                    | STG_IND_SALI_USER         |  1363 | 29986 |       |    11   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("GRAG"."I_AGNT"="USR"."I_AGNT")
   9 - access("ACCT"."I_CUST"="MIN(CUST.I_CUST)")
  12 - filter( EXISTS (SELECT 0 FROM "SFDC_STAGING"."STG_IND_SALI_GRUP" "GRUP","SFDC_STAGING"."STG_IND_SALI_GRAG" "GRAG" 
              WHERE "GRAG"."I_GRUP"=8722127 AND "GRUP"."I_GRUP"=8722127 AND TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'2013083
              1' GROUP BY "GRAG"."I_GRUP" HAVING MAX("GRAG"."D_ESTABLISH")=:B1))
  15 - filter("EMAL"."C_STAT"='A' OR "EMAL"."C_STAT" IS NULL)
  16 - access("CUST"."I_CUST"="EMAL"."I_CUST"(+))
  19 - access("GRUP"."I_GRUP"="GRAG"."I_GRUP")
  22 - storage(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
       filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
  23 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
  24 - filter("GRAG"."D_RELEASE" IS NOT NULL)
  26 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
  27 - filter("CUST"."C_STAT"='A')
  29 - access("ADDR"."I_GRUP"="GRUP"."I_GRUP")
  30 - filter("ADDR"."C_STAT"='A')
  31 - filter(MAX("GRAG"."D_ESTABLISH")=:B1)
  34 - filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
  35 - access("GRUP"."I_GRUP"=8722127)
  38 - access("GRAG"."I_GRUP"=8722127)
  41 - access("ACCT"."I_CUST"="CUS2"."I_CUST"(+))
  43 - access("ACCT"."I_CUST"="EMAL"."I_CUST"(+))
  44 - access("ACCT"."I_CUST"="CUST"."I_CUST")
  45 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
  46 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
 
Note
-----
   - dynamic sampling used for this statement (level=6)

*BlackSwan added {code} tags.
PLEASE do so yourself in the future

Here is the DDLs for all tables involved, as well as the indeces:

CREATE TABLE STG_IND_SALI_ADDR
(
I_ADDR INTEGER NOT NULL,
I_GRUP INTEGER NOT NULL,
D_CREA DATE NOT NULL,
D_UPDT DATE NOT NULL,
C_STAT VARCHAR2(1 CHAR) NOT NULL,
T_TYPE VARCHAR2(10 CHAR) NOT NULL,
T_ADDR_1 VARCHAR2(30 CHAR),
T_ADDR_2 VARCHAR2(30 CHAR),
T_CITY VARCHAR2(24 CHAR),
T_STATE VARCHAR2(2 CHAR),
I_ZIPC VARCHAR2(11 CHAR),
C_COUNTY VARCHAR2(30 CHAR),
C_COUNTY_CODE VARCHAR2(5 CHAR)
);

CREATE TABLE STG_IND_SALI_CUGR
(
I_CUST INTEGER NOT NULL,
I_GRUP INTEGER NOT NULL,
D_CREA DATE NOT NULL,
D_UPDT DATE NOT NULL,
C_STAT VARCHAR2(1 CHAR) NOT NULL
);

CREATE TABLE STG_IND_SALI_CUS2
(
I_CUST INTEGER NOT NULL,
D_CREA DATE NOT NULL,
D_UPDT DATE NOT NULL,
C_STAT_EMP VARCHAR2(1 CHAR),
C_HMO_FAMILIAR VARCHAR2(1 CHAR),
T_PHYSICIAN VARCHAR2(30 CHAR),
T_HOSP_PREF VARCHAR2(30 CHAR),
T_PHAR_PREF VARCHAR2(15 CHAR),
T_CARRIER VARCHAR2(24 CHAR),
C_INS_STAT VARCHAR2(1 CHAR),
C_MEDICAID_STAT VARCHAR2(1 CHAR),
T_UNINSURED VARCHAR2(24 CHAR),
C_HMO_CVG VARCHAR2(1 CHAR),
C_EMPL_CVG VARCHAR2(1 CHAR),
T_PRI_EMPL VARCHAR2(20 CHAR),
T_SPSE_EMPL VARCHAR2(20 CHAR),
T_INSUR_REASON VARCHAR2(50 CHAR),
T_HOUSHLD_INCM VARCHAR2(12 CHAR),
T_ETHNCTY VARCHAR2(40 CHAR),
C_TBCCO_STAT VARCHAR2(1 CHAR),
T_FMLY_TYP VARCHAR2(100 CHAR),
T_EDCTN_LVL VARCHAR2(20 CHAR),
C_PRI_INS_COVG VARCHAR2(1 CHAR),
C_SBSDY_ELIG VARCHAR2(1 CHAR),
T_EMPLMNT_STAT VARCHAR2(20 CHAR),
T_FDRL_PVRTY_LVL VARCHAR2(5 CHAR),
C_MEDICAID_WITHIN_LST_YR VARCHAR2(1 CHAR),
D_CVRD_BY_WLP_SINCE DATE,
C_MEDICAID VARCHAR2(1 CHAR),
C_MEDICARE VARCHAR2(1 CHAR),
I_SBSDY_AMT NUMBER(12,2),
C_CUR_COVG VARCHAR2(1 CHAR)
);

CREATE TABLE STG_IND_SALI_CUST
(
I_CUST INTEGER,
I_CURR_GRUP INTEGER,
I_SSN VARCHAR2(9 CHAR),
D_CREA TIMESTAMP(6),
D_UPDT TIMESTAMP(6),
C_STAT VARCHAR2(1 CHAR),
N_PREFIX VARCHAR2(4 CHAR),
N_FIRST VARCHAR2(15 CHAR),
N_MI VARCHAR2(1 CHAR),
N_LAST VARCHAR2(24 CHAR),
N_SUFFIX VARCHAR2(4 CHAR),
T_EVENING_PHONE VARCHAR2(10 CHAR),
T_DAY_PHONE VARCHAR2(10 CHAR),
T_DAY_EXT VARCHAR2(5 CHAR),
T_FAX VARCHAR2(10 CHAR),
T_MARITAL_STAT VARCHAR2(9 CHAR),
D_BIRTH TIMESTAMP(6),
C_GEND VARCHAR2(1 CHAR),
C_LEVEL VARCHAR2(1 CHAR),
T_EMPLOYER VARCHAR2(24 CHAR),
T_SUPPRESS VARCHAR2(10 CHAR),
T_REL_TYPE VARCHAR2(10 CHAR),
I_SPOUSE INTEGER,
T_CNSNT_DYS VARCHAR2(4 CHAR),
D_CNSNT_END_DT TIMESTAMP(6),
T_HCID VARCHAR2(15 CHAR)
);

CREATE TABLE STG_IND_SALI_EMAIL
(
I_EMAIL INTEGER,
I_CUST INTEGER,
T_E_MAIL VARCHAR2(120 CHAR),
C_STAT VARCHAR2(1 CHAR),
D_CREA DATE,
D_UPDT DATE
);

CREATE TABLE STG_IND_SALI_GRAG
(
I_GRUP INTEGER,
I_AGNT VARCHAR2(12 CHAR),
D_CREA DATE,
D_UPDT DATE,
D_ESTABLISH DATE,
D_RELEASE DATE,
D_SKED_RELEASE DATE
);

CREATE TABLE STG_IND_SALI_GRUP
(
I_GRUP INTEGER,
D_CREA DATE,
D_UPDT TIMESTAMP(6),
C_STAT CHAR(1 CHAR),
C_OWNR_TYPE CHAR(1 CHAR),
C_MARKET_ORG CHAR(12 CHAR),
T_SUPPRESS CHAR(10 CHAR),
T_LANGUAGE CHAR(2 CHAR)
);

CREATE TABLE STG_IND_SALI_PERSON_ACCNT
(
I_CUST INTEGER,
N_PREFIX VARCHAR2(10 CHAR),
N_FIRST VARCHAR2(20 CHAR),
N_LAST VARCHAR2(30 CHAR),
T_EVENING_PHONE VARCHAR2(20 CHAR),
T_DAY_PHONE VARCHAR2(20 CHAR),
D_BIRTH DATE,
C_GEND CHAR(1 CHAR),
T_SUPPRESS VARCHAR2(10 CHAR),
D_CNSNT_END_DT DATE,
I_GRUP INTEGER,
P_ADDR_1 VARCHAR2(30 CHAR),
P_ADDR_2 VARCHAR2(30 CHAR),
P_CITY VARCHAR2(30 CHAR),
P_STATE VARCHAR2(2 CHAR),
P_ZIPC VARCHAR2(15 CHAR),
);

CREATE TABLE STG_IND_SALI_USER
(
I_AGNT VARCHAR2(20 CHAR),
C_TYPE VARCHAR2(10 CHAR),
T_NAME VARCHAR2(30 CHAR),
T_TITLE VARCHAR2(30 CHAR),
T_AGENCY_NAME VARCHAR2(60 CHAR),
T_PHON_VDN VARCHAR2(10 CHAR),
T_PHON VARCHAR2(10 CHAR),
T_PHON_EXT VARCHAR2(10 CHAR),
T_PHON_800 VARCHAR2(10 CHAR),
T_FAX VARCHAR2(10 CHAR),
T_BRAND VARCHAR2(15 CHAR),
T_LICENSE VARCHAR2(60 CHAR),
T_ENCRYPTED_TIN VARCHAR2(10 CHAR),
C_CERTIFICATION_NUM VARCHAR2(30 CHAR),
C_NATL_PRODUCER VARCHAR2(30 CHAR),
C_WLP_PRODUCER VARCHAR2(30 CHAR),
C_PARENT_TIN VARCHAR2(20 CHAR),
T_MICROSITE_URL VARCHAR2(255 CHAR),
C_LANID VARCHAR2(10 CHAR),
EMAIL_ADDR VARCHAR2(100 CHAR)
);

CREATE INDEX STG_IND_SALI_ADDR_PK ON STG_IND_SALI_ADDR (I_GRUP);

CREATE INDEX STG_IND_SALI_CUGR_PK ON STG_IND_SALI_CUGR (I_GRUP);

CREATE UNIQUE INDEX STG_IND_SALI_CUS2_PK ON STG_IND_SALI_CUS2 (I_CUST);

CREATE INDEX STG_IND_SALI_CUST_PK ON STG_IND_SALI_CUST (I_CUST);

CREATE INDEX STG_IND_SALI_CUST_PK2 ON STG_IND_SALI_CUST (I_CURR_GRUP);

CREATE INDEX STG_IND_SALI_CUST_PK3 ON STG_IND_SALI_CUST (I_CUST, I_CURR_GRUP);

CREATE INDEX STG_IND_SALI_EMAIL_PK ON STG_IND_SALI_EMAIL(I_CUST);

CREATE INDEX STG_IND_SALI_GRAG_PK ON STG_IND_SALI_GRAG (I_GRUP);

CREATE INDEX STG_IND_SALI_GRUP_PK ON STG_IND_SALI_GRUP (I_GRUP);


I don't have the access to run SQL_TRACE(tkprof). I asked the DBA team to do it, but it may be tomorrow morning 9 am ET before I get a response.

I'm hoping the explain plan above will help. Is it doing a table scan everywhere it says "TABLE ACCESS STORAGE FULL"? If so, should that be the table I have an index created on?

[Updated on: Sun, 13 September 2015 20:10] by Moderator

Report message to a moderator

Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642513 is a reply to message #642512] Sun, 13 September 2015 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
> If so, should that be the table I have an index created on?
More often than not, columns in any WHERE clause would benefit from having an INDEX on them.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642514 is a reply to message #642513] Sun, 13 September 2015 20:15 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
EXPLAIN PLAN estimates query runs about 5.5 HOURS

What is "VW_NSO_1"? I suspect it is a VIEW.
More specifically does it reference any remote DB?
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642515 is a reply to message #642514] Sun, 13 September 2015 20:31 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
I don't what "VW_NSO_1" is. It's not a table in my query. I'm not referencing any views in this query and nothing accessed remotely. All tables are in the same database.

Is there a way I can identify which line in my query "VW_NSO_1" may be referencing?
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642516 is a reply to message #642514] Sun, 13 September 2015 20:34 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
...by the way, I put an index on just about all of the columns referenced in the where clauses in my query and now it does come back in less than 3 minutes consistently. But 3 minutes is still way too long when my innermost subquery is only pulling data for one group.

Again, it only takes a long time when I added the outermost query.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642518 is a reply to message #642516] Sun, 13 September 2015 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
We only know what you actually post.

> it only takes a long time when I added the outermost query.
post both short query & EXPLAIN PLAN
&
query with outermost query & EXPLAIN PLAN
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642519 is a reply to message #642518] Sun, 13 September 2015 21:50 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
I put on index on every field in the where clause and now when I run the query with 1 group in the innermost query, it comes back in 1 sec with the correct data. Smile

However, I also tried running it by removing/commenting out line where I hardcoded the 1 group "AND GRAG.I_GRUP = 8722127" in the innermost query and it has been running for over 10 minutes. Sad

Below is the new explain plan with the entire SQL (all subqueries). I counted about 17K unique I_GRUPs, which is what I'm groupin by.

Also attached is the tkprof from SQL_TRACE. Was able to get the oncall DBA to run it for me.


Plan hash value: 2051384253

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 630 | 17 (12)| 00:00:01 |
| 1 | HASH UNIQUE | | 3 | 630 | 17 (12)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 630 | 16 (7)| 00:00:01 |
| 3 | NESTED LOOPS | | 11 | 630 | 16 (7)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 188 | 15 (7)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 175 | 14 (8)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 147 | 13 (8)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 141 | 12 (9)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 129 | 11 (10)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 121 | 10 (10)| 00:00:01 |
| 10 | VIEW | VW_NSO_2 | 1 | 13 | 9 (12)| 00:00:01 |
| 11 | HASH GROUP BY | | 1 | 84 | 9 (12)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 84 | 8 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 84 | 8 (0)| 00:00:01 |
|* 14 | FILTER | | | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 76 | 7 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 68 | 6 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 54 | 5 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 37 | 4 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 |
| 20 | VIEW | VW_NSO_1 | 1 | 9 | 2 (0)| 00:00:01 |
| 21 | HASH GROUP BY | | 1 | 31 | 2 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 1 | 31 | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK3 | 1 | 17 | 1 (0)| 00:00:01 |
| 24 | BUFFER SORT | | 1 | 14 | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| STG_IND_SALI_GRAG | 1 | 14 | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_GRAG | 1 | 22 | 1 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK4 | 1 | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK3 | 1 | 17 | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_CUST | 1 | 14 | 1 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | STG_IND_SALI_CUST_PK2 | 1 | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_EMAIL | 1 | 8 | 1 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | STG_IND_SALI_EMAIL_PK | 1 | | 1 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | STG_IND_SALI_ADDR_PK | 1 | | 1 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_ADDR | 1 | 8 | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_PERSON_ACCNT | 1 | 108 | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | STG_IND_SALI_PA1_PK | 1 | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_CUS2 | 1 | 8 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | STG_IND_SALI_CUS2_PK | 1 | | 1 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | STG_IND_SALI_CUST_PK3 | 1 | 12 | 1 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK | 1 | 6 | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_EMAIL | 1 | 28 | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | STG_IND_SALI_EMAIL_PK | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_GRAG | 1 | 13 | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | STG_IND_SALI_USER_PK | 11 | | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_USER | 3 | 66 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------

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

14 - filter("EMAL"."C_STAT"='A' OR "EMAL"."C_STAT" IS NULL)
23 - access("GRUP"."I_GRUP"=8722127)
filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
26 - access("GRAG"."I_GRUP"=8722127)
27 - filter("GRAG"."D_RELEASE" IS NOT NULL)
28 - access("GRAG"."D_ESTABLISH"="$kkqu_col_1")
29 - access("GRUP"."I_GRUP"="GRAG"."I_GRUP")
30 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
31 - filter("CUST"."C_STAT"='A')
32 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
34 - access("CUST"."I_CUST"="EMAL"."I_CUST"(+))
35 - access("ADDR"."I_GRUP"="GRUP"."I_GRUP")
36 - filter("ADDR"."C_STAT"='A')
38 - access("ACCT"."I_CUST"="MIN(CUST.I_CUST)")
40 - access("ACCT"."I_CUST"="CUS2"."I_CUST"(+))
41 - access("ACCT"."I_CUST"="CUST"."I_CUST")
42 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
44 - access("ACCT"."I_CUST"="EMAL"."I_CUST"(+))
46 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
47 - access("GRAG"."I_AGNT"="USR"."I_AGNT")

Note
-----
- dynamic sampling used for this statement (level=6)
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642521 is a reply to message #642519] Sun, 13 September 2015 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
I give UP!

Since you choose to ignore Posting Guidelines & refuse to use {code} tags to make the details understandable, I choose to stop trying to assist.
I don't have the intimate knowledge of your query & need to compare & contrast to know where changes occur.

> I also tried running it by removing/commenting out line where I hardcoded the 1 group "AND GRAG.I_GRUP = 8722127" in the innermost query and it has been running for over 10 minutes.

don't you think it might be enlightening to compare the two EXPLAIN PLANS to understand what the difference is between quick & slow queries?
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642524 is a reply to message #642521] Sun, 13 September 2015 22:30 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
My apologies, from reading the notes, I thought the code tags were the indentation, etc (not literally the word "code") to help make it more readable. I added the code tags below. When I do the explain plan, it has indentation, so I leave it as it so as to not mess it up. Also attached the tkprof again because the DBA gave me another one.

Here is the SQL and explain plan from the inner subqueries, without the outermost query:
SELECT DISTINCT MIN(CUST.I_CUST)   
     FROM SFDC_STAGING.STG_IND_SALI_CUST CUST,
          SFDC_STAGING.STG_IND_SALI_ADDR ADDR,
          SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
          SFDC_STAGING.STG_IND_SALI_GRUP GRUP
     WHERE CUST.I_CURR_GRUP = GRUP.I_GRUP
       AND ADDR.I_GRUP = GRUP.I_GRUP
       AND CUST.I_CUST = EMAL.I_CUST(+)
       AND  CUST.C_STAT = 'A'
       AND  ADDR.C_STAT = 'A'
       AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL)
       AND  GRUP.I_GRUP IN
             (SELECT DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (SELECT DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITHH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,           
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
                        AND GRAG.I_GRUP IN (8722127)
                     GROUP BY GRAG.I_GRUP
                    )
                 AND GRAG.I_GRUP = GRUP.I_GRUP
                 AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
   )
 GROUP BY GRUP.I_GRUP


Plan hash value: 421818038

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 9 (12)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 84 | 9 (12)| 00:00:01 |
| 2 | HASH GROUP BY | | 1 | 84 | 9 (12)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 84 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 84 | 8 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 76 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 68 | 6 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 54 | 5 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 37 | 4 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 |
| 11 | VIEW | VW_NSO_1 | 1 | 9 | 2 (0)| 00:00:01 |
| 12 | HASH GROUP BY | | 1 | 31 | 2 (0)| 00:00:01 |
| 13 | MERGE JOIN CARTESIAN | | 1 | 31 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK3 | 1 | 17 | 1 (0)| 00:00:01 |
| 15 | BUFFER SORT | | 1 | 14 | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID| STG_IND_SALI_GRAG | 1 | 14 | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_GRAG | 1 | 22 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK4 | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK3 | 1 | 17 | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_CUST | 1 | 14 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | STG_IND_SALI_CUST_PK2 | 1 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_EMAIL | 1 | 8 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | STG_IND_SALI_EMAIL_PK | 1 | | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | STG_IND_SALI_ADDR_PK | 1 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_ADDR | 1 | 8 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

5 - filter("EMAL"."C_STAT"='A' OR "EMAL"."C_STAT" IS NULL)
14 - access("GRUP"."I_GRUP"=8722127)
filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
17 - access("GRAG"."I_GRUP"=8722127)
18 - filter("GRAG"."D_RELEASE" IS NOT NULL)
19 - access("GRAG"."D_ESTABLISH"="$kkqu_col_1")
20 - access("GRUP"."I_GRUP"="GRAG"."I_GRUP")
21 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
22 - filter("CUST"."C_STAT"='A')
23 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
25 - access("CUST"."I_CUST"="EMAL"."I_CUST"(+))
26 - access("ADDR"."I_GRUP"="GRUP"."I_GRUP")
27 - filter("ADDR"."C_STAT"='A')

Note
-----
- dynamic sampling used for this statement (level=6)


SQL and explain plan from the entire query:
SELECT DISTINCT ACCT.*, USR.T_NAME, GRUP.I_GRUP, EMAL.T_E_MAIL, CUS2.C_TBCCO_STAT
FROM SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT,
     SFDC_STAGING.STG_IND_SALI_GRAG GRAG, 
     SFDC_STAGING.STG_IND_SALI_USER USR,
     SFDC_STAGING.STG_IND_SALI_CUST CUST,
     SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
     SFDC_STAGING.STG_IND_SALI_CUS2 CUS2,
     SFDC_STAGING.STG_IND_SALI_GRUP GRUP 
WHERE ACCT.I_CUST = CUS2.I_CUST(+)
  AND ACCT.I_CUST = CUST.I_CUST
  AND ACCT.I_CUST = EMAL.I_CUST(+)
  AND CUST.I_CURR_GRUP = GRUP.I_GRUP
  AND GRAG.I_GRUP = GRUP.I_GRUP
  AND GRAG.I_AGNT = USR.I_AGNT 
  AND ACCT.I_CUST IN 
   (SELECT DISTINCT MIN(CUST.I_CUST)   
     FROM SFDC_STAGING.STG_IND_SALI_CUST CUST,
          SFDC_STAGING.STG_IND_SALI_ADDR ADDR,
          SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
          SFDC_STAGING.STG_IND_SALI_GRUP GRUP
     WHERE CUST.I_CURR_GRUP = GRUP.I_GRUP
       AND ADDR.I_GRUP = GRUP.I_GRUP
       AND CUST.I_CUST = EMAL.I_CUST(+)
       AND  CUST.C_STAT = 'A'
       AND  ADDR.C_STAT = 'A'
       AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL)
       AND  GRUP.I_GRUP IN
             (SELECT DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (SELECT DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITHH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,           
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
                        --AND GRAG.I_GRUP = 8117178
                     GROUP BY GRAG.I_GRUP
                    )
                 AND GRAG.I_GRUP = GRUP.I_GRUP
                 AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
   )
 GROUP BY GRUP.I_GRUP
)



Plan hash value: 754084898

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 630 | 14G (1)|999:59:59 |
| 1 | HASH UNIQUE | | 3 | 630 | 14G (1)|999:59:59 |
| 2 | NESTED LOOPS | | 3 | 630 | 14G (1)|999:59:59 |
| 3 | NESTED LOOPS | | 11 | 630 | 14G (1)|999:59:59 |
| 4 | NESTED LOOPS | | 1 | 188 | 14G (1)|999:59:59 |
| 5 | NESTED LOOPS OUTER | | 1 | 175 | 14G (1)|999:59:59 |
| 6 | NESTED LOOPS | | 1 | 147 | 14G (1)|999:59:59 |
| 7 | NESTED LOOPS | | 1 | 141 | 14G (1)|999:59:59 |
| 8 | NESTED LOOPS OUTER | | 1 | 129 | 14G (1)|999:59:59 |
| 9 | NESTED LOOPS | | 1 | 121 | 14G (1)|999:59:59 |
| 10 | VIEW | VW_NSO_1 | 1 | 13 | 14G (1)|999:59:59 |
| 11 | HASH GROUP BY | | 1 | 75 | 14G (1)|999:59:59 |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS | | 750K| 53M| 92094 (1)| 00:18:26 |
| 14 | NESTED LOOPS | | 750K| 53M| 92094 (1)| 00:18:26 |
|* 15 | FILTER | | | | | |
| 16 | NESTED LOOPS OUTER | | 680K| 43M| 71675 (1)| 00:14:21 |
| 17 | NESTED LOOPS | | 718K| 40M| 50131 (1)| 00:10:02 |
| 18 | NESTED LOOPS | | 620K| 26M| 31510 (1)| 00:06:19 |
| 19 | NESTED LOOPS | | 614K| 22M| 19220 (1)| 00:03:51 |
|* 20 | INDEX FULL SCAN | STG_IND_SALI_GRUP_PK3 | 472K| 7838K| 330 (1)| 00:00:04 |
|* 21 | TABLE ACCESS BY INDEX ROWID| STG_IND_SALI_GRAG | 1 | 22 | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_CUST | 1 | 14 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | STG_IND_SALI_CUST_PK2 | 1 | | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_EMAIL | 1 | 8 | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | STG_IND_SALI_EMAIL_PK | 1 | | 1 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | STG_IND_SALI_ADDR_PK | 1 | | 1 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_ADDR | 1 | 8 | 1 (0)| 00:00:01 |
|* 30 | FILTER | | | | | |
| 31 | SORT GROUP BY NOSORT | | 6343 | 192K| 19220 (1)| 00:03:51 |
| 32 | NESTED LOOPS | | 634K| 18M| 19220 (1)| 00:03:51 |
| 33 | NESTED LOOPS | | 634K| 18M| 19220 (1)| 00:03:51 |
|* 34 | INDEX FULL SCAN | STG_IND_SALI_GRUP_PK3 | 472K| 7838K| 330 (1)| 00:00:04 |
|* 35 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_GRAG | 1 | 14 | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_PERSON_ACCNT | 1 | 108 | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | STG_IND_SALI_PA1_PK | 1 | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_CUS2 | 1 | 8 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | STG_IND_SALI_CUS2_PK | 1 | | 1 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | STG_IND_SALI_CUST_PK3 | 1 | 12 | 1 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | STG_IND_SALI_GRUP_PK | 1 | 6 | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_EMAIL | 1 | 28 | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | STG_IND_SALI_EMAIL_PK | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_GRAG | 1 | 13 | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | STG_IND_SALI_GRAG_PK | 1 | | 1 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | STG_IND_SALI_USER_PK | 11 | | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | STG_IND_SALI_USER | 3 | 66 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------

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

12 - filter( EXISTS (SELECT 0 FROM "SFDC_STAGING"."STG_IND_SALI_GRUP" "GRUP","SFDC_STAGING"."STG_IND_SALI_GRAG"
"GRAG" WHERE "GRAG"."I_GRUP"="GRUP"."I_GRUP" AND TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831'
GROUP BY "GRAG"."I_GRUP" HAVING MAX("GRAG"."D_ESTABLISH")=:B1))
15 - filter("EMAL"."C_STAT"='A' OR "EMAL"."C_STAT" IS NULL)
20 - filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
21 - filter("GRAG"."D_RELEASE" IS NOT NULL)
22 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
23 - access("GRUP"."I_GRUP"="GRAG"."I_GRUP")
24 - filter("CUST"."C_STAT"='A')
25 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
27 - access("CUST"."I_CUST"="EMAL"."I_CUST"(+))
28 - access("ADDR"."I_GRUP"="GRUP"."I_GRUP")
29 - filter("ADDR"."C_STAT"='A')
30 - filter(MAX("GRAG"."D_ESTABLISH")=:B1)
34 - filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
35 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
38 - access("ACCT"."I_CUST"="MIN(CUST.I_CUST)")
40 - access("ACCT"."I_CUST"="CUS2"."I_CUST"(+))
41 - access("ACCT"."I_CUST"="CUST"."I_CUST")
42 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
44 - access("ACCT"."I_CUST"="EMAL"."I_CUST"(+))
46 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
47 - access("GRAG"."I_AGNT"="USR"."I_AGNT")

Note
-----
- dynamic sampling used for this statement (level=6)
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642525 is a reply to message #642524] Sun, 13 September 2015 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
which is easier to read & understand yours above or mine below?
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |     1 |    84 |     9  (12)| 00:00:01 |
|   1 |  HASH UNIQUE                              |                       |     1 |    84 |     9  (12)| 00:00:01 |
|   2 |   HASH GROUP BY                           |                       |     1 |    84 |     9  (12)| 00:00:01 |
|   3 |    NESTED LOOPS                           |                       |     1 |    84 |     8   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                          |                       |     1 |    84 |     8   (0)| 00:00:01 |
|*  5 |      FILTER                               |                       |       |       |            |          |
|   6 |       NESTED LOOPS OUTER                  |                       |     1 |    76 |     7   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                       |                       |     1 |    68 |     6   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                      |                       |     1 |    54 |     5   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                     |                       |     1 |    37 |     4   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                    |                       |     1 |    31 |     3   (0)| 00:00:01 |
|  11 |            VIEW                           | VW_NSO_1              |     1 |     9 |     2   (0)| 00:00:01 |
|  12 |             HASH GROUP BY                 |                       |     1 |    31 |     2   (0)| 00:00:01 |
|  13 |              MERGE JOIN CARTESIAN         |                       |     1 |    31 |     2   (0)| 00:00:01 |
|* 14 |               INDEX RANGE SCAN            | STG_IND_SALI_GRUP_PK3 |     1 |    17 |     1   (0)| 00:00:01 |
|  15 |               BUFFER SORT                 |                       |     1 |    14 |     1   (0)| 00:00:01 |
|  16 |                TABLE ACCESS BY INDEX ROWID| STG_IND_SALI_GRAG     |     1 |    14 |     1   (0)| 00:00:01 |
|* 17 |                 INDEX RANGE SCAN          | STG_IND_SALI_GRAG_PK  |     1 |       |     1   (0)| 00:00:01 |
|* 18 |            TABLE ACCESS BY INDEX ROWID    | STG_IND_SALI_GRAG     |     1 |    22 |     1   (0)| 00:00:01 |
|* 19 |             INDEX RANGE SCAN              | STG_IND_SALI_GRAG_PK4 |     1 |       |     1   (0)| 00:00:01 |
|* 20 |           INDEX RANGE SCAN                | STG_IND_SALI_GRUP_PK  |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |          INDEX RANGE SCAN                 | STG_IND_SALI_GRUP_PK3 |     1 |    17 |     1   (0)| 00:00:01 |
|* 22 |         TABLE ACCESS BY INDEX ROWID       | STG_IND_SALI_CUST     |     1 |    14 |     1   (0)| 00:00:01 |
|* 23 |          INDEX RANGE SCAN                 | STG_IND_SALI_CUST_PK2 |     1 |       |     1   (0)| 00:00:01 |
|  24 |        TABLE ACCESS BY INDEX ROWID        | STG_IND_SALI_EMAIL    |     1 |     8 |     1   (0)| 00:00:01 |
|* 25 |         INDEX RANGE SCAN                  | STG_IND_SALI_EMAIL_PK |     1 |       |     1   (0)| 00:00:01 |
|* 26 |      INDEX RANGE SCAN                     | STG_IND_SALI_ADDR_PK  |     1 |       |     1   (0)| 00:00:01 |
|* 27 |     TABLE ACCESS BY INDEX ROWID           | STG_IND_SALI_ADDR     |     1 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("EMAL"."C_STAT"='A' OR "EMAL"."C_STAT" IS NULL)
  14 - access("GRUP"."I_GRUP"=8722127)
       filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
  17 - access("GRAG"."I_GRUP"=8722127)
  18 - filter("GRAG"."D_RELEASE" IS NOT NULL)
  19 - access("GRAG"."D_ESTABLISH"="$kkqu_col_1")
  20 - access("GRUP"."I_GRUP"="GRAG"."I_GRUP")
  21 - access("GRAG"."I_GRUP"="GRUP"."I_GRUP")
       filter(TO_CHAR(INTERNAL_FUNCTION("GRUP"."D_UPDT"),'YYYYMMDD')>'20130831')
  22 - filter("CUST"."C_STAT"='A')
  23 - access("CUST"."I_CURR_GRUP"="GRUP"."I_GRUP")
  25 - access("CUST"."I_CUST"="EMAL"."I_CUST"(+))
  26 - access("ADDR"."I_GRUP"="GRUP"."I_GRUP")
  27 - filter("ADDR"."C_STAT"='A')
 
Note
-----
   - dynamic sampling used for this statement (level=6)


Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642528 is a reply to message #642525] Mon, 14 September 2015 00:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
You have the right idea with the divide and conquer strategy of looking at query pieces. Try this and see if it does not help you figure something out. I have not tested any of this code for syntax, so if you find any problems, fix them. I have also attached the normal stuff I attach for tuning: link to my book on SQL Tuning, first chapter for free, scripts for free, tuning worksheet beta document. This queries below will help you see if what you think is happening is in fact actually happening, or maybe if there is something else going on.

Remember to post the formatted results back here once you run the code.

------------------------------------------------------------------------------------------------------------------------------------

select count(*) from SFDC_STAGING.STG_IND_SALI_GRAG;
select count(*) from SFDC_STAGING.STG_IND_SALI_GRUP;

------------------------------------------------------------------------------------------------------------------------------------

create table temp1
nologging
as
                    (SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,                  
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'         --WHERE DATE GREATER THAN THIS DATE
                        AND GRAG.I_GRUP = 8722127                                 --FOR THIS SPECIFIC GROUP
                     GROUP BY GRAG.I_GRUP
                    )
/
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
select count(*) from temp1;

------------------------------------------------------------------------------------------------------------------------------------

select count(*) from SFDC_STAGING.STG_IND_SALI_GRAG;
select count(*) from SFDC_STAGING.STG_IND_SALI_GRUP;

------------------------------------------------------------------------------------------------------------------------------------

create table temp2
nologging
as
             (SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (select * from temp1)
                 AND GRAG.I_GRUP = GRUP.I_GRUP
                 AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'                --USE DATE HERE TOO OR ELSE IT WON'T WORK
   )
/
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
select count(*) from temp2;

------------------------------------------------------------------------------------------------------------------------------------

select count(*) from SFDC_STAGING.STG_IND_SALI_CUST;

select count(*) from SFDC_STAGING.STG_IND_SALI_ADDR;

select count(*) from SFDC_STAGING.STG_IND_SALI_EMAIL;

select count(*) from SFDC_STAGING.STG_IND_SALI_GRUP;

------------------------------------------------------------------------------------------------------------------------------------

create table temp3
nologging
as
   (SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT MIN(CUST.I_CUST)   
     FROM SFDC_STAGING.STG_IND_SALI_CUST CUST,
          SFDC_STAGING.STG_IND_SALI_ADDR ADDR,
          SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
          SFDC_STAGING.STG_IND_SALI_GRUP GRUP
     WHERE ACCT.I_CUST = CUST.I_CUST
       AND CUST.I_CURR_GRUP = GRUP.I_GRUP
       AND ADDR.I_GRUP = GRUP.I_GRUP
       AND CUST.I_CUST = EMAL.I_CUST(+)
       AND  CUST.C_STAT = 'A'                                                     --ONLY GET ACTIVE CUSTOMERS
       AND  ADDR.C_STAT = 'A'                                                     --WITH A VALID ADDRESS
       AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL)                             --AND ONLY IF THE EMAIL STATUS NOT INACTIVE
       AND  GRUP.I_GRUP IN
             (select * from temp2)
 GROUP BY GRUP.I_GRUP
)
/
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
select count(*) from temp3;

------------------------------------------------------------------------------------------------------------------------------------

select count(*) from SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT;

------------------------------------------------------------------------------------------------------------------------------------

create table temp4
nologging
as
SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT ACCT.I_CUST
FROM SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT
WHERE ACCT.I_CUST IN 
   (select * from temp3)
/
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
select count(*) from temp4;


Kevin

Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities

[Updated on: Mon, 14 September 2015 00:53]

Report message to a moderator

Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642539 is a reply to message #642514] Mon, 14 September 2015 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Mon, 14 September 2015 02:15
EXPLAIN PLAN estimates query runs about 5.5 HOURS

What is "VW_NSO_1"? I suspect it is a VIEW.
More specifically does it reference any remote DB?


That matches the naming convention for temporary views oracle creates itself as part of the plan.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642544 is a reply to message #642508] Mon, 14 September 2015 04:56 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
I see lines such as this in your exec plans
TABLE ACCESS STORAGE FULL
one sees this only in an Exadata system, unless you have changed your cell_offload_plan_display parameter from default for some reason? Can you confirm that your OS is indeed AIX?

Apart from that, you have some basic errors. For example:

Wrapping colums in functions will confuse the CBO. For example, this
AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
should be changed to this
AND GRUP.D_UPDT > to_timestamp('20130831','yyyymmdd')


Unnecessary aggregations will slow down everything, for example the DISTINCTs in this block
       AND  GRUP.I_GRUP IN
             (SELECT DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (SELECT DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITHH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,           
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
                        --AND GRAG.I_GRUP = 8117178
                     GROUP BY GRAG.I_GRUP
                    )
are not needed.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642545 is a reply to message #642544] Mon, 14 September 2015 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should also get rid of this line from the where clause:
ACCT.I_CUST = CUST.I_CUST

It just replicates what the outer-most IN does.
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642546 is a reply to message #642544] Mon, 14 September 2015 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 14 September 2015 10:56
For example, this
AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'
should be changed to this
AND GRUP.D_UPDT > to_timestamp('20130831','yyyymmdd')


They're not actually equivalent, because of times.

I imagine it should be this:
AND GRUP.D_UPDT >= to_timestamp('20130831','yyyymmdd') + INTERVAL '1' DAY
Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642667 is a reply to message #642546] Wed, 16 September 2015 15:18 Go to previous messageGo to next message
aft5425@anthem.com
Messages: 11
Registered: September 2015
Junior Member
Thank you all for your help and suggestions. I was able to get my long running SQL to run in less than 3 minutes primarily by


    building indexes on fields used in the "where" clause

      running a separate pre-SQL query to populate my base table with fields that I was joining to other tables to get

        eliminating nested correlated subqueries
        Re: Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642668 is a reply to message #642509] Wed, 16 September 2015 15:24 Go to previous message
        aft5425@anthem.com
        Messages: 11
        Registered: September 2015
        Junior Member
        Black Swan thanks for your help with the problem I posted. I'll be sure to follow the rules better if I ever post again. I'm new to this forum, so I was not familiar with all of the rules for posting, but I am now.

        My query now does finish in less than 3 minutes processing over 8 millions rows. Below is what I did to get it finally work. I posted it before, but will it again since I forgot a step.

          building indexes on fields used in the "where" clause

            running a separate pre-SQL query to populate my base table with fields that I was joining to other tables to get

              eliminating nested correlated subqueries


                get rid of MAX functions that really were not helping the CBO
                Previous Topic: Performance improvement required
                Next Topic: Unable to interpret number of rows accessed during Full Table scan
                Goto Forum:
                  


                Current Time: Wed Feb 21 21:59:44 CST 2018

                Total time taken to generate the page: 0.07171 seconds