Script: Oracle Table/Tablespace/DB Growth Prediction(预测表/表空间/库增长)
Most production databases grow over the course of time. Planning for growth is a very important task of every professional Oracle DBA. such problems as the system being out of space are likely to be avoided. It is very good when the DBA proactively resolves such space related issues.
There are few important views provided by Oracle which helps us track the growth. These can be listed as:
- DBA_SEGMENTS
- DBA_TABLESPACES
- DBA_HIST_SEG_STAT
- DBA_HIST_TABLESPACE_STAT
- DBA_HIST_TBSPC_SPACE_USAGE
- DBA_HIST_SNAPSHOT
- DBMS_SPACE
One of the most important features of Oracle10g is its ability to predict the growth of the segments. The object_growth_trend prediction mechanism is based on data collected and
stored by the AWR, and the growth trend reporting is also built into the Oracle database kernel and is available by default. This provides sufficient information, over time, to perform growth trending of individual objects in the database as well as the database as a whole.
The query below allows the estimation of the segment current allocate space for ICME.ICME_NOPROJECT_SCORE TABLE.
icmedb>select bytes from dba_segments where owner=’ICME’ AND segment_name=’ICME_NOPROJECT_SCORE’;
BYTES
———-
4701814784
Note: the following Script requires AWR license
The query below allows the estimation of the segment growth trend for the ‘ICME.ICME_NOPROJECT_SCORE’ table last three days:
alter session set nls_timestamp_format='RRRR-MM-DD hh24:mi:ss';
col TIMEPOINT for a30
SELECT *
FROM TABLE(DBMS_SPACE.object_growth_trend ('ICME','ICME_NOPROJECT_SCORE','TABLE',null,sysdate-3))
ORDER BY timepoint;
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- --------------------
2012-12-23 10:54:53 4408834129 4634705920 GOOD
2012-12-23 13:18:53 4408850465 4634705920 INTERPOLATED
2012-12-23 15:42:53 4409037936 4634705920 INTERPOLATED
2012-12-23 18:06:53 4409103113 4634705920 INTERPOLATED
2012-12-23 20:30:53 4409260669 4634705920 INTERPOLATED
2012-12-23 22:54:53 4409456318 4634705920 GOOD
2012-12-24 01:18:53 4409517617 4634705920 INTERPOLATED
2012-12-24 03:42:53 4409539129 4634705920 INTERPOLATED
2012-12-24 06:06:53 4409544108 4634705920 INTERPOLATED
2012-12-24 08:30:53 4409233698 4634705920 INTERPOLATED
2012-12-24 10:54:53 4410617460 4634705920 GOOD
2012-12-24 13:18:53 4413688741 4701814784 INTERPOLATED
2012-12-24 15:42:53 4414351973 4701814784 INTERPOLATED
2012-12-24 18:06:53 4414435291 4701814784 INTERPOLATED
2012-12-24 20:30:53 4416715906 4701814784 INTERPOLATED
2012-12-24 22:54:53 4417056254 4701814784 GOOD
2012-12-25 01:18:53 4417161166 4701814784 INTERPOLATED
2012-12-25 03:42:53 4417163794 4701814784 INTERPOLATED
2012-12-25 06:06:53 4417149112 4701814784 INTERPOLATED
2012-12-25 08:30:53 4416864781 4701814784 INTERPOLATED
2012-12-25 10:54:53 4418027333 4701814784 GOOD
2012-12-25 13:18:53 4418637343 4701814784 INTERPOLATED
2012-12-25 15:42:53 4419910142 4701814784 INTERPOLATED
2012-12-25 18:06:53 4422665830 4701814784 INTERPOLATED
2012-12-25 20:30:53 4423143461 4701814784 INTERPOLATED
2012-12-25 22:54:53 4423431886 4701814784 GOOD
2012-12-26 01:18:53 4423642425 4701814784 INTERPOLATED
2012-12-26 03:42:53 4423656843 4701814784 INTERPOLATED
2012-12-26 06:06:53 4423656843 4701814784 INTERPOLATED
2012-12-26 08:30:53 4423429547 4701814784 INTERPOLATED
2012-12-26 10:54:53 4423903669 4701814784 INTERPOLATED
2012-12-26 13:18:53 4427752546 4761346841 PROJECTED
2012-12-26 15:42:53 4428362817 4764323444 PROJECTED
2012-12-26 18:06:53 4428973089 4767300047 PROJECTED
2012-12-26 20:30:53 4429583360 4770276650 PROJECTED
2012-12-26 22:54:53 4430193632 4773253253 PROJECTED
The space_usage column shows how many bytes the ICME.ICME_NOPROJECT_SCORE table actually consumes, and space_alloc reports the size, in bytes, of space used by the table.
QUALITY: A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.
The values of the QUALITY column are:
GOOD: The value whenever the value of TIME is based on recorded statistics with a recorded timestamp within 10% of the INTERVAL specified in the input parameters.
INTERPOLATED: The value did not meet the criteria for GOOD, but was based on recorded statistics before and after the value of TIME. Current in-memory statistics can be collected across all instances in a cluster and treated as the “recorded” value for the present time.
PROJECTION: The value of TIME is in the future as of the time the table was produced. In an Oracle Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected.
what is the value for 3rd parameter?
TABLE/INDEX/TABLE PARTITION/INDEX PARTITION/…
May lead to some bug in different versions
EXCEPTION in chrow processing – code: -14551 msg: ORA-14551: cannot perform a DML operation inside a query
Solution
set serveroutput off
bug
ORA-600(ktspNextL1:4)
Solution
1. Ignore the error.
2. Upgrade to 11.1.0.7.
3. Rerun the job.
Oracle saves a lot of information about the oracle DB history , So we access some historic tables (included in the Diagnostic Pack) get this.
col stime for a20
col object_name for a30
SELECT to_char(savtime,'yyyy-mm-dd hh24:mi') stime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
dba_objects o
WHERE
o.owner='ICME'
AND o.object_name='ICME_NOPROJECT_SCORE'
and o.object_id = W.OBJ#
ORDER BY w.savtime,o.owner, o.object_name;
noties the WRI$_OPTSTAT_TAB_HISTORY don't contains all the table information.
select obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in ('SYS','SYSTEM')
and obj.object_name='ICME_NOPROJECT_SCORE'
and end_interval_time between SYSDATE-10
and SYSDATE
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
OWNER OBJECT_NAME START_DAY BLOCK_INCREASE
------------------------------ ------------------------------ -------------- --------------
ICME ICME_NOPROJECT_SCORE 2012-DEC-18 320
ICME ICME_NOPROJECT_SCORE 2012-DEC-19 115376
ICME ICME_NOPROJECT_SCORE 2012-DEC-20 102096
ICME ICME_NOPROJECT_SCORE 2012-DEC-21 274576
ICME ICME_NOPROJECT_SCORE 2012-DEC-22 31008
ICME ICME_NOPROJECT_SCORE 2012-DEC-23 10928
ICME ICME_NOPROJECT_SCORE 2012-DEC-24 131056
ICME ICME_NOPROJECT_SCORE 2012-DEC-25 124080
ICME ICME_NOPROJECT_SCORE 2012-DEC-26 30480
or show the detail by wrh$_seg_stat by AWR table for every snapshot,using following statement
select ss.begin_interval_time, space_used_delta, space_allocated_delta from sys.wrh$_seg_stat st, sys.wrm$_snapshot ss,dba_objects obj where st.snap_id = ss.snap_id and st.obj#=obj.object_id --AND ss.begin_interval_time >SYSDATE-7 and obj.owner='ICME' AND OBJECT_NAME='ICME_NOPROJECT_SCORE' order by st.snap_id;
other script:
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);
SEGMENT_TYPE Space used (M) Total Space (M) Percent of Total Disk Usage
------------------ -------------- --------------- -----------------------------------------
INDEX 1307.84976 663101.438 .2%
TABLE 54.8532715 1121512 0%
1362.70303 1784613.44 .08%
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) -&days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');
Enter value for days_back: 5
old 8: where begin_interval_time > trunc(sysdate) -&days_back
new 8: where begin_interval_time > trunc(sysdate) -5
Enter value for segment_name: ICME_NOPROJECT_SCORE
old 13: and c.segment_name = '&segment_name'
new 13: and c.segment_name = 'ICME_NOPROJECT_SCORE'
MYDATE Space used (MB) Total Object Size (MB) Percent of Total Disk Usage
-------- --------------- ---------------------- ---------------------------
12/21/12 6.27 4,484.00 .01
12/22/12 2.27 4,484.00 .00
12/23/12 .95 4,484.00 .00
12/24/12 7.27 4,484.00 .01
12/25/12 6.08 4,484.00 .01
12/26/12 2.01 4,484.00 .00
Script #2: List object growth over last N days, sorted by tablespace growth asc
COL segment_name FOR A30 HEADING "Object Name"
SELECT *
FROM ( SELECT c.TABLESPACE_NAME,
c.segment_name,
b.object_type,
ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)"
FROM dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE begin_interval_time > TRUNC (SYSDATE) - &days_back
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.object_name = c.segment_name
AND c.owner = 'ICME'
GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)
ORDER BY 1, 4 ASC;
Script #3: To check Growth rate of Tablespace.
select day_date,con_id, --sum(cur_size_mb),
sum(usedsize_mb) max_usedsize_MB from (
SELECT TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD') day_date,tsu.con_id ,ts.tsname,
count(*),
MAX (
ROUND ( (tsu.tablespace_size * dt.block_size) / (1024 * 1024), 2))
cur_size_MB,
MAX (
ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024),
2))
usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu,
DBA_HIST_TABLESPACE_STAT ts,
DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id = ts.ts#
AND tsu.snap_id = sp.snap_id
and tsu.con_id=ts.con_id
AND tsu.snap_id =ts.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname in(SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='PERMANENT' and TABLESPACE_NAME NOT IN ('SYSAUX', 'SYSTEM','UNDOTBS1'))
and sp.begin_interval_time >trunc(sysdate-7)
GROUP BY TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD'), ts.tsname,tsu.con_id
Script #3: To check Growth rate of DB.
column max_usedsize_MB for 999,999,999,990.0
column growth_ratio for 9,990.99
select day_date,con_id,max_usedsize_MB, max_usedsize_MB-lag(max_usedsize_MB,1) over(order by day_date) growth_mb,
round((max_usedsize_MB-lag(max_usedsize_MB,1) over(order by day_date) )/decode(lag(max_usedsize_MB,1) over(order by day_date),0,-1,max_usedsize_MB),4)*100 growth_ratio
from (
select day_date,con_id, --sum(cur_size_mb),
sum(usedsize_mb) max_usedsize_MB from (
SELECT TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD') day_date,tsu.con_id ,ts.tsname,
count(*),
MAX (
ROUND ( (tsu.tablespace_size * dt.block_size) / (1024 * 1024), 2))
cur_size_MB,
MAX (
ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024),
2))
usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu,
DBA_HIST_TABLESPACE_STAT ts,
DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id = ts.ts#
AND tsu.snap_id = sp.snap_id
and tsu.con_id=ts.con_id
AND tsu.snap_id =ts.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname in(SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='PERMANENT' and TABLESPACE_NAME NOT IN ('SYSAUX', 'SYSTEM','UNDOTBS1'))
and sp.begin_interval_time >trunc(sysdate-7)
GROUP BY TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD'), ts.tsname,tsu.con_id
)
group by day_date,con_id
);
Script #4: List object growth over last N days, sorted by growth
COL segment_name FOR A30 HEADING "Object Name" SELECT * FROM ( SELECT c.TABLESPACE_NAME, c.segment_name, b.object_type, ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)" FROM dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c WHERE begin_interval_time > TRUNC (SYSDATE) - &days_back AND sn.snap_id = a.snap_id AND b.object_id = a.obj# AND b.owner = c.owner AND b.object_name = c.segment_name AND c.owner = '&SCHEMANAME' GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type) ORDER BY 1,4 ASC;
# Display tablesapce USERS Using the distribution
WITH ts AS
(SELECT tablespace_name
, block_size
FROM dba_tablespaces
WHERE tablespace_name = 'USERS'
)
, freeUsed AS
(SELECT ex.file_id
, segment_name
, ex.extent_id
, ts.block_size
, ex.block_id first_block
, ex.block_id + blocks - 1 last_block
--hwm
FROM dba_extents ex
, ts
WHERE ex.tablespace_name = ts.tablespace_name
UNION ALL
SELECT fs.file_id
, 'free'
, NULL
, ts.block_size
, fs.block_id
, fs.block_id + blocks - 1
FROM dba_free_space fs
, ts
WHERE fs.tablespace_name = ts.tablespace_name
)
SELECT freeUsed.*
--, 'alter database datafile ''/tmp/shrink_me.dbf'' resize ' || last_block * block_size / 1024 || 'k;' as cmd
FROM freeUsed
ORDER BY file_id
, first_block;
References Donald K. Burleson.article
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。