1 2 Previous Next 15 Replies Latest reply: Aug 31, 2012 10:30 AM by Ravetd RSS

    Smart scan not working with Insert Select statements

    user6394091
      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
          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
            No, they are different tables
            • 3. Re: Smart scan not working with Insert Select statements
              robinsc
              Can you give a sanitised example of the query ?
              • 4. Re: Smart scan not working with Insert Select statements
                Uwehesse-Oracle
                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
                  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
                    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-Oracle
                      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
                        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-Oracle
                          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
                            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
                              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
                                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
                                  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
                                    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