block utilization parameters
A reader, July 15, 2002 - 1:59 pm UTC
are you concerned about these in LMT
July 15, 2002 - 8:39 pm UTC
if you mean pctfree and pctused -- yes, unless you are using the new 9i feature of automatic segment space management, then only pctfree counts.
Thank you for the guidance.
Yogeeraj, July 16, 2002 - 1:26 am UTC
Hello,
Thank you for your guidance.
Please clear my last doubts.
A.
I have 5 new tables to create t1, t2, t3, t4 and t5 which i predict to take about 15M, 84M, 200K, 54M and 40K of space over the next 2 years.
Will it be wise to create tables (t1,t2,t4) on the same tablespace (tbs_lmt_medium - 1024K uniform extents) ignoring their sizes?
I will be modifying my scripts to look as follows:
===========================================================
create table RFT860 (
STKCAT varchar2(2) /*Stock Category */
constraint NN_RFT860_STKCAT
NOT NULL,
TRNTYP varchar2(2) /* Transaction */
constraint NN_RFT860_TRNTYP
NOT NULL,
SEQCOD number(2) /* Sequence */
constraint NN_RFT860_SEQCOD
NOT NULL,
STKDESC varchar2(15), /*Stk description */
LCNCOD varchar2(3), /* Unit */
LSTVAL varchar2(6), /* last value*/
CHRCOD varchar2(6), /* Character Generation*/
STATUS varchar2(1), /* Active sts*/
RECDAT date, /*Sysdate*/
RECID varchar2(4), /* user id*/
constraint PK_RFT860
primary key (STKCAT,TRNTYP,SEQCOD)
Using Index Tablespace PFS_DMT_DATA_MEDIUM
Pctfree 5
)
Tablespace PFS_DMT_DATA_MEDIUM
Pctfree 5
Pctused 90
Initrans 1
Maxtrans 10
;
===========================================================
B.
How many tablespaces would you recommend on a typical OLTP?
------------------------------------------------------------
Uniform Size Autoextend Striped
Extent Maxsize over disks
------------------------------------------------------------
tbs_lmt_small 64K 10M 50M d1,d2,d3
tbs_lmt_medium 1024K 100M 1024M d2,d3,d4
tbs_lmt_large 10M 1024M 2048M d5,d6,d7
------------------------------------------------------------
I had 10 rather loosely connected Modules, can i review them to the following?
------------------------------------------------------------
Uniform Size Autoextend Striped
Extent Maxsize over disks
------------------------------------------------------------
M1_tbs_lmt_small 64K 10M 50M d1,d2,d3
M1_tbs_lmt_medium 1024K 100M 1024M d2,d3,d4
M1_tbs_lmt_large 10M 1024M 2048M d5,d6,d7
-----------
M2_tbs_lmt_small 64K 10M 50M d5,d6,d7
M2_tbs_lmt_medium 1024K 100M 1024M d2,d3,d4
M2_tbs_lmt_large 10M 1024M 2048M d1,d2,d3
-----------
M3_tbs_lmt_small 64K 10M 50M d2,d3,d4
M3_tbs_lmt_medium 1024K 100M 1024M d5,d6,d7
M3_tbs_lmt_large 10M 1024M 2048M d6,d2,d3
------------------------------------------------------------
Thank you for your precious time and guidance
Best Regards
Yogeeraj
July 16, 2002 - 7:24 am UTC
a) sure
b) as many as you can afford. as many as you need. If only OLTP meant exactly the same thing to every person -- i mean the same amount of data, some number of users, same number of transactions, same transaction size, etc
Thank you. Some people will be Delighted about this ;)
Mauritius, July 16, 2002 - 7:45 am UTC
Thank you.
My collegue software developers will be delighted about this. They will be relieved from the burden of spending hours calculating/sizing tables...
thanks a lot
Best Regards
Yogeeraj
Data and Index Tablespace
Hien Nguyen, August 06, 2003 - 6:49 pm UTC
Hi Tom,
You quote "I would suggest you LEAVE THE STORAGE PARAMETERS OFF. If you are going with a
simple "index tablespace" and "data tablespace" -- you might just as well go
with "a single tablespace" and put everything in it (no performance gains to be
had by separating the two structures -- none)."
My question is if data and index tablespace are on different spindles, is there any performance gain at all? If so how much?
August 09, 2003 - 11:51 am UTC
no, none.
your goal -- achieve even IO distribution.
your method -- up to you
index on one, data on another -- poor way to achieve it.
raid striping -- good way to achieve it.
The original goal of index on /dev1, data on /dev2 (since lost in the mists of time, so now it is just a ritual of the religion that is "database") was to SPREAD IO OUT. "Hey, we read an index to read data. If we have multiple users -- we can get some reading indexes over there and others reading data over here".
It never made sense in the manner of "it leaves the disk head where it was (that 'spindle' argument"). The heads never stayed there anyway -- that and an INDEX is a data structure made up of single blocks scattered ALL OVER the place -- hence every index read moved the heads anyway. Also, if you are using an index to read a table -- IT TOO is using single block IO all over the place -- so, it didn't leave the heads anywhere either.
OFA & Parallel Processing
Hien Nguyen, August 11, 2003 - 11:28 am UTC
So OFA is strictly for even IO distribution? It has nothing to do with "maximizing Oracle parallel processing architecture"?
Oracle does not parallel processing the index and data read?
Let raid controller does the parallel read/write by stripping?
If I have 4 disks, it better to do 0+1 and put both data and index on it than have 2 mirrors and put data on one and index on the other?
If I have 8 disks, it better to do all 0+1 and put both data and index on it than have 2 0+1 and put data on one and index on the other?
Sorry for too many questions, I don't have the tools to measure which is the best configuration. Maybe Oracle has some data that we can use as guideline?
August 11, 2003 - 1:41 pm UTC
using an index to access data is by its very nature a sequential process
step a) read index to find interesting leaf nodes
step b) take the rowid from leaf nodes and access table data
you will most likely get the most even IO distribution by simply striping the disks -- rather then attempting to segregate data.
Dumb Question
A reader, August 11, 2003 - 11:16 pm UTC
Which system view tells us the type of tablespace whether LMT or DMT
August 12, 2003 - 8:22 am UTC
ops$tkyte@ORA920> desc dba_tablespaces
Name Null? Type
-------------------------- -------- ------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
ops$tkyte@ORA920> select extent_management from dba_tablespaces;
EXTENT_MAN
----------
LOCAL
LOCAL
LOCAL
LOCAL
.....
dmt to lmt
karthik, September 17, 2003 - 8:30 am UTC
Please tell me segment_space_management column in dba_tablespaces;
karthik
September 17, 2003 - 11:47 am UTC
tell you what?
seggregate date or stripping disks
A reader, September 17, 2003 - 4:18 pm UTC
hi tom,
I thought it's more efficient to separate data from index using 2 disks controllers in a environment with a lot of concurrent database access, even if looking to index then getting data is a serial process. Do you mean this kind of configuration is a "myth" as well?
pito
September 17, 2003 - 5:55 pm UTC
how about this -- stripe the two disks. now you have achieved nirvana -- even IO across your devices.
that is what "index over there" and "data over here" was an attempt to do -- balance io out.
striping would be infinitely better then separate index from data.
Size of uniform size
Pravesh Karthik from chennai, October 12, 2004 - 9:04 pm UTC
Tom,
What determines the size of "extent management local uniform size XXX."
I am confused on what to decide for my tablespace.
Thanks,
Pravesh Karthik
October 13, 2004 - 7:50 am UTC
you need to say "i would like my objects to be in no more than about M extents".
Then you divide.
If your biggest segment in that tablespace is to be about 10gig
And you say "i want no more than 500 extents" (for whatever reason), you would pick 10gig/500 as your extent size.
You determine what size you would like to use, it is generally based on the estimated size of the objects that will be in the tablespace.
Excellent !!
Pravesh karthik from chennai, October 13, 2004 - 10:41 am UTC
Thanks a always
Pravesh karthik