Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted October 27, 2014

Oracle In-Memory Database: The Bloom Filter Example Re-visited

By David Fitzjarrell

Oracle 12c (version 12.1.0.2) offers the option of using in-memory processing to speed things along. Called the In-memory option, it's installed when you install the 12.1.0.2 software. Using it can make a considerable difference in processing speed, provided you have sufficient resources (RAM) available. Let's revisit an older example, on Bloom filters, and see if Oracle processes things any faster in-memory.

Looking again at the Bloom filter example using 11.2.0.3 on a Windows installation it's evident that the Bloom filter provides an increased level of performance (only the abbreviated output section is reproduced here):


SQL> select /*+ Bloom join 2 parallel 2 use_hash(emp emp_dept) */ e.empid, e.empnm, d.deptnm, e.empsal	  --, count(*) emps
  2  from emp e join emp_dept ed on (ed.empid = e.empid) join dept_info d on (ed.empdept = d.deptnum)
  3  where ed.empdept = 20;

     EMPID EMPNM                                    DEPTNM                        EMPSAL                                                              
---------- ---------------------------------------- ------------------------- ----------                                                              
   1670281 Fnarm1670281                             PROCUREMENT                     5000                                                              
   1670289 Fnarm1670289                             PROCUREMENT                     6000                                                              
   1670297 Fnarm1670297                             PROCUREMENT                     7000                                                              
   1670305 Fnarm1670305                             PROCUREMENT                     1000                                                              
   1670313 Fnarm1670313                             PROCUREMENT                     2000                                                              
   1670321 Fnarm1670321                             PROCUREMENT                     3000                                                              
   1670329 Fnarm1670329                             PROCUREMENT                     4000                                                              
   1670337 Fnarm1670337                             PROCUREMENT                     5000                                                              
   1670345 Fnarm1670345                             PROCUREMENT                     6000                                                              
   1670353 Fnarm1670353                             PROCUREMENT                     7000                                                              
   1670361 Fnarm1670361                             PROCUREMENT                     1000                                                              
...
   1857369 Fnarm1857369                             PROCUREMENT                     4000                                                              
   1857377 Fnarm1857377                             PROCUREMENT                     5000                                                              
   1857385 Fnarm1857385                             PROCUREMENT                     6000                                                              

250000 rows selected.

Elapsed: 00:00:54.86

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2643012915                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------                    
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                    
----------------------------------------------------------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT                     |              |   273K|    26M|  1378   (1)| 00:00:01 |        |      |            |                    
|   1 |  PX COORDINATOR                      |              |       |       |            |          |        |      |            |                    
|   2 |   PX SEND QC (RANDOM)                | :TQ10002     |   273K|    26M|  1378   (1)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |                    
|*  3 |    HASH JOIN                         |              |   273K|    26M|  1378   (1)| 00:00:01 |  Q1,02 | PCWP |            |                    
|   4 |     PX RECEIVE                       |              |   273K|    13M|   536   (1)| 00:00:01 |  Q1,02 | PCWP |            |                    
|   5 |      PX SEND BROADCAST               | :TQ10001     |   273K|    13M|   536   (1)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |                    
|   6 |       NESTED LOOPS                   |              |   273K|    13M|   536   (1)| 00:00:01 |  Q1,01 | PCWP |            |                    
|   7 |        BUFFER SORT                   |              |       |       |            |          |  Q1,01 | PCWC |            |                    
|   8 |         PX RECEIVE                   |              |       |       |            |          |  Q1,01 | PCWP |            |                    
|   9 |          PX SEND BROADCAST           | :TQ10000     |       |       |            |          |        | S->P | BROADCAST  |                    
|  10 |           TABLE ACCESS BY INDEX ROWID| DEPT_INFO    |     1 |    27 |     1   (0)| 00:00:01 |        |      |            |                    
|* 11 |            INDEX UNIQUE SCAN         | DEPT_INFO_PK |     1 |       |     1   (0)| 00:00:01 |        |      |            |                    
|  12 |        PX BLOCK ITERATOR             |              |   273K|  6947K|   535   (1)| 00:00:01 |  Q1,01 | PCWC |            |                    
|* 13 |         TABLE ACCESS FULL            | EMP_DEPT     |   273K|  6947K|   535   (1)| 00:00:01 |  Q1,01 | PCWP |            |                    
|  14 |     PX BLOCK ITERATOR                |              |  2099K|    96M|   840   (1)| 00:00:01 |  Q1,02 | PCWC |            |                    
|* 15 |      TABLE ACCESS FULL               | EMP          |  2099K|    96M|   840   (1)| 00:00:01 |  Q1,02 | PCWP |            |                    
----------------------------------------------------------------------------------------------------------------------------------                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   3 - access("ED"."EMPID"="E"."EMPID")                                                                                                               
  11 - access("D"."DEPTNUM"=20)                                                                                                                       
  13 - filter("ED"."EMPDEPT"=20)                                                                                                                      
  15 - filter(SYS_OP_Bloom_FILTER(:BF0000,"E"."EMPID"))                                                                                               
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic sampling used for this statement (level=2)                                                                                               


