cancel
Showing results for 
Search instead for 
Did you mean: 

Question on Lava Query plan

Former Member
0 Kudos

In the below lava operator tree, there is a big difference in in the logical estimated IO and actual IO (l:874794 el:556). I'm wondering what kind of actions can contribute to this logical Io during an insert and how we can reduce this difference to improve the performance.

============================================================

        The type of query is INSERT.

        4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)

       |

       |   |INSERT Operator (VA = 3)

       |   |  The update mode is direct.

       |   |

       |   |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left Outer Join)

       |   |   |

       |   |   |   |SCAN Operator (VA = 0)

       |   |   |   |  FROM TABLE

       |   |   |   |  #step1

       |   |   |   |  leb

       |   |   |   |  Table Scan.

       |   |   |   |  Forward Scan.

       |   |   |   |  Positioning at start of table.

       |   |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

       |   |   |

       |   |   |   |SCAN Operator (VA = 1)

       |   |   |   |  FROM TABLE

       |   |   |   |  # test1234

       |   |   |   |  lsp

       |   |   |   |  Using Clustered Index.

       |   |   |   |  Index :  test1234_idx

       |   |   |   |  Forward Scan.

       |   |   |   |  Positioning by key.

       |   |   |   |  Keys are:

       |   |   |   |    cusip_id ASC

       |   |   |   |    wi_in ASC

       |   |   |   |  Using I/O Size 4 Kbytes for data pages.

       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  calc_data

       |   |  Using I/O Size 4 Kbytes for data pages.

Total estimated I/O cost for statement 1 (at line 1): 953911.

Parse and Compile Time 0.

Adaptive Server cpu time: 0 ms.

==================== Lava Operator Tree ====================

                                       Emit

                                    (VA = 4)

                                    r:128879 er:128879

                                    cpu: 5700

                         /

                        Insert

                        calc_data

                        (VA = 3)

                        r:128879 er:128879

                        l:874794 el:556

                        p:0 ep:556

             /

            NestLoopJoin

            Left Outer Join

            (VA = 2)

            r:128879 er:128879

/                      \

TableScan               IndexScan

#step1 (leb)            test1234 (lsp)

(VA = 0)                (VA = 1)

r:128879 er:128879      r:128879 er:128879

l:4957 el:4957          l:386637 el:386637

p:0 ep:622              p:0 ep:2492

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

Usually a difference in estimates vs actual has to do with the statistics on the tables involved.  If there are missing or old statistics, then the optimizer is going to do the best that it can with the information it has.

You can use set option show_missing_stats on to see which statistics are missing.

See the following for more details:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1570/html/queryproc...

Lisa

Former Member
0 Kudos

Satesh

When you have questions like this, you need to post the full DDL (create table plus all create indices) for the affected tables.

  • calc_data has some REFERENCES to leb and lsp
  • access to lsp is fine, it is using the CI
  • On leb, there is no *useful* index that the Optimiser can use.  Ie. the columns in leb that are REFERENCED by the columns in calc_data, are not indexed.

The correction is to add an index for those columns ... but I would not advise that until I have seen the full DDL for the three tables.

Cheers

Derek

Former Member
0 Kudos

Hi

In my experience, a logical I/O per row inserted into a table with some small relatively overhead (OAM pages and maybe something else) is normal and expected, no matter how you insert a row. The rest of your 874794 logical I/Os are possibly caused by indexes created on calc_data table.

As to the difference between the estimated and actual logical I/O during the insert into calc_cata - I see the same behavior and I don't know its reason. I think that you can just ignore the estimation in this particular case. No matter how the optimizer choose to execute your query, it has only one way to insert the data into calc_data table.

Hope it helps