Basic versus OLTP compression

If you are going to archive a table having a very large amount of data from a running system into a historical table and you expect from this archived table to be read-only then you have better to use the free BASIC compression mode instead of the paid OLTP mode. This article aims to show you the reason for this compression mode preference based on real life example.

In order to take advantage from the free BASIC compression mode you need first to insert data using a direct path load (this is why this compression mode used to be known as compression for direct path load operations or bulk load operations) and ensure that there is no trigger or integrity constraint on the inserted table that will make Oracle silently ignoring your requested direct path load mode. In passing, in contrast to the preceding Oracle releases, 12.1.0.2 Oracle database will inform you when a direct path is ignored via the Note at the bottom of the corresponding insert execution plan:

Note
-----
   - PDML disabled because triggers are defined
   - Direct Load disabled because triggers are defined

Here’s an example of BASIC compression I’ve executed on a 12.1.0.2 database?

SELECT  
    t.owner,
    t.table_name,
    t.compress_for,
    s.bytes/1024/1024/1024 GB
FROM 
    dba_tables t ,
    dba_segments s
WHERE 
    s.segment_name = t.table_name
AND s.owner        = 'XXX'
AND t.table_name   = 'T1'
ORDER BY 4;
   
OWNER      TABLE_NAME   COMPRESS_FOR    GB
---------- ------------ ------------- ----------
XXX        T1                         263.114136 

263 GB worth of data of which we need to keep only 2 years and archive the rest.

The first step in this process was to create an empty table cloned from the production table and define it to accept the BASIC mode compression:

-- create a table compressed with BASIC mode
CREATE TABLE T1_HIST
      COMPRESS BASIC
      TABLESPACE HIST_TABSPACE 
         AS
      SELECT * FROM T1
      WHERE 1 = 2;   

As far as I was going to send a huge amount of data into this archived table I decided to use a parallel insert which needs to be preceded by enabling parallel DML either by altering the session or by using the appropriate hint (for 12c)

-- enable parallel dml 
SQL> alter session enable parallel dml;        

The way is now paved to start sending historical data into their new destination:

-- direct path load data older than 2 years into the archived table
INSERT /*+ append parallel(bsic,4) */ 
  INTO T1_HIST bsic
  SELECT
          /*+ parallel(oltp,4) */
              *
    FROM  T1 oltp
       WHERE HIST_DAT > add_months(sysdate,-24);

2,234,898,367 rows created.

More than 2 billion of rows direct path loaded.

And the agreeable surprise is:

SQL> SELECT
    t.owner,
    t.table_name,
    t.compress_for,
    s.bytes/1024/1024/1024 GB
FROM
    dba_tables t ,
    dba_segments s
WHERE
    t.compress_for ='BASIC'
AND
    s.segment_name   = t.table_name
AND s.owner       = 'XXX'
ORDER BY 4;

OWNER      TABLE_NAME   COMPRESS_FOR   GB
---------- ------------ ------------- ----------
XXX         T1_HIST     BASIC         53.2504272 

We went from a source table with 263GB worth of data to a cloned table compressed using BASIC mode to end up with only 53GB or historical data.

If you are wondering how much rows I have not send into the archived table then here’s

SQL> select /*+ parallel(8) */ count(1) 
     from T1
     where HIST_DAT <= add_months(sysdate,-24);
   
7,571,098 

This means that using the BASIC free compression mode I have archived almost all rows from the production table and succeeded to pack the initial 263GB into 53GB only. That’s an excellent compression ratio of 5 (we have divided the initial size by 5). Though that Oracle is saying that the compression ration depends on the nature of your data and it could range between a factor of 2x to 4x.

Should you have used the paid OLTP compression mode you would have got an archived table with a size approximatively 10% higher (~60GB). This is due to Oracle considering the table compressed in BASIC mode to be read only and not subject to any update with a default PCT_FREE set to 0 behind the scene:

SQL> select table_name, pct_free
    from dba_tables
    where table_name = 'T1_HIST';

