Poor's man Capacity Planning

Companies are struggling to follow the pace with IT governance standards; for instance, ITIL has released it's third delivery, doubling the number of 'books' documenting the guidelines.

On the other side, the IT operation is facing an explosive growth of business requirements and information flow that most of the time lends to insufficient resources, even if we planned ahead.

ITIL's Capacity planning intend to cover the broad range of IT operations, from document printing requests to server renovation or upgrade. I'll focus on ideas related to Database Capacity planning and related resources.

The Avalanche is here!
Yes, data growth is a headache and if you don't take actions in advance, you'll be buried by your data. Fortunately for us, this doesn't happen overnight and growth follows a pattern that will help you to forecast purchase of additional storage... or an eventual failure if nothing is done.

First you need to start collecting data for every one of your databases, consolidation of results may depend on your storage architecture, server assignation, business area or the grouping criteria of your choice.

This is very simple, you'll need to query the tablespace free and total space and store it in a table . If you have more than one database, its better to centralize information sending results to a repository. With oracle that is pretty straightforward: you'll need cron, sql*plus and sql*loader, just that.

The repository DB must be added to the local tnsnames.ora file, because sqlldr will access the repository using the username/password@database login form. You will need a table to store DB Name, Tablespace, Free Space, Used Space and the vital, Date of Sample.

You'll get the information from just three views of the Oracle Data Dictionary views: DBA_DATA_FILES, DBA_FREE_SPACE, V$PARAMETER. This is a sample of the query used.

SELECT p.value, 
       to_char(sysdate,'DD-MM-YYYY'),
       d.tablespace_name,
       NVL (a.BYTES / 1024 / 1024, 0),
       NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024)
FROM SYS.dba_tablespaces d,
     (SELECT tablespace_name, SUM (BYTES) BYTES
        FROM dba_data_files
    GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM (BYTES) BYTES
        FROM dba_free_space
    GROUP BY tablespace_name) f,
     v$parameter p
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
  AND p.name like '%instance%name%'

To be Continued... (growth patterns)

Subscribe to Oracle Database Disected by Email