| Capacity Planning – Responses Highly Appreciated [message #56500] |
Thu, 03 April 2003 21:06  |
Raim
Messages: 4
Registered: April 2003
|
Junior Member |
|
|
Hello,
We are trying to do Capacity planning for our oracle 9i database,
But we have no budget to buy tools like Enterprise Manager's Capacity
Planning pack etc.
Please does anyone have something like a home grown tool that
could be used for this purpose. Ideally I will appreciate a tool that
can capture
1. Growth in tablespaces
2. Growth in key tables
and be able to display that over time, at least historically and (ideally) as a forward projection.
Best option will be a tool that can captured data regularly and somehow be able to graph it.
If the above tool is not available.
A SQL code, procedure etc that can capture the above information (capacity planning info)
and store it in a table in the database will be equally appreciated.
Thanks
Raim
|
|
|
|
| Re: Capacity Planning – Responses Highly Appreciated [message #56508 is a reply to message #56500] |
Fri, 04 April 2003 06:20  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Is a report something like this OK for you?
This is a part of my database report which is generated as an html output.
IT DOESNT INLCUDE GROWTH RATE in key tables.(Actually,
this tool is still in development. I was also considering to capture the growth rate of selected tables). Right now it needs to executed as an sql script or procedure. I am looking into converting this with more scalable features so as the results can be directly published to our web or email it to the requestor~
Oracle username : DBADMIN
OS usernme : magvivek
Report Generated from m/c : CCDOMITLOANER1
Using :
dated : 04-Apr-2003 07:46
--------------------------------------------------------------------------------
INSTANCE
--------------------------------------------------------------------------------
DB Name : CHIPDB2
Date created : 06/22/01
Mode : NOARCHIVELOG
[b]Database Uptime[/b]
Host Name : biochip
Instance Name : chipdb2
Uptime : 0 days 3 hours 9 minutes
[b]OBJECTS / SCHEMA [/b]
Owner Tables Indexes Views Sequences Synonyms Clusters
--------------- -------- -------- -------- --------- -------- --------
CHIPDB2ADMIN 77 71 0 1 0 0
DBADMIN 2 0 0 0 0 0
DBSNMP 0 0 0 0 4 0
GWLA_RESULTS 1 0 0 0 0 0
OUTLN 2 3 0 0 0 0
PUBLIC 0 0 0 0 1,063 0
SYS 174 187 1,260 17 6 9
SYSTEM 48 69 3 11 8 0
[b]SOURCE / SCHEMA [/b]
Owner Procedures Packages Pckg Bodies DB Links
--------------- ---------- -------- ----------- --------
CHIPDB2ADMIN 0 0 0 0
DBADMIN 0 0 0 0
DBSNMP 0 0 0 0
GWLA_RESULTS 0 0 0 0
OUTLN 0 0 0 0
PUBLIC 0 0 0 0
SYS 10 206 201 0
SYSTEM 1 1 1 0
[b]Database growth rate [/b]
MONTH_YEAR GROWTH_IN_MEG
-------------- -------------
January 2003 200
July 2002 1,536
June 2001 2,925
May 2002 1,500
November 2002 6,473
October 2002 50
-------------
sum 12,684
Size of the database : 12,684.63 MB
Free space : 7,532.75 MB
Used space : 5,149.77 MB
[b]TABLESPACE USAGE[/b]
TABLESPACE TOTAL_MB USED_MB FREE_MB PCT_USED GRAPH
------------------------------ ---------- ---------- ---------- ---------- --------------------
CHIPDB2RBS01 125 10.95 114.05 8.76 *
GWLA_RESULTS_DATA 100 2 98 2
GWLA_RESULTS_IDX 100 1 99 1
HUMANDATA 1536 .01 1535.99 0
LARGEDATA 9144 4352.27 4791.73 47.6 *********
MEDIUMDATA 500 273.6 226.4 54.72 **********
SMALLDATA 50.63 1.7 48.93 3.36
SYSTEM 629 510.34 118.66 81.14 ****************
TEMPORARY 500 .01 499.99 0
|
|
|
|
| Re: Capacity Planning – Responses Highly Appreciated [message #56510 is a reply to message #56508] |
Fri, 04 April 2003 07:13 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Neat output report.
Just a few comments:
1. How do you plan to save the 'Database growth rate' and show for the last 12/18 months ?
2. In the 'tablespace usage', it would be better to show 'PCT_FREE' than 'PCT_USED'. Reason being this report viewed with a maintenance perspective.
Here is a part of my weekly report.
SUMMARY DATABASE REPORT
Mon, 10 Mar 2003, 09:00 AM
****************************************
GENERAL INFORMATION
===================
DB Instance: rcat
DB Start Time: Thu, 27 Feb 2003, 01:02 AM
Version: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
Host Name: nrisp2
Host OS: AIX
Archive Log Mode: OFF
INIT.ORA SETTING
================
Shared Pool Size = 10485760 Bytes
DB Block Buffer = 32768 Blocks
Log Buffer = 32768 Bytes
Sort Area Size = 1048576 Bytes
CONTROL FILES
=============
/u02/oradata/rcat/control01.ctl
/u02/oradata/rcat/control02.ctl
/u02/oradata/rcat/control03.ctl
/u02/oradata/rcat/control04.ctl
TABLESPACE USAGE
================
Name (1) Allocated MB Used MB % Free MB %
----------------- ------------- -------------------- --------------------
RBS 100.00 8.21 8 % 91.79 92 %
RMAN_DATA1 50.00 12.02 24 % 37.98 76 %
SYSTEM 100.00 46.53 47 % 53.47 53 %
TEMP 100.00 .00 0 % 100.00 100 %
TOOLS 25.00 4.89 20 % 20.11 80 %
----------------- ------------- -------------------- --------------------
TOTAL 375.00 71.65 19 % 303.35 81 %
----------------- ------------- -------------------- --------------------
DATA FILES
==========
Tablespace (1) Data File Name (2) Bytes (MB)
--------------- --------------------------------------------- ------------
RBS /u02/oradata/rcat/rbs01.dbf 100.0
RMAN_DATA1 /u02/oradata/rcat/rman_data101.dbf 50.0
SYSTEM /u02/oradata/rcat/system01.dbf 100.0
TEMP /u02/oradata/rcat/temp01.dbf 100.0
TOOLS /u02/oradata/rcat/tools01.dbf 25.0
--------------- --------------------------------------------- ------------
LOG FILES
=========
Group (1) Log File Name (2) MB
------------ --------------------------------------------- ---------------
1 /u02/oradata/rcat/log01a.log 1.0
1 /u02/oradata/rcat/log01b.log 1.0
2 /u02/oradata/rcat/log02a.log 1.0
2 /u02/oradata/rcat/log02b.log 1.0
3 /u02/oradata/rcat/log03a.log 1.0
3 /u02/oradata/rcat/log03b.log 1.0
4 /u02/oradata/rcat/log04a.log 1.0
4 /u02/oradata/rcat/log04b.log 1.0
------------ --------------------------------------------- ---------------
ROLLBACK SEGMENTS
=================
Segment (1) Init KB Next KB Min Ext Max Ext % Incr Status
-------------- -------- --------- --------- --------- --------- ------
RBS01 100 100 2 1000 0 ONLINE
RBS02 100 100 2 1000 0 ONLINE
RBS03 100 100 2 1000 0 ONLINE
RBS04 100 100 2 1000 0 ONLINE
SYSTEM 52 1,024 2 121 0 ONLINE
-------------- -------- --------- --------- --------- --------- ------
|
|
|
|
|
|
| Re: Capacity Planning – Responses Highly Appreciated [message #56515 is a reply to message #56508] |
Fri, 04 April 2003 09:49 |
Raim
Messages: 4
Registered: April 2003
|
Junior Member |
|
|
Thanks Mahesh and Sanjay. I appreciate your help. I don’t know if you happen to have the SQL that generated those reports especially the Database growth rate and TABLESPACE USAGE reports. A definition of the table structure and the kind of data used to generate the report will be nice.
If my questions sound off beat, it is just because I have not done this before. It is getting much clearer as I go along.
Thanks for all your help.
Phraim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