TABLE_NAME    PCT_FREE
----------- ----------
T1_HIST          0 

As you can see, if you intend to archive a huge amount of data into a read only table, want to gain disk space with this operation and you don’t want to pay for the OLTP compression then you can opt for the free BASIC compression mode.

There are few interesting things that come up along with this archiving process. Particularly the new 12c way (HYBRID TSM/HWMB) Oracle is using to keep down the number of new extents and to avoid HV enqueue wait event during parallel direct path load with a high DOP across several instances:

SQL Plan Monitoring Details (Plan Hash Value=3423860299)
====================================================================================
| Id |              Operation               |   Name   |  Rows   |Execs |   Rows   |
|    |                                      |          | (Estim) |      | (Actual) |
====================================================================================
|  0 | INSERT STATEMENT                     |          |         |    5 |        8 |
|  1 |   PX COORDINATOR                     |          |         |    5 |        8 |
|  2 |    PX SEND QC (RANDOM)               | :TQ10000 |    819M |    4 |        8 |
|  3 |     LOAD AS SELECT (HYBRID TSM/HWMB) |          |         |    4 |        8 |
|  4 |      OPTIMIZER STATISTICS GATHERING  |          |    819M |    4 |       2G |
|  5 |       PX BLOCK ITERATOR              |          |    819M |    4 |       2G |
|  6 |        TABLE ACCESS FULL             | T1       |    819M |  442 |       2G |
====================================================================================

Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z) filter("HIST_DAT">ADD_MONTHS(SYSDATE@!,-24))

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

But this will be detailed in a separate blog article.

Bind aware secret sauce (again)

I am sure many of you have already become bored by my posts on adaptive cursor sharing. I hope this article will be the last one :-). In part III of the installment I was unable to figure out the secret sauce Oracle is using to mark a cursor bind aware when count of the 3 buckets of a bind sensitive cursor are greater than 0. For those who want to know what bucket and count represent they can start by reading part I and part II

Thanks to a comment dropped by an anonymous reader coupled with my understanding of the adaptive cursor sharing mechanism I think I have succeeded to figure out that secret sauce. My goal is to publish it and let readers break it and invalidate it.

To make things simple I created the following function

------------------------------------------------------------------------------
-- File name:   fv_will_cs_be_bind_aware
-- Author   :   Mohamed Houri (Mohamed.Houri@gmail.com)
-- Date     :   29/08/2015
-- Purpose  :   When supplied with 3 parameters 
--                   pin_cnt_bucket_0 : count of bucket_id n°0
--                   pin_cnt_bucket_1 : count of bucket_id n°1
--                   pin_cnt_bucket_2 : count of bucket_id n°2
-- 
--              this function will return a status:
--
--              'Y' if the next execution at any bucket_id will mark the cursor bind aware          
--               
--              'N' if the next execution any bucket_id will NOT mark the cursor bind aware                
--
--------------------------------------------------------------------------------
create or replace function fv_will_cs_be_bind_aware
  (pin_cnt_bucket_0 in number
  ,pin_cnt_bucket_1 in number
  ,pin_cnt_bucket_2 in number)
return 
   varchar2
is
  lv_will_be_bind_aware 
                 varchar2(1) := 'N';
  ln_least_0_2   number      := least(pin_cnt_bucket_0,pin_cnt_bucket_2);
  ln_great_0_2   number      := greatest(pin_cnt_bucket_0,pin_cnt_bucket_2);
 
begin
 if pin_cnt_bucket_0 + pin_cnt_bucket_2 >= pin_cnt_bucket_1
  and ln_least_0_2 >= ceil ((ln_great_0_2-pin_cnt_bucket_1)/3)
  then
    return 'Y';
  else
    return 'N';
  end if;
end fv_will_cs_be_bind_aware;

If you have a cursor with a combination of 3 buckets having a count > 0 and you want to know whether the next execution will mark the cursor bind aware or not then you have just to do this:

