title: Reducing buffer size used by an SQL (Oracle performance tuning)
body: <p>I am trying to reduce the buffer size used by an SQL.
  Below is the explain plan.</p>
  
  <p>Looking at the explain plan I tend to focus on line 3 below</p>
  
  <pre><code>3 NESTED LOOPS  Cost: 1,407  Bytes: 11,902,228  Cardinality: 276,796    
  </code></pre>
  
  <p>Because the cost is jumping from 55 (at line 2)  to 1407</p>
  
  <pre><code>2 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_PROD_U1 Cost: 55  Bytes: 247,359  Cardinality: 11,779  
  </code></pre>
  
  <p>I have few questions around this:</p>
  
  <ol>
  <li><em>Is my analysis above to focus around those line correct?</em></li>
  <li>At line 2, it told me that the index used is S_PROD_LN_PROD_U1 , but actually we don't have S_PROD_LN_PROD_U1 at S_PROD_LN table. We have S_PROD_LN_U1 index though. Any reason why the explain plan used kind of "virtual" index?</li>
  <li>What's the meaning of VIEW VIEW SYS.VW_NSO_1 at line 4</li>
  </ol>
  
  <p>Thanks a lot in advance.</p>
  
  <pre><code>Plan
  SELECT STATEMENT  HINT: FIRST_ROWSCost: 2,101  Bytes: 2,563  Cardinality: 1                                                 
      32 NESTED LOOPS  Cost: 2,101  Bytes: 2,563  Cardinality: 1                                              
          29 NESTED LOOPS  Cost: 2,097  Bytes: 2,518  Cardinality: 1                                          
              27 NESTED LOOPS  Cost: 2,095  Bytes: 2,500  Cardinality: 1                                      
                  24 HASH JOIN RIGHT ANTI  Cost: 2,092  Bytes: 2,444  Cardinality: 1                                  
                      4 VIEW VIEW SYS.VW_NSO_1 Cost: 1,407  Bytes: 8,857,472  Cardinality: 276,796                            
                          3 NESTED LOOPS  Cost: 1,407  Bytes: 11,902,228  Cardinality: 276,796                        
                              1 TABLE ACCESS FULL TABLE SIEBEL.S_PROD_LN Cost: 83  Bytes: 528  Cardinality: 24                    
                              2 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_PROD_U1 Cost: 55  Bytes: 247,359  Cardinality: 11,779                    
                      23 NESTED LOOPS OUTER  Cost: 104  Bytes: 39,200  Cardinality: 28                            
                          20 NESTED LOOPS OUTER  Cost: 50  Bytes: 37,604  Cardinality: 28                         
                              17 NESTED LOOPS OUTER  Cost: 50  Bytes: 36,540  Cardinality: 28                     
                                  14 NESTED LOOPS OUTER  Cost: 23  Bytes: 35,700  Cardinality: 28                 
                                      11 NESTED LOOPS OUTER  Cost: 23  Bytes: 34,300  Cardinality: 28             
                                          8 HASH JOIN RIGHT OUTER  Cost: 23  Bytes: 32,900  Cardinality: 28       
                                              5 TABLE ACCESS FULL TABLE SIEBEL.S_PROD_INT_CRSE Cost: 2  Bytes: 32,718  Cardinality: 82    
                                              7 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 20  Bytes: 11,252  Cardinality: 29      
                                                  6 INDEX RANGE SCAN INDEX SIEBEL.S_PROD_INT_X2_X Cost: 3  
                                          10 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_LIT Cost: 0  Bytes: 50  Cardinality: 1        
                                              9 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_LIT_P1 Cost: 0  Cardinality: 1      
                                      13 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_LIT Cost: 0  Bytes: 50  Cardinality: 1            
                                          12 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_LIT_P1 Cost: 0  Cardinality: 1         
                                  16 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_LN Cost: 1  Bytes: 30  Cardinality: 1                
                                      15 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_P1 Cost: 0  Cardinality: 1             
                              19 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_LN Cost: 0  Bytes: 38  Cardinality: 1                    
                                  18 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_P1 Cost: 0  Cardinality: 1                 
                          22 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 2  Bytes: 57  Cardinality: 1                       
                              21 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_INT_P1 Cost: 1  Cardinality: 1                    
                  26 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_VOD Cost: 3  Bytes: 56  Cardinality: 1                                
                      25 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_VOD_U2 Cost: 2  Cardinality: 1                              
              28 INDEX RANGE SCAN INDEX SIEBEL.S_ISS_OBJ_DEF_M3 Cost: 2  Bytes: 36  Cardinality: 2                                    
          31 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_VOD_VER Cost: 4  Bytes: 45  Cardinality: 1                                        
              30 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_VOD_VER_U1 Cost: 2  Cardinality: 1                                      
  </code></pre>