ORACLE space management experiment 3: Impact of Area Management on I/O performance

Source: Internet
Author: User
What are the advantages and disadvantages of a large residential area? Is it meaningful if the area is larger than 1 M? Table space management technology manages partitions. Local Management of table space LMT adds bitmap areas to each data file header to manage EXTENT usage. when EXTENT is used and released, ORACLE updates records in the bitmap area of the data file header. For large cells, even if the system automatically allocates the district size

What are the advantages and disadvantages of a large residential area? Is it meaningful if the area is larger than 1 M? Table space management technology manages partitions. Local Management of table space LMT adds bitmap areas to each data file header to manage EXTENT usage. when EXTENT is used and released, ORACLE updates records in the bitmap area of the data file header. For large cells, even if the system automatically allocates the district size


What are the advantages and disadvantages of a large residential area? Is it meaningful if the area is larger than 1 M? Table space management technology manages partitions. Local Management of table space LMT adds bitmap areas to each data file header to manage EXTENT usage.
When EXTENT is used and released, ORACLE updates records in the bitmap area of the data file header.
For large residential areas, in fact, even if the system automatically allocates the partition size, 8 m zones are also common, as shown below:
The size of the system management area is automatically allocated by the system,
The first 1 m space in the segment: the size of 8 blocks is 64 K, and the first 16 blocks are like this.
In the range of 1M---64M: 1 MB, 128 Blocks
After the segment is 64 M, the partition size is 8 M.

Summary of advantages and disadvantages of large residential areas:

Residential Area: Advantages and saving space

: The disadvantage is that the number of allocation times may be too large, and even the data file header bitmap area may compete.

Large Area: The disadvantage is a waste of space, and a little more CPU is consumed during allocation and recycling.
: Advantage: Reduce the number of I/O operations during full scan-details the next question
Reduce the number of assignments.

Segment scanning also reduces the number of segment headers-the segment header will be cached

Large Area, large IO, faster full table scanning without concurrency

Cell, small IO, full table scan faster when concurrent

What is the impact of large cells on concurrency?
The zone does not have much impact on concurrency, because the final operation of concurrency is the size of the data block-the buffer pin will be locked during the operation block, and buffer busy waits may be generated. For example, if the block size is large and the number of rows is large, such as 500 rows, the competition is large and the concurrency is relatively small. Smaller blocks and fewer rows, such as 100 rows, less competition, and larger concurrency.
However, large residential areas also have a slight impact on concurrency, that is, the promotion of high water level is generally promoted in a single zone. For details, see:

Will the region increase the I/O performance of OLTP? Why?

It has no effect on OLTP or has little impact.

OLAP can reduce I/O times or change random I/O to sequential I/O.

However, consider the following:

OS and hardware drivers: The maximum I/O size is 1 MB.
The area space is continuous. -- Table fragmentation ???
Read an 8 m partition, which contains 8 1 m I/O. It is very likely that these 8 I/O are sequential IO.
Read eight 1 m zones, at least 8 random I/O.
However, when the system automatically allocates 8 m zones after 64 m, the system only needs to allocate 8 m zones.
Unless there are many middle table spaces and many small tables are scanned for a full table larger than 8 M and less than M, you can set the tablespace to a uniform partition size.
Here we need to consider the AU of the underlying storage. For example, the 8 m au and 8 M zones ensure that the 8 M of each AU is in the same disk. For example, if the storage supports a maximum of 4 M, the partition size is 4 M or no matter, the ASM layer AU is also 4 M. -- It seems not practical.
This section should be considered during OLAP.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.