This discussion is archived
4 Replies Latest reply: Mar 1, 2013 5:37 PM by LaserSoft RSS

Query offload

972132 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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
    user296828 Expert
    Currently Being Moderated
    I think anything which is pushed to the storage is run time decision.
  • 3. Re: Query offload
    Nikolay Savvinov Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points