Statistics
----------------------------------------------------------                                                                                            
         33  recursive calls                                                                                                                          
        139  db block gets                                                                                                                            
      36224  consistent gets                                                                                                                          
      17657  physical reads                                                                                                                           
          0  redo size                                                                                                                                
    9526012  bytes sent via SQL*Net to client                                                                                                         
     183846  bytes received via SQL*Net from client                                                                                                   
      16668  SQL*Net roundtrips to/from client                                                                                                        
          5  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
     250000  rows processed                                                                                                                           

SQL>

Now let's get Oracle's In-memory option working. By default it's disabled; the initialization parameter inmemory_size is set to 0. To get In-memory configured and working three steps need to be performed:


1. Set inmemory_size to a non-zero value; in the database used for this example it's been set to 576M.  
   As this is a parameter that cannot be modified directly it will need to be changed using scope=spfile.
2. If desired increase the sga_max_size by the value that inmemory_size has been set to.  This is not a 
   requirement but remember that any memory allocated to the in-memory database will be taken from the SGA allocation.
3. Restart the database to effect the changes.

One caveat on this is calculating how much memory will be needed to provide a workable In-memory configuration; it's similar to configuring the KEEP pool (a task from earlier releases) as you should allocate sufficient memory to contain all of the tables that will be used to prevent delays in loading the data to the IM column store. Now you have the in-memory option configured and ready to use. Alter a table to use inmemory and you will start seeing the possible benefits of this configuration. (By default the following in-memory options are set:


  PRIORITY NONE -- Oracle controls when table data is populated in the IM column store.  Population may be 
   delayed if memory is needed for other processes.  Other values are LOW, MEDIUM, HIGH and CRITICAL.  
   As expected a higher PRIORITY loads before any lower priority.
  MEMCOMPRESS FOR QUERY LOW -- This option provides the best query performance and the least compression.
  DISTRIBUTE AUTO -- Used for RAC this controls how the data is distributed between nodes.  BY ROWID RANGE, 
   BY PARTITION and BY SUBPARTITION are other options.
  NO DUPLICATE -- Another RAC parameter, this controls how many nodes will have duplicated data.  DUPLICATE 
   involves one node for a total of two (so in a two-node RAC configuration DUPLICATE and DUPLICATE ALL 
   provide the same duplication),  DUPLICATE ALL duplicates the table data across all available RAC nodes.

Re-writing the Bloom filter example to take advantage of this new feature allows us to see how it will perform:


SQL>
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
  2  	     empid   number,
  3  	     empnm   varchar2(40),
  4  	     empsal  number,
  5  	     empssn  varchar2(12),
  6  	     constraint emp_pk primary key (empid)
  7  ) inmemory;

Table created.

SQL>
SQL> create table emp_dept(
  2  	     empid   number,
  3  	     empdept number,
  4  	     emploc  varchar2(60),
  5  	     constraint emp_dept_pk primary key(empid)
  6  ) inmemory;

Table created.

SQL>
SQL> create table dept_info(
  2  	     deptnum number,
  3  	     deptnm  varchar2(25),
  4  	     constraint dept_info_pk primary key(deptnum)
  5  ) inmemory;

