Home » RDBMS Server » Server Administration » Capacity Planning – Responses Highly Appreciated
Capacity Planning – Responses Highly Appreciated [message #56500] Thu, 03 April 2003 21:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #56513 is a reply to message #56508] Fri, 04 April 2003 08:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Agreed~
PCT_FREE is better that PCT_USED.
Thanks for the input
Re: Capacity Planning – Responses Highly Appreciated [message #56515 is a reply to message #56508] Fri, 04 April 2003 09:49 Go to previous message
Raim
Messages: 4
Registered: April 2003
Junior Member
Thanks Mahesh and Sanjay. I appreciate your help. I dont 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
Previous Topic: moving data from an oracle 7 db to a 9i
Next Topic: move db to new server
Goto Forum:
  


Current Time: Wed Apr 17 08:19:18 CDT 2024