Compare Oracle Collections

In programming we don't just compare variables/integers with each other, we tend to compare arrays too. Collections aka arrays can be compared in different ways, lets see them in detail.

Traditional Method

Option 1

               The traditional method to compare collection would be just to loop through them and compare one with another, like below         

SQL> set timing on
SQL> 
SQL> set serveroutput on
SQL> declare
  2  type basket is table of number;
  3  num_balls basket := basket(1,2,3);
  4  num_color_balls basket := basket(3,2,1);
  5  inter_match number:=0;
  6  begin
  7  if num_balls.count = num_color_balls.count then
  8    for i in num_balls.first .. num_balls.last loop
  9      for j in num_color_balls.first .. num_color_balls.last loop
 10        if num_balls(i) = num_color_balls(j) then
 11            inter_match := inter_match+1;
 12        end if;
 13      end loop;
 14    end loop;
 15    if inter_match = num_balls.count then
 16        dbms_output.put_line('Arrays are equal');
 17    end if;
 18  else
 19  dbms_output.put_line('Arrays are not equal');
 20  end if;
 21  end;
 22  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> 

Option 2 

              Using Table Cast with MINUS operator thus avoiding loops

SQL> 
SQL> create type basket is table of number;
  2  /

Type created.

Elapsed: 00:00:00.72
SQL> 
SQL> declare
  2  num_balls basket := basket(1,2,3);
  3  num_color_balls basket := basket(3,2,1);
  4  v_count number;
  5  
  6  begin
  7  
  8  select column_value into v_count from
  9  (
 10  (select column_value from table(cast(num_balls as basket)))
 11  minus
 12  (select column_value from table(cast(num_color_balls as basket)))
 13  );
 14  
 15  if v_count>0 then
 16  dbms_output.put_line('Arrays are not equal');
 17  end if;
 18  
 19  exception
 20  when no_data_found then
 21  dbms_output.put_line('Arrays are equal');
 22  
 23  end;
 24  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 

You will have to go only with the above two options until Oracle 10g, From 10g we have got many options to compare collections, lets see them in detail. 

 Comparing collection is now as same as comparing variables/integers, example given below

SQL> 
SQL> declare
  2  num_balls basket := basket(1,2,3);
  3  num_color_balls basket := basket(1,2,3);
  4  begin
  5    if num_balls = num_color_balls then
  6      dbms_output.put_line('Arrays are equal');
  7    else
  8      dbms_output.put_line('Arrays are not equal');
  9    end if;
 10  end;
 11  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 

Note : The order of elements in an array is irrelevant while comparing collections. It means that an array with data 1,2,3 is equal to 3,2,1 and that's what we have seen above. Ok this is fine, can we do the same with Associative and Varrays 

Varrays

SQL> 
SQL> declare
  2  --type basket is table of number;
  3  type basket is varray(10) of number;
  4      num_balls basket := basket(1,2,3);
  5      num_color_balls basket := basket(1,2,3);
  6      begin
  7        if num_balls = num_color_balls then
  8          dbms_output.put_line('Arrays are equal');
  9        else
 10          dbms_output.put_line('Arrays are not equal');
 11        end if;
 12     end;
 13     /
      if num_balls = num_color_balls then
                   *
ERROR at line 7:
ORA-06550: line 7, column 20: 
PLS-00306: wrong number or types of arguments in call to '=' 
ORA-06550: line 7, column 7: 
PL/SQL: Statement ignored 

Elapsed: 00:00:00.01
SQL> 

Means you cannot compare all elements at one shot, have to do it one by one

SQL> 
SQL> declare
  2  --type basket is table of number;
  3  type basket is varray(10) of number;
  4      num_balls basket := basket(1,2,3);
  5      num_color_balls basket := basket(1,2,3);
  6      begin
  7        if num_balls(1) = num_color_balls(1) then
  8          dbms_output.put_line('Arrays are equal');
  9        else
 10          dbms_output.put_line('Arrays are not equal');
 11        end if;
 12     end;
 13     /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 

Associative Array - Cannot be compared at one shot, should be looped,

