Oracle Tips and Tricks — David Fitzjarrell

September 25, 2014

Map Reading

Filed under: General — dfitzjarrell @ 11:55

Consider the following concept: When you are born you are issued a map showing the direction your life will take. Along the way people will come into your life and help you make sense of parts of that map. You may not know these people at the time but they will be important in establishing where you are to be headed and possibly what you should be doing. Eventually you can read the entire map and know where you should go; hopefully you’ll already be there.

Continuing with that premise there have been four extremely influential people who have come into my life to help me read my map and set the direction that landed me here, as an Oracle DBA. I would like to acknowledge these people here; they will be listed in ‘order of appearance’, to take a phrase from the television industry. Any one of them would have been very influential; it’s the fact that they all entered my life at some point and helped guide me along the way that I find important.

If it weren’t for Janet Hamilton, Ph.D., I probably never would have stayed with my plans for a college degree in Chemistry. Janet is a one of a kind person, extremely intelligent yet willing and able to share her knowledge. She taught both Freshman Inorganic Chemistry and Sophomore Organic Chemistry so those of us who were Chemistry majors, Pre-Med or Nursing students had her for two full years. She also taught the Organic Chemistry labs so many of us saw a lot of her each week. She was always willing to help during her office hours and I took advantage of that as often as I could. She was passionate about Chemistry and instilled that passion in us. What she taught me was this: knowledge is best when it’s shared. She was a shining example of this every day and fueled in me a desire to share what I know.

Next in line was Cora Schweitzer, a lovely woman who taught Advanced World Literature and did it with a passion and flair I have yet to experience again. Unlike many English teachers Cora brought the subject to life and did it on a daily basis. I could not wait to go to class and experience her knowledge and wisdom. I learned from her that passion is a key to workplace success and happiness, and that you should do what you love as a career. That is sometimes a difficult task to complete as thought, logic and passion can ‘collide’; doing what you love may not be the most financially advantageous direction to take but it can make a difference in how your life is spent. Sometimes it’s better to make a living doing something you are passionate about than it is to go for the financial success, especially if financial success leads to a great deal of stress. One day in class Cora admitted she would sit near her office door waiting for the sound of others leaving the building. She would then call out, “Going home?” in hopes of getting a ride after her day was done. I made regular pilgrimages to her office soon after that to offer to drive her home. We had lovely conversations while I drove, and I learned even more how interesting and fascinating she was. Sadly Cora died in 1982; she left this world a better place than she found it and I am truly grateful to have been a part of her wonderful life.

Eunice Bonar taught me Quantitative Analysis, and did it like no other person could. She was a stickler for details, but that’s how you had to be when performing quantitative procedures and tests. She showed me the beauty of the Q-test and the satisfaction of producing repeatable results through care and planning. No detail was too small to overlook; she was preparing us for work as Analytical Chemists, a Chemistry career based on precision and accuracy. And she taught us the difference between the two as you can have good precision and poor accuracy, good accuracy and poor precision, or good precision and good accuracy. Regardless of the tasks at hand Eunice made the work interesting and fun, at least to me. Just like my grandfather taught me years before Eunice further instilled in me the desire to do my best at whatever task lies ahead; if you can’t sign your name to it, it isn’t worth doing.

Last, but certainly not least, is Dennis Marynick, who taught Physical Chemistry. This is the toughest subject a Chemistry or Chemical Engineering major would take. Bumper stickers were sold by the local American Chemical Society chapter stating “Honk If You Passed P-Chem!”. The subject matter was tough but Dennis could make it interesting. He could translate all of his years of Doctoral research into concepts that promoted understanding. True, there were topics that he just couldn’t work his magic on but, for the most part, he made Physical Chemistry less intimidating, possibly because he, too, loved what he did for a living. He also never stopped learning, not because he had to but because he wanted to. That is an important concept that I integrated into my life.

What I learned from these four outstanding people is this: never stop learning, do what you are passionate about and share what you know, no matter how unimportant you think it might be. Knowledge is power, and sharing what you know can empower others. Because of these people I have become who I am today, a person constantly learning new things and sharing that knowledge as often as I can.

Each of us has at least one person who has given us direction and purpose; remember these people and what they did for you. Maybe some day someone will remember you for your positive contributions to their life.

Advertisements

September 23, 2014

“ID, Please”

Filed under: General — dfitzjarrell @ 10:13

