本文共 2333 字，大约阅读时间需要 7 分钟。
Database Extensible Tablespace Usage Analysis
The following analysis provides an overview of the extensible tablespace usage across different tablespaces in the database system. The data is derived from the sys.dbuffet_space and sys.db_data_files views, rollup by tablespace_name.
Key Metrics Overview:
- tablespace_name: Name of the tablespace.
- extensible_bytes: Total bytes allocated for extensible tables in the tablespace.
- allocated_bytes: Total bytes allocated across all files in the tablespace.
- alloc_free: Total free bytes across all files in the tablespace.
- used: Bytes used in the tablespace calculated as
allocated_bytes - alloc_free. - pct_used: Percentage of used bytes relative to allocated bytes.
- pct_used_ext: Percentage of extensible bytes consumed relative to the total extensible bytes.
Tablespace Analysis:
SYSTEM:
- extensible_bytes: 34,359,721,984
- allocated_bytes: 786,432,000
- alloc_free: 6,684,672
- used: 779,747,328
- pct_used: 99.15%
- pct_used_ext: 33,579,974,656
- extensible_usage_ratio: 2.2693644854
SYSAUX:
- extensible_bytes: 34,359,721,984
- allocated_bytes: 587,202,560
- alloc_free: 37,617,664
- used: 549,584,896
- pct_used: 93.59375%
- pct_used_ext: 33,810,137,088
- extensible_usage_ratio: 1.5995033262
USERS:
- extensible_bytes: 34,359,721,984
- allocated_bytes: 5,242,880
- alloc_free: 3,866,624
- used: 1,376,256
- pct_used: 26.25%
- pct_used_ext: 34,358,345,728
- extensible_usage_ratio: 0.004005434
UNDOTBS1:
- extensible_bytes: 34,359,721,984
- allocated_bytes: 94,371,840
- alloc_free: 73,465,856
- used: 20,905,984
- pct_used: 22.1527777778%
- pct_used_ext: 34,338,816,000
- extensible_usage_ratio: 0.0608444504
TESTTBS:
- extensible_bytes: 68,719,443,968
- allocated_bytes: 209,715,200
- alloc_free: 207,618,048
- used: 2,097,152
- pct_used: 1
- pct_used_ext: 68,717,346,816
- extensible_usage_ratio: 0.0030517593
Observations:
- The SYSTEM and SYSAUX tablespaces exhibit very high usage of extensible bytes, with almost all allocated bytes being used.
- The USERS and TESTTBS tablespaces show lower usage, with less than 30% of allocated bytes being utilized.
- The extensible_usage_ratio indicates the portion of extensible bytes consumed relative to the total allocated, with most tablespaces utilizing a significant portion.
This analysis helps in understanding the distribution of database resources and optimizing the configuration for different workloads.
转载地址：http://ruiiz.baihongyu.com/