Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Manoharan.

Asked: December 10, 2003 - 4:18 am UTC

Last updated: February 27, 2024 - 5:08 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I have a some questions regarding the capacity planning.Thanks in advance. there any way we can match LIOs & PIOs to the no of CPUs & no of disks ? there any place , i can find documents to do capacity planning for the oracle database/sun solaris environment?

3.I am very much confused about the sort_area_size
my understanding is
-- sort_area_size is the max threshold to do sort on memory and only one only sort_area_size per session .Alloc from UGA
--sort_area_retained is to store the result set from SAS and it can be
many per session at a it correct? Alloc from PGA.

When we do first sorting which is lesser than sort_area_size,the memory allocated from PGA or UGA? is it sort_area_size or sort_area_reatined?

Thanks in advance

and Tom said...

1) not really. not even a little. not in any way that I'm aware of at all.

2) you need to size YOUR application. oracle -- by itself, with no data - take the minimums documented in the install guide.

once you build YOUR application however, you have this totally 100% unique beast that no one else on the planet has.

You might have a cpu intensive algorithm no one else does -- or not. (and hundreds of other things)

you need to benchmark your system -- you can use modeling tools (flaky, limited if any success with outputs) or prototype it, benchmark it and size from there.

If you are buying a packaged app -- ask that vendor, they should be able to provide their guidelines.

3) sort area size is a maximum (we allocate bit by bit UP TO that threshold)

it is allocated from the PGA -- sort area retained is in the UGA -- but the UGA is normally in the UGA.

sort_area_retained is the low water mark of the sort area -- it is UGA memory (normally in the pga)


  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment


How to approach ?

A reader, July 28, 2004 - 1:20 am UTC

Hi Tom,

That was a very useful input. Was intrested in knowing what your approach would be? How to begin ? Say your manager comes and says we need to prepare something for capacity planning... Where to begin now ?

o Should we first look into the storage part ? Starting with business questions like No of bookings in a year and the based on these bookings estimate the no of rows/annum. Then calculate the size of table then tablespace based on the number of rows. Then estimate the size of the database.
o Then run some transactions and find the CPU usage and then sum up all to estimate the CPU usage etc.

I know this is big topic to be answered shortly. Just needed guildance on how to approach towards capacity planning, may be steps. Any good link to a document would be very helpful.

Thanks and Regards

Tom Kyte
July 28, 2004 - 8:11 am UTC

tell them "well, we need a good test machine and some load generation software so we can get some data points to see how the real live system will behave".

You can calculate potential worst case and best case scenarios but that is about it.

capacity/performance improvements 8i vs 9i vs 10g

Johann, March 14, 2006 - 5:44 am UTC

Hi Tom,

Thanks for this great site! Learned a lot by just doing a search and reading the posts. Although I never figured out how to post a new question - whenever I log on, status is always "please ask a question later" and I can't find a link to ask a new question. So I hope you don't mind me posting this as a followup.

I have quite a predicament here. We're currently hosting a large (well, by local standards, it is quite large), yellowpages site, which uses JDBC and 8i. For various reasons, its been stuck on 8i for years now. Management is considering investing on an upgrade, but wants to see projected performance benefits before they do. They can't wait for me to do a lab test - they said they will be satisfied with "rated" capacities.

From your experience, is there any measurable performance benefit from just upgrading databases, without changing any code? (I know, I know - To maximize the investment, we should change some code to take advantage of the new features, but for now I will have to give that as added benefit). Is there any document/case study/etc I can use?

Thanks a lot


Tom Kyte
March 14, 2006 - 11:00 am UTC

No. because no one else is using your system as their benchmark.

tell them the answer is 1.53215

it is as meaningful as their request really.


Johann, March 14, 2006 - 7:52 pm UTC

Read your post first thing in the morning. And you just made my day =D

reda, March 15, 2006 - 2:48 am UTC

helo tom
tell them "well, we need the best siez of table count rows=4000000
so you can calculate the best siez

Tom Kyte
March 15, 2006 - 4:39 pm UTC

the answer will be between 8,000,000 and infinity. That is as close as you'll get given "4,000,000 rows"

4,000,000 rows is meaningless in this context.

num of extend

reda, May 07, 2006 - 9:23 am UTC

helo tom
tell them "well, we need the best siez of table count rows=4000000
so you can calculate the best siez and plase tell me waht i do for num of extend 100 in this table

Tom Kyte
May 07, 2006 - 11:39 am UTC


extend, May 07, 2006 - 2:28 pm UTC