In a forum I frequent the following question was raised:

We see some sqls that have been running for a long time in our prod DB, 11.2. when we check the v$session, i see null sql ids for them.
The sql_address shows up as 00. I would like to know what these sqls are doing because they correspond to processes that are burning up
the CPU and driving the usage to > 85%.

An interesting question, indeed. What are those NULL sql_ids doing, and are they actually NULL? To find out I set up an experiment using both a long series of insert statements and a PL/SQL block fetching 100,000 rows at a time. The results were surprising; let’s look at the example code and see what happened when it ran. Here is the code:


set echo on serveroutput on size 1000000

create table emp (empid number not null,
                  empname varchar2(40),
                  deptno        number);

set timing on

begin
        for i in 1..5000000 loop
                insert into emp
                values (i, 'Smorthorper'||i, mod(i, 40)+1);
        end loop;

        commit;
end;
/

set timing off

exec dbms_stats.gather_schema_stats('BING')

set timing on
declare
        type empcoltyp is table of emp%rowtype;
        emp_c empcoltyp;
        recct   number:=0;

        cursor get_emp_data is
        select * from emp;

begin
        open get_emp_data;
        loop
        fetch get_emp_data bulk collect into emp_c limit 100000;
        exit when get_emp_data%notfound;

        for i in 1..emp_c.count loop
                recct:=recct+1;
        end loop;
        dbms_output.put_line('Total records processed: '||recct);
        dbms_lock.sleep(3);
        end loop;
end;
/

set timing off

It’s a fairly simple script, really. I used the bulk collect and sleep to create a long-running fetch cycle to see if that displayed the observed behavior. Now, let’s run the script and see how the sql_id column in V$SESSION behaved. I used two sessions, one running the script and one to check the sql_id values from V$SESSION.  Session 1 generated the following output for the long-running insert:


SQL> create table emp (empid number not null,
  2                    empname varchar2(40),
  3                    deptno        number);

Table created.

SQL>
SQL> set timing on
SQL>
SQL> begin
  2          for i in 1..5000000 loop
  3                  insert into emp
  4                  values (i, 'Smorthorper'||i, mod(i, 40)+1);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:43.27
SQL>
SQL> set timing off

Session 2 reported the following behavior for the sql_id column:


SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469               gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469               gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469               gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        782        8469 gc37k1290dm1c gc37k1290dm1c

SQL>

Notice that occasionally the sql_id becomes NULL then returns to its previous value, as reported in prev_sql_id. When long-running SQL statements are being processed it’s possible that the sql_id column can go NULL, and the sql_address be reported as 00, even though the same statement that was started minutes or even hours ago is still running. In such cases the prev_sql_id holds the sql_id for the currently running SQL. This doesn’t hold true for PL/SQL blocks, even with a sleep added. Session 1 reports the PLSQL block and it’s apparent progress::


SQL> set timing on
SQL> declare
  2          type empcoltyp is table of emp%rowtype;
  3          emp_c empcoltyp;
  4          recct   number:=0;
  5
  6          cursor get_emp_data is
  7          select * from emp;
  8
  9  begin
 10          open get_emp_data;
 11          loop
 12          fetch get_emp_data bulk collect into emp_c limit 100000;
 13          exit when get_emp_data%notfound;
 14
 15          for i in 1..emp_c.count loop
 16                  recct:=recct+1;
 17          end loop;
 18          dbms_output.put_line('Total records processed: '||recct);
 19          dbms_lock.sleep(3);
 20          end loop;
 21  end;
 22  /
Total records processed: 100000
Total records processed: 200000
Total records processed: 300000
Total records processed: 400000
Total records processed: 500000
Total records processed: 600000
Total records processed: 700000
Total records processed: 800000
Total records processed: 900000
Total records processed: 1000000
Total records processed: 1100000
Total records processed: 1200000
Total records processed: 1300000
Total records processed: 1400000
Total records processed: 1500000
Total records processed: 1600000
Total records processed: 1700000
Total records processed: 1800000
Total records processed: 1900000
Total records processed: 2000000
Total records processed: 2100000
Total records processed: 2200000
Total records processed: 2300000
Total records processed: 2400000
Total records processed: 2500000
Total records processed: 2600000
Total records processed: 2700000
Total records processed: 2800000
Total records processed: 2900000
Total records processed: 3000000
Total records processed: 3100000
Total records processed: 3200000
Total records processed: 3300000
Total records processed: 3400000
Total records processed: 3500000
Total records processed: 3600000
Total records processed: 3700000
Total records processed: 3800000
Total records processed: 3900000
Total records processed: 4000000
Total records processed: 4100000
Total records processed: 4200000
Total records processed: 4300000
Total records processed: 4400000
Total records processed: 4500000
Total records processed: 4600000
Total records processed: 4700000
Total records processed: 4800000
Total records processed: 4900000
Total records processed: 5000000

PL/SQL procedure successfully completed.

Elapsed: 00:02:34.16
SQL>
SQL> set timing off

Session 2 again is used to display the sql_id throughout the PL/SQL run; notice the sql_id does not go NULL at any time during the run [the output has been abbreviated to reduce the large number of repeated lines]:


SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

...
SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

...
SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

...
SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

SQL> /

        SID     SERIAL# SQL_ID        PREV_SQL_ID
----------- ----------- ------------- -------------
        717        6367 gjcn0r825u7wz 9babjv8yq8ru3

SQL>

At no time did the sql_id display as NULL during the PL/SQL execution. I suspect it’s because the repeated fetch operations, limited to 1/50th of the total data, kept the cursor, and its associated SQL statement, active the entire duration of the run.

It would appear (and I think I have a valid test case as proof) that during long-running SQL statements the sql_id can be reported as NULL even though the statement is still executing. In such cases the prev_sql_id usually provides the identifier for the currently running SQL. Of course not all NULL sql_ids are associated with long-running statements, and V$SESSION_LONGOPS does not, by default, report every long-running statement or operation to hit a database. The ‘long-running’ criteria according to Oracle is any statement that runs for more than 6 seconds. A partial list of operations that are recorded follows:


•Table scan
•Index Fast Full Scan
•Hash join
•Sort/Merge
•Sort Output
•Rollback
•Gather Table Index Statistics

NESTED LOOP JOINS are not automatically recorded in V$SESSION_LONGOPS although HASH JOINS are. It is possible to circumvent this by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to register your long-running operation with the view. Modifying the previous code to make V$SESSION_LONGOPS report interim activity:


declare
        type empcoltyp is table of emp%rowtype;
        emp_c empcoltyp;
        recct   number:=0;
        r_idx    BINARY_INTEGER;
        l_no      BINARY_INTEGER;
        ttlwrk number;
        so_far     number;
        obj       BINARY_INTEGER;

        cursor get_emp_data is
        select * from emp;

begin
        r_idx := dbms_application_info.set_session_longops_nohint;
        so_far := 0;
        ttlwrk := 50;
        select object_id
        into obj
        from user_objects
        where object_name = 'EMP';

        open get_emp_data;
        loop
        fetch get_emp_data bulk collect into emp_c limit 100000;
        exit when get_emp_data%notfound;

        while so_far < 50 loop
             for i in 1..emp_c.count loop
                recct:=recct+1;
             end loop;
             dbms_output.put_line('Total records processed: '||recct);
             dbms_lock.sleep(3);
             so_far := so_far + 1;
             dbms_application_info.set_session_longops(r_idx, l_no, 'Bounded fetch ', obj, 0, so_far, ttlwrk, 'EMP', 'fetches');
             end loop;
        end loop;
end;
/

V$SESSION_LONGOPS now reports progress for the bulk collect fetch operations:


SQL> select message from v$session_longops where sofar  totalwork;

MESSAGE
-----------------------------------------------------------------
Bounded fetch : EMP 113315: 1 out of 50 fetches done

SQL> /

MESSAGE
-----------------------------------------------------------------
Bounded fetch : EMP 113315: 2 out of 50 fetches done

SQL> /

MESSAGE
-----------------------------------------------------------------
Bounded fetch : EMP 113315: 3 out of 50 fetches done

SQL>
...

Oracle can exhibit ‘strange’ behavior with the sql_id values in V$SESSION, but just because the sql_id is NULL that doesn’t mean Oracle isn’t processing a long-running statement. It’s likely processing the same SQL reported in prev_sql_id so you can look to that column to find (possibly) active SQL. As a side note you can also get V$SESSION_LONGOPS to report on any operation you consider to be long using the example code as a guide. Obviously you can’t get V$SESSION_LONGOPS to report on ‘canned’ application operations if they don’t meet the reporting criteria (that would require the vendor to modify their code, not an easy task to accomplish). The idea to remember is that the sql_id for some long-running operations isn’t lost when the sql_id value goes NULL, it’s just been moved to the prev_sql_id column.

