Lesson 10
===============================================================================
- OMF (db_create_dest)
- block space, 11gR2 PCT_FREE default 10%
- Row chaining and migration will affect
performance, due to pct_free 10% db will allow update statement in
same data
block. However, if block not enough space. It will cause row migration, db will
migrate half records
to new data
block. Segment advisor will discover row migration. It will update row header
and cause row chaining.
In segment(table,
index, undo…) have a lot extent (group of data block), each extent have BMB (bitmap
block) to
keep check
each data block HDD physical address, if data block change, BMB also need to
update records by
automatic
space management. It will save dba time to shrink table by manual.
- extent allocation by uniform or auto-allocate
Check extent
map in EM > tbs > view tbs > show tbs contents
- Segment Advisor: for tablespace extent check
EM
> advisor central > segment advisor > add tbs > submit
- Allocating Space (if:
deferred_segment_creation=true)
Create table
without insert data, will not use storage in tablespace
Config by
spfile or alter session or by create table clause
* unusable indexes, if statistics not yet gather
the index, it will not use storage
- compression (to use table compression for DML,
tbs compression must enable)
Option: {compress
[Basic | for OLTP | nocompress}
"Basic"
is for bulk import (SQL Loader /direct-path), command: "create table ….compress
basic;"
"for
OLTP" support DML, command: "create table …. compress for OLTP;"
- not
support cluster table (IOT, indexes organized table )
- will
not compress existing records ( for existing records, you need to use datapump
export and import)
-
predick compression level by dbms_compression package
e.g. set
serveroutput on;
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME=>'USERS',
OWNNAME=>'SCOTT',
TABNAME=>'BIGFILE',
PARTNAME =>null,
COMPTYPE =>2,
BLKCNT_CMP=>lv_BLKCNT_CMP,
BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('==============================');
dbms_output.put_line('1. Compression Ratio
:'||lv_cmp_ratio);
dbms_output.put_line('2. Block Count
:'||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type
:'||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed
:'||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count
Un-compressed:'||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed
:'||lv_row_cmp);
dbms_output.put_line('4. Row Count
Un-Compressed:'||lv_row_uncmp);
dbms_output.put_line('===============================');
end;
/
- proactive tablespace monitoring
By thresholds,
tablespace alerts or warning (MMON)
By AWR
compare snapshot between 2 times
- Segments Shrink
To find
out high water mark by command: "select block, empty_blocks from
dba_tables where…;"
e.g. alter
table 'name' enable row movement;
alter
table 'name' shrink space compact;
alter
table 'name' shrink space [ cascade ];
* the cascade clause extends the segment shrink operation
to all dependent (index)
Or
by segment advisor
EM
> advisor central > segment advisor > tbs > select all permanent
tbs > submit
* segment advisor support permanent tbs only
* automatic maintenance will run segment advisor everyday,
check the report in scheduler central >
Automatic maintenance
task) to find any recommendation
- Resumable Space Allocation (if tbs full, process
will suspend)
By spfile
resumable_timeout or session
It will
resume when space problem fixed, otherwise it will abort the process when over the
timeout setting.
- 4 KB-sector disks (old HDD usually are 512B
sector, new 1 TB HDD is 4 KB)
* 11gR2 auto
detect sector size, if set the db_block_size to 4096 (4KB), database only need
read/write 1 time
for 1 data block to improve performance. Also
BIOS need set to native mode (emulation mode will use more
loading).
In native mode the archivelog and redo log also consider to use 4KB by "blocksize
4096"
- Transporting Database
System,
sysaux tbs cannot transport
Flow: make
tbs read-only
Use
datapump to extract metadata
Check
platform endian, if endian format not same, convert datafiles by RMAN (better perform
on target)
"select
* from v$transportable_platform;" and "select platform_name from v$database;"
Copy
datafiles and dump file to target
Use
datapump import metadata and make tablespace read/write
e.g. exec
dbms_tt.transport_set_check('tbs',true);
select
* from transport_set_violations;
alter
tablespace 'name' read only;
mkdir
-p /tmp/transport
create
or replace directory transport as '/tmp/transport';
expdp
\'/ as sysdba \' logfile=trans.log dumpfile=trans.dmp directory=transport
transport_tablespace='name'
copy
tbs.dbf to /tmp/transport
copy
all /tmp/transport folder to target server
in
target server
move
dbf to organial path which like source server
create
or replace directory transport as '/tmp/transport';
impdp
\'/ as sysdba \' logfile=trans.log dumpfile=trans.dmp directory=transport
transport_tablespace='name'
alter
tablespace 'name' read write;
if
need convert
RMAN
> CONVERT DATAFILE '/tmp/transport/name.dbf' TO PLATFORM="Solaris[tm]
OE (64-bit)"
FROM PLATFORM="Microsoft
Windows x86 64-bit"
DB_FILE_NAME_CONVERT=
'/tmp/transport/', '/tmp/transport/import';
