CODEWITHSUNDEEP
oracle-databaseperformancestatistics
Cameron Castillo
Cameron Castillo

Reputation: 2832

Update Oracle statistics while query is running to improve performance?

I've got a Oracle Insert query that runs and it has been busy for almost 24 hours.
The SELECT part of the statement had a cost of 211M.
Now I've updated the statistics on the source tables and the cost has came down significantly to 2M.

Should I stop and restart my INSERT statement, or will the new updated statistics automatically have an effect and start speeding up the performance?

I'm using Oracle 11g.

Upvotes: 0

Views: 2712

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Should I stop and restart my INSERT statement, or will the new updated statistics automatically have an effect and start speeding up the performance?

New statistics will be used the next time Oracle parses them.

So, optimizer cannot update the execution plan based on the stats gathered at run time, since the query is already parsed and the execution plan has already been chosen.

What you can expect from 12c optimizer is, adaptive dynamic execution. It has the ability to adapt the plan at run time based on actual execution statistics. You can read more about it here http://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm

Upvotes: 2

Related Questions