On March 26, 2008 I gave a webcast on physical tuning named “Goal! Success through Storage.” Due to some delays starting it overran a bit so I wasn’t able to answer questions afterward as I usually do. So, here are the answers to all the questions that weren’t handled by Darren Mallet (who handles the questions online when I am webcasting, thanks Darren!)
During the webcast I discussed topics such as choosing the right blocksize, the effects of blocksize on indexes and tables, use of partitioning and parallel query, matching blocksize and stripe depth for RAID, the problems with fragmented indexes and the use of Automatic Storage Management (ASM) in Oracle.
Session Questions and Answers:
Sai: In a Data warehouse environment, do you prefer to analyze the index online? The table is around 250 GB.
Yes I would use the online option
Pon Venkat: Does rebuild index need an outage in a 9i database?
Starting in 9i, Oracle uses the online index to rebuild a copy and the index is only unavailable during the very small amount of time it takes it to internally swap the old index with the new index. In 9i, an index rebuild should not require an outage, but it will require that space be available for the additional copy of the index. During the rebuild process any locked rows are skipped and retired in subsequent passes. Once all rows are accounted for the needed data dictionary changes are made (the index rename) and the old index is dropped.
Naku Mayo: Would fragmented indexes cause deadlocks to occur on the DB?
No, a fragmented index should not cause deadlocks. A fragmented index will cause long running operations which may hold row locks longer than usual and block other operations from being able to access those rows, but that is not a deadlock it is normal locking behavior.
Satya Sridhar: What's an AU?
In Automatic Storage Management an AU is an allocation unit. In Pre-11g ASM, the AU size was fixed at 1 megabyte for data and 128 kilobytes for non-data. The AU specifies how the data was striped against the underlying disks. In Oracle11g the AU can be set for a diskgroup and internally Oracle will increase the AU as objects get bigger. This increasing AU size is to help reduce the impact of tracking extents on the shared pool.
Craig H. Steblen: Why would someone coalesce indexes instead of rebuild indexes?
An index coalesce is a less intrusive operation that preserves the available space in an index. A rebuild usually collapses the index in size.
Malak: What about the I/O chunk size?
For the purposes of this webcast, stripe width per disk is equivalent to what some experts call I/O chunk size. Usually we refer to stripe depth as the amount of the stripe on each disk and stripe width as the entire stripe size so a non-redundant ASM stripe across 6 disks using the pre-11g AU for data of 1 megabyte would result in a stripe depth of 1 megabyte and a stripe width of 6 megabytes. This would equate to an IO chunk size of 1 megabyte.