Tuesday, October 21, 2014

How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>
 
I recalculated the index's statistics:
 
SQL> exec dbms_stats.gather_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
After that, the BLEVEL column in DBA_INDEXES showed the index’s height:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
              3
 
SQL>
 
I used to calculate an index's height by analyzing it then looking in INDEX_STATS. First I checked that INDEX_STATS was empty (you will see why I showed the username later):

SQL> select name, height from index_stats
  2  /
 
no rows selected
 
SQL> show user
USER is "ORACLE"
SQL>
 
Then I analyzed the index:

SQL> analyze index uimsmgr.ubbchst_serv_index
  2  validate structure
  3  /
 
Index analyzed.
 
SQL>
 
... and checked its height in INDEX_STATS again. This height comes out as BLEVEL + 1. I guess Oracle starts to count it from a different place as this happened in Oracle 9 too:
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
UBBCHST_SERV_INDEX                      4
 
SQL>
 
INDEX_STATS is a public synonym for the SYS.INDEX_STATS view. It is far too complicated for me to follow but I know that it never has more than one row, which holds details for the index you analyzed most recently. It is also session specific so, if you start a new session, it will be empty again:
 
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select name, height from index_stats
  2  /
 
no rows selected
 
SQL>
 
You can read more about this starting on page 69 of the book advertised below:


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>