SQL> REM ASSOCIATIVE ARRAYS
SQL> 
SQL> declare
  2  type basket is table of number index by pls_integer;
  3      num_balls basket;
  4      num_color_balls basket;
  5      begin
  6      select level bulk collect into num_balls from dual connect by level < 10;
  7      select level bulk collect into num_color_balls from dual connect by level < 10;
  8        if num_balls = num_color_balls then
  9          dbms_output.put_line('Arrays are equal');
 10        else
 11          dbms_output.put_line('Arrays are not equal');
 12        end if;
 13     end;
 14     /
      if num_balls = num_color_balls then
                   *
ERROR at line 8:
ORA-06550: line 8, column 20: 
PLS-00306: wrong number or types of arguments in call to '=' 
ORA-06550: line 8, column 7: 
PL/SQL: Statement ignored 


Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> declare
  2  type basket is table of number index by pls_integer;
  3      num_balls basket;
  4      num_color_balls basket;
  5      begin
  6      select level bulk collect into num_balls from dual connect by level < 10;
  7      select level bulk collect into num_color_balls from dual connect by level < 10;
  8        if num_balls(1) = num_color_balls(1) then
  9          dbms_output.put_line('Arrays are equal');
 10        else
 11          dbms_output.put_line('Arrays are not equal');
 12        end if;
 13     end;
 14     /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>  

Will see about Multiset operators in the next post.

Partitions- Kind of Global Vs Local Partitioned Indexes

Had a great discussion with few techi folks today. It started with SQL tuning (my area of interest ) and eventually partitioning. I was sticking towards SQL Tuning + Partition Elimination + Global Indexes.  And they were sticking towards Full table Scan + Maintenance Activities + Local Indexes. The discussion was heating up and i made up my mind to switch lane :) when two more AK47 was kept before, yes Partitioned table with Tera Bytes of data + Archiving Strategy. 

             Does this look like OLTP vs OLAP. Big Table Maintenance + Archiving Strategy would shatter the usage of global indexes, but lets see what happens when global indexes are not present. Let's see them in detail. 

SQL> set timing on
SQL> 
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.40
SQL> 
SQL> CREATE TABLE t2
  2      (
  3        x   int not null primary key,
  4        dt  date,
  5        y   varchar2(30)
  6      )
  7      PARTITION BY RANGE(dt)
  8      (
  9      PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
 10      PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
 11      PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
 12      PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
 13      PARTITION junk VALUES LESS THAN (MAXVALUE)
 14     );

Table created.

Elapsed: 00:00:00.10
SQL> 
SQL> create index idx_y on t2(y);

Index created.

Elapsed: 00:00:00.03
SQL>

Loading data

