How does the optimizer in Oracle Database use I/O statistics?

Question

I would like to help Oracle's query optimizer make better-informed decisions. I have been unable to find much information on how the optimizer determines the cost of I/O operations, other than unsourced remarks that it uses the time that it takes to retrieve a single data block. Here are some things that I would like to know:

1. Is it possible to view the current I/O benchmarks used by the optimizer?

2. Is it possible to trigger an update of those benchmarks?

3. Does the optimizer account for the current I/O cost of an operation? For example, if there is heavy concurrent load on the storage device causing a reduction in performance, does it consider that the relative cost of I/O will temporarily be higher?

4. Does the optimizer consider latency/random-access performance versus throughput/sequential performance?

5. If you have data files on multiple storage devices with different performance characteristics, does the optimizer have and apply device-specific costs?

• Michael Kutz: (5) seems to imply that you have a bad physical design. All the disks in your disk group [ASM]/RAID set should have the same performance characteristics.
• novog: For (5), I didn't say that the devices were in the same RAID set. I am talking more of employing tablespaces with different performance characteristics for different intended usage patterns. For example, one could use cheap, slow storage for infrequently-accessed readonly data, low-latency storage for objects subjected to a lot of random access, etc.

Answer 1

1. You can see currently used statistics in SYS.AUX_STATS$.
2. DBMS_STATS.GATHER_SYSTEM_STATS collects system statistics. Running it with gathering_mode='NOWORKLOAD' will capture IO related stats of the server. You usually also run it under workload ("START/STOP", "INTERVAL").
3. (4,5) Collecting system statistics during typical load should take care of potential problems you mentioned. In addition , (4) will be affected by optimizer mode (ALL_ROWS vs FIRST_ROWS)

Also, with procedures in dbms_stats packages it's possible to collect stats multiple times, export (EXPORT_SYSTEM_STATS ), import (EXPORT_SYSTEM_STATS), or delete it (DELETE_SYSTEM_STATS).

Answer 2

I don't have any real answers. I mainly have pointers for where you can go to look for more information.

Viewing I/O Statistics (1)

You might enjoy this URL:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/poster/OUTPUT_poster/poster.html#

It appears that Oracle keeps "round trip" information within V$FILESTAT.

Updating I/O Statistics (2)

Oracle ORION was the first thing that came to my mind. After a quick search, it appears that there is a DBMS Package to update I/O statistics.

https://oracle-base.com/articles/misc/measuring-storage-performance-for-oracle-systems

Question 3 and 4

I only have my own thoughts and reasoning.

SAS vs SATA (5)

The concept of "different characteristics" is not foreign to the Oracle Database (at least, not within the documentation). 12c's ILM clause allows you to automatically move data from a tablespace on ASM diskgroup [+SAS] to a tablespace on ASM diskgroup [+SATA].

However, I would not mix files with different characteristics within the same tablespace.

Inner/Outer Sections of a Disk Platter

ASM seems to have the ability to distinguish these. I would look through the documentation.

https://docs.oracle.com/database/121/OSTMG/GUID-857680CA-35B0-4C88-9DF4-55537C8F1B7C.htm#OSTMG13790