This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Aug 31, 2012 8:30 AM by Ravetd RSS

Smart scan not working with Insert Select statements

user6394091 Newbie
Currently Being Moderated
We have observed that smart scan is not working with insert select statements but works when select statements are execute alone.
Can you please help us to explain this behavior?

The question was originally posted in General Questions Re: Smart scan not working with Insert Select statements

Edited by: user6394091 on Aug 29, 2012 2:26 AM
  • 1. Re: Smart scan not working with Insert Select statements
    robinsc Explorer
    Currently Being Moderated
    are these correlated subqueries ? are you inserting into the same table you are selecting from ?
  • 2. Re: Smart scan not working with Insert Select statements
    user6394091 Newbie
    Currently Being Moderated
    No, they are different tables
  • 3. Re: Smart scan not working with Insert Select statements
    robinsc Explorer
    Currently Being Moderated
    Can you give a sanitised example of the query ?
  • 4. Re: Smart scan not working with Insert Select statements
    UweHesse Expert
    Currently Being Moderated
    just falsified my previous answer myself. Never mind :-)

    Edited by: Uwe Hesse on 29.08.2012 18:08
  • 5. Re: Smart scan not working with Insert Select statements
    user6394091 Newbie
    Currently Being Moderated
    Table1 - Transaction table
    Table2 - Staging Table

    Insert Into Table1
                   (Column1,
                   Coulmn2,
                   Coulmn3,
                   Coulmn4)
         Select      Column1,
                   Coulmn2,
                   Coulmn3,
                   Coulmn4
         From Table2
         Where date = '30-Aug-2012'
  • 6. Re: Smart scan not working with Insert Select statements
    601262 Explorer
    Currently Being Moderated
    Please post the plans for the select and the insert (full output from dbms_xplan.display)

    --
    Regards,
    Greg Rahn | blog | twitter | linkedin
  • 7. Re: Smart scan not working with Insert Select statements
    gsalem Explorer
    Currently Being Moderated
    Hi,
    Is there any DOP declared on the transaction table? As stated we have no idea if the SQL will go serial or not, and if it uses direct reads or not. Like Greg said, better send the plan.
    You can try positioning "_serial_direct_read"=always and give it a try.
  • 8. Re: Smart scan not working with Insert Select statements
    user6394091 Newbie
    Currently Being Moderated
    I would be very difficult to read but that is the best I could do.

    Plan of Select Statement

    Global Stats
    ================================================================
    | Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Cell |
    | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
    ================================================================
    | 0.26 | 0.07 | 0.18 | 0.00 | 0.00 | 0.02 | 1 | 43948 | 354 | 342MB | 99.99% |
    ================================================================


    ================================================================
    Operation | Rows | Cost | Rows | Cell | Mem | Activity Detail |
    | (Estim) | | (Actual) | Offload | (Max) | (# samples) |
    ================================================================
    SELECT STATEMENT | | | | | | |
    SEQUENCE | | | | | | |
    HASH JOIN RIGHT OUTER | 442 | 12135 | 0 | | 2M | |
    TABLE ACCESS STORAGE FULL | 1 | 17 | 4300 | | | |
    HASH JOIN | 442 | 12117 | | | 223K | |
    HASH JOIN | 442 | 12012 | 0 | | 549K | |
    VIEW | 3 | 2 | 3 | | | |
    HASH JOIN | | | 3 | | 877K | |
    INLIST ITERATOR | | | 3 | | | |
    INDEX UNIQUE SCAN | 3 | | 3 | | | |
    INDEX STORAGE FAST FULL SCAN | 3 | 1 | 8 | | | |
    PARTITION RANGE SINGLE | 736 | 12010 | | | | |
    TABLE ACCESS STORAGE FULL | 736 | 12010 | | 99.99% | | |
    TABLE ACCESS STORAGE FULL | 7580 | 105 | | | | |


    Plan of Insert Statement

    Global Stats
    ===================================================================
    | Elapsed | Cpu | IO | Cluster | Buffer | Read | Read |
    | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
    ===================================================================
    | 3.20 | 1.77 | 1.38 | 0.05 | 381K | 1703 | 13MB |
    ===================================================================

    ========================================================================================================================
    | Id | Operation | Rows | Cost | Execs | Rows | Mem | Activity | Activity Detail |
    | | |(Estim) | | | (Actual) | (Max) | (%) | (# samples) |
    ========================================================================================================================
    | 0 | INSERT STATEMENT | | | 1 | | | | |
    | 1 | LOAD TABLE CONVENTIONAL | | | 1 | 0 | | 66.67 | Cpu (2) |
    | 2 | SEQUENCE | | | 1 | 15949 | | 33.33 | Cpu (1) |
    | 3 | HASH JOIN RIGHT OUTER | 52 | 3156 | 1 | 15949 | 2M | | |
    | 4 | TABLE ACCESS STORAGE FULL | 1 | 17 | 1 | 4300 | | | |
    | 5 | NESTED LOOPS | | | 1 | 15949 | | | |
    | 6 | NESTED LOOPS | 52 | 3139 | 1 | 15949 | | | |
    | 7 | HASH JOIN | 52 | 3087 | 1 | 15949 | 569K | | |
    | 8 | VIEW | 3 | 2 | 1 | 3 | | | |
    | 9 | HASH JOIN | | | 1 | 3 | 550K | | |
    | 10 | INLIST ITERATOR | | | 1 | 3 | | | |
    | 11 | INDEX UNIQUE SCAN | 3 | | 3 | 3 | | | |
    | 12 | INDEX STORAGE FAST FULL SCAN | 3 | 1 | 1 | 8 | | | |
    | 13 | PARTITION RANGE SINGLE | 87 | 3085 | 1 | 15949 | | | |
    | 14 | TABLE ACCESS STORAGE FULL | 87 | 3085 | 1 | 15949 | | | |
    | 15 | INDEX UNIQUE SCAN | 1 | | 15949 | 15949 | | | |
    | 16 | TABLE ACCESS BY INDEX ROWID | 1 | 1 | 15949 | 15949 | | | |
  • 9. Re: Smart scan not working with Insert Select statements
    gsalem Explorer
    Currently Being Moderated
    Well,
    To begin with, this is not just an insert into a select from b. The select part is a join between several tables.
    The plan for the INSERT version has got 2 FTS, and these can use smart scan. The SELECT only version has a different plan with more FTSs
    How are you measuring the use of smart scan?
  • 10. Re: Smart scan not working with Insert Select statements
    tychos Expert
    Currently Being Moderated
    Hi,
    The two plans look quit similar the insert statement uses a index unique scan plus nested loop and the select uses the table access storage full and hash join. The statistics on some resembling operations look very different:
    PARTITION RANGE SINGLE | 736 | 12010 | | | | |
    vs
    PARTITION RANGE SINGLE 87 3085 1 15949
    Did you use the same objects in both queries?
    Did you try to make the index invisible which is used in the "INDEX UNIQUE SCAN" operation?
    Regards,
    Tycho
  • 11. Re: Smart scan not working with Insert Select statements
    user6394091 Newbie
    Currently Being Moderated
    I have used the same select statements for both and made the index invisible to ensure the use of smart scan. I concluded the use of smart scan from the cell offloading part. You'll can try it at your end and if it works then probably I am doing something wrong.
  • 12. Re: Smart scan not working with Insert Select statements
    tychos Expert
    Currently Being Moderated
    Hi,
    I agree with gsalem your "Table2 - Staging Table" needs a join of 2 tables according to the plan you posted.
    Can you post the output of:
    select owner, object_name, object_type from dba_objects where object_name = '<Table2>';
    Regards,
    Tycho

    Edited by: tychos on 31-aug-2012 13:17
  • 13. Re: Smart scan not working with Insert Select statements
    user6394091 Newbie
    Currently Being Moderated
    There are multiple tables used in the select statement. I had just used Table2 - Staging table to explain the scenario.
  • 14. Re: Smart scan not working with Insert Select statements
    tychos Expert
    Currently Being Moderated
    Hi,
    Here my test results which show the plans are the same.
    select *
    from table1 where a < 11
    /
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------
    Plan hash value: 963482612

    ------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 33M| 14G| 609K (1)| 02:01:59 |
    |* 1 | TABLE ACCESS STORAGE FULL| TABLE1 | 33M| 14G| 609K (1)| 02:01:59 |
    ------------------------------------------------------------------------------------

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

    1 - storage("A"<11)
    filter("A"<11)
    insert into table2
    select *
    from table1 where a < 11
    /
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------
    Plan hash value: 963482612

    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | INSERT STATEMENT | | 33M| 14G| 609K (1)| 02:01:59 |
    | 1 | LOAD TABLE CONVENTIONAL | TABLE2 | | | | |
    |* 2 | TABLE ACCESS STORAGE FULL| TABLE1 | 33M| 14G| 609K (1)| 02:01:59 |
    -------------------------------------------------------------------------------------

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

    2 - storage("A"<11)
    filter("A"<11)
    Rgds,
    Tycho
1 2 Previous Next

Legend

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