SQL> select fv_will_cs_be_bind_aware(10,1,3) acs from dual;

ACS
---
Y

Or this

SQL> select fv_will_cs_be_bind_aware(10,1,2) acs from dual;

ACS
---
N

In its first call the function is indicating that the next cursor execution will compile a new optimal plan while the second call indicates that the existing child cursor will still be shared.

It’s now time to practice:

SQL> alter session set cursor_sharing=FORCE;

SQL> select count(1) from t_acs where n2 = 100;

  COUNT(1)
----------
       100

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          0
           0          2          0

SQL> select count(1) from t_acs where n2 = 100;

  COUNT(1)
----------
       100
SQL> /

-- repeat this 7 times

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         10 --> 10 executions at bucket_id 0
           0          1          0
           0          2          0

Now change the bind variable value so that the bucket_id n°1 will be incremented

SQL> select count(1) from t_acs where n2 = 10000;

  COUNT(1)
----------
    100000

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         10
           0          1          1 --> 1 executions at bucket_id 1
           0          2          0

Now change again the bind variable value so that the bucket_id n°2 will be incremented

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5 from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         10
           0          1          1
           0          2          2 --> 2 executions at bucket_id 2

If, at this stage, you want to know whether the next execution at bucket id n°2 will mark the cursor bind aware or not then make a call to the function:

SQL> select fv_will_cs_be_bind_aware(10,1,2) acs from dual;

ACS
----
N

No, it will not and here’s below the proof:

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         10
           0          1          1
           0          2          3

And what about the next execution, say at bucket_id n° 0?

SQL> select fv_will_cs_be_bind_aware(10,1,3) acs from dual;

ACS
----
Y

The function is indicating that the next execution will compile a new child cursor; let’s check:

SQL> select count(1) from t_acs where n2 = 100;

  COUNT(1)
----------
       100

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          1
           1          1          0
           1          2          0
           0          0         10
           0          1          1
           0          2          3

Yes it did.

Can we assume from a single test that this function is reliable? No.

You want another example? Here it is:

SQL> -- run the following sql 19 times at bucket_id n°0
SQL> select count(1) from t_acs where n2 = 100;

SQL> -- run the same sql 6 times at bucket_id n°1
SQL> select count(1) from t_acs where n2 = 10000;

SQL> -- run the same sql 2 times at bucket_id n°2
SQL> select count(1) from t_acs where n2 = 1000000;

And here’s the resulting cursor sharing picture:

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5 from
  6     v$sql_cs_histogram
  7 where  sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         19
           0          1          6
           0          2          2

Will the next execution compile a new execution plan?

SQL> select fv_will_cs_be_bind_aware(19,6,2) acs from dual;

ACS
---
N

No, it will not as proofed here below:

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where  sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         19
           0          1          6
           0          2          3

And what about the next execution at the same bucket_id n°2? And the next next execution?

SQL> select fv_will_cs_be_bind_aware(19,6,3) acs from dual;

ACS
---
N

SQL> select fv_will_cs_be_bind_aware(19,6,4) acs from dual;

ACS
---
N

And the next execution?

SQL> select fv_will_cs_be_bind_aware(19,6,5) acs from dual;

ACS
----
Y

At the (bucket_id, count) situation shown below the function is indicating that the next execution will mark the cursor bind aware and compile a new execution plan:

SQL> select
  2     child_number
  3    ,bucket_id
  4    ,count
  5  from
  6    v$sql_cs_histogram
  7  where  sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         19
           0          1          6
           0          2          5

Isn’t it?

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select
  2      child_number
  3     ,bucket_id
  4     ,count
  5  from
  6     v$sql_cs_histogram
  7  where  sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0         19
           0          1          6
           0          2          5

Yes it did as you can point it out via the apparition of the new child cursor n°1

Want another example? Here’s

SQL> select
        child_number
       ,bucket_id
       ,count
    from
        v$sql_cs_histogram
    where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          3
           0          1          1
           0          2         11