I need to see your ID, please.

September 8, 2014

Blooms

Filed under: Exadata,General,Performance — dfitzjarrell @ 09:41

In Oracle releases 10.2.0.x and later join processing can be made more efficient by the use of Bloom filters, primarily to reduce traffic between parallel query slaves. What is a Bloom filter? Named after Burton Howard Bloom, who came up with the concept in the 1970s, it’s an efficient data structure used to quickly determine if an element has a high probability of being a member of a given set. It’s based on a bit array that allows for rapid searches and returns one of two results: either the element is probably in the set (which can produce false positives) or the element is definitely not in the set. The filter cannot produce false negatives, and the incidence of false positives is relatively rare. Another advantage to Bloom filters is their small size relative to other data structures used for similar purposes (self-balancing binary search trees, hash tables, or linked lists). The possibility of false positives necessitates the addition of another filter to eliminate them from the results, yet such a filter doesn’t add appreciably to the process time and, therefore, goes relatively unnoticed.

When Bloom filters are used for a query on an Exadata system, the filter predicate and the storage predicate will list the SYS_OP_Bloom_FILTER function as being called. This function includes the additional filter to eliminate any false positives that could be returned. It’s the storage predicate that provides the real power of the Bloom filter on Exadata. Using a Bloom filter to pre-join the tables at the storage server level reduces the volume of data the database servers need to process and can significantly reduce the execution time of the given query.

An example of Bloom filters in action follows; a script named Bloom_fltr_ex.sql was executed to generate this output on an Exadata system; the script can be emailed on request:


SQL> --
SQL> -- Create sample tables
SQL> --
SQL> -- Create them parallel, necessary 
SQL> -- to get a Smart Scan on these tables
SQL> --
SQL> create table emp(
  2  	empid   number,
  3  	empnmvarchar2(40),
  4  	empsal  number,
  5  	empssn  varchar2(12),
  6  	constraint emp_pk primary key (empid)
  7  ) parallel 4;

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  ) parallel 4;

Table created.

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

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> -- Run join query using Bloom filter
SQL> --
SQL> -- Generate execution plan to prove Bloom
SQL> -- filter usage
SQL> --
SQL> -- Also report query execution time
SQL> --
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select /*+ Bloom join 2 parallel 2 use_hash(empemp_dept) */ e.empid, e.empnm, d.deptnm, e.empsal, count(*) emps
  2  fromemp e join emp_depted on (ed.empid = e.empid) join dept_info d on (ed.empdept = d.deptnum)
  3  whereed.empdept = 20
  4  group by e.empid, e.empnm, d.deptnm, e.empsal;

     EMPID EMPNM                  DEPTNM                        EMPSAL       EMPS
---------- ---------------------- ------------------------- ---------- ----------
    904505 Fnarm904505            PROCUREMENT                     1000          1
    907769 Fnarm907769            PROCUREMENT                     3000          1
    909241 Fnarm909241            PROCUREMENT                     5000          1
    909505 Fnarm909505            PROCUREMENT                     3000          1
    909641 Fnarm909641            PROCUREMENT                     6000          1
    910145 Fnarm910145            PROCUREMENT                     6000          1
...
    155833 Fnarm155833            PROCUREMENT                     7000          1
    155905 Fnarm155905            PROCUREMENT                     2000          1
    151081 Fnarm151081            PROCUREMENT                     1000          1
    151145 Fnarm151145            PROCUREMENT                     2000          1

250000 rows selected.

Elapsed: 00:00:14.27