Table created.

SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
  2  	     for i in 1..2000000 loop
  3  		     insert into emp
  4  		     values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
  5  		     insert into emp_dept
  6  		     values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
  7  		     commit;
  8  	     end loop;
  9  	     insert into dept_info
 10  	     select distinct empdept, case when empdept = 10 then 'SALES'
 11  					   when empdept = 20 then 'PROCUREMENT'
 12  					   when empdept = 30 then 'HR'
 13  					   when empdept = 40 then 'RESEARCH'
 14  					   when empdept = 50 then 'DEVELOPMENT'
 15  					   when empdept = 60 then 'EMPLOYEE RELATIONS'
 16  					   when empdept = 70 then 'FACILITIES'
 17  					   when empdept = 80 then 'FINANCE' end
 18  	     from emp_dept;
 19
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Gather statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats('BING')

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Run join query using bloom filter
SQL> --

SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select /*+ bloom join 2 parallel 2 use_hash(emp emp_dept) */ e.empid, e.empnm, d.deptnm, e.empsal	  --, count(*) emps
  2  from emp e join emp_dept ed on (ed.empid = e.empid) join dept_info d on (ed.empdept = d.deptnum)
  3  where ed.empdept = 20;

     EMPID EMPNM                                    DEPTNM                        EMPSAL
---------- ---------------------------------------- ------------------------- ----------
   1548633 Fnarm1548633                             PROCUREMENT                     3000
   1548641 Fnarm1548641                             PROCUREMENT                     4000
   1548649 Fnarm1548649                             PROCUREMENT                     5000
 ...
    897969 Fnarm897969                              PROCUREMENT                     3000
    897977 Fnarm897977                              PROCUREMENT                     4000
    897985 Fnarm897985                              PROCUREMENT                     5000
    897993 Fnarm897993                              PROCUREMENT                     6000
    898001 Fnarm898001                              PROCUREMENT                     7000

250000 rows selected.

Elapsed: 00:00:25.67

Execution Plan
----------------------------------------------------------
Plan hash value: 2613779428

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |   250K|    10M|       |  3863   (2)| 00:00:01 |
|*  1 |  HASH JOIN                    |           |   250K|    10M|  8304K|  3863   (2)| 00:00:01 |
|   2 |   JOIN FILTER CREATE          | :BF0000   |   250K|    10M|       |  3863   (2)| 00:00:01 |
|*  3 |    HASH JOIN                  |           |   250K|  5371K|       |    94  (23)| 00:00:01 |
|*  4 |     TABLE ACCESS INMEMORY FULL| DEPT_INFO |     1 |    13 |       |     1   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS INMEMORY FULL| EMP_DEPT  |   250K|  2197K|       |    92  (22)| 00:00:01 |
|   6 |   JOIN FILTER USE             | :BF0000   |  2000K|    41M|       |   137  (17)| 00:00:01 |
|*  7 |    TABLE ACCESS INMEMORY FULL | EMP       |  2000K|    41M|       |   137  (17)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - access("ED"."EMPID"="E"."EMPID")
   3 - access("ED"."EMPDEPT"="D"."DEPTNUM")
   4 - inmemory("D"."DEPTNUM"=20)
       filter("D"."DEPTNUM"=20)
   5 - inmemory("ED"."EMPDEPT"=20)
       filter("ED"."EMPDEPT"=20)
   7 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"E"."EMPID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"E"."EMPID"))

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
    9659722  bytes sent via SQL*Net to client
     183877  bytes received via SQL*Net from client
      16668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     250000  rows processed

SQL>

Notice that the Bloom filter is still used, and in conjunction with the In-memory option the execution time was considerably shorter than the original, 11.2.0.3 Windows server example. Notice also that since the table was in-memory the memory sorts are 0 (since all processing was done in-memory). If you have the resources available using the In-memory option for Oracle 12.1.0.2 might improve performance significantly.

Bloom filters plus the In-memory database option with Oracle 12.1.0.2 can improve query performance dramatically, and that can make for very happy end users. It's a bit of a chore to get In-memory configured (the hardest part being the available memory calculations) but it appears to be worth every bit of time spent to get it working.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM