Monday, March 30, 2015

Improved index locking in 11g

 
During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as it’s done previously until all these prior active transactions have completed. No change so far.
However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully (However prior to 11g these transactions will be locked out). The indexing process no longer impacts other concurrent transactions on the base table. It will be the only process potentially left hanging while waiting to acquire its associated lock resource.
 
/******************
In Oracle 10g database
*******************/
 
session#1
       create table t(x int,y varchar2(10));
       insert into t values(1,'a');
      
session#2
       create index t_idx on t(x) ONLINE;
      
session#1
rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- ------------------------------
       142                 1              148 session#2  enq: TM - contention
       144                                    session#3  SQL*Net message from client
       148                                    session#1  SQL*Net message to client
       152                                               SQL*Net message from client
 
4 rows selected.
 
rajesh@ORA10G>
So, Session#2 is blocked by session#1
 
session#3    
       insert into t values(2,'b');
      
session#1
rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- ------------------------------
       142                 1              148 session#2  enq: TM - contention
       144                 1              142 session#3  enq: TM - contention
       148                                    session#1  SQL*Net message to client
       152                                               SQL*Net message from client
 
4 rows selected.
 
rajesh@ORA10G>
 
So, Session#2 is blocked by session#1 & Session#3 is blocked by Session#2
 
rajesh@ORA10G> commit;
 
Commit complete.
 
rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- ------------------------------
       142                 1              144 session#2  enq: TM - contention
       144                                    session#3  SQL*Net message from client
       148                                    session#1  SQL*Net message to client
       152                                               SQL*Net message from client
 
4 rows selected.
 
rajesh@ORA10G>
 
So when session#1 commit, the session#2 is locked out by session#3
 
 
/******************
In Oracle 11g database
*******************/
 
session#1
       create table t(x int,y varchar2(10));
       insert into t values(1,'a');
      
session#2
       create index t_idx on t(x) ONLINE;
      
session#1
rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- -------------------------------
        67                                    session#3  SQL*Net message from client
        68                                    session#1  SQL*Net message from client
       134                 1               68 session#2  enq: TX - row lock contention
 
3 rows selected.
 
rajesh@ORA11G>
 
So, Session#2 is blocked by session#1 (as usual, similar to 10g database)
 
session#3    
       insert into t values(2,'b');
      
session#1
rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- ---------------------------------
        67                                    session#3  SQL*Net message from client
        68                                    session#1  SQL*Net message from client
       134                 1               68 session#2  enq: TX - row lock contention
 
3 rows selected.
 
rajesh@ORA11G>
 
Session#3 is not being locked out. (Were as in 10g it is locked out)
 
rajesh@ORA11G> commit;
 
Commit complete.
 
rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event
  2  from v$session s
  3  where username = user ;
 
       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT
---------- ----------------- ---------------- ---------- -------------------------------
        67                                    session#3  SQL*Net message from client
        68                                    session#1  SQL*Net message from client
       134                 1               67 session#2  enq: TX - row lock contention
 
3 rows selected.
 
rajesh@ORA11G>
 
Upon commit session#2 is locked out by session#3 (similar to 10g behavior)
 
So transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully in Oracle 11g databases.
 
 
 

Saturday, March 21, 2015

Enabling parallel dml in 12c

The conventional way of enabling parallel DML is to enable it with an ALTER SESSION command. This command enables parallel DML for the session and all subsequent DML statements are candidates for parallel execution provided that the rules and restrictions (like no FKs, no triggers) for parallel DML are met.

12c introduces a new way of enabling and disabling parallel DML. Rather than enabling or disabling it session-wise you can enable or disable it statement-wise using new hints. The hint ENABLE_PARALLEL_DML enables parallel DML for the statement, and the hint DISABLE_PARALLEL_DML disables it for the statement.

rajesh@PDB1> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3050126167

----------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |    39 |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL | T1       |       |        |      |            |
|   2 |   PX COORDINATOR         |          |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |    39 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |    39 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL   | T        |    39 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property
   - PDML is disabled in current session

17 rows selected.

rajesh@PDB1>

We can see that parallel DML is not enabled for the above statement indicated by the LOAD operation being above the PX COORDINATOR in the plan.

rajesh@PDB1> explain plan for
  2     insert /*+ enable_parallel_dml parallel(t1) */ into t1
  3     select /*+ parallel(t) */ * from t ;

Explained.

rajesh@PDB1>
rajesh@PDB1> select * from table(dbms_xplan.display)    ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 550883001

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |    39 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |    39 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T1       |       |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |    39 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |    39 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | T        |    39 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property

17 rows selected.

rajesh@PDB1>


Here we see that the notes section does not mention parallel DML as disabled and the LOAD operation is under the PX COORDINATOR, both of these indicate that parallel DML is enabled

Friday, March 20, 2015

Advanced Index Compression

Compressing an index can be an excellent way to permanently reduce the size of an index in a very cost effective manner. Index entries with many duplicate values (or duplicate leading columns within the index) can be “compressed” by Oracle to reduce both storage overheads and potentially access overheads for large index scans. Oracle basically de-duplicates repeated indexed column values within each individual leaf block by storing each unique occurrence in a prefix section within the block.

But it’s important to compress the right indexes in the right manner. If indexes do not have enough repeated data, it’s quite possible to make certain indexes larger rather than smaller when using compression (as the overheads of having the prefix section in the index block outweighs the benefits of limited reduction of repeated values). So one needs to be very selective on which indexes to compress and take care to compress the correct number of columns within the index. Oracle will only protect you from yourself if you attempt to compress all columns in a unique index, as in this scenario there can be no duplicate values to compress.

So, wouldn’t it be nice if Oracle made it all a lot easier for us and automatically decided which indexes to compress, which columns within the index to compress and which indexes to simply not bother compressing at all. Additionally, rather than an all or nothing approach in which all index leaf blocks are compressed in the same manner, wouldn’t it be nice if Oracle decided for each and every individual leaf block within the index how to best compress it. For those index leaf block that have no duplicate entries, do nothing, for those with some repeated columns just compress them and for those leaf blocks with lots of repeated columns and values to compress all of them as efficiently as possible.

All these are being addressed by the recent release of Oracle database 12.1.0.2, one of the really cool new feature got introduced was Advanced Index Compression

rajesh@PDB1> create table t
  2  nologging
  3  as
  4  select owner,object_name,subobject_name,object_id,
  5     data_object_id,object_type,created,last_ddl_time,
  6     timestamp,status,temporary,generated,secondary,
  7     namespace,edition_name,sharing,editionable,
  8     oracle_maintained, case when id between
  9                     0 and 250000 then 0
 10                     else id end id
 11  from big_table;

Table created.

So, I have fabricated the data such that values in the ID column are effectively unique within 75% of the table but the other 25% consist of repeated values.

If we create a normal B-Tree index without compression:

rajesh@PDB1> create index t_idx on t(id) nologging ;

Index created.

rajesh@PDB1> column index_name format a10
rajesh@PDB1> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2124

1 row selected.

rajesh@PDB1> 

We noticed that the index consist of 2124 leaf blocks.
If we try to use normal compression on the index:

rajesh@PDB1> alter index t_idx rebuild compress nologging;

Index altered.

rajesh@PDB1> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ENABLED              2685

1 row selected.

rajesh@PDB1>

We notice that the compressed index rather than decrease in size has actually increased in size, up to 2685 leaf blocks. So the index has grown by some 25% due to the fact the index predominately contains unique values which don’t compress at all and the resultant prefix section in the leaf blocks becomes nothing more than additional overhead. The 25% section of the index containing all the repeated values has indeed compressed effectively but these savings are more than offset by the increase in size associated with the other 75% of the index where the index entries had no duplication.

However, if we use the new advanced index compression capability via the COMPRESS ADVANCED LOW clause:

rajesh@PDB1> alter index t_idx rebuild compress advanced low nologging;

Index altered.

rajesh@PDB1>
rajesh@PDB1> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED LOW         2054

1 row selected.

rajesh@PDB1>

We notice the index has now indeed decreased in size from the original 2124 leaf blocks down to 2054. Oracle has effectively ignored all those leaf blocks where compression wasn’t viable and compressed just the 25% of the index where compression was effective. Obviously, the larger the key values (remembering the rowids associated with the index entries can’t be compressed) and the larger the percentage of repeated data, the larger the overall compression returns.

Advanced Index Compression enables Oracle to automatically just compress those index leaf blocks where compression is beneficial.

If we look at a couple of partial block dumps from this index, first a dump from a leaf block that did have duplicate index entries

rajesh@PDB1> column header_file new_val hf
rajesh@PDB1> column header_block new_val hb
rajesh@PDB1>
rajesh@PDB1> select file_id as header_file , block_id +5 as header_block
  2  from dba_extents
  3  where segment_name ='T_IDX'
  4  and owner = user
  5  and extent_id = 0 ;

HEADER_FILE HEADER_BLOCK
----------- ------------
         12        17197

1 row selected.

rajesh@PDB1>
rajesh@PDB1> alter system flush buffer_cache;

System altered.

rajesh@PDB1> @tkfilename.sql
D:\APP\179818\diag\rdbms\cdb1\cdb1\trace\cdb1_ora_5384.trc

rajesh@PDB1> alter system dump datafile &hf block &hb ;
old   1: alter system dump datafile &hf block &hb
new   1: alter system dump datafile         12 block      17197

System altered.

