?
Solved

Properly sizing the extents

Posted on 1998-04-12
4
Medium Priority
?
707 Views
Last Modified: 2011-10-03
We have a Oracle database(7.3.2.1)  with db_block_size 8k on Sun UltraSparc machine(Solaris Ver 2.6).
I created a tablepsace with the default storage parameter  'initial extent'  as 64K(ie  8 db blocks). But the objects created in the tablespace takes up 80K(ie 10db blocks) for its initial extent. I created the object in the tablespace by explicitly specifying the storage parameter for the object.(with initial extent size as 64K). This also creates extents with 80K.
How to prevent the Oracle from doing this rounding and get the correct extent size which I need?
0
Comment
Question by:srikumar_p
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
xiaodong earned 150 total points
ID: 1083058
When Oracle allocate new extents, the smallest increment is 5 DB blocks, i.e., for a database using 2K block, the increment is 10K, 4K, it's 20K. Thus, in your case, it's 80K(2x5=10 DB blocks) instead of 64K as you specified.  
Specifying a bigger block size as VS. the default 2K size will yield better performance but at the expense of a little waste of space.  If you can live with that 16K 'waste', then do nothing, otherwise, recreate your database using a smaller block size.
If I were you, I will do nothing.  Since the 'waste' will not really be wasted in the long run.
0
 

Author Comment

by:srikumar_p
ID: 1083059
Thanks xiaodong for the answer.
I don't mind wasting 16K. But the thing is I have set the db_multiblock_read_count parameter as 8. That means Oracle will read 64K in one read.(8*8Kb) If my extent size is 80K this will result in 2 reads(during full table scans)- one for the first 64K and the second one for remaining 16K. These reads can't span extents. So this will reduce the preformance as two reads are required for an extent. Is it advisable to set the db_multiblock_read_count to 10?

Then, I don't think the smallest increment is 5 DB blocks. Because I am able to create the extent with 4 DB blocks(ie 32KB in my database). But any thing above 4 it rounds to 10.
0
 
LVL 2

Expert Comment

by:xiaodong
ID: 1083060
db_multiblock_read_count really depends on the file I/O on the O/S.  I believe most UNIX systems allows at most 64K read in one I/O operation.  So this parameter should always set to take advantage of the O/S capability.  In your case, 8 is fine.  If you set it to 10, you will see no gains at all.
Secondly, if you can show me any proof(such as select from dba_segments) that you are able to create an extent of 32K in a database using block size of 8K, I will be very surprised.
0
 

Author Comment

by:srikumar_p
ID: 1083061
Hi xiaodong,
Thanks for your suggestions.
 For the 32K extent using 8K db block size - you can see the commands I cut and pasted from
sql*plus
SQL> create table emp (empno integer, empname varchar2(20))
  2  storage (initial 32k minextents 1);

Table created.

SQL> select * from user_extents where segment_name = 'EMP';

SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- -----------------
TABLESPACE_NAME                EXTENT_ID     BYTES    BLOCKS
------------------------------ --------- --------- ---------
EMP                                                                               TABLE
DATA_TBSP                              0     32768         4


SQL>

and the db_block_size is 8K- trust me :-)

By the way, can I know more about you? Where are you working? What environment etc. etc.
My mail id is kumarp@wipsys.soft.net. You can mail to me directly rather than through the web.

The web is very slow during our office hours. It is World Wide Wait(www) really.

Thanks again.

0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question