-->
Storage related problem can be generated from different reasons. The following is an interesting example.
Once a database performance has stalled without any change in hardware level and physical layout level. The detail analysis shows that, the application running on the database is mistakenly submitting huge number of jobs in oracle job queue table. The application’s problem
was immediately fixed and all the jobs was immediately deleted.
was immediately fixed and all the jobs was immediately deleted.
But, still the performance of the db was not improved though the job queue table now contains only 3 rows. Later problem has been found for the following reason. The “Highwater mark” [The last block oracle reads when scan occurs] is several thousand blocks higher. Though we have deleted all the rows from the oracle job tables but oracle still thinks that it contains several thousand rows because of high water mark of the table. To resolve the problem a truncation of the table was been performed to fix high water mark of the table.
Locally Managed Tablespace [LMT]
A ”Bitmap” is used to keep block status in each of the datafile whether they are free or used. In locally managed tablespace this bit map is stored under tablespace itself rather than the system tablespace. The LMT improves the db performance.
Multiple block sizes
We can assign multiple block size in table space other than Oracle db block size. Most of the objects are in oracle db will benefit from standard 4K or 8K blocksize. But, large blocksize 16K or 32K will help in the following situation.
1. Most indexes because of the serial nature of the index range scan.
2. Tables with larger objects [blob or clob]
3. large tables which are subject to full table scans
4. temp tablespace used for sorting
Oracle Object’s Storage Planning
After learning the above information, lets check how can we optimize the storage performance. Previously only PCTFREE and PCTUSED was used for any oracle object’s storage management. Now you should consider following.
a. Use multipleblock size = For the above mentioned situations, use the larger block size for a table space.
b. Use LMT
c. Separate tables and their child indexes = tables and their child indexes should be placed in two separate disks
d. Use partitioning = For a growing table or for a table which is scanned too often, always use partitioning
e. Use Automatic Segment Management = the tables and the indexes which are the target of too much insert or update should be configured to use “automatic segment management”.
f. Use read only tablespace wherever possible.
Avoiding Oracle Space Related Downtime Problem
Unmanaged space related problem can lead to database downtime. To avoid this kind of scenario, tablespace can be created with AUTOEXTEND features. But, proper capacity planning can work perfectly well. In that case, the following query can be used to know the exact size of each tablespace.
==========================
select
tablespace_name,
autoextend,
round ((total_space / 1024 / 1024), 2) as
total_space,
round ((total_free_space /
1024 / 1024), 2) as total_free,
round (((total_space - total_free_space) /
1024 / 1024), 2) as used_space,
to_char (nvl (round ((100 * sum_free_blocks / sum_alloc_blocks),2),0)) || '%'
as pct_free
from
(select
tablespace_name,
max (autoextensible) autoextend,
sum (blocks) sum_alloc_blocks,
sum (bytes) as total_space
from
dba_data_files
group by
tablespace_name),
(select
b.tablespace_name fs_ts_name,
nvl (sum (bytes), 0) as total_free_space,
sum (blocks) as sum_free_blocks
from
dba_free_space a, dba_tablespaces b
where
a.tablespace_name (+) = b.tablespace_name
group by
b.tablespace_name, status)
where
tablespace_name = fs_ts_name
union all
select
d.tablespace_name, autoextend,
round ((a.bytes / 1024 / 1024), 2),
round ((a.bytes / 1024 / 1024) -
(nvl (t.bytes, 0) / 1024 / 1024), 2),
round (nvl (t.bytes, 0) / 1024 / 1024, 2),
to_char (100 - (nvl (t.bytes /
a.bytes * 100, 0)), '990.00')
from
sys.dba_tablespaces d,
(select
tablespace_name,
max (autoextensible) autoextend,
sum (bytes) bytes
from
dba_temp_files
group by
tablespace_name) a,
(select
tablespace_name, sum (bytes_cached) bytes
from
sys.v_$temp_extent_pool
group by
tablespace_name) t
where
d.tablespace_name = a.tablespace_name (+)
and
d.tablespace_name = t.tablespace_name (+)
and
d.extent_management like 'LOCAL'
and
d.contents like 'TEMPORARY'
order by
1;
==========================
Sample output is as follows.
To check whether the datafiles of tablespaces have autoextend facilty enabled or not, check the following.
========================
select
b.file_name,
b.tablespace_name,
decode(c.inc,null,'no','yes') autoextend
from
sys.dba_data_files b,
sys.filext$ c
where
c.file# (+)= b.file_id
order by
2, 1;
========================
Output =
Unlimited Object Extents
When a Oracle objects need to expand, Oracle automatically provide additional extent with this object. But, previous releales of Oracle has serious problem with extent management. DB suddenly froze because a table or index has reached its maximum extent limit. If that limit is reached, the DBA can increase maximum extent limit to a higher number.
Alternatively one can alter the object to use unlimited extent.
Alter table address storage(maxextents unlimited)
But, all these problems regarding extent management will be resolved if we use LMT tablespaces.
How to manage Oracle Storage Fragmentation
The oracle fragmentation can be divided in the following category.
- Tablespace Fragmentationa. Free space honeycombsb. Free space bubbles
- Object Fragmentation
Tablespace Fragmentation
Honeycomb type fragmentation occurs when two free space extents in a tablespace exist beside one another. An example is if there are two free extents 10MB and 15MB size. So, if a object is required to expand 20 MB, oracle has to coalesce the honeycombs to make space. So, this type of fragmentation is easier to resolve.
Bubbles type of fragmentation occurs when objects are continuously added and dropped from the tablespace. This resluts free space pockets being interspersed between objectsthat remain in the tablespace.
Way to detecting Oracle Tablespace fragmentation
The following script will help you to determine the tablespace fragmentation level.
======================
select
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
from
sys.dba_free_space
group by
tablespace_name
order by
2 desc, 1;
======================
The above sql will show the fragmentation level in tablespace level. To check the fragmentation in the datafile level, use the following sql.
======================
select
b.file_name, b.tablespace_name,
nvl(round(sqrt(max(a.blocks)/
sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)) )),2),0)
fragmentation_index,
decode(c.inc,null,'no','yes') autoextend,
count (*) free_chunks,
decode (round ((max (a.bytes) / 1024000), 2),
null, 0,
round ((max (a.bytes) / 1024000), 2)) largest_chunk
from
sys.dba_free_space a,
sys.dba_data_files b,
sys.filext$ c
where
b.tablespace_name = a.tablespace_name (+) and
c.file# (+)= a.file_id and
b.file_id = a.file_id (+)
group
by b.file_name,
decode(c.inc,null,'no','yes'),
b.tablespace_name
order
by 5 desc, 1;
======================
Another interesting part is that, if we find too many free chunks in locally managed tablespace that will not be an issue.
During fragmentation analysis, first we need to analyze whether the fragmentation type is bubble or honeycomb type. For that we need to find the block id levels of entire tablespace. Then after analyzing that thoroughly one can identify the fragmentation type.
The following query gives that kind of map.
=======================
select
'free space' object_owner,
' ' object_type,
' ' object_name,
file_id,
block_id,
bytes / 1024 size_kb,
blocks
from
sys.dba_free_space
where
tablespace_name = 'APPS_TS_TX_DATA'
union all
select
owner,
segment_type,
decode (partition_name,null,segment_name,segment_name ||
'.' || partition_name),
file_id,
block_id,
bytes / 1024,
blocks
from
sys.dba_extents
where
tablespace_name = 'APPS_TS_TX_DATA'
order by
4,5;
=======================
No comments :
Post a Comment