SQL> insert into t2 values (1,sysdate-120,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> insert into t2 values (2,sysdate-90,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> insert into t2 values (3,sysdate-60,'ORACLE');

1 row created.

Elapsed: 00:00:00.03
SQL> insert into t2 values (4,sysdate-30,'ORACLE');

1 row created.

Elapsed: 00:00:00.01
SQL> insert into t2 values (5,sysdate,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>

Lets check the status of my indexes

SQL> select index_name,status from user_indexes where table_name ='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
SYS_C0012417                   VALID                                            

Elapsed: 00:00:00.08

As part of archiving strategy lets drop a Partition

SQL> alter table t2 drop partition part2_MAY2013;

Table altered.

Elapsed: 00:00:00.07
SQL> 
SQL> select index_name,status from user_indexes where table_name='T2'
  2  ;

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          UNUSABLE                                         
SYS_C0012417                   UNUSABLE                                         

Elapsed: 00:00:00.04
SQL>

Dropping partition leading to un-usability of indexes, If i create a Global Index, then would be looking forward to use my Global Index though skip_unusable_indexes defaults to true in Oracle 11g. In general archiving strategy can include the below options 

1. Drop Partition with Update Indexes Clause.Update Global Index makes you wait 
2. Truncate partition(Partition wise) with Update Indexes option + Drop Partition
3. Drop Partition + Rebuild the Index 
4. Delete data partition wise + Drop partition - Delete would update the Global Index And the easiest way would be
5. Drop partitions - Table without Global Indexes. 

 Now the question here is if we have to create a table without global index, how would the primary key look like,

SQL> CREATE TABLE t2
  2      (
  3        x   int not null,
  4        dt  date not null,
  5        y   varchar2(30)
  6      )
  7      PARTITION BY RANGE(dt)
  8      (
  9      PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
 10      PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
 11       PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
 12       PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
 13       PARTITION junk VALUES LESS THAN (MAXVALUE)
 14     );

Table created.       

Lets create the unique index on column x

SQL> 
SQL> create unique index idx_xdt on t2(x) local;
create unique index idx_xdt on t2(x) local
                               *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index 

In just simple words, it means that the partition key and key columns should have a link

SQL> REM - Lets create it in the form of a subset of key columns
SQL> 
SQL> create unique index idx_xdt on t2(x,dt) local;

Index created.
SQL> 
SQL> 
SQL> create index idx_y on t2(y);

Index created.
SQL> 
SQL> 
SQL> insert into t2 values (1,sysdate-120,'ORACLE');

1 row created.

SQL> insert into t2 values (2,sysdate-90,'ORACLE');

1 row created.

SQL> insert into t2 values (3,sysdate-60,'ORACLE');

1 row created.

SQL> insert into t2 values (4,sysdate-30,'ORACLE');

1 row created.

SQL> insert into t2 values (5,sysdate,'ORACLE');

1 row created.

SQL> commit;

Commit complete.

And now their impact over dropping partitions

SQL> 
SQL> select index_name,status from user_indexes where table_name in('T2');

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
IDX_XDT                        N/A                                              

SQL> 
SQL> select index_name,locality from user_part_indexes where table_name in('T2');

INDEX_NAME                     LOCALI                                           
------------------------------ ------                                           
IDX_XDT                        LOCAL                                            

SQL> 
SQL> 
SQL> alter table t2 drop partition part2_MAY2013;

Table altered.

SQL> 
SQL> select index_name,status from user_indexes where table_name='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          UNUSABLE                                         
IDX_XDT                        N/A                                              

SQL> 

The actual core issue is GLOBAL INDEX becoming unusable and then they have to be rebuild. It really becomes too time consuming when the table is huge and DBA's would often start praying when they rebuild index

SQL> 
SQL> alter index idx_y rebuild;

Index altered.

SQL> select index_name,status from user_indexes where table_name='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
IDX_XDT                        N/A                                              

SQL>

So worried about rebuilding global index post dropping a partition as part of archival strategy, Now lets see what happens when we don't have global index. To demonstrate the same, let me create a table with 133 partitions,

drop table t2;
CREATE TABLE t2
        (
          x   int not null,
          dt  date not null,
          y   varchar2(30)
        )
        PARTITION BY RANGE(dt)
        (
PARTITION part1_JAN2003 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')) ,
PARTITION part1_FEB2003 VALUES LESS THAN (to_date('01-FEB-2003','dd-mon-yyyy')) ,
PARTITION part1_MAR2003 VALUES LESS THAN (to_date('01-MAR-2003','dd-mon-yyyy')) ,
PARTITION part1_APR2003 VALUES LESS THAN (to_date('01-APR-2003','dd-mon-yyyy')) ,
PARTITION part1_MAY2003 VALUES LESS THAN (to_date('01-MAY-2003','dd-mon-yyyy')) ,
PARTITION part1_JUN2003 VALUES LESS THAN (to_date('01-JUN-2003','dd-mon-yyyy')) ,
PARTITION part1_JUL2003 VALUES LESS THAN (to_date('01-JUL-2003','dd-mon-yyyy')) ,
PARTITION part1_AUG2003 VALUES LESS THAN (to_date('01-AUG-2003','dd-mon-yyyy')) ,
PARTITION part1_SEP2003 VALUES LESS THAN (to_date('01-SEP-2003','dd-mon-yyyy')) ,
PARTITION part1_OCT2003 VALUES LESS THAN (to_date('01-OCT-2003','dd-mon-yyyy')) ,
PARTITION part1_NOV2003 VALUES LESS THAN (to_date('01-NOV-2003','dd-mon-yyyy')) ,
PARTITION part1_DEC2003 VALUES LESS THAN (to_date('01-DEC-2003','dd-mon-yyyy')) ,
PARTITION part1_JAN2004 VALUES LESS THAN (to_date('01-JAN-2004','dd-mon-yyyy')) ,
PARTITION part1_FEB2004 VALUES LESS THAN (to_date('01-FEB-2004','dd-mon-yyyy')) ,
PARTITION part1_MAR2004 VALUES LESS THAN (to_date('01-MAR-2004','dd-mon-yyyy')) ,
PARTITION part1_APR2004 VALUES LESS THAN (to_date('01-APR-2004','dd-mon-yyyy')) ,
PARTITION part1_MAY2004 VALUES LESS THAN (to_date('01-MAY-2004','dd-mon-yyyy')) ,
PARTITION part1_JUN2004 VALUES LESS THAN (to_date('01-JUN-2004','dd-mon-yyyy')) ,
PARTITION part1_JUL2004 VALUES LESS THAN (to_date('01-JUL-2004','dd-mon-yyyy')) ,
PARTITION part1_AUG2004 VALUES LESS THAN (to_date('01-AUG-2004','dd-mon-yyyy')) ,
PARTITION part1_SEP2004 VALUES LESS THAN (to_date('01-SEP-2004','dd-mon-yyyy')) ,
PARTITION part1_OCT2004 VALUES LESS THAN (to_date('01-OCT-2004','dd-mon-yyyy')) ,
PARTITION part1_NOV2004 VALUES LESS THAN (to_date('01-NOV-2004','dd-mon-yyyy')) ,
PARTITION part1_DEC2004 VALUES LESS THAN (to_date('01-DEC-2004','dd-mon-yyyy')) ,
PARTITION part1_JAN2005 VALUES LESS THAN (to_date('01-JAN-2005','dd-mon-yyyy')) ,
......
PARTITION part1_DEC2013 VALUES LESS THAN (to_date('01-DEC-2013','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
);
Table created.

SQL>
SQL> create unique index idx_xdt on t2(x,dt) local;

Index created.
SQL> select partition_count from user_part_tables where table_name = 'T2';

PARTITION_COUNT                                                                 
---------------                                                                 
            133 

Have loaded 12,60,000 records, which is off-course very tiny. Loaded using similar statements like below,

insert into t2 
select level, 
       to_date('01-JUL-2003','dd-mon-yyyy')+ mod(level,30),
       'ORACLE'||mod(level,1000) 
from dual 
connect by level < 10001; 

The records are spread across evenly, like below

SQL> select count(*),y from t2 group by y;

  COUNT(*) Y                                                                    
---------- ------------------------------                                       
      1260 ORACLE90                                                             
      1260 ORACLE101                                                            
      1260 ORACLE102                                                            
      1260 ORACLE107                                                            
      1260 ORACLE110                                                            
      1260 ORACLE118                                                            
      1260 ORACLE124                                                            
      1260 ORACLE132                                                            
      1260 ORACLE141                                                            
      1260 ORACLE157                                                            
      1260 ORACLE163                                                            
      1260 ORACLE171                                                            
      1260 ORACLE172                                                            
      1260 ORACLE177                                                            
      1260 ORACLE181                                                            
      1260 ORACLE185                      

....

Lets create a global index on column- Y

SQL> create index idx_y on t2(y) global;

Index created.
Elapsed: 00:00:31.30
SQL> exec dbms_stats.gather_table_stats('HARI','T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.39
SQL> 

Issue the culprit :)

SQL> update t2 set y='ORACLE_ORACLE' where x = 124587;

1 row updated.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> 

Lets check the selection part,

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:12.13
SQL> 
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11

SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:01.37
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2614863613                                                     
                                                                                
------------------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                    
------------------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                   |       |       |       |  1265 (100)|          |       |       |                                                    
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T2    |  1259 | 28957 |  1265   (0)| 00:00:16 | ROWID | ROWID |                                                    
|*  2 |   INDEX RANGE SCAN                 | IDX_Y |  1259 |       |     7   (0)| 00:00:01 |       |       |                                                    
------------------------------------------------------------------------------------------------------------                                                    
                                                                               
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - access("Y"='ORACLE_ORACLE')                                              

19 rows selected.

Elapsed: 00:00:02.68
SQL> 

Let me drop the Global Index and check the same

SQL> drop index idx_y;

Index dropped.
Elapsed: 00:00:01.68
SQL> 
SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:05.72
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2884929167                                                     
                                                                                
--------------------------------------------------------------------------------------------                                                                    
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                    
--------------------------------------------------------------------------------------------                                                                    
|   0 | SELECT STATEMENT    |      |       |       |  1486 (100)|          |       |       |                                                                    
|   1 |  PARTITION RANGE ALL|      |  1260 | 28980 |  1486   (2)| 00:00:18 |     1 |   133 |                                                                    
|*  2 |   TABLE ACCESS FULL | T2   |  1260 | 28980 |  1486   (2)| 00:00:18 |     1 |   133 |                                                                    
--------------------------------------------------------------------------------------------                                                                    
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - filter("Y"='ORACLE_ORACLE')                                              
19 rows selected.

Elapsed: 00:00:02.47
SQL> 

As expected Partition Elimination doesn't happen and the optimizer has to look into all the partition. Though the elapsed time for FTS + Partition ALL is slightly cheaper in this case, we will have to pay when the table grows and Partition Pruning doesn't happen. Global Indexes helps us in identifying the partition. But for a moment do you think you can go with Local Index, Ok lets check that too :)

SQL> create index idx_local_y on t2(y) local;

Index created.

Elapsed: 00:00:11.94
SQL> select index_name, locality from user_part_indexes where table_name='T2';

INDEX_NAME                     LOCALI                                           
------------------------------ ------                                           
IDX_XDT                        LOCAL                                            
IDX_LOCAL_Y                    LOCAL                                            

Elapsed: 00:00:00.23
SQL> 
SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:03.26
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2685712566                                                     
                                                                                
------------------------------------------------------------------------------------------------------------------                                              
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                              
------------------------------------------------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT                   |             |       |       |  1396 (100)|          |       |       |                                              
|   1 |  PARTITION RANGE ALL               |             |  1259 | 28957 |  1396   (1)| 00:00:17 |     1 |   133 |                                              
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T2          |  1259 | 28957 |  1396   (1)| 00:00:17 |     1 |   133 |                                              
|*  3 |    INDEX RANGE SCAN                | IDX_LOCAL_Y |  1259 |       |   137   (0)| 00:00:02 |     1 |   133 |                                              
------------------------------------------------------------------------------------------------------------------                                              
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   3 - access("Y"='ORACLE_ORACLE')                                              
                                                                                

20 rows selected.

Elapsed: 00:00:05.22
 

The above is a clear example of reading multiple volumes of a book with each chapter being identified through Book Index :), Got it, i.e, the local index is being used to read all partitions of the table. 

Conclusion 

         Can partitioned table exists without a Single Global Index.Yes, Rebuilding Index is a nightmare in few circumstances, but at what cost, shouldn't i help the optimizer in performing Partition Pruning through Global Index. Dropping Partitions-Wise is definitely the efficient/effective and easiest way to perform data archival(Facilitated through Local Index). Its always Benefit Vs Cost, It is up to the application/architect team is decide to achieve maximum benefit.

Locks in Oracle - Part 2

Transaction--> Locks

1. How is my transaction tracked 
2. How does oracle ensures or rather maintains data integrity and table definition integrity during the due course of my transaction
3. How is lock escalation handled in Oracle
4. Where is the Lock info stored in Oracle

Does these question are of your interest, then proceed reading below,  

A transaction begins with a DML/Select FOR UPDATE and ends with commit, rollback or  even with a DDL. Every transaction is identified with a unique transaction ID.  Let's try it out, 

SQL> 
SQL> update t1 set id_val='ORC' where id = 1;

1 row updated.

Elapsed: 00:00:00.16
SQL>

Lets check for the transaction ID

SQL> 
SQL> select xid from v$transaction;

XID                                                                             
----------------                                                                
01000C0083060000                                                                

Elapsed: 00:00:00.01
SQL>

If you would like to see the decimal notation of the transaction id, then try below

SQL> 
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID                                                            
--------------------------------------------------------------------------------
1.12.1667                                                                       

Elapsed: 00:00:00.02

The transaction ID has three parts in it, Rollback Segment number, Slot Number and a Wrap Number. Lets check the v$transaction to validate the same,

SQL> select xid,ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;

XID              ADDR         XIDUSN    XIDSLOT     XIDSQN                      
---------------- -------- ---------- ---------- ----------                      
01000C0083060000 3B0F7554          1         12       1667                      

Elapsed: 00:00:00.01
SQL>

Well the column significance are 

XIDUSN   - Transaction ID's Undo Segment Number 

XIDSLOT - Transaction ID's Slot Number 

XIDSQN   - Transaction ID's Sequence Number

           Transaction ID is the single identifier which would help us to proceed further with the series of investigation,

SQL> 
SQL> select * from v$locked_object;

   XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID   ORACLE_USERNAME   OS_USER_NAME   PROCESS   LOCKED_MODE                                            
---------- ---------- ---------- ---------- ----------  ---------------- --------------- --------- -----------                                            
    1          12        1667      74916        203              HARI      HARI-PC\HARI  6076:5924  3                                            
                                                                                
Elapsed: 00:00:00.05
SQL>

LOCKED_MODE in v$locked_Object indicates in what mode the Object_ID is locked. In our case the Object_Id 74916 (Table Name - T2) is locked in SX(Row- Exclusive) mode i.,e "Rows are locked in the table in exclusive mode", Now lets see the types of locks the transaction is holding,

SQL> 
SQL> select * from v$lock where sid=203;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST       CTIME      BLOCK      
-------- -------- ---------- -- ---------- ---------- ---------- ----------     ---------- ----------   
3D378880 3D3788AC        203 AE        100          0          4          0         435          0      
0EEB6AA0 0EEB6AD0        203 TM      74916          0          3          0          13          0      
3B0F7554 3B0F7594        203 TX      65548       1667          6          0          13          0      
                                                                                

Elapsed: 00:00:00.05
SQL> 

v$Lock helps us to identify the types of locks associated with the transaction. The locks bundled with the transaction are AE,TM, TX. Keeping aside AE at the moment lets see what these TX and TM are,

TX Lock/Enqueue - This represents the transaction and are enforced to maintain data integrity.  Its the Row-Level locking, a modified row is always locked in exclusive manner so that other users cannot modify the row unless untill the parent transaction ends. And that's the reason it shows LMODE = 6 indicating exclusive lock on the row and REQUEST = 0 means am holding the lock and am the owner, therefore doesn't request any. The ID1(usn+slot) and ID2(seq) together represents the transaction ID. This exclusive lock doesn't mean DML are prohibited on the table, it's only on that particular row so that other session cannot modify it. So a lock is never escalated it can only be released when the transaction ends.

TM Lock/Enqueue - This represents the table and they lock the table from structural change, ensuring no change to data definition of the table during the due course of the transaction. And that's the reason it shows LMODE = 3 indicating rows are locked in exclusive mode in this table. ID1 shows the Object_ID being locked.

Oracle doesn't have the concept of Lock Manager which would normally act as the central locking system to handle locks in few database's, remember a central locking is prone to contention. In oracle the lock info is stored right at the block itself. The Data Block holds the lock bit information which in turn points to the ITL - Interested Transaction List and this in turn points to the undo segment header to validate the validity of the data.

Locks in Oracle - Part 1

Apart from parsing

1. What happens when i issue an update statement ?
2. How does Oracle comply with the properties of a Database Transaction aka ACID properties

If interested read further :)

A transaction is nothing but SQL statements which is tracked with an unique transaction ID until commit / rollback / DDL are issued. ooh ooh hold on, before i get into the internals lets have a quick view on the basic's of Locks.

Is this true - "Picture Speaks Louder Than Words"

For the next series of diagrams concentrate on the below arrow in each diagram- 

Lets start with a basic query and check what happens
Step-1

Step-2

Step-3

Step-4

Step-5

Step-6

Step-7

does these diagrams helped you to understand something, will continue internals soon 

ORA-08103: object no longer exists

"Object no longer exists" 

Which means that the object was there but not now :) This simple phrase means so much, it could be interpreted like any one of the below

1. The object is vanished in the middle of my activity, i would anticipate "ora-01555 - snapshot too old"  but how can oracle afford to loose an object when it is being used
2. Does this mean block corruption
3. Or is it by mistake i get "object no longer exists" instead of "ORA-00942: table or view does not exist" - kidding ahh :)  

