If a row is modified by another user, the database will need to query the undo segment
to see the row as it existed at the time your query began. Application designs that call for queries
to frequently access data that others may be changing at the same time force the database to do
more work??”it has to look in multiple locations for one piece of data. Again, this is a design
Chapter 5: Developing and Implementing Applications 129
issue. DBAs may be able to configure the undo segment areas to reduce the possibility of queries
encountering ???Snapshot too old??? errors, but correcting the fundamental problem requires a
change to the application design.
Tell the Database What It Needs to Know
Oracle??™s optimizer relies on statistics when it evaluates the thousands of possible paths to take
during the execution of a query. How you manage those statistics can significantly impact the
performance of your queries.
Keep Your Statistics Updated
How often should you gather statistics? With each major change to the data in your tables, you
should reanalyze the tables. If you have partitioned the tables, you can analyze them on a
partition-by-partition basis. As of Oracle Database 10g, you can use the Automatic Statistics
Gathering feature to automate the collection of statistics. By default, that process gathers statistics
during a maintenance window from 10 P.M to 6 A.M. each night and all day on weekends. Of
course, manual statistics gathering is still available when you have volatile tables that are being
dropped or deleted during the day, or when bulk-loaded tables increase in size by more than
10 percent.
Pages:
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236