These statistics relate to the tables, columns, and indexes involved in the execution
plan. The statistics for each type of data structure are listed in Table 4-1.
Figure 4-4. The effect of optimization choices
1 logical I/O
per join
LARGETAB
SMALLTAB
LARGETAB
SMALLTAB
1 logical I/O
per join
10 logical I/Os
10,000 logical I/Os
Total
20 logical I/Os
Total
20,000 logical I/Os
112 | Chapter 4: Oracle Data Structures
Oracle Database 10g and more current database releases also collect overall system statistics,
including I/O and CPU performance and utilization. These statistics are stored in
the data dictionary, described in this chapter??™s final section, ???Data Dictionary Tables.???
You can see that these statistics can be used individually and in combination to
determine the overall cost of the I/O required by an execution plan. The statistics
reflect both the size of a table and the amount of unused space within the blocks; this
space can, in turn, affect how many I/O operations are needed to retrieve rows. The
index statistics reflect not only the depth and breadth of the index tree, but also the
uniqueness of the values in the tree, which can affect the ease with which values can
be selected using the index.
Pages:
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262