okie lets do some blogging,

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Keeping aside block corruption scenario, Lets create the table in session S1 and simulate the error

SQL>REM CREATE TABLE
SQL> create table tbl_t1 as select * from dba_objects;

Table created.

SQL>REM FUNNY QUERY
SQL> select count(*) from
(
select t1.object_id,t2.data_object_id from (select * from tbl_t1)t1,(select * from tbl_t1)t2 where t1.LAST_DDL_TIME >=t2.LAST_DDL_TIME and
t2.STATUS in (select status from tbl_t1)
);  2    3    4    5
select t1.object_id,t2.data_object_id from (select * from tbl_t1)t1,(select * from tbl_t1)t2 where t1.LAST_DDL_TIME >=t2.LAST_DDL_TIME and
                                                          *
ERROR at line 3:
ORA-08103: object no longer exists

Ops what happen to my table, lets check its definition

SQL>REM CHECK FOR TABLE DEFINITION
SQL> desc tbl_t1
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL>

Well what happened during my query execution was, an truncate statement was issued at the same time in session S2 which has caused the issue at session S1

SQL> truncate table tbl_t1;

Table truncated.

SQL>

Truncate is causing the issue? Lets validate what truncate is doing at the background

SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74714

Elapsed: 00:00:00.03
SQL>
SQL> select count(1) from tbl_t1;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.00
SQL> insert into tbl_t1 select * from dba_objects;

