Question: I see from your details on the
optimizer cost model that the SQL optimizer is aware of the details
about a disk blocksize as well as the RAID level and striping details.
Where is this information stored in the data dictionary?
Answer:
As a review, the CBO
gathers information from many sources, and he has the lofty goal of using
DBA-provided metadata to always make the "best" execution plan decision, and
this does include details on the external storage, including average I/O
tunings and details on the RAID and stripe size::

Oracle uses data from many sources to make an execution plan
ASM X$ tables
Inside the ASM instance we see these V$ views and their underlying X$ fixed
tables:
X$ Table |
v$ View |
X$KFGRP
|
V$ASM_DISKGROUP
|
X$KFGRP_STAT
|
V$ASM_DISKGROUP_STAT
|
X$KFDSK
|
V$ASM_DISK
|
X$KFKID |
V$ASM_DISK
|
X$KFDSK_STAT
|
V$ASM_DISK_STAT
|
X$KFKID
|
V$ASM_DISK_STAT
|
X$KFFIL
|
V$ASM_FILE
|
X$KFALS
|
V$ASM_ALIAS
|
X$KFTMTA
|
V$ASM_TEMPLATE
|
X$KFNCL
|
V$ASM_CLIENT
|
X$KFGMG
|
V$ASM_OPERATION
|
X$KFENV |
V$ASM_ATTRIBUTE |
X$KFNSDSKIOST |
V$ASM_DISK_IOSTAT |
Source:
CERN
From the docs, here are the main ASM V$ views and their
underlying contents:
VIEW |
ASM INSTANCE |
DB INSTANCE |
v$asm_diskgroup |
Describes a disk group (number, name,
size related info, state, and redundancy type) |
Contains one row for every open ASM
disk in the DB instance. |
v$asm_client
|
Identifies databases using disk groups
managed by the ASM instance. |
Empty |
v$asm_disk
|
Contains one row for every disk
discovered by the ASM instance, including disks that are not part of
any disk group. |
Contains rows only for disks in the
disk groups in use by that DB instance. |
v$asm_file |
Contains one row for every ASM file in
every disk group mounted by the ASM instance. |
Contains rows only for files that are
currently open in the DB instance. |
v$asm_template |
Contains one row for every template
present in every disk group mounted by the ASM instance.
|
Empty |
v$asm_alias
|
Contains one row for every alias
present in every disk group mounted by the ASM instance.
|
Empty |
v$asm_operation |
Contains one row for every active ASM long running operation
executing in the ASM instance. |
Empty |
v$asm_attribute
|
All ASM configuration attributes |
|
v$asm_disk_iostat
|
Disk I/O statistics |
|
ASM internals and stripe size mapping
The stripe size for ASM data files is critical when estimating the cost of a
full-table scan.
In these days when most Oracle databases adopt either ASM or RAID-10 (stripe
and mirror everywhere) approach, every table is physically mapped across
many physical disk drives and the internal mapping is hidden inside many
layers:

Oracle Certified Master Steve Karam shares the internals between ASM and
disk mapping:
See:
Mapping LUN and MAS to ASM
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |