title: Using Oracle hint "FIRST_ROWS" to improve Oracle database performances
body: <p>I have a statement that runs on Oracle database server. The statement has about 5 joins and there is nothing unusual there. It looks pretty much like below:</p>
  <pre><code>SELECT field1, field2, field3, ...  
  FROM table1, table2, table3, table4, table5  
  WHERE table1.id = table2.id AND table2.id = table3.id AND ...  
        table5.userid = 1
  </code></pre>
  <p><strong>The problem (and what is interesting) is that statement for userid = 1 takes 1 second to return 590 records. Statement for userid = 2 takes around 30 seconds to return 70 records.</strong></p>
  <p>I don't understand why is difference so big.</p>
  <p>It seems that different execution plan is chosen for statement with userid = 1 and different for userid = 2.</p>
  <p>After I implemented Oracle Hint FIRST_ROW, performance become significantly better. Both statements (for both ids 1 and 2) produce return in under 1 second.</p>
  <pre><code>SELECT /*+ FIRST_ROWS */
         field1, field2, field3, ...  
  FROM table1, table2, table3, table4, table5  
  WHERE table1.id = table2.id AND table2.id = table3.id AND ...  
        table5.userid = 1
  </code></pre>
  <p><strong>Questions:</strong></p>
  <ol>
  <li>What are possible reasons for bad performance when userid = 2 (when hint is not used)?</li>
  <li>Why would execution plan be different for one vs another statement (when hint is not used)?</li>
  <li>Is there anything that I should be careful about when deciding to add this hint to my queries?</li>
  </ol>