72514 rows created.

Elapsed: 00:00:00.61
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Issue truncate now

SQL>
SQL> truncate table tbl_t1;

Table truncated.

Elapsed: 00:00:01.13
SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74715

Elapsed: 00:00:00.00
SQL>

Note : DATA_OBJECT_ID is incremented, Now it is very clear that Truncate recreates the tables with a new DATA_OBJECT_ID. Now does this happen when i issue truncate on a table at all times ?

SQL> REM WITHOUT DATA
SQL>
SQL> select count(1) from tbl_t1;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.00
SQL>
SQL> truncate table tbl_t1;

Table truncated.

Elapsed: 00:00:00.01
SQL>
SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74715

Elapsed: 00:00:00.00
SQL>

Thus DATA_OBJECT_ID is incremented only when truncate is issued on a table which holds data. This change in DATA_OBJECT_ID has led to "ORA-08103: object no longer exists" error while executing the query which has referenced the same table being truncated simultaneously.

Haven't we got another point to answer the interview question "what is the difference between truncate and delete" :)

Real Time SQL Monitoring in Oracle 11g

Do you want to know, 

Can i see the plan while the SQL is running ?

Where is my SQL is in its Execution Plan ?

Need statistics at each step of the plan and what are the resources consumed for the just completed step ?