Execution Plan
----------------------------------------------------------
Plan hash value: 2313925751

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows | Bytes | Cost (%CPU)| Time     |   TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |  218K|    21M|  1378   (1)| 00:00:01 |       |      |            |
|   1 |  PX COORDINATOR                          |              |      |       |            |          |       |      |            |
|   2 |   PX SEND QC (RANDOM)                    | :TQ10003     |  218K|    21M|  1378   (1)| 00:00:01 | Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                         |              |  218K|    21M|  1378   (1)| 00:00:01 | Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                           |              |  218K|    21M|  1378   (1)| 00:00:01 | Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                        | :TQ10002     |  218K|    21M|  1378   (1)| 00:00:01 | Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY                      |              |  218K|    21M|  1378   (1)| 00:00:01 | Q1,02 | PCWP |            |
|*  7 |        HASH JOIN                         |              |  218K|    21M|  1376   (1)| 00:00:01 | Q1,02 | PCWP |            |
|   8 |         PX RECEIVE                       |              |  218K|    11M|   535   (1)| 00:00:01 | Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST               | :TQ10001     |  218K|    11M|   535   (1)| 00:00:01 | Q1,01 | P->P | BROADCAST  |
|  10 |           NESTED LOOPS                   |              |  218K|    11M|   535   (1)| 00:00:01 | Q1,01 | PCWP |            |
|  11 |            BUFFER SORT                   |              |      |       |            |          | Q1,01 | PCWC |            |
|  12 |             PX RECEIVE                   |              |      |       |            |          | Q1,01 | PCWP |            |
|  13 |              PX SEND BROADCAST           | :TQ10000     |      |       |            |          |       | S->P | BROADCAST  |
|  14 |               TABLE ACCESS BY INDEX ROWID| DEPT_INFO    |    1 |    27 |     1   (0)| 00:00:01 |       |      |            |
|* 15 |                INDEX UNIQUE SCAN         | DEPT_INFO_PK |    1 |       |     1   (0)| 00:00:01 |       |      |            |
|  16 |            PX BLOCK ITERATOR             |              |  218K|  5556K|   534   (1)| 00:00:01 | Q1,01 | PCWC |            |
|* 17 |             TABLE ACCESS STORAGE FULL    | EMP_DEPT     |  218K|  5556K|   534   (1)| 00:00:01 | Q1,01 | PCWP |            |
|  18 |         PX BLOCK ITERATOR                |              | 1657K|    75M|   839   (1)| 00:00:01 | Q1,02 | PCWC |            |
|* 19 |          TABLE ACCESS STORAGE FULL       | EMP          | 1657K|    75M|   839   (1)| 00:00:01 | Q1,02 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------

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

   7 - access("ED"."EMPID"="E"."EMPID")
  15 - access("D"."DEPTNUM"=20)
  17 - storage("ED"."EMPDEPT"=20)
       filter("ED"."EMPDEPT"=20)
  19 - storage(SYS_OP_Bloom_FILTER(:BF0000,"E"."EMPID"))
       filter(SYS_OP_Bloom_FILTER(:BF0000,"E"."EMPID"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
     60  recursive calls
    174  db block gets
  40753  consistent gets
  17710  physical reads
   2128  redo size
9437983  bytes sent via SQL*Net to client
 183850  bytes received via SQL*Net from client
  16668  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
 250000  rows processed

SQL>

In less than 15 seconds 250,000 rows were returned from a three table join of over 4 million rows; the Bloom filter made a dramatic difference in how this query was processed and provided exceptional performance given the volume of data queried.

Without the storage level execution of the Bloom filter, the query execution time increased by 2.33 seconds, a 16.3 percent increase. For longer execution times, this difference can be significant. It isn’t the Bloom filter that gives Exadata such power with joins, it’s the fact that Exadata can execute it not only at the database level but also at the storage level, something commodity hardware configurations can’t do.

Of course Bloom filters are not exclusive to Exadata, as the following run of the same script on a non-Exadata system shows (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>

The only differences between the two plans involve the storage component of Exadata. Notice also the vast difference in elapsed times between Exadata and non-Exadata systems; Exadata ran the query returning 250,000 rows in just over 14 seconds and the non-Exadata system returned the same volume of data in just under 55 seconds. Both times are substantial improvements over not using a Bloom filter so any hardware running 11.2.0.2 or later will benefit from Bloom filters.

Of course there is a ‘gotcha’ with Bloom filters, and Oracle recognizes this as a bug in releases older than 12.1.0.2. Hash left outer joins, hash right outer joins and hash full outer joins do not use a Bloom Filter and for those interested the unpublished bug number is 17716301. MOS document 1919508.1 reports details on this behavior including an example. As reported in that document the patch can be backported to earlier releases when an SR is filed requesting it.

Bloom filters are a welcome addition to both Exadata and non-Exadata installations as they can improve query performance with parallel queries, as evidenced here. Now that you know how to spot when they are used you may find they are utilized more often than you expected.

It’s a bloomin’ miracle.

Blog at WordPress.com.