SQL> select fv_will_cs_be_bind_aware(3,1,11) acs from dual;

ACS
---
N

SQL> select count(1) from t_acs where n2 = 10000;

  COUNT(1)
----------
    100000

SQL> select
        child_number
       ,bucket_id
       ,count
    from
        v$sql_cs_histogram
    where sql_id = '7ck8k47bnqpnv';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          3
           0          1          2
           0          2         11

SQL> select fv_will_cs_be_bind_aware(3,2,11) acs from dual;

ACS
---
Y

SQL> select count(1) from t_acs where n2 = 1000000;

  COUNT(1)
----------
   1099049

SQL> select
        child_number
       ,bucket_id
       ,count
    from
        v$sql_cs_histogram
    where sql_id = '7ck8k47bnqpnv';


CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0          3
           0          1          2
           0          2         11

I am sure that someone will come up with a simple situation where the function is returning a wrong result. Bear in mind that this is what I want and you’re welcome.

Footnote

If you want to break this function then here’s the model you can use (you need to have histogram on n2 column)

create table t_acs(n1  number, n2 number);

BEGIN
     for j in 1..1200150 loop
      if j = 1 then
       insert into t_acs values (j, 1);
      elsif j>1 and j<=101 then insert into t_acs values(j, 100); elsif j>101 and j<=1101 then insert into t_acs values (j, 1000); elsif j>10001 and j<= 110001 then
      insert into t_acs values(j,10000);
     else
      insert into t_acs values(j, 1000000);
     end if;
    end loop;
   commit;
END;
/ 
create index t_acs_i1 on t_acs(n2);

Update : 08/09/2015 : added a supplementary if condition to the function

CBO decision: unique or non-unique index?

I have been asked to look at one of those few particular frustrating situations that only running systems can procure. It is an update of a single table using a complete set of its primary key columns in order to locate and update a unique row. This update looks like:

UPDATE T1
SET 
  {list of columns}
WHERE 
    T1_DATE    = :B9
AND T1_I_E_ID  = :B8
AND T1_TYPE    = :B7
AND DATE_TYPE  = :B6
AND T1_AG_ID   = :B5
AND T1_ACC_ID  = :B4
AND T1_SEC_ID  = :B3
AND T1_B_ID    = :B2
AND T1_FG_ID   = :B1;

The 9 columns in the above where clause represent the primary key of the T1 table. You might be surprised to know that this update didn’t used the primary key index and preferred instead a range scan of an existing 3 columns index plus a table access by index rowid to locate and update a unique row:

----------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |        |       |       |     1 (100)|
|   1 |  UPDATE                      | T1     |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |   122 |     1   (0)|
|   3 |    INDEX RANGE SCAN          | IDX_T1 |     1 |       |     1   (0)|
----------------------------------------------------------------------------
   2 - filter("T1_TYPE"=:B7 AND "DATE_TYPE"=:B6 AND "T1_I_E_ID"=:B8 AND 
              "T1_AG_ID"=TO_NUMBER(:B5) AND "T1_DATE"=TO_TIMESTAMP(:B9) AND 
              "T1_FG_ID"=TO_NUMBER(:B1))
   3 - access("T1_SEC_ID"=TO_NUMBER(:B3) AND "T1_B_ID"=TO_NUMBER(:B2) AND 
              "T1_ACC_ID"=TO_NUMBER(:B4)) 

The same update, when hinted with the primary key index, uses the following much desirable execution plan:

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |        |     1 |   126 |     1   (0)|
|   1 |  UPDATE            | T1     |       |       |            |
|*  2 |   INDEX UNIQUE SCAN| PK_T19 |     1 |   126 |     1   (0)|
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1_I_E_ID"=:B8 AND "T1_TYPE"=:B7 AND "DATE_TYPE"=:B6 
              AND "T1_AG_ID"=TO_NUMBER(:B5) AND "T1_ACC_ID"=TO_NUMBER(:B4) AND 
              "T1_SEC_ID"=TO_NUMBER(:B3) AND "T1_B_ID"=TO_NUMBER(:B2) AND 
              "T1_FG_ID"=TO_NUMBER(:B1) AND "T1_DATE"=TO_TIMESTAMP(:B9))

