Thursday, October 16, 2014

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>

Thursday, October 09, 2014

Worked Examples with the SET ROLE Command

I tested this on Oracle 11.2. First I created a role:
 
SQL> conn / as sysdba
Connected.
SQL> create role role1
  2  /
 
Role created.
 
SQL> grant select on dba_tables to role1
  2  /
 
Grant succeeded.
 
SQL>
 
Then I created a user and granted the role to him:
 
SQL> create user andrew identified by reid
  2  /
 
User created.
 
SQL> grant create session, role1 to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
The user had this as a default role:
 
SQL> select granted_role, default_role
  2  from dba_role_privs
  3  where grantee = 'ANDREW'
  4  /
 
GRANTED_ROLE DEFAULT_ROLE
------------ ------------
ROLE1        YES
 
SQL>
 
...so when he logged in, it appeared in SESSION_ROLES, which shows the roles you currently have enabled:
 
SQL> conn andrew/reid
Connected.
SQL> select role from session_roles
  2  /
 
ROLE
-----
ROLE1
 
SQL>
 
... and he could use the permissions granted by that role:
 
SQL> select count(*) from dba_tables
  2  /
 
  COUNT(*)
----------
      3149
 
SQL>
 
I changed the user so he had no default roles:
 
SQL> conn / as sysdba
Connected.
SQL> alter user andrew default role none
  2  /
 
User altered.
 
SQL>
 
... so the role was no longer shown as a default role for him in DBA_ROLE_PRIVS:
 
SQL> select granted_role, default_role
  2  from dba_role_privs
  3  where grantee = 'ANDREW'
  4  /
 
GRANTED_ROLE DEFAULT_ROLE
------------ ------------
ROLE1        NO
 
SQL>
 
When he logged in this time, the role did not appear in SESSION_ROLES:
 
SQL> conn andrew/reid
Connected.
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL>
 
... and he could not use the permissions granted via that role:
 
SQL> select count(*) from dba_tables
  2  /
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL>
 
However, he was able to enable the role like this:
 
SQL> set role role1
  2  /
 
Role set.
 
SQL>
 
… and then it appeared in SESSION_ROLES:
 
SQL> select role from session_roles
  2  /
 
ROLE
-----
ROLE1
 
SQL>
 
... and he could use the permissions granted by the role:
 
SQL> select count(*) from dba_tables
  2  /
 
  COUNT(*)
----------
      3149
 
SQL>
 
This has always seemed a bit strange to me, either you want a user to have a role or you don't!
 
Then the user decided he no longer needed the role to be enabled so he removed it from SESSION_ROLES like this:
 
SQL> set role none
  2  /
 
Role set.
 
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL>
 
Next, I changed the role so it had to be enabled with a password:
 
SQL> conn / as sysdba
Connected.
SQL> alter role role1 identified by secret_password
  2  /
 
Role altered.
 
SQL>
 
...so when the user tried to enable the role, Oracle needed a password. The user did not provide one so Oracle returned an error:
 
SQL> conn andrew/reid
Connected.
SQL> set role role1
  2  /
set role role1
*
ERROR at line 1:
ORA-01979: missing or invalid password for role
'ROLE1'
 
SQL>
 
…and the role did not appear in SESSION_ROLES:
 
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL>
 
The user tried again, this time including the password:
 
SQL> set role role1 identified by secret_password
  2  /
 
Role set.
 
SQL>
 
The role appeared in SESSION_ROLES:
 
SQL> select role from session_roles
  2  /
 
ROLE
-----
ROLE1
 
SQL>
 
…and the user could look at DBA_TABLES:
 
SQL> select count(*) from dba_tables
  2  /
 
  COUNT(*)
----------
      3149
 
SQL>