I need your vote for OracleOpenWorld!!!

Hi folks!!!

I’m planning to attend Oracle OpenWorld 2008 as a speaker, therefore I will appreciate your kind support for these proposals Capacity Planning with Oracle products in 1 day and 11g Advanced Compression unleashed.

I’m counting on your vote to get one of my proposals approved.
Thank you very much!

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 pWHERE 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)

Please, before you go, don’t forget to vote the poll regarding the content of this blog, thank you!