Tips for Better Capacity Planning on Exadata
Exadata X8-2 comes with up to 3 Petabytes (PB) of raw disk capacity. This post briefly explains the basics of capacity planning on Exadata.capacity planning on Exadata.
Why should you care about capacity planning when you’re already loaded with Petabytes of space?
First, you don’t really have petabytes (PB) of space. A full rack configuration of 8 compute nodes and 14 cell nodes provides about 2.3 PB of space, which, while accounting for high redundancy disk groups, effectively provides about 780 terabytes (TB) storage for the databases. On a quarter rack, that comes out to be 168 TB.
Even 168 TB on a quarter rack configuration is a huge amount of storage space. Different applications have different rates of data accumulation though, so without an effective capacity planning strategy, you might find yourself running out of space very quickly. This is particularly worrisome given that you can’t add storage to an Exadata rack without some sort of expansion. Not to mention, such an undertaking tends to be expensive.
So, how do you go about capacity planning on an Oracle database? To project our future storage needs, we need to look at past trends. Here are a few structured query languages (SQL) that will help you gather the past space utilization of your databases.
The below SQL shows the growth of an Oracle database in the last 30 days. The data is pulled from automatic workload repository (AWR) snaps and our AWR retention period was 30 days.
SQL> with TS_GROUP as ( select /*+ materialize parallel(t,2) ordered */ to_char(c.begin_interval_time,'yyyy-mm-dd') report_date, b.name ts_name, (round(max((a.tablespace_size*8192))/1024/1024/1024,2)) size_gb, (round(max((tablespace_usedsize*8192))/1024/1024/1024,2)) used_gb from dba_hist_tbspc_space_usage a, v$tablespace b, dba_hist_snapshot c where a.tablespace_id=b.ts# and a.snap_id=c.snap_id group by to_char(c.begin_interval_time,'yyyy-mm-dd'), b.name) select report_date, sum(size_gb) total_size_GB, sum(used_gb) used_size_GB from TS_GROUP group by report_date order by report_date; REPORT_DAT TOTAL_SIZE_GB USED_SIZE_GB ---------- ------------- ------------ 2021-08-17 277.98 131.82 2021-08-18 278.01 133.31 2021-08-19 278.02 132.14 2021-08-20 278.12 132.26 2021-08-21 278.18 132.32 2021-08-22 278.2 132.08 2021-08-23 278.2 132.55 .. ..
To get the growth report for a specific tablespace, use the below query:
SQL> select /*+ materialize parallel(t,2) ordered */ to_char(c.begin_interval_time,'yyyy-mm-dd') get_date, b.name ts_name, (round(max((a.tablespace_size*8192))/1024/1024/1024,2)) size_gb, (round(max((tablespace_usedsize*8192))/1024/1024/1024,2)) used_gb from dba_hist_tbspc_space_usage a, v$tablespace b, dba_hist_snapshot c where a.tablespace_id=b.ts# and a.snap_id=c.snap_id and b.name='&tablespace_name' group by to_char(c.begin_interval_time,'yyyy-mm-dd'), b.name order by 1; Enter value for tablespace_name: USERS old 12: and b.name='&tablespace_name' new 12: and b.name='USERS' GET_DATE TS_NA SIZE_GB USED_GB ---------- ----- ---------- ---------- 2021-08-17 USERS 11.01 10.44 2021-08-18 USERS 11.01 10.44 2021-08-19 USERS 11.01 10.44 2021-08-20 USERS 11.01 10.44 2021-08-21 USERS 11.01 10.44 .. ..
What if your AWR retention is only one week and you need data from previous months? You can mine this information from the Oracle Enterprise Manager (OEM) repository database using the following SQLs.
To get the growth of a database use the below query:
SQL> select to_char(rollup_timestamp,'YYYY-MM-DD') DAY, target_name as DATABASE_NAME, sum(average/1024) SIZE_USED_GB from sysman.mgmt$metric_daily where target_name='&Database_name' and column_label = 'Tablespace Used Space (MB)' group by rollup_timestamp, target_name order by 1; DAY DATABASE_NAME SIZE_USED_GB ---------- ------------------------------ ------------ 2021-07-05 orcl 111.918549 2021-07-06 orcl 111.955902 2021-07-07 orcl 111.927277 2021-07-08 orcl 111.971161 2021-07-09 orcl 111.923553 .. ..
To get the growth of a specific tablespace in a database use the below query:
SQL> select to_char(rollup_timestamp,'YYYY-MM-DD') DAY, key_value as TABLESPACE_NAME, sum(average/1024) SIZE_USED_GB from sysman.mgmt$metric_daily where target_name='&Database_Name' and key_value='&Tablespace_name' and column_label = 'Tablespace Used Space (MB)' group by rollup_timestamp, key_value order by 1; DAY TABLESPACE_NAME SIZE_USED_GB ---------- ------------------------------ ------------ 2021-07-05 SYSAUX 1.36871338 2021-07-06 SYSAUX 1.36993408 2021-07-07 SYSAUX 1.36572266 2021-07-08 SYSAUX 1.36590576 .. ..
Once we understand the past growth of the database, we have a reasonable projection of the kind of storage we’d require in the future.
In our experience, the best way to manage storage in an Oracle database is to always look for data that can be archived. Several customers use extract, transform, and load (ETL) jobs on data warehouses to process data and delete the rows that are no longer needed.
What if you need to hang onto all of the data, say for regulatory purposes? Compression is the way to go. Oracle provides upto 10X compression when using hybrid columnar compression on Exadata. Hybrid columnar compression (HCC) is an effective way to archive older data that would not see much data manipulation language (DML). This blog post by Oracle provides more details on the FAQs of HCC:
https://blogs.oracle.com/dbstorage/post/hybrid-columnar-compression-common-questions
Capacity planning is something that can easily be overlooked when planning a migration onto Exadata or even after moving to Exadata. However, having an effective strategy to manage space growth on your Oracle database will keep you in good stead for the lifecycle of the database.
Have you encountered capacity planning issues with Exadata? Let me know in the comments how I can help.
Don’t forget to sign up for more updates here.
