Bug 8477973

I ran the following query on an Oracle 11.1.0.6 database but it failed with an ORA-02020:

 

SQL> SELECT ppc.sttlmnt_dt day_date

  2  FROM   vrm_d18_ppc   ppc,

  3         meter_nhh     mtr,

  4         mtd_registers reg

  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr

  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

  7  AND    ppc.prfl_clss_id = mtr.profile_class_id

  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id

  9  AND    reg.mpan_core = mtr.mpan_core

 10  AND    reg.mpan_core = '1900008212269'

 11  AND    mtr.meter_id = 3232

 12  AND    reg.meter_serial_id = 'L8103349'

 13  AND    reg.meter_register_id = 'TO'

 14  AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

 15  /

ERROR:

ORA-02020: too many database links in use

 

no rows selected

 

SQL>

 

I checked the value of the open_links initialization parameter:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'open_links'

SQL> /

 

VALUE

----------

4

 

SQL>

 

I looked at the execution plan and saw that it accessed several tables down the same database link. It also used nested loops:

********************************************************************************

 

SELECT ppc.sttlmnt_dt day_date

FROM   vrm_d18_ppc   ppc,

       meter_nhh     mtr,

       mtd_registers reg

WHERE  ppc.tm_pttrn_rgm = reg.tpr

AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

AND    ppc.prfl_clss_id = mtr.profile_class_id

AND    ppc.gsp_grp_id = mtr.gsp_group_id

AND    reg.mpan_core = mtr.mpan_core

AND    reg.mpan_core = '1900008212269'

AND    mtr.meter_id = 3232

AND    reg.meter_serial_id = 'L8103349'

AND    reg.meter_register_id = 'TO'

AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.04       0.19          0          0          1           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.05       0.52          7          8          0           1

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

total        4      0.09       0.72          7          8          1           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 78  (SRCE)

 

Rows     Row Source Operation

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

      1  FILTER  (cr=7 pr=7 pw=7 time=0 us)

      1   NESTED LOOPS  (cr=7 pr=7 pw=7 time=0 us cost=10 size=216 card=1)

      1    NESTED LOOPS  (cr=7 pr=7 pw=7 time=0 us cost=9 size=190 card=1)

      1     NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=8 size=156 card=1)

      1      NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=7 size=139 card=1)

      1       NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=6 size=125 card=1)

      2        NESTED LOOPS  (cr=3 pr=3 pw=3 time=379976 us cost=5 size=113 card=1)

      2         NESTED LOOPS  (cr=3 pr=3 pw=3 time=255776 us cost=4 size=86 card=1)

      2          NESTED LOOPS  (cr=3 pr=3 pw=3 time=127596 us cost=3 size=66 card=1)

      2           NESTED LOOPS  (cr=3 pr=3 pw=3 time=972 us cost=2 size=40 card=1)

      1            TABLE ACCESS BY INDEX ROWID METER_NHH (cr=3 pr=3 pw=3 time=0 us cost=1 size=23 card=1)

      1             INDEX UNIQUE SCAN METER_NHH_PK (cr=2 pr=2 pw=2 time=0 us cost=1 size=0 card=1)(object id 106394)

      2            REMOTE  D18_780 (cr=0 pr=0 pw=0 time=967 us cost=1 size=17 card=1)

      2           REMOTE  D18_781 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)

      2          REMOTE  D18_782 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)

      2         REMOTE  D18_783 (cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1)

      1        REMOTE  D18_784 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)

      1       REMOTE  D18_787 (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)

      1      REMOTE  D18_789 (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)

      1     TABLE ACCESS BY INDEX ROWID MTD_REGISTERS (cr=5 pr=4 pw=4 time=0 us cost=1 size=34 card=1)

      1      INDEX RANGE SCAN MR_PK (cr=4 pr=3 pw=3 time=0 us cost=1 size=0 card=1)(object id 106689)

      1    REMOTE  D18_790 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   FILTER

      1    NESTED LOOPS

      1     NESTED LOOPS

      1      NESTED LOOPS

      1       NESTED LOOPS

      1        NESTED LOOPS

      2         NESTED LOOPS

      2          NESTED LOOPS

      2           NESTED LOOPS

      2            NESTED LOOPS

      1             TABLE ACCESS   MODE: ANALYZED (BY INDEX

                        ROWID) OF 'METER_NHH' (TABLE)

      1              INDEX   MODE: ANALYZED (UNIQUE SCAN) OF

                         'METER_NHH_PK' (INDEX (UNIQUE))

      2             REMOTE OF 'D18_780' (REMOTE)

                        [PQE_TO_DMV_LINK]

                       SELECT "D18_780_ID","STTLMNT_DT" FROM "DMV"."D18_780"

                       "A" WHERE TO_CHAR("D18_780_ID") LIKE '%' AND

                         "STTLMNT_DT">=:1 AND "STTLMNT_DT"<=:2

      2            REMOTE OF 'D18_781' (REMOTE) [PQE_TO_DMV_LINK]

 

                      SELECT "D18_781_ID","D18_780_ID" FROM "DMV"."D18_781"

                      "B" WHERE TO_CHAR("D18_781_ID") LIKE '%' AND :1=

                        "D18_780_ID"

      2           REMOTE OF 'D18_782' (REMOTE) [PQE_TO_DMV_LINK]

                     SELECT "D18_782_ID","D18_781_ID" FROM "DMV"."D18_782"

                     "C" WHERE TO_CHAR("D18_782_ID") LIKE '%' AND

                       "D18_781_ID"=:1

      2          REMOTE OF 'D18_783' (REMOTE) [PQE_TO_DMV_LINK]

                    SELECT "D18_783_ID","D18_782_ID","GSP_GRP_ID" FROM

                    "DMV"."D18_783" "D" WHERE TO_CHAR("D18_783_ID") LIKE

                      '%' AND "GSP_GRP_ID"=:1 AND :2="D18_782_ID"

      1         REMOTE OF 'D18_784' (REMOTE) [PQE_TO_DMV_LINK]

                   SELECT "D18_784_ID","D18_783_ID","PRFL_CLSS_ID" FROM

                   "DMV"."D18_784" "E" WHERE TO_CHAR("D18_784_ID") LIKE '%'

                     AND "PRFL_CLSS_ID"=:1 AND "D18_783_ID"=:2

      1        REMOTE OF 'D18_787' (REMOTE) [PQE_TO_DMV_LINK]

                  SELECT "D18_787_ID","D18_784_ID",

                  "STNDRD_STTLMNT_CNFGRTN_ID" FROM "DMV"."D18_787" "F"

                  WHERE TO_CHAR("D18_787_ID") LIKE '%' AND

                  TO_NUMBER("STNDRD_STTLMNT_CNFGRTN_ID")=:1 AND :2=

                    "D18_784_ID"

      1       REMOTE OF 'D18_789' (REMOTE) [PQE_TO_DMV_LINK]

                 SELECT "D18_789_ID","D18_787_ID","TM_PTTRN_RGM" FROM

                 "DMV"."D18_789" "G" WHERE TO_CHAR("D18_789_ID") LIKE '%'

                   AND :1="D18_787_ID"

      1      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                 'MTD_REGISTERS' (TABLE)

      1       INDEX   MODE: ANALYZED (RANGE SCAN) OF 'MR_PK' (INDEX

                  (UNIQUE))

      1     REMOTE OF 'D18_790' (REMOTE) [PQE_TO_DMV_LINK]

               SELECT "D18_790_ID","D18_789_ID" FROM "DMV"."D18_790" "H"

                 WHERE TO_CHAR("D18_790_ID") LIKE '%' AND "D18_789_ID"=:1

 

