Search BC Oracle Sites! Oracle Capacity planning tips Oracle Tips by Burleson Consulting Reliable capacity planning for Oracle The easiest way to perform Oracle capacity planning is with the Oracle.

Oracle Database Capacity Planning How to Scientifically start doing Capacity Planning for an Oracle Database AIOUG Tech Day @ Pune Date: 28th July, 2012. There is not too many resources for Oracle capacity sizing and. Oracle Capacity planning and Sizing. » Re: Oracle Capacity planning and Sizing Spreadsheets. Capacity Planning for Database Objects. Oracle Database provides two ways to plan capacity for database. The size of a database table can vary greatly depending.

Determining the future size of Oracle tables, the size of Oracle indexes, and the overall tablespace size is a formidable challenge which depends on many factors: - Your blocksize (db_block_size) - How many rows will fit into a data block? - Your PCTFREE - At what point do you move a data block off of the freelist? - Your average row length - What is your avg_row_len in dba_tables?

- How are you implementing your freelists (i.e. Using ASSM (bitmap freelists))? - And many, many other internal factors that effects the total size of tablespaces, tables and indexes. Oracle capacity planning generally involves these decisions - Get tablespace sizing computations. - See Oracle table sizing rules and index sizing spreadsheets. - Get a framework for Oracle capacity planning. - Get SGA sizing calculations.

Most professionals create spreadsheets for accurate Oracle capacity planning but they are quite complex and time-consuming. Again, we recommend downloading our Oracle. Oracle Data file Capacity planning Datafiles should be sized based on the size of the objects that will store segments in their related tablespaces. To this end, before tablespace and datafile sizing can be accomplished the size of tables and indexes contained in them must be determined.

The Oracle DBA manual contains sizing procedures allowing a DBA to manually calculate sizing for both tables and indexes, however it is suggested that spread sheets be obtained or created to automate and standardize the calculations. The table sizing spreadsheet sonly requires the input of the table column sizes in the appropriate column (character, date and number) and the input of table specific items that deal with size such as initial transactions, percent free in a block, and number of rows. Additionally block size is specified.

If the table is clustered then that too is taken into account to give a size estimate. Using the index sizing spreadsheet shown above the only required inputs are the size of the columns to be included in the index, initial transactions, percent free, blocksize and number of rows expected. Guitar Sound Samples Free Download.

If the index is a non-unique index, you can also enter the expected percent of time the index column(s) will contain data. Using standardized spreadsheets ensures that the calculation is performed identically and with repeatable results, it also allows for tweaking of the calculation until after the third or fourth time it is used it should be giving accurate estimates based on your system. Once all of the tables and indexes are sized for the first year?s data (or whatever time interval you desire) add the total sizes together for each object in each tablespace. These totals give the amount of space required for current data up to and including estimates for this year?s growth, usually you double it to allow for an additional year. These adjusted totals become the sizes for the tablespaces.

If the estimated sizes are too large for either operating system file size limits or available space, multiple datafiles will be required for that tablespace. Also, by figuring out the total physical size for the database you can estimate the SGA base parameters such as DB_BLOCK_BUFFERS by use of a spread sheet. Temporary tablespaces are used to hold user?s sort segments when a sort operation exceeds the size of the memory allocated by the SORT_AREA_SIZE initialization parameter. Sorts to disk are hundreds of times slower than RAM sorts and so should be avoided if possible.

Always assign a user a temporary tablespace, if one is not assigned the temporary tablespace defaults to the SYSTEM tablespace. The sizing of the temporary tablespace is an empirical calculation involving assumed transaction sizes that involve sorts. Usually I set temporary tablespace storage at one megabyte initial and next and a percent increase of zero. I also set the initialization parameter SORT_AREA_SIZE to one megabyte and SORT_AREA_RETAINED_SIZE to one hundred kilobytes. Usually these values are enough, but if you have a sort intensive application you may have to increase the size of the sort area to reduce disk sorts.

Any change in sort area size should also mean a change in the temporary tablespace initial and next extent sizes. The new views V$SORT_SEGMENT and V$SORT_USAGE should help in determining the proper settings for sort area size and the temporary tablespaces. I suggest having multiple temporary tablespaces each serving a subset of users if your environment does heavy sorting, these multiple temporary tablespaces should be as physically separate as possible. Capacity planning for UNDO Segments UNDO segments are used to store information on changed rows until those rows are rolled back or committed. If a rollback occurs, either from manual initiation using a ROLLBACK command or due to abnormal termination of a user process due to errors or failures, the data in the UNDO segment is used to restore the database to the state before the transaction began. The care and feeding or UNDO segments is another empirical process. A few general observations: 1.

If your users are doing heavy DML operations make sure there are enough properly sized rollback segment extents to allow each user performing DML to have an UNDO segment extent without waiting. If you expect heavy DML loads, have multiple UNDO tablespaces spread across multiple disk arrays. Set the default sizing in a UNDO tablespace such that you can simply issue a CREATE ROLLBACK SEGMENT command specifying the tablespace and you don?t have to worry about the STORAGE specification. Try to limit the number of simultaneous users per UNDO to four. Size rollback segments such that: a. INITIAL=NEXT=size of average transaction b. OPTIMAL=size of average large transaction (rounded up to the next NEXT value) c.

MINIMUM EXTENTS = OPTIMAL/INITIAL. For large batch transactions create special large extent rollback segments in a separate tablespace from the other rollback segments, only bring them online when needed and use SET TRANSACTION command to assign them to specific transactions.

Try to avoid running large batch transactions simultaneously with OLTP or smaller transactions. Generally you won?t know the size of the average transaction, average large transaction or number of simultaneous DML users until the application has been running for a while. Use the DBA_ROLLBACK_SEGS, V$ROLLSTAT and V$ROLLNAME views to calculate the average values. UNDO segments are assigned in a round-robin methodology. Each user gets assigned one extent to begin with which is used in a circular fashion if possible. When a user overwrites his own data it is called a WRAP.

If the users data needs exceed the size of one UNDO extent then a second is assigned, this is called an EXTEND. When a user forces a rollback segment to extend beyond the OPTIMAL setting for that rollback segment, the next user to use the rollback segment must wait for the rollback segment to go back to the OPTIMAL setting, this is called a SHRINK.

When a WRAP, EXTEND or SHRINK occurs this results in an UNDO WAIT (either block or header) condition. A WRAP causes a very minor wait, an EXTEND into a newly assigned extent a slightly more expensive wait and the SHRINK the most expensive wait. By properly sizing rollback segment extents virtually all waits will be of the lower expense WRAP and EXTEND type and not the SHRINK type.

Ideally all UNDO WAIT conditions should be caused by WRAP situations. This is an excerpt from the eBook '. For more details on Oracle database administration, see the ' by Robert Freeman and Steve Karam. It?s only $19.95 when you buy it directly from the publisher.

Also see my collected notes on Oracle data file and tablespace management. • • • • • • • • • • • • • • • �� Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.

All legitimate Oracle experts publish their. Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.

Burleson Consulting The Oracle of Database Support Copyright © 1996 - 2017 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation. Dell Monitor Drivers Mac Os X. Remote Emergency Support provided.