helo tom
i need the best siez of table count rows=4000000
so you can calculate the best siez and plase tell me waht i do for num of
extend 100 in this table

select owner "Owner",
segment_name "Segment Name",
segment_type "Type",
tablespace_name "Tablespace",
extents "Ext",
max_extents "Max"
from dba_segments
where ((max_extents - extents) >=3)
and owner ='ADMIN'
order by extents;

Segment Name Type Tablespace Ext Max
plase fast replay

Tom Kyte
May 07, 2006 - 4:16 pm UTC

I don't get the "rows=4000000" bit- are you trying to ask "how much space is this table with about 4.7 million records taking?

If you only have 100 extents, you are doing just fine, if you need to add more - no problem. 100 extents is great, so is 1,000, so is 1. They are all just fine.


A reader, May 08, 2006 - 8:41 am UTC

helo tom
how much space is
this table with about 4.7 million records taking?

If iwant only have 3 extents just
how and how tunig table ,sga
mor thanks

Tom Kyte
May 08, 2006 - 10:33 am UTC

query dba_segments, it'll tell you allocated space in bytes, blocks...

Why would you want "3" - what possible logic did you use to come up with "3".


reda, May 09, 2006 - 8:57 am UTC

hell tome
iwant Become little num of extend
Verily that i do
alter table P_INV_ITEMS move tablespace POS_TRANS;

num of extend for P_INV_ITEMS
after 39 being 1
Verily that resolve slow fro database
which the steps he felt plase hlpe me

Tom Kyte
May 09, 2006 - 9:51 am UTC


The number of extents is not affecting your runtime query performance.

How did you decipher that??

Kevin, May 09, 2006 - 10:23 am UTC

That's the worst case of stuck keyboard I've seen... ;0)


A reader, May 10, 2006 - 7:44 am UTC

hi tome
tell me
who affecting your runtime query performance
Thanks in advance

Tom Kyte
May 10, 2006 - 9:20 am UTC

everyone else using the database ?

Basic Matrics for Forcasting

Suraj Sharma, May 04, 2007 - 2:24 am UTC

Hi Tom,

First of all thanks a lot once again to shall all the knowledge you have here.

I am new into capacity planning and forecasting. Please let me know the general matrices, which we have to consider during the capacity forecasting or some questions, which we can put to customers when they come at us for the same.


capacity planning

jaypee, October 01, 2007 - 6:02 am UTC

Hi Tom,

Can you guide me to the steps for hardware estimation/capacity planning for a new oracle database (I need to know RAM(SGA) and appropriate CPU speed). The statistics I have in hand is oracle database size = 300 GB and no of transactions per day ~ 70,000.
Thanks in advance for you contributions and patience!!
Tom Kyte
October 03, 2007 - 2:45 pm UTC


the size of the database and number of transactions... not very useful.

what if 90% of the transactions happen at 3pm.
what if most of the transaction affect 300 rows (versus someone else that affects 1 row, versus someone else that affects 3000 rows)

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 0 );

1 row created.

ops$tkyte%ORA10GR2> set timing on
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 70000
  3          loop
  4                  update t set x = x+1;
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.97

given your requirements, I can apparently satisfy your needs on my laptop in about 16 seconds.

capacity planning,

A reader, May 24, 2010 - 11:42 am UTC

Using data dictionary views like DBA_HIST%, how to calculate the CPU used, Memory used, # of IOs, IO latency (in m.s) in a 3 hour interval?

Is there a way to point out which user_id (or session_id) contributed to higher CPU or any other metrics?

I can get in AWR report but I like to store them in user defined table and I can plot a graph of my choice.


Tom Kyte
May 24, 2010 - 1:13 pm UTC

averaging over many hours isn't going to be very helpful for capacity planning is it? What would it mean for capacity if you used N units of cpu in 3 hours. If you were really busy for 30 minutes and not at all busy for 2.5 hours - you would not have a very good picture of what is going on would you...

You'd want detailed (snapshots as close to together as you have) and graph those and that - that is already what EM does???

I don't have a query on hand for you - I'd just use the existing user interfaces if I wanted to look at that over time.

capacity planning,

A reader, May 24, 2010 - 1:28 pm UTC

Hi Tom,

Thanks for a quick response. EM gives the information I want. However, if I want history like going back a month or two, I thought it would be helpful to store the related information in user defined tables that way we can draw a graph as needed.

If you get time to generate SQLs (at least the list of data dictionary views required for this) and share it with us, that would be really helpful.