What is the Actual Cardinality at each step ?

       There are many ways to retrieve the above data and they are costly in terms of impacting the system performance. Oracle 11g introduces Real Time SQL Monitoring which would answer the above question with  almost no impact on the system.

Lets try these,  Below is the query written with the only intention to run for longer period of time :)

SQL> create table tbl_parallel as select rownum id,'DOD'||rownum id_val,sysdate
id_dt from dual connect by level < 10000;
SQL>
SQL>
SQL> with tp as (
select * from tbl_parallel),
tl as (
select length(id_val) ld from tbl_parallel where length(id_val)> (select min(length(id_val)) from tbl_parallel))
select * from tp t1,tp t2,tl where t1.id > t2.id and length(t1.id_val)>tl.ld;
SQL>

Lets take a look at the Execution Plan and check what is the step it is currently executing 

 The report is so cool, the SQL is now performing SORT JOIN. Take a look at the statistics - Elapsed time, Buffer gets and writes and I/O wait time being provided. We could also see the actual cardinality for each step. It also provides the SQL Bind info, Session id etc

will see how SQL Monitoring can help in analyzing SQL's in the next part

Cardinality Feedback in Oracle 11.2.0.3

In the previous blog we have seen Cardinality Feedback in Oracle 11.2.0.1, where the reason for hard parse during second run was not tangible. Oracle overcomes this in 11.2.0.3 with an additional column named USE_FEEDBACK_STATS in v$sql_shared_cursor.  

