Hey there, let’s break down why your query drags on the first run (up to 56s) but speeds up to under 2s on the second pass when the DB is idle, and map out actionable steps to fix this:

1. Start with the Trace File (TRACE.OUT) – It’s Your Best Clue #

First, dig into that trace file to get granular, actionable details:

• Spot wait events: Check what the database was waiting on during the slow first execution. Common culprits here are db file sequential read (index block I/O) or db file scattered read (full table scan I/O) – these point to disk bottlenecks or inefficient/missing indexes. Since you mentioned the DB was idle, latch or enqueue waits are less likely, but still worth checking.
• Compare parse vs exec/fetch times: If PARSE time is disproportionately high on the first run, you’re dealing with hard parsing. This happens if your query uses literal values instead of bind variables, forcing Oracle to recompile the execution plan every time.
• Extract execution plans: Check if the plan changes between the first and second runs. Oracle 11g’s bind variable peeking might pick a suboptimal plan for the first execution if your query uses skewed data values, then reuse a better plan later.

2. Validate Statistics (Even After Index Rebuilds) #

Index rebuilds don’t fix stale table statistics, and with 1.4 billion rows, outdated stats will make Oracle guess wrong on execution plans:

• Check when stats were last updated:

  SELECT last_analyzed, num_rows FROM user_tables WHERE table_name = 'PUBLICOBJECTS';
  

• If stats are older than a week (or after major data changes), refresh them with auto-sampling to capture accurate data distribution:

  EXEC DBMS_STATS.GATHER_TABLE_STATS(
      ownname => 'YOUR_SCHEMA',
      tabname => 'PUBLICOBJECTS',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- Captures histograms for skewed columns
      cascade => TRUE -- Updates index stats too
  );
  

3. Audit Index Usage & Selectivity #

Rebuilding indexes doesn’t guarantee they’re being used effectively:

• Check if your query’s filter columns are actually leveraging indexes:

  SELECT index_name, used, last_used FROM user_indexes WHERE table_name = 'PUBLICOBJECTS';
  

• If the index isn’t being used, calculate its selectivity. For a filter column, if less than 5-10% of rows match the condition, an index scan is better; if more, a full table scan might be faster. Oracle might have picked the wrong plan on the first run due to bad stats.
• For large tables like this, consider covering indexes – include all columns needed by the query in the index so Oracle doesn’t have to do a table lookup after the index scan (this eliminates db file sequential read waits entirely).

4. Address Cache Bottlenecks #

The second run is fast because data is now in the Buffer Cache. Fix the first-run slowness by optimizing cache behavior:

• Check if your Buffer Cache is sized appropriately:

  SELECT name, value/1024/1024 AS size_mb FROM v$parameter WHERE name = 'db_cache_size';
  

  If you’re using Automatic Memory Management (AMM), Oracle adjusts this automatically, but you might need to increase the total memory allocation if the cache is too small to hold frequently accessed data.
• If the query is run repeatedly and data doesn’t change often, use the result cache hint to store the query result in memory:

  SELECT /*+ RESULT_CACHE */ column1, column2 FROM PUBLICOBJECTS WHERE ...;
  

  This makes subsequent runs near-instant even if the base data isn’t cached.

5. Consider Partitioning for the 1.4B-Row Table #

A table this large will benefit massively from partitioning, especially if your query filters on a column like create_date or object_type:

• Range partitioning: Split the table by date ranges so queries only scan relevant partitions (partition pruning).
• List partitioning: If you filter by a categorical column (e.g., object status), list partitioning lets Oracle skip irrelevant partitions entirely.
  Partitioning reduces the amount of data the query needs to scan on the first execution, cutting down I/O time drastically.

Final Next Steps #

1. Pull the execution plan and wait events from TRACE.OUT first – this will narrow down the root cause (I/O, bad plan, parsing, etc.).
2. Refresh table/index statistics if they’re stale.
3. Test with bind variables if you’re using literals to eliminate hard parsing.

内容的提问来源于stack exchange，提问作者Karlendev