Skip to Content
1
Former Member
Sep 20, 2013 at 09:24 AM

Question on Lava Query plan

87 Views

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