Your Oracle Database has three basic blocks.
- Blocks
- Extents
Blocks
Block is the lowest storage unit of database. Block must equal or multiple of the os block size to mazimize the I/O performance. Define the lowest block size during database creation using following init parameter DB_BLOCK_SIZE.
A newly created block contain free space for new rows or index [only one type of data is allowed in block]. When block data exceed PCTFREE of a block new row or updated row will be assigned to a new block with a pointer pointing the new block from old block.
PCTFREE parameter defines the free space inside a block which is kept for future update of row value.
Starting with Oracle9i Release 2, you can use Automatic Segment Space Management (ASSM) to manage free space within blocks; you enable ASSM in locally managed tablespaces by using the segment space management auto keywords in the create tablespace command (although this is the default for locally managed tablespaces).
Extents
Extents holds the object and consists of several blocks. When a table is created, an initial extent is allocated. Once the space is used in the initial extent, incremental extents are allocated. In a locally managed tablespace, these subsequent extents can either be the same size (using the UNIFORM keyword when the tablespace is created) or optimally sized by Oracle (AUTOALLOCATE). For extents that are optimally sized, Oracle starts with aminimum extent size of 64KB and increases the size of subsequent extents as multiples of the initial extent as the segment grows.
For checking extents information use the following sql command sets.
SQL> create table xxxx (a char(2000))
2 storage (initial 1m next 2m pctincrease 50)
3 tablespace users;
SQL> begin
2 for i in 1..3000 loop
3 insert into xxxx ('a');
4 end loop;
5 end;
6 /
SQL> select segment_name, extent_id, bytes, blocks from user_extents where segment_name = 'XXXX';
Unless a table is truncated or the table is dropped, any blocks allocated to an extent remain allocated for the table, even if all rows have been deleted from the table. The maximum number of blocks ever allocated for a table is known as the high-water mark (HWM).
Segments
Groups of extents are allocated for a single segment. A segment must be wholly contained within one and only one tablespace. Every segment represents one and only one type of database object, such as a table, a partition of a partitioned table, an index, or a temporary segment. For partitioned tables, every partition resides in its own segment; however, a cluster (with two or more tables) resides within a single segment. Similarly, a partitioned index consists of one segment for each index partition.
Database space usage views
The following objects gives the details of database space usage.
- DBA_TABLESPACES
- DBA_SEGMENTS
- DBA_EXTENTS
- DBA_FREE_SPACE
- DBA_LMT_FREE_SPACE
- DBA_THRESHOLDS
- DBA_OUTSTANDING_ALERTS
- DBA_ALERT_HISTORY
- V$ALERT_TYPESselect reason_id, object_type, scope, internal_metric_category,internal_metric_name from v$alert_typeswhere group_name = 'Space';
Sample select queries.
- select tablespace_name, block_size, contents, extent_management from dba_tablespaces;
- select tablespace_name, count(*) NUM_OBJECTS,sum(bytes), sum(blocks), sum(extents) from dba_segments group by rollup (tablespace_name);
- select owner, segment_name, tablespace_name,extent_id, file_id, block_id, bytes from dba_extents where segment_name = 'XXX$';
- select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;
OMF
OMF has been introduced in oracle database by which without mentioning the exact file location we can create the datafiles, redo log or backup files in specified destination. The specific destinations are mentioned in the following init parameter.
DB_CREATE_FILE_DEST = The default operating system file directory where datafiles and tempfiles are created if no pathname is specified in the create tablespace command.
This location is used for redo log files and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
DB_CREATE_ONLINE_LOG_DEST_n = Specifies the default location to store redo log files and control files when no pathname is specified for redo log files or control files at database-creation time. Up to five destinations can be specified with this parameter, allowing up to five multiplexed control files and five members of each redo log group.
DB_RECOVERY_FILE_DEST = Defines the default pathname in the server’s file system where RMAN backups, archived redo logs, and flashback logs are located. Also used for redo
log files and control files if neither DB_CREATE_FILE_DEST nor DB_CREATE_ONLINE_LOG_DEST_n is specified.
How to know about the Segments That Cannot Allocate Additional Extents?
select s.tablespace_name, s.segment_name,
s.segment_type, s.owner
from dba_segments s
where s.next_extent >=
(select max(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name)
or s.extents = s.max_extents
order by tablespace_name, segmen
How to know the datafile and tablespace space usage and free space?
Check the DIAGNOSTIC_DEST parameter for logfile, trace file and alert file location.
How to set Space Usage Warning Levels in Oracle database ?
SQL> select metrics_name, warning_operator warn, warning_value wval,
critical_operator crit, critical_value cval,
consecutive_occurrences consec
from dba_thresholds;
--
-- PL/SQL anonymous procedure to update the Tablespace Space Usage threshold
--
declare
/* OUT */
warning_operator number;
warning_value varchar2(100);
critical_operator number;
critical_value varchar2(100);
observation_period number;
consecutive_occurrences number;
/* IN */
metrics_id number;
instance_name varchar2(50);
object_type number;
object_name varchar2(50);
new_warning_value varchar2(100) := '60';
begin
metrics_id := DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL;
object_type := DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE;
instance_name := 'xx';
object_name := NULL;
-- retrieve the current values with get_threshold
dbms_server_alert.get_threshold(
metrics_id, warning_operator, warning_value,
critical_operator, critical_value,
observation_period, consecutive_occurrences,
instance_name, object_type, object_name);
-- update the warning threshold value from 85 to 60
dbms_server_alert.set_threshold(
metrics_id, warning_operator, new_warning_value,
critical_operator, critical_value,
observation_period, consecutive_occurrences,
instance_name, object_type, object_name);
end;
Now check the status from dba_thresholds again.
How to check and monitor space usage by index
SQL> alter index hr.emp_ix monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING
--------------- --------------- --- ---- -------------------
EMP_IX EMPLOYEES YES NO 12/23/2012 10:00:00
SQL> alter index hr.emp_ix nomonitoring usage;
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
---------- --------------- --- ---- ------------------- ---------------
EMP_IX EMPLOYEES NO YES 12/23/2012 10:04:55 12/24/2012 11:00:00
Sure enough, the index appears to be used at least once during a typical day.
SQL> analyze index hr.emp_ix validate structure;
SQL> select pct_used from index_stats where name = 'EM_IX';
PCT_USED
----------
76
SQL> analyze index hr.emp_ix validate structure;
SQL> select pct_used from index_stats where name = 'EMP_IX';
PCT_USED
----------
26
SQL> alter index hr.emp_job_ix rebuild online;
Index altered.
SEGMENT Space Management
Frequent inserts, updates, and deletes on a table may, over time, leave the space within a table fragmented. Oracle can perform segment shrink on a table or index.
SQL> alter table hr.emp add (work_record varchar2(4000));
SQL> alter table hr.emp enable row movement;
We have enabled ROW MOVEMENT in the table so that shrink operations can be performed on the table if recommended by Segment Advisor.
variable task_id number
-- PL/SQL block follows
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name := ''; -- unique name generated from create_task
descr := 'Check HR.EMPLOYEE table';
dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, NULL);
dbms_advisor.create_object(name, 'TABLE', 'HR', 'EMP', NULL, NULL, obj_id);
dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.execute_task(name);
end;
/
SQL> print task_id
TASK_ID
----------
380
SQL>
Now that we have a task number from invoking Segment Advisor, we can query DBA_ADVISOR_FINDINGS to see what we can do to improve the space utilization of the HR.EMP table.
SQL> select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id = 380;
WORK_RECORD columns may create blocks in the table with free space that can be reclaimed. The view DBA_ADVISOR_RECOMMENDATIONS provides similar information.
SQL> select owner, task_id, task_name, benefit_type from dba_advisor_recommendations
where task_id = 380;
OWNER TASK_ID TASK_NAME
---------- ------- ----------
RJB 380 TASK_00003
BENEFIT_TYPE
--------------------------------------------------
Perform shrink, estimated savings is 107602 bytes.
In any case, we will shrink the segment HR.EMP to reclaim the free space. As an added time-saving benefit to the DBA, the SQL needed to perform the shrink is provided in the view DBA_ADVISOR_ACTIONS.
SQL> select owner, task_id, task_name, command, attr1 from dba_advisor_actions where task_id = 384;
OWNER TASK_ID TASK_NAME COMMAND
---------- ------- ---------- -----------------
RJB 6 TASK_00003 SHRINK SPACE
ATTR1
-----------------------------------------------------
alter table HR.EMPLOYEES shrink space
1 row selected.
SQL> alter table HR.EMPLOYEES shrink space;
Table altered.
As mentioned earlier, the shrink operation does not require extra disk space and does not prevent access to the table during the operation, except for a very short period of time at the end of the process to free the unused space.
No comments :
Post a Comment