title: Simple Oracle query is extremely slow the first time, cannot control the database?
body: <p>Really I'm stuck with this because the database is not of mine, out of my control. I can build a similar database to test in my environment but of course should not modify anything on that database. Now what I can do to improve this is try to change the query.</p>
  
  <p>But even when it's just simple like this, it takes about <em>20</em> seconds the first time?</p>
  
  <pre><code>SELECT count(*) FROM SomeTableOf_500K_Rows
  </code></pre>
  
  <p>Whereas for a similar SQL Server database (with that same table in schema and size), it takes just 1 second the first time, the next times it reports <code>00:00:00</code></p>
  
  <p>My customers want it faster but really it's limited by their database/server.</p>
  
  <p>I don't know much about how Oracle server works, the test database is right in the same local network, there should not be any reason involving networking speed here.</p>
  
  <p>I've also tried this but still the same problem:</p>
  
  <pre><code>SELECT /*+ NOCACHE */ count(*) FROM SomeTableOf_500K_Rows
  </code></pre>
  
  <p>I want Oracle server to somehow remove all unnecessary jobs while performing such a simple query the first time so that it works just as fast as the SQL Server (at least a bit around that execution time).</p>
  
  <p><strong>Update</strong>:
  Today I get back to my environment and done some more tests, it's strange that even for another table of just 74 rows, the <code>select count(*) from that_table</code> still takes about 13-20 seconds the first time? It is much slower than <code>select * from that_table</code>, really strange.</p>
  
  <p><strong>UPDATE with tracing info</strong>:
  By using <code>tkprof</code> and some statement to enable tracing for the current session, I  could obtain the trace-file, analyzed it into an output text file (using <code>tkprof</code>). Really the result shows me nothing new, just a black-box of table-access-full consuming <strong><em>28 seconds</em></strong> to load just <strong><em>74</em></strong> rows (of course for the first time). The <em>black-box</em> I mean looks like this:</p>
  
  <hr>
  
  <pre><code>SQL ID: 9jpk7931105mh Plan Hash: 1282975746
  
  select * 
  from
   TestTable
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.01       0.01          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1      2.92      28.96       7427       7436          0          74
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      2.93      28.97       7427       7436          0          74
  
  Misses in library cache during parse: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 92  
  Number of plan statistics captured: 1
  
  Rows (1st) Rows (avg) Rows (max)  Row Source Operation
  ---------- ---------- ----------  ---------------------------------------------------
          74         74         74  TABLE ACCESS FULL TestTable (cr=7436 pr=7427 pw=0 time=2017 us starts=73 cost=2017 size=6734 card=74)
  </code></pre>
  
  <hr>
  
  <p>The total time to run the test query is about <strong><em>29.651</em></strong> seconds, as you can see the black-box above consumes almost the time (<strong><em>28.97</em></strong> seconds).</p>
  
  <p>If possible please take a look at the full output file (after analyzing) here <a href="https://pastebin.com/Euv4mseu" rel="nofollow noreferrer">https://pastebin.com/Euv4mseu</a></p>
  
  <p>Also note that for each test, I always run the following statements to clear cache, so that the query will not be affected by caching:</p>
  
  <pre><code>alter system flush BUFFER_CACHE;
  alter system flush shared_pool;
  </code></pre>