Sizing Exadata - Oracle Exadata Recipes: A Problem-Solution Approach - page 124

FROM dba_hist_snapshot s,v$instance i, v$database d,
dba_hist_tablespace_stat t,dba_hist_tbspc_space_usage u,SYS.ts$
ts
WHERE s.snap_id = t.snap_id
AND s.dbid=d.dbid and s.dbid=t.dbid and s.dbid=u.dbid
AND i.instance_number = s.instance_number
AND s.instance_number = t.instance_number
AND t.snap_id = u.snap_id
AND ts.ts# = t.ts#
AND t.ts# = u.tablespace_id)
WHERE latest = 1) tsmax
WHERE tsmin.tsname = tsmax.tsname and tsmax.snaptime > tsmin.snaptime)
/
Curr Curr Alloc Growth Used Growth
Alloc (GB) Used (GB) % Used GB/day GB/day
------------ ---------- ---------- -------------- -------------
303.84 209.92 69.09 .96 .67
Now you can forecast how large you expect your database to be based on the current growth rates. Typically,
organizations will forecast capacity based on long term budgetary milestones and with an Exadata deployment on the
horizon, the example below in Listing 5-19 displays a 1-year, 2-year, 3-year, and 5-year capacity plan:
Listing 5-19. lst05-19-db-growth-horizon.sql
SYS @ PROD> SELECT
ROUND (sum(curr_alloc_gb), 2) curralloc,
greatest(sum(alloc_gbperday),sum(used_gbperday)) grate,
(sum(curr_alloc_gb) +
365*(greatest(sum(alloc_gbperday),sum(used_gbperday))))
-(sum(curr_alloc_gb)-sum(curr_used_gb)) year1,
(sum(curr_alloc_gb) +
2*365*(greatest(sum(alloc_gbperday),sum(used_gbperday))))
-(sum(curr_alloc_gb)-sum(curr_used_gb)) year2,
(sum(curr_alloc_gb) +
3*365*(greatest(sum(alloc_gbperday),sum(used_gbperday))))
-(sum(curr_alloc_gb)-sum(curr_used_gb)) year3,
(sum(curr_alloc_gb) +
5*365*(greatest(sum(alloc_gbperday),sum(used_gbperday))))
-(sum(curr_alloc_gb)-sum(curr_used_gb)) year5
FROM (SELECT tsmin.tsname tbs, tsmin.tablespace_size init_alloc_gb,
tsmin.tablespace_usedsize init_used_gb,
tsmax.tablespace_size curr_alloc_gb,
tsmax.tablespace_usedsize curr_used_gb,
(tsmax.tablespace_size - tsmin.tablespace_size) / (tsmax.snaptime -
tsmin.snaptime) alloc_gbperday,
(tsmax.tablespace_usedsize - tsmin.tablespace_usedsize)
/ (tsmax.snaptime - tsmin.snaptime) used_gbperday
FROM (SELECT *
FROM (SELECT TRUNC (s.begin_interval_time) snaptime,
t.tsname, (ts.BLOCKSIZE * u.tablespace_size) / 1024 / 1024 / 1024
tablespace_size,