rajesh@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Leaf block dump
===============
header address 408388196=0x18578264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 652
kdxcofbo 1348=0x544
kdxcofeo 2164=0x874
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 50348846=0x300432e
kdxleprv 50348844=0x300432c
kdxledsz 0
kdxlebksz 8036
kdxlepnro 1
kdxlepnco 1 (Adaptive)
prefix row#0[8032] flag: -P-----, lock: 0, len=4
col 0; len 1; (1):  80
prc 652
row#0[8023] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 96 00 04
psno 0
row#1[8014] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 96 00 05
psno 0
row#2[8005] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 96 00 06
psno 0
row#3[7996] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 96 00 07
psno 0
row#4[7987] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 96 00 08
psno 0
. . . . . . . .
. . . . . . . .
row#650[2173] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 a2 00 10
psno 0
row#651[2164] flag: -------, lock: 0, len=9
col 0; len 6; (6):  03 00 41 a2 00 11
psno 0
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 12 minblk 17197 maxblk 17197


The red section is a portion of the index header that determines the number of rows in the prefix table of the index (kdxlepnro 1). The prefix table basically lists all the distinct column values in the leaf blocks that are to be compressed. The value 1 denotes there is actually only just the 1 distinct column value in this specific leaf block (i.e. all index entries have the same indexed value). This section also denotes how many of the indexed columns are to be compressed (kdxlepnco 1). As this index only has the one column, it also has a value of 1. Note this value can potentially be anything between 0 (no columns compressed) up to the number of columns in the index.  The (Adaptive) reference tells us that Index Advanced Compression has been used and that the values here can change from leaf block to leaf block depending on the data characteristics of the index entries within each leaf block (a dump of a basic compressed index will not have the “Adaptive” reference).

The blue section is the compression prefix table and details all the unique combinations of index entries to be compressed within the leaf block. As all indexed values are the same in this index (value 0, internally represented as 80 hex), the prefix table only has the one row. prc 652 denotes that all 652 index entries in this leaf block have this specific indexed value

Next follows all the actual index entries, which now only consist of the rowid (the 6 byte col 0 column) as they all reference psno 0, which is the unique row id of the only row within the prefix table (row#0).

So rather than storing the indexed value 652 times, we can just store the index value (0) just the once within the prefix table and simply reference it from within the actual index entries. This is why index compression can save us storage, storing something once within a leaf block rather than multiple times.

If we now look at a partial block dump of another index leaf block within the index,  that consists of many differing (basically unique) index entries:

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

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 21 11:25:55 2015

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

Last Successful login time: Sat Mar 21 2015 11:22:46 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1> set timing off
rajesh@PDB1>
rajesh@PDB1> column header_file new_val hf
rajesh@PDB1> column header_block new_val hb
rajesh@PDB1> select file_id as header_file , block_id +3 as header_block
  2  from dba_extents
  3  where segment_name ='T_IDX'
  4  and owner = user
  5  and extent_id = 29 ;

HEADER_FILE HEADER_BLOCK
----------- ------------
         12        36867

1 row selected.

rajesh@PDB1>
rajesh@PDB1> @tkfilename.sql
D:\APP\179818\diag\rdbms\cdb1\cdb1\trace\cdb1_ora_11688.trc

rajesh@PDB1> alter system dump datafile &hf block &hb ;
old   1: alter system dump datafile &hf block &hb
new   1: alter system dump datafile         12 block      36867

System altered.

rajesh@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Prod
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\179818>

Leaf block dump
===============
header address 462979684=0x1b988264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 938=0x3aa
kdxcofeo 1754=0x6da
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 50368516=0x3009004
kdxleprv 50368514=0x3009002
kdxledsz 0
kdxlebksz 8036
kdxlepnro 0
kdxlepnco 0 (Adaptive)
row#0[8022] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 36 32
col 1; len 6; (6):  03 00 7c 0a 00 40
row#1[8008] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 36 33
col 1; len 6; (6):  03 00 7c 0a 00 41
row#2[7994] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 36 34
col 1; len 6; (6):  03 00 7c 0a 00 42
row#3[7980] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 36 35
col 1; len 6; (6):  03 00 7c 0a 00 43
row#4[7966] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 36 36
col 1; len 6; (6):  03 00 7c 0a 00 44
. . . . . . . .
. . . . . . . .
row#447[1768] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 3a 61
col 1; len 6; (6):  03 00 7d 03 00 20
row#448[1754] flag: -------, lock: 0, len=14
col 0; len 4; (4):  c3 57 3a 62
col 1; len 6; (6):  03 00 7d 03 00 21
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 12 minblk 36867 maxblk 36867


We notice that in the red section, both kdxlepnro 0 and kdxlepnco 0 (Adaptive) have a value of 0, meaning we have no rows and no columns within the prefix table. As such, we have no prefix table at all here and that this leaf block has simply not been compressed.

If we look at the actual index entries, they all have an additional column now in blue, that being the actual indexed value as all the index values in this leaf block are different from each other. Without some form of index entry duplication, there would be no benefit from compression and Index Advanced Compression has automatically determined this and not bothered to compress this leaf block. An attempt to compress this block would have actually increased the necessary overall storage for these index entries, due to the additional overheads associated with the prefix table (note it has an additional 2 byes of overhead per row within the prefix table).