标签归档:12c

Bloom Filter For Non-Parallel query on Non-Partition Table

I notice an enhancement in 12.1.0.2.0 beta, bloom filter is enabled for non-parallel query on non-partition table. The enhancement may help improve the hash join performance, this page show a little demo. The testing is on Exadata X4-2 instance.

> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT          VERSION        STATUS
---------------------------------------- -------------------- --------------------
NLSRTL           12.1.0.2.0       Beta
Oracle Database 12c Enterprise Edition   12.1.0.2.0       64bit Beta
PL/SQL           12.1.0.2.0       Beta
TNS for Linux:         12.1.0.2.0       Beta

> select platform_id, platform_name from v$database;

PLATFORM_ID PLATFORM_NAME
----------- ----------------
   13 Linux x86 64-bit

Prepare two table for hash join, 10k rows in t1 and 10m rows in t2. 100 records for each value on the column t1.n1 and t2.n1.

drop table t1;
drop table t2;

-- t1 with 10,000 rows
create table t1
as
select
    rownum                      id,
    lpad(rownum, 10, '0')       v1,
    trunc((rownum - 1)/100)     n1,
    rpad(rownum, 100)           padding
from
    dual
connect by level <= 10000
;

-- t2 with 10,000,000 rows
create table t2
as
select
    rownum                      id,
    lpad(rownum, 10, '0')       v1,
    trunc((rownum - 1)/100)     n1,
    rpad(rownum, 100)           padding
from
    t1, t1
where
    rownum <= 1e7;

begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );

    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T2',
        method_opt   => 'for all columns size 1'
    );
end;
/

The hash join between t1 and t2 return only 100 rows, if the bloom filter is created after t1 is scanned, and applied on t2 when t2 is scanned, most of the rows of t2 is filtered at the first place. The sql monitor report show that how bloom filter takes effect. With bloom filter enabled, the rows returned from smart scan on t2 is 15k, cell offload efficiency is 100%(only 2MB bytes returned from Exadata). Without bloom filter, the rows returned from smart scan on t2 is 10M, cell offload efficiency is 84%(230MB bytes returned from Exadata).

alter system flush buffer_cache;
select
    /*+ monitor */
    count(t2.v1)
from
    t1, t2
where
    t1.id = t2.id
and t1.n1 = 0;

alter system flush buffer_cache;
select
    /*+ monitor no_px_join_filter(t2) */
    count(t2.v1)
from
    t1, t2
where
    t1.id = t2.id
and t1.n1 = 0;

Update on Mar 25th

Serial generation for bloom filters on Exadata is enabled in 12.1.0.2, and backported to 11.2.0.4 with bug 16710321. Hidden parameter _bloom_serial_filter control the function.

> @pd _bloom_serial_filter

NAME                  VALUE     DESCRIPTION
--------------------- --------- -------------------------------------------------------
_bloom_serial_filter  ON        enable serial bloom filter on exadata

Oracle 12c new feature: Temporary undo

This is quick blog to demostrate the tiny new feature in 12C: temporary undo. Temporary undo is introdued in 12 and well documented.

By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables. However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.

Temporary undo is controlled by The Parameter TEMP_UNDO_ENABLED. With TEMP_UNDO_ENABLED enable, significantly less Redo is only generated for the very first DML(insert/update/delete) on the global temporary table, there is no redo for the following DML on the temporary tables within the same transaction totally. This may be a great benifit for the ETL processes, which take temporary table as the staging table, load the huge data into temporary table for heavy process before move the data into the final fact table. With less redo, both the sql performance on global temporary table and system loading may be improved a lot.

Perform the same insert/update/delete on the same global temporary table(see the script below), from below session statistics comparision, we can see: 1) With TEMP_UNDO_ENABLED disabled, the total redo size for the insert/update/delete is 4MB, while enabled, only 280 bytes redo size for the first insert statment, and the following update and delete produce zero redo; 2) With TEMP_UNDO_ENABLED disable, The temporary space requirement is 2MB, while enabled, it’s 7MB.

Here is the script for the demo:

1. Prepare the table.

drop table t1 purge;
create global temporary table t1
(
  id number,
  padding varchar2(100)
)
on commit preserve rows;

2. Run insert/update/delete statement with TEMP_UNDO_ENABLED disabled and enabled.

alter session set temp_undo_enabled = FALSE;
--alter session set temp_undo_enabled = TRUE;
show parameter temp_undo_enabled;

exec snap_stats.start_snap;
insert into t1 select rownum, lpad(rownum, 100, ' ') from dual connect by level exec snap_stats.end_snap;
select count(*) from t1;

exec snap_stats.start_snap;
update t1 set padding = padding;
exec snap_stats.end_snap;

exec snap_stats.start_snap;
delete from t1;
exec snap_stats.end_snap;
commit;

P.S. The TEMP_UNDO_ENABLED can’t be changed dynamically within a session: “When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session.”