Sunday, May 22, 2016

Unindexed foreign keys – Part IV

Very recently got into an application to investigate a performance issue, where one of the delete statement took very long to complete.

rajesh@ORA12C> set timing on
rajesh@ORA12C> delete from t1 where id = 55;

1 row deleted.

Elapsed: 00:00:15.27
rajesh@ORA12C>

Say in this case it took nearly fifteen seconds to just delete one row from this table.  So what is causing this slowness, what could be the reason?

With the help of few trace file, we are able to identify the issue.

********************************************************************************
delete from t1
where
 id = 55


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          0          0           0
Execute      1      0.00       0.01          1          3          8           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.04          1          3          8           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  T1 (cr=156871 pr=156774 pw=0 time=15110100 us)
         1          1          1   INDEX UNIQUE SCAN T1_PK (cr=3 pr=1 pw=0 time=12999 us cost=2 size=6 card=1)(object id 108040)


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

SQL ID: 53d5nbqjhjj85 Plan Hash: 3321871023

select /*+ all_rows */ count(1)
from
 "RAJESH"."T2" where "ID" = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      2.29      15.09     156773     156861          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.29      15.09     156773     156861          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=156861 pr=156773 pw=0 time=15094891 us)
         0          0          0   TABLE ACCESS FULL T2 (cr=156861 pr=156773 pw=0 time=15094882 us cost=56093 size=6 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                       3848        0.48         12.81



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


The delete against Table T1 is really faster, it is the SELECT statement against T2 is causing slowness (took almost 15 secs), but why does Oracle have this Select against T2 when performing Delete against T1?

Peeking into the data dictionary helped us here.

rajesh@ORA12C> select t2.table_name,t2.constraint_name,t2.constraint_type
  2  from user_constraints t1 ,
  3     user_constraints t2
  4  where t1.table_name ='T1'
  5  and t1.constraint_type ='P'
  6  and t2.r_constraint_name = t1.constraint_name ;

TABLE_NAME CONSTRAINT CONSTRAINT
---------- ---------- ----------
T2         T2_FK      R

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> select index_name
  2  from user_ind_columns
  3  where table_name ='T2' ;

no rows selected

rajesh@ORA12C>

So basically we had unindexed foreign key on table T2 which references table T1, for each delete on T1 we full scan the Table T2 to check if any child record exists, if exists then delete fails, if not then delete succeeds. 

Having index on foreign keys will eliminate those full scan on T2 and replace them in small index range scans – that in turn make things to go faster.

rajesh@ORA12C> delete from t1 where id = 54;

1 row deleted.

Elapsed: 00:00:11.67
rajesh@ORA12C> rollback;

Rollback complete.

Elapsed: 00:00:00.03
rajesh@ORA12C> create index t2_idx on t2(id) nologging;

Index created.

Elapsed: 00:00:32.71
rajesh@ORA12C> delete from t1 where id = 54;

1 row deleted.

Elapsed: 00:00:00.08
rajesh@ORA12C>


The dreaded “unindexed foreign key” strikes again!

Leaf block of a BTree index

Very recently a question came up regarding B*Tree index in Oracle database. The question is “How do we say (or prove) that Leaf blocks are double linked list in B*Tree index”?

Here is my test case to prove that.

rajesh@ORA11G> create table t as
2  select *
3  fromall_objects;

Table created.

rajesh@ORA11G>
rajesh@ORA11G> create index t_idx on t(object_id);

Index created.

rajesh@ORA11G> select owner,segment_name,extent_id,blocks,block_id,file_id
2  fromdba_extents
3  wheresegment_name ='T_IDX'
4  and owner = user
5  order by extent_id ;

OWNER          SEGMENT_NAME    EXTENT_ID     BLOCKS   BLOCK_ID    FILE_ID
-------------- -------------- ---------- ---------- ---------- ----------
RAJESH         T_IDX                   0          8       1400       6
RAJESH         T_IDX                   1          8       1480       6
RAJESH         T_IDX                   2          8       1488       6
RAJESH         T_IDX                   3          8       1496       6
RAJESH         T_IDX                   4          8       1504       6
RAJESH         T_IDX                   5          8       1512       6
RAJESH         T_IDX                   6          8       1520       6
RAJESH         T_IDX                   7          8       1528       6
RAJESH         T_IDX                   8          8       1624       6
RAJESH         T_IDX                   9          8       1632       6
RAJESH         T_IDX                  10          8       1640       6
RAJESH         T_IDX                  11          8       1648       6
RAJESH         T_IDX                  12          8       1656       6
RAJESH         T_IDX                  13          8       1736       6
RAJESH         T_IDX                  14          8       1744       6
RAJESH         T_IDX                  15          8       1752       6
RAJESH         T_IDX                  16        128     183936       6

17 rows selected.

rajesh@ORA11G> alter system checkpoint;

System altered.

rajesh@ORA11G>

Created a Table and index, and the index spans seventeen extents (while each extend holds 8 blocks except the last which is 128 blocks) and did a checkpoint manually to flush contents to Disk.
Let me pickup any extent, say extend_id = 6 here and that has total of eight blocks from 1520 to 1527.
So let us dump the block 1521 and 1522 and 1523 and see are they connected via double linked list.

Dump block 1521
rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_8480.trc


PL/SQL procedure successfully completed.

rajesh@ORA11G> alter system dump datafile 6 block 1521;

System altered.

rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Dump block 1522
C:\Users\179818>sqlplus rajesh/oracle@ora11g

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 18:09:00 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_8580.trc


PL/SQL procedure successfully completed.

rajesh@ORA11G> alter system dump datafile 6 block 1522;

System altered.

rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Dump block 1523

C:\Users\179818>sqlplus rajesh/oracle@ora11g

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 18:09:17 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_11120.trc


PL/SQL procedure successfully completed.

rajesh@ORA11G> alter system dump datafile 6 block 1523;

System altered.

rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Note:
Kdxlenxt – represents the address of the next leaf block in the B*Tree index
Kdxleprv – represents the address of the previous leaf block in the B*Tree index

Looking at the Trace file, block 1521 shows this

        Repeat 1 times
016C9A1F0 00000000 00000000 00000000 04770602  [..............w.]
Block header dump:  0x018005f1
Object id on Block? Y
seg/obj: 0x19a7c  csc: 0x507.9d6a0469  itc: 2  flg: E  typ: 2 - INDEX
brn: 0  bdba: 0x18005f0 ver: 0x01 opc: 0
inc: 0  exflg: 0

ItlXidUbaFlag  LckScn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0507.9d6a0469
Leaf block dump
===============
header address 382304868=0x16c98264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1755=0x6db
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25167346=0x18005f2
kdxleprv 25167343=0x18005ef
kdxledsz 0
kdxlebksz 8036
row#0[8022] flag: ------, lock: 0, len=14

Looking at the Trace file, block 1522 shows this

0168FA7F0 00000000 00000000 00000000 04770602  [..............w.]
Block header dump:  0x018005f2
Object id on Block? Y
seg/obj: 0x19a7c  csc: 0x507.9d6a0469  itc: 2  flg: E  typ: 2 - INDEX
brn: 0  bdba: 0x18005f0 ver: 0x01 opc: 0
inc: 0  exflg: 0

ItlXidUbaFlag  LckScn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0507.9d6a0469
Leaf block dump
===============
header address 378505316=0x168f8864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1754=0x6da
kdxcoavs 820
kdxlespl 0
kdxlende 0
kdxlenxt 25167347=0x18005f3
kdxleprv 25167345=0x18005f1
kdxledsz 0
kdxlebksz 8036

Looking at the Trace file, block 1523 shows this

Block header dump:  0x018005f3
Object id on Block? Y
seg/obj: 0x19a7c  csc: 0x507.9d6a0469  itc: 2  flg: E  typ: 2 - INDEX
brn: 0  bdba: 0x18005f0 ver: 0x01 opc: 0
inc: 0  exflg: 0

ItlXidUbaFlag  LckScn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0507.9d6a0469
Leaf block dump
===============
header address 251299940=0xefa8864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1755=0x6db
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25167348=0x18005f4
kdxleprv 25167346=0x18005f2
kdxledsz 0
kdxlebksz 8036


From the above highlighted – it was clear that the block that appears next to block 1521 is 1522. Since block 1521 has kdxlenxt =0x18005f2that in-turn is the address for the block 1522.

The block that appears next to block 1522 is 1523. Since block 1522 haskdxlenxt =0x18005f3that in-turn is the address for the block 1523, and the block that appears prior to 1523 is 1522 since the block 1523 has kdxleprv =0x18005f2 that in-turn is the address for the block 1522.

Monday, May 2, 2016

Optimizer_Secure_View_Merging

This parameter can be either True or False. The default value is True.

False – This is similar to the behavior in earlier release such as 9.2. Oracle allows view/plsql function in queries to be merged / moved around during query optimization in order to try and get the lowest cost execution plan even if view/function have different owners.

True – Oracle does not use view merging or predicate move around /pushing in some cases where the query has user defined function and views ( regular, in line, un-nested etc) if the views/functions are owned by someone other than the person running the query.

The goal of this parameter is to prevent functions owned by some user B from seeing data of some other user A that is should not see

connect a/a
/* The entire would can see the data for x =1 only!!! */
create view v as select * from t where x =1;
grant select on v to public;

And then if someone queried:

select * from a.v where f(col) = something;

if we interpret that as

for x in (select * from a.v )
loop
       if ( f(col) = something) then output    
end loop;

Then f(col) would see only x =1 values from the table. However if we merge/push the predicates then we end up with:

select * from t where x =1 and f(col) = something;

Now the function F will possibly “see” data for rows that it should not be able to see, because you don’t know if F(col) will be evaluated before or after x=1 is,

For example

rajesh@ORA12C> create user a identified by a
  2             quota unlimited on users;

User created.

rajesh@ORA12C> create user b identified by b
  2             quota unlimited on users;

User created.

rajesh@ORA12C> grant create session,create table,
  2             create view,create procedure
  3             to a;

Grant succeeded.

rajesh@ORA12C> grant create session,create procedure,
  2             alter system to b;

Grant succeeded.

rajesh@ORA12C> conn a/a@ora12c
Connected.
a@ORA12C> create table t(x int, y varchar2(15));

Table created.

a@ORA12C> insert into t values(1,'I am ok');

1 row created.

a@ORA12C> insert into t values(2,'I am not OK');

1 row created.

a@ORA12C> commit;

Commit complete.

a@ORA12C> exec dbms_stats.set_table_stats(user,'T',numblks=>10000,numrows=>1000000);

PL/SQL procedure successfully completed.

a@ORA12C> create or replace function foo(x int)
2  returnint as
3  begin
  4     if x = 1 then return 1 ;
  5     else return 0 ;
  6     end if;
7  end;
8  /

Function created.

a@ORA12C> create or replace view v as select * from t where foo(x) = 1;

View created.

Intention: the world can see “where foo(x)=1”, no other data should be visible.

a@ORA12C> grant select on t to b;

Grant succeeded.

a@ORA12C> grant select on v to b;

Grant succeeded.

a@ORA12C> conn b/b@ora12c
Connected.
b@ORA12C>
b@ORA12C> create or replace function F(x varchar2)
2  return varchar2 as
3  begin
  4     dbms_output.put_line (' I see ='||x);
  5     return x;
6  end;
7  /

Function created.

That function will print out the data it ‘sees’

b@ORA12C> set autotrace on explain
b@ORA12C> select * from a.v where F(y) = 'I am ok';

         X Y
---------- ---------------
         1 I am ok

1 row selected.

I see =I am ok

Execution Plan
----------------------------------------------------------
Plan hash value: 1931062764

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   214K|  2703   (4)| 00:00:01 |
|*  1 |  VIEW              | V    | 10000 |   214K|  2703   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    | 10000 |   214K|  2703   (4)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter("F"("Y")='I am ok')
   2 - filter("FOO"("X")=1)

That is clear, FOO(x) is evaluated, then F(Y) is

b@ORA12C> alter system set OPTIMIZER_SECURE_VIEW_MERGING = false;

System altered.

b@ORA12C>
b@ORA12C> select * from a.v where F(y) = 'I am ok';

         X Y
---------- ---------------
         1 I am ok

1 row selected.

I see =I am ok
 I see =I am not OK


Oops, my function saw the data that it should not


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2200 |  2705   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   100 |  2200 |  2705   (4)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("F"("Y")='I am ok' AND "FOO"("X")=1)


b@ORA12C> set autotrace off
b@ORA12C>
b@ORA12C> alter system set OPTIMIZER_SECURE_VIEW_MERGING = true;

System altered.

b@ORA12C>

By default, we ensure that this remotely possible issue don’t even occur.