I don’t know how Oracle manage to get the same cost (Cost = 1) for two completely different indexes one with 9 columns and one(which is a subset of the first index) with only 3 columns (not necessarily of the same starting order though)?

So why Oracle has not selected the primary key unique index?

First here are below the available statistics on the primary key columns:

SQL> select
      column_name
     ,num_distinct
     ,num_nulls
     ,histogram
    from
      all_tab_col_statistics
    where
     table_name = 'T1'
   and
    column_name in ('T1_DATE'
                   ,'T1_I_E_ID'
                   ,'T1_TYPE'
                   ,'DATE_TYPE'
                   ,'T1_AG_ID'
                   ,'T1_ACC_ID'
                   ,'T1_SEC_ID'
                   ,'T1_B_ID'
                   ,'T1_FG_ID' );

COLUMN_NAME         NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------- ------------ ---------- ---------------
T1_I_E_ID              2          0 		FREQUENCY
T1_TYPE                5          0 		FREQUENCY
DATE_TYPE              5          0 		FREQUENCY
T1_AG_ID               106        0 		FREQUENCY
T1_ACC_ID             182         0 		FREQUENCY
T1_DATE               2861        0 		HEIGHT BALANCED
T1_SEC_ID             3092480     0 		NONE
T1_B_ID               1452        0 		HEIGHT BALANCED
T1_FG_ID              1           0 		FREQUENCY

And here’s the corresponding “update” 10053 trace file restricted only to the important part related to my investigations

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 387027527  #Blks:  6778908  AvgRowLen:  126.00  ChainCnt:  0.00

Index Stats::
Index: IDX_T1  Col#: 7 8 5
 LVLS: 3  #LB: 1568007  #DK: 3314835  LB/K: 1.00   DB/K: 25.00  CLUF: 84758374.00

Index: PK_T19  Col#: 1 2 3 4 5 7 8 37 6
 LVLS: 4  #LB: 4137117  #DK: 377310281  LB/K: 1.00  DB/K: 1.00  CLUF: 375821219.00

And the part of same trace file where the index choice is done

Access Path: index (UniqueScan)
    Index: PK_T19
    resc_io: 5.00  resc_cpu: 37647 ----------------------> spot this
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 1.00  Resp: 1.00  Degree: 1
    ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
    ColGroup Usage:: PredCnt: 3  Matches Full:  Partial:

Access Path: index (AllEqRange)
    Index: IDX_T1
    resc_io: 5.00  resc_cpu: 36797 ----------------------> spot this
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 1.00  Resp: 1.00  Degree: 1

Best:: AccessPath: IndexRange
  Index: IDX_T1
    Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.00  Bytes: 0

Looking closely to the above trace file I didn’t find any difference in the index costing information (ix_sel_with_filters, resc_io, cost) which favours the non-unique IDX_T1 index over the PK_T19 primary key unique index except the resc_cpu value which equals 36797 for the former and 37647 for the latter index. I haven’t considered the clustering factor information because the index I wanted the CBO to use is a unique index. The two indexes have the same cost in this case. What extra information the CBO is using in this case to prefer the non-unique index over the unique one?

This issue remembered me an old otn thread in which the Original Poster says that, under the default CPU costing model, when two indexes have the same cost, Oracle will consider using the less CPU expensive index.

As far as I have a practical case of two different indexes with the same cost, I have decided to check this assumption by changing the costing model from CPU to I/O

SQL> alter session set "_optimizer_cost_model"=io;

SQL> explain plan for 
UPDATE T1
SET 
  {list of columns}
WHERE 
    T1_DATE   = :B9
AND T1_I_E_ID = :B8
AND T1_TYPE   = :B7
AND DATE_TYPE = :B6
AND T1_AG_ID  = :B5
AND T1_ACC_ID = :B4
AND T1_SEC_ID = :B3
AND T1_B_ID   = :B2
AND T1_FG_ID  = :B1;

SQL> select * from table(dbms_xplan.display);

Plan hash value: 704748203
-------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | UPDATE STATEMENT   |        |     1 |   126 |     1 |
|   1 |  UPDATE            | T1     |       |       |       |
|*  2 |   INDEX UNIQUE SCAN| PK_T19 |     1 |   126 |     1 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1_I_E_ID"=:B8 AND "T1_TYPE"=:B7 AND
              "DATE_TYPE"=:B6 AND "T1_AG_ID"=TO_NUMBER(:B5) AND
              "T1_ACC_ID"=TO_NUMBER(:B4) AND "T1_SEC_ID"=TO_NUMBER(:B3) AND
              "T1_B_ID"=TO_NUMBER(:B2) AND "T1_FG_ID"=TO_NUMBER(:B1) AND
              "T1_DATE"=TO_TIMESTAMP(:B9))

Note
-----
   - cpu costing is off (consider enabling it)

Spot on. We get the desired primary key index without any help.

However changing the default costing model is not acceptable in the client PRODUCTION database. Continuing my root causes investigations I was getting the feeling that histograms are messing up this CBO index choice. This is why I decided to give it a try and get rid of histograms and analyze the corresponding 10053 trace file:

SQL> exec dbms_stats.gather_table_stats (user, 'T1', method_opt => 'for all columns size 1');


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 387029216  #Blks:  6778908  AvgRowLen:  126.00  ChainCnt:  0.00

Index Stats::
  Index: IDX_T1  Col#: 7 8 5
  LVLS: 3  #LB: 1625338  #DK: 3270443  LB/K: 1.00   DB/K: 26.00  CLUF: 87831324.00
  
  Index: PK_T19  Col#: 1 2 3 4 5 7 8 37 6
  LVLS: 4  #LB: 4335908  #DK: 395902926  LB/K: 1.00  DB/K: 1.00  CLUF: 394578898.00

Access Path: index (UniqueScan)
    Index: PK_T19
    resc_io: 5.00  resc_cpu: 37647
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 1.00  Resp: 1.00  Degree: 1
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0000
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0000

  Access Path: index (AllEqRange)
    Index: IDX_T1
    resc_io: 31.00  resc_cpu: 370081
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 6.20  Resp: 6.20  Degree: 1 ------> spot the cost

Access Path: index (AllEqUnique)
    Index: PK_T19
    resc_io: 5.00  resc_cpu: 37647
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 1.00  Resp: 1.00  Degree: 1
 One row Card: 1.000000

  Best:: AccessPath: IndexUnique
  Index: PK_T19
    Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.00  Bytes: 0

And now that the cost of accessing the non-unique index became 6 times (Cost = 6,2) greater than that of the unique index (Cost =1) Oracle preferred the primary key index without any help:

============
Plan Table
============
-------------------------------------------------+----------------------+
| Id  | Operation           | Name  | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+----------------------+
| 0   | UPDATE STATEMENT    |       |       |       |     1 |           |
| 1   |  UPDATE             | T1    |       |       |       |           |
| 2   |   INDEX UNIQUE SCAN | PK_T19|     1 |   126 |     1 |  00:00:01 |
-------------------------------------------------+-----------------------+

The final acceptable decision to solve this issue was to hint an instance of the same query to use the primary key index and attach an SQL profile to the original packaged query using the plan_hash_value of the primary key index execution plan.

Bottom Line: under the default CPU costing model, when two (or more) indexes have the same cost Oracle will prefer using the index that it is going to consume the less amount of CPU (resc_cpu). And, before jumping on collecting histograms (particularly the Height Balanced ones) by default, be informed that they do participate in the perception Oracle has on the amount of CPU the different indexes will need and ultimately on the CBO index desirability.