Have repeated the same test case in Oracle 11.2.0.3, lets review it

SQL> set linesize 100
SQL> set timing on
SQL> 
SQL> create table tblt1 (id number,id1 number,id_val varchar2(10),id_val1 varchar2(10));

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> insert into tblt1
  2  select round(dbms_random.value(4,10000)) id,
  3  rownum id1,
  4  round(dbms_random.value(-100,40)) id_val,
  5  round(dbms_random.value(4,10000)) id_val1
  6  from dual connect by level < 10000;

9999 rows created.

Elapsed: 00:00:00.54
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> begin
  2  dbms_stats.gather_table_stats('HARI','TBLT1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39

And the select returned

SQL> select * from tblt1 where id_val1 > 18 and id < 99999;

        ID        ID1 ID_VAL     ID_VAL1                                                            
---------- ---------- ---------- ----------                                                         
      9917        694 28         4162                                                               
      7489        695 -40        6788                                                               
      3705        696 -80        2162                      
..
..
.....

9983 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  80tf1dj5fhu58, child number 0                                                               
-------------------------------------                                                               
select * from tblt1 where id_val1 > 18 and id < 99999                                               
                                                                                                    
Plan hash value: 1166073720                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |       |       |       |    11 (100)|          |                         
|*  1 |  TABLE ACCESS FULL| TBLT1 |   500 |  8500 |    11   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBLT1@SEL$1                                                                          
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999 data-blogger-escaped------------------------------------------------------------="" data-blogger-escaped--="" data-blogger-escaped-00:00:00.09="" data-blogger-escaped-1="" data-blogger-escaped-29="" data-blogger-escaped-by="" data-blogger-escaped-column="" data-blogger-escaped-elapsed:="" data-blogger-escaped-identified="" data-blogger-escaped-information="" data-blogger-escaped-operation="" data-blogger-escaped-pre="" data-blogger-escaped-projection="" data-blogger-escaped-rows="" data-blogger-escaped-selected.="" id="">

Got the same plan as we got in 11.2.0.1, now lets check v$sql_shared_cursor

SQL> column sql_id format a20
SQL> column child_number format 99
SQL> column use_feedback_stats format a20
SQL> 
SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58';

SQL_ID               CHILD_NUMBER USE_FEEDBACK_STATS                                                
-------------------- ------------ --------------------                                              
80tf1dj5fhu58                   0 Y                                                                 

Elapsed: 00:00:00.02

USE_FEEDBACK_STATS indicates that feedback can be used during the next rerun so as to obtain better cardinality estimates. On rerunning the query,

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  80tf1dj5fhu58, child number 1                                                               
-------------------------------------                                                               
select * from tblt1 where id_val1 > 18 and id < 99999                                               
                                                                                                    
Plan hash value: 1166073720                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |       |       |       |    11 (100)|          |                         
|*  1 |  TABLE ACCESS FULL| TBLT1 |  9983 |   165K|    11   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBLT1@SEL$1                                                                          
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
   1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999))                                             
                                                                                                    
Column Projection Information (identified by operation id):                                         
-----------------------------------------------------------                                         
                                                                                                    
   1 - "ID"[NUMBER,22], "TBLT1"."ID1"[NUMBER,22],                                                   
       "TBLT1"."ID_VAL"[VARCHAR2,10], "ID_VAL1"[VARCHAR2,10]                                        
                                                                                                    
Note                                                                                                
-----                                                                                               
   - cardinality feedback used for this statement                                                   
                                                                                                    

33 rows selected.

Elapsed: 00:00:00.08

Yes Cardinality Feedback is used and the correct cardinality 9983 is obtained. And the reason for hard parse is given below

SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58';

SQL_ID               CHILD_NUMBER USE_FEEDBACK_STATS                                                
-------------------- ------------ --------------------                                              
80tf1dj5fhu58                   0 Y                                                                 
80tf1dj5fhu58                   1 N                                                                 

Elapsed: 00:00:00.00

Isn't USE_FEEDBACK_STATS much useful to affirm the usage of Cardinality Feedback in Oracle 11.2.0.3