首页 » ORACLE 9i-23ai » Script: Oracle Table/Tablespace/DB Growth Prediction(预测表/表空间/库增长)

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:

  1. DBA_SEGMENTS
  2. DBA_TABLESPACES
  3. DBA_HIST_SEG_STAT
  4. DBA_HIST_TABLESPACE_STAT
  5. DBA_HIST_TBSPC_SPACE_USAGE
  6. DBA_HIST_SNAPSHOT
  7. 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

打赏

,

对不起,这篇文章暂时关闭评论。