Tom Kyte
May 24, 2010 - 1:31 pm UTC

You can extend the amount of data that EM keeps to be as much as you want, that would be the best way - you don't have to write any code at all.

Production Server Design

Rajeshwaran, Jeyabal, December 12, 2011 - 10:50 pm UTC


We are designing an Application and below is the memory details and Explain plan for query, which executes for more than 2 hour.

a) I know that you would ask to look at Tkprof for tuning this query, but unluckly the DBA is unavailable to get the Trace files.

b) The explain plan shows temp_space as 602MB, But the PGA memory is 512MB - do you think that we need to increase this PGA and SGA memory settings?

c) Can you provide any documentation link about how to set the optimial size for PGA / SGA memory settings?

app@appdev> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1G
sga_target                           big integer 1G
app@appdev> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 512M
app@appdev> select sum(bytes)/1024 Kbytes_alloc,
  2        sum(maxbytes)/1024 Kbytes_max,
  3        tablespace_name
  4  from sys.dba_temp_files
  5  group by tablespace_name
  6  /

------------ ---------- ------------------------------
     5120000          0 TEMP

Elapsed: 00:00:00.36

app@appdev> exec show_space(user,'IDX_STG_INBOUND','INDEX PARTITION','P_4');
l_total_blocks****************  13216
l_total_bytes*****************  108265472
l_unused_blocks***************  0
l_unused_bytes****************  0
l_last_used_extent_file_id****  6
l_last_used_extent_block_id***  412800
l_last_used_block*************  928
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  5
l_fs2_bytes*******************  40960
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  13055
l_full_bytes******************  106946560

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
Execution Plan
Plan hash value: 682711079

| Id  | Operation                   | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT            |                 |  2699K|   502M|       |   367K  (1)| 01:13:35 |       |       |
|   1 |  WINDOW SORT                |                 |  2699K|   502M|   602M|   367K  (1)| 01:13:35 |       |       |
|   2 |   WINDOW SORT               |                 |  2699K|   502M|   602M|   367K  (1)| 01:13:35 |       |       |
|   3 |    WINDOW SORT              |                 |  2699K|   502M|   602M|   367K  (1)| 01:13:35 |       |       |
|   4 |     WINDOW SORT             |                 |  2699K|   502M|   602M|   367K  (1)| 01:13:35 |       |       |
|*  5 |      VIEW                   |                 |  2699K|   502M|       | 23095   (1)| 00:04:38 |       |       |
|   6 |       WINDOW SORT           |                 |  2699K|    64M|    93M| 23095   (1)| 00:04:38 |       |       |
|   7 |        PARTITION LIST SINGLE|                 |  2699K|    64M|       |  3557   (1)| 00:00:43 |   KEY |   KEY |
|   8 |         INDEX FAST FULL SCAN| IDX_STG_INBOUND |  2699K|    64M|       |  3557   (1)| 00:00:43 |    56 |    56 |

Predicate Information (identified by operation id):

   5 - filter("CLM_RNK" IS NOT NULL)

app@appdev> select tablespace,segtype,segfile#,segblk#,extents,blocks
  2  from v$tempseg_usage;

------------------------------- --------- ---------- ---------- ---------- ----------
TEMP                            LOB_DATA        1502     287872          1        128
TEMP                            SORT            1502     284672        119      15232
TEMP                            DATA            1502     283520          1        128
TEMP                            LOB_DATA        1502     283392          1        128
TEMP                            INDEX           1502     283264          1        128
TEMP                            LOB_DATA        1502     283136          1        128

6 rows selected.

Elapsed: 00:00:00.40
app@appdev> select event#,event,p1text,p1,p2text,p2,p3text
  2  from gv$session
  3  where osuser ='testuser'
  4  /

EVENT# EVENT                          P1TEXT                             P1 P2TEXT                    P2 P3TEXT
------ ------------------------------ ------------------------------ ------ -------------------- ------- ----------
   347 SQL*Net message to client      driver id                      ###### #bytes                     1
   197 direct path read temp          file number                      1502 first dba             267591 block cnt
   351 SQL*Net message from client    driver id                      ###### #bytes                     1
   351 SQL*Net message from client    driver id                      ###### #bytes                     1

Elapsed: 00:00:00.20

Tom Kyte
December 13, 2011 - 7:17 am UTC

the temp area referenced in a plan is a total guess. It is based on the estimated cardinality and a 'generic' guess at how much memory might be available at runtime (which might not actually be available at runtime...)

if your DBA is "unavailable", you won't be able to fix anything - suggest you make them "available"

