4 Replies Latest reply: Mar 1, 2013 7:37 PM by LaserSoft RSS

    Query offload

    972132
      What is offloaded to the storage.

      select * from xxx where zzzz > 1000;

      zzz > 1000; offloaded

      What about

      select * from x,y where x.a=y.a;

      would where x.a=y.a be offloaded?
        • 1. Re: Query offload
          tychos
          Hi,
          The answer is yes....
          (sorry for the layout).
          R,
          Tycho

          table P -> 10 GB and 31394778 rows
          table C -> 6 GB and 4867719 rows

          Query result and plan for:
          SELECT count(*) from P
          WHERE p.pid2cid > 530000000
          /

          COUNT(*)
          ----------
          266941

          Elapsed: 00:00:00.31
          ----------------------------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
          ----------------------------------------------------------------------------------------------
          | 0 | SELECT STATEMENT | | 1 | 2 | 351K (1)| 01:10:18 |
          | 1 | SORT AGGREGATE | | 1 | 2 | | |
          |* 2 | TABLE ACCESS STORAGE FULL| P | 105K| 205K| 351K (1)| 01:10:18 |
          ----------------------------------------------------------------------------------------------
          Query result and plan for:
          SELECT count(*) from P,
          C
          WHERE p.pid2cid = c.id
          /
          COUNT(*)
          ----------
          4354658

          Elapsed: 00:00:06.90
          ----------------------------------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
          ----------------------------------------------------------------------------------------------------
          | 0 | SELECT STATEMENT | | 1 | 9 | | 573K (1)| 01:54:37 |
          | 1 | SORT AGGREGATE | | 1 | 9 | | | |
          |* 2 | HASH JOIN | | 4102K| 35M| 54M| 573K (1)| 01:54:37 |
          |* 3 | TABLE ACCESS STORAGE FULL| P | 4102K| 8013K| | 351K (1)| 01:10:18 |
          | 4 | TABLE ACCESS STORAGE FULL| C | 4855K| 32M| | 214K (1)| 00:42:54 |
          ----------------------------------------------------------------------------------------------------
          • 2. Re: Query offload
            12cdb
            I think anything which is pushed to the storage is run time decision.
            • 3. Re: Query offload
              Nikolay Savvinov
              Hi,

              if I understand your question correctly, you're asking about join offloading.

              Yes, it's possible. For simple joins, there is a mechanism called "bloom filters" which can be offloaded to cells.
              You can check if this mechanism is working for you by flipping "_bloom_predicate_pushdown_to_storage" switch
              and comparing performance.

              Best regards,
              Nikolay
              • 4. Re: Query offload
                LaserSoft
                Hi Friend,

                The bloom filter is passed to the exadata storage servers as an additional predicate. Processing the bloom filter inside the exadata storage server can reduce the amount of data transported to the database from storage server.

                Thanks
                LaserSoft