********************************************************************************

 

According to My Oracle Support:

              

This issue can appear as a regression introduced in 11.1.0.6 due to improved nested loops code added in 11g.

 

and

 

If a SQL is accessing multiple remote tables at the same remote site then depending on the execution plan chosen it is possible to get multiple database link connections opened when this did not occur in earlier releases. The opening of more than one database link for the SQL can lead to ORA-2020 errors.

 

I increased the value of the open_links parameter:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set open_links = 10

  2  scope = spfile

  3  /

 

System altered.

 

SQL> startup force

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  630702080 bytes

Fixed Size                  2091232 bytes

Variable Size             381687584 bytes

Database Buffers          243269632 bytes

Redo Buffers                3653632 bytes

Database mounted.

Database opened.

SQL> select value from v$parameter

  2  where name = 'open_links'

  3  /

 

VALUE

----------

10

 

SQL>

 

I checked that the query then worked:

 

SQL> SELECT ppc.sttlmnt_dt day_date

  2  FROM   vrm_d18_ppc   ppc,

  3         meter_nhh     mtr,

  4         mtd_registers reg

  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr

  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

  7  AND    ppc.prfl_clss_id = mtr.profile_class_id

  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id

  9  AND    reg.mpan_core = mtr.mpan_core

10  AND    reg.mpan_core = '1900008212269'

11  AND    mtr.meter_id = 3232

12  AND    reg.meter_serial_id = 'L8103349'

13  AND    reg.meter_register_id = 'TO'

14  AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

15  /

 

DAY_DATE

---------

01-AUG-14

02-AUG-14

03-AUG-14

04-AUG-14

05-AUG-14

Etc

 

Once the query had finished, I checked the number of links the session had opened:

 

SQL> select db_link from v$dblink

  2  /

 

DB_LINK

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

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

 

8 rows selected.

 

SQL>

 

It should have needed only one, not eight. This is another feature of this bug.

 

I reran the query in a copy of the same database on Oracle 11.2.0.4, which still had its open_links parameter set to the default of four. It ran successfully so I checked the explain plan. It still did as many nested loops but, when I checked the number of links the session had opened, I only saw one so the bug does not appear to affect this version of Oracle:

 

SQL> select db_link from v$dblink

  2  /

 

DB_LINK

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

PQE_TO_DMV_LINK

 

SQL>