Then they, as the DBA, would know to review an AWR or statspack report to see what the PGA advisor says - it would be the thing that would tell you if increasing the PGA settings would reduce single and multi-pass sorts to disk.

Production Server Design

Rajeshwaran, Jeyabal, December 15, 2011 - 1:55 am UTC


This is what is see in Development machine.

1) How the workarea_size_policy becomes AUTO when pga_aggregate_target = 0. this doesn't match with Oracle doc quotes. We have not changed any parameter setting in Development machine, we just created a database by accepting defaults.

2) I am unable to understand the quotes from AWR PGA Advisor When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

Is that something i have to set the pga_aggregate_target=86MB (based on AWR PGA Memory Advisory) to achieve better performance? is that my understand is correct? please confirm.

rajesh@ORA11GR2> show parameter workarea;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
workarea_size_policy                 string      AUTO
rajesh@ORA11GR2> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
pga_aggregate_target                 big integer 0

Oracle docs provide's this
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL

Running the above query in Development database and PGA Advisor from AWR shows this.

PGA Memory Advisory                     DB/Inst: ORA11GR2/ora11gr2  Snap: 3247
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd P Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/    Cache Overallo    Estd
  Est (MB)   Factr        Processed Written to Disk   Hit %    Count    Time
---------- ------- ---------------- ---------------- ------ -------- -------
        22     0.1            405.6            535.8   43.0        5 1.4E+06
        43     0.3            405.6            132.4   75.0        1 7.7E+05
        86     0.5            405.6            132.4   75.0        0 7.7E+05
       129     0.8            405.6            132.4   75.0        0 7.7E+05
       172     1.0            405.6            132.4   75.0        0 7.7E+05
       206     1.2            405.6            132.4   75.0        0 7.7E+05
       241     1.4            405.6            132.4   75.0        0 7.7E+05
       275     1.6            405.6            132.4   75.0        0 7.7E+05
       310     1.8            405.6            132.4   75.0        0 7.7E+05
       344     2.0            405.6            132.4   75.0        0 7.7E+05
       516     3.0            405.6            132.4   75.0        0 7.7E+05
       688     4.0            405.6            132.4   75.0        0 7.7E+05
     1,032     6.0            405.6            132.4   75.0        0 7.7E+05
     1,376     8.0            405.6            132.4   75.0        0 7.7E+05

Tom Kyte
December 16, 2011 - 6:15 am UTC

it would be using manual at runtime since there is no setting for the pga aggregate target.

it should not be zero, why are you using manual pga memory management.

Capacity Planning

Parag Patankar, February 02, 2012 - 1:36 am UTC

Hi Tom,

Somebody as me a generic question, that currently there is mutual funds system running on oracle 10g having approx 1500 users, in month of May planning to migrate on a new server expecing load of 5000 users. Before migrating they want to ensure that they have done their home work "very well" and ensure it is a successful activity.

1. Can you suggest what are the thing look into as a Capacity Planning ?

2. From Oracle DB side, what are things are "must" to do study ?

Pl suggest

thanks & regards

Tom Kyte
February 02, 2012 - 7:50 am UTC

they should benchmark it, simulate 5000 users on the new hardware. That is the only way I know to do something like this.


Parag J Patankar, February 02, 2012 - 11:12 pm UTC

Hi Tom,

Agree to do benchmarking. But can you guide us in detail how to do benchmarking specially in terms of hardware and oracle DB point of view

Tom Kyte
February 03, 2012 - 9:59 am UTC

you get representative hardware

you program a simulation, something that simulates as close as possible what you are going to be doing in real life

you run it and measure, you probably run it many many many times with different user loads - to get many data points.

will it involve a lot of work?

will it be fairly difficult?

will it take a significant amount of time?
only if you do it right

do we need to do it?
only if you want to really know what probably will happen

capacity planning

Arvind, April 26, 2013 - 1:23 pm UTC

Hi Tom,

Can you please give me some links and may be some book names which are good to learn Oracle capacity planning.



More automated approach to Capacity Planning is available

Steve Lemme, February 27, 2024 - 12:00 am UTC

When I was a production DBA I spent countless hours trying to find a repeatable way to perform monthly and quarterly capacity planning, but I didn't have the hardware onsite or time to do it. Years later and using the cloud, DBAs able able to do so much more using Oracle Operations Insights. and there is an Oracle LiveLabs workshop to even try it out
Connor McDonald
February 27, 2024 - 5:08 am UTC

Thanks for stopping by Steve

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library