ORACLE APPLICATIONS ARCHIVES
Topicwise collection of
Postings on Mail Lists
ON
DBA - Memory Management
Rollback Segments' size
Date: Fri, 02 Jul 1999 07:59:03 +0500
From: S V MANSATA kunal@blr.vsnl.net.in
To: oraapps-dba@cpa.qc.ca
Subject: why do rollback segments need to be of the same size
hi all
i would like to know what happens if rollback segments are not of the same
size, how would this affect performance, what has optimal got to do with
the size of rollback segments. what exactly is mirroring and multiplexing
my understanding is as follows
by just having two or more members in a log group it is multiplexing and by
having those members on different disks it is mirroring
any help ..
thanks in advance
promeet s mansata
Date: Fri, 2 Jul 1999 11:31:47 +0100
From: Philip West Philip.West@exco.co.uk
Subject: RE: why do rollback segments need to be of the same size
You have two questions here it seems: Rollback and Redo.
I would suggest that you get a book to read up on the why of everything.
The DBA handbook from Oracle Press is a good start.
anyway, my own immediate thoughts (open to criticism/ciomment) are:
Rollback.
The rollback tablespace should hold nothing but rollback segments.
To prevent fragmentation (as rollback segments extent and then contract back
to optimal) size all rollback segments with the same initial and next extent
size.
Next prevent the database from automatically coalescing freespace in the
tablespace by setting its pctincrease to zero. This will aid database
performance. Over time you will end up with a tablespace full of uniformly
sized chunks ready for any segment into extend into.
It is best to size rollback extents so that most single database
transactions can fit into a single extent.
Have min_extents set to allow larger transactions to complete without
forcing the segment to dynamically extend or risk 'snapshot too old' errors.
Also allow that more than one transaction will use a segment.
Have enough segments so that you limit the number of transactions in any
segment.
Ideally have enough segments so that only one transaction will use any one
segment at a time.
Have enough 'slack' space in the tablespace so that rollback segments can
extend beyond there minextents if necessary.
Set optimal so that they can shrink back - otherwise over time all your
segments will grow and you either have to allocate a massive tablespace
(waste of space) or run out of space at the end of a large transaction (a
bit annoying)
Lastly if you do have regular VERY BIG transactions, and access to the code,
create a separate rollback tablespace and put a BIG rollback segment in it;
then use the 'set transaction use rollback segment' command in the code. If
you dont have this luxury then you need to size your 'normal' rollback
tablespace accordingly.
These statements may seem a bit contradictory but DBA'ing is an art form and
not a science.
Set up your rollback at a best guess and then view v$waitstat and v$rollstat
to guage contention.
A typical small database (say 50 concurrent users) may have 10 rollback
segments, 4Mb extent size and minextents of 10. As I said this is not cast
in bronze and another dba might come along and decide to change this to 8
segments, 2Mb extents and minextents of 12. You have to use your judgement,
the perfomance tables and the space available and then give it your best
shot.
Redo logs:
Redo logs are critical to the recovery of the database. No redo logs - no
recovery from failure. Of course archive redo is what is really needed -
otherwise you are only likely to be able to recover to your last full cold
backup should recovery be necessary.
Because redo logs are critical we want to keep copies of them. This can be
done in two ways:
Oracle allows you to multplex redo using log groups as you rightly said. If
you implement this make sure that you split the groups across physical
devices for redundancy otherwise the exercise is pointless.
You can use redundancy at o/s level by implementing raid technology. As
redo logs are written to linearly it is best to have them on flat or striped
disks rather than raid3 or raid5 where data is distributed in a fairly
random way across a disk set. So if you are on a single disk (or stripe)
and then mirror that disk/stripe you have added another level of redundancy.
I always use both groups and mirroring.
Finally - if you possibly can - always put redo logs on there own disks to
prevent i/o contention.
Best Regards
Phil West - Oracle Financials DBA
Telephone: 44 171 9509385
E-mail: philip.west@exco.co.uk
Index next_extent size
Date: Fri, 2 Jul 1999 13:15:29 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'OraApps-DBA@cpa.qc.ca'" OraApps-DBA@cpa.qc.ca
Subject: index next_extent size
Hi, Guys I have question regarding indexes.
We are using oracle apps version 11.02 with 8.0.4.2.1 and my below indexes
have the pct_increase 100.
I would like to know it is right for these indexes or something wrong about
it.
As of now one of the indexes it already hit 80MB next_extents .
The owner of these index is APPLSYS.
WF_ITEM_ACTIVITY_STATUSES_PK
WF_ITEM_ACTIVITY_STATUSES_N1
WF_ITEM_ATTRIBUTE_VALUES_PK
I talked to oracle regarding it and they told me that it is business issue.
Any idea?
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Fri, 2 Jul 1999 14:42:30 -0400
From: cwroblewski@orioncapital.com
Subject: Re: index next_extent size
Alter the Pctincrease setting to = 0 and extents will stay consistent.
Date: Fri, 2 Jul 1999 14:03:09 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: index next_extent size
I would like to know the reason why it's pctincrease 100 before doing
anything?
Are you using the same version of apps?
thanks.
Bharat Patel
Date: Fri, 2 Jul 1999 15:02:54 -0400
From: "Venkatayogi, Swami" SVenkatayogi@comintertech.com
Subject: RE: index next_extent size
I would not like to have a non zero pctincrease for any segment in the
database because that leads to a lot fragmentation and unequal
extents within that segment.Keep the initial and next extents multiples of
64k and pctincrease 0 .In our case these indexes have pctincrease 0.
I was wondering if there are any other implications involved.
/swamy venkatayogi
Date: Fri, 2 Jul 1999 14:33:02 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: index next_extent size
are you on 11.02 with 8.0.4.2.1?
thanks.
Bharat Patel
Date: Tue, 6 Jul 1999 08:09:14 -0400
From: "Venkatayogi, Swami" SVenkatayogi@comintertech.com
Subject: RE: index next_extent size
No. We are on 10.7NCA on 7.3.4.4 .But does it really matter on which version
we are on because I thought that the index segments should be same at the
database level.
Correct me if I am wrong.
Thanks.
./swamy
Date: Tue, 6 Jul 1999 13:10:50 -0400
From: Margaret Murray mmurray@delorme.com
Subject: RE: index next_extent size
Bharat,
I agree that you should set the pctincrease to 0 so that all extents are
sized equally. In this case, I think oracle set the pctincrease to 100
because these particular tables/indexes grow at a very fast rate - our table
is 6.3 million rows for 10 months of data at R11. It's simpler from Oracle's
perspective to have it grab 100% bigger chunk each time it extends that have
it have it take smaller chunks and run out of extents. This particular
module (Workflow) is different in most respects that all other modules so I
surmise a different development team worked on the creation (hence, the use
of pctincrease 100, and sometimes 50, instead of the usual 0 that most other
modules use).
As a side note, we're working on purging this table. In the Oracle Workflow
guide (on page 7-42) some API's are described. We've been told by support
to apply (for Solaris) patch 897214 to allow a purge routine to be run from
the concurrent manager; we're in the process of applying and testing that
patch. Also found in our tar: "Ct has another option to run specific
scripts for deletion of specific WF items. All pertinent scripts are listed
on pages 11-2 through 11-8 of the Oracle Workflow Guide Release
2.0.3(A56104). One script will delete all: on page 11-5 of the manual the
Wfrmall.sql will delete ALL workflow definitions, but there is a warning
associated with it that needs to be heeded." I'd really love a TRM
(Technical Reference Manual) for Workflow, so far no joy from doc sales.
Sigh, maybe someday...
HTH,
Margaret Murray, DBA
DeLorme Mapping
Explain plan and index access
Date: Wed, 7 Jul 1999 16:36:02 +0700
From: Arief Dharmawan arief@federal.co.id
To: oraapps-dba@cpa.qc.ca
Subject: index access
Hi all,
I've used explain plan for a statement like this :
EXPLAIN PLAN
SET STATEMENT_ID = 'AD 2'
FOR
select dtls_ds_code, txn_price from gsppc_gl00_txnswhs
where subl_part_partnum = '01-10-003';
and I have the result like this :
SQL> SELECT OPERATION, OPTIONS
2 FROM PLAN_TABLE
3 WHERE STATEMENT_ID = 'AD 2';
SELECT STATEMENT
TABLE ACCESS FULL
But the field (subl_part_partnum) is a foreign key, and I look up in
dba_indexes with this result :
SQL> select index_name, table_name, column_name from dba_ind_columns
2 where column_name = 'SUBL_PART_PARTNUM';
TSWH_SUBL_FK_I ------ GSPPC_GL00_TXNSWHS
SUBL_PART_PARTNUM
I have checked the status of TSWH_SUBL_FK_I in dba_indexes and it's valid.
Why the result of explain plan is FULL scan table ?
I think It's supposed to be RANGE SCAN (for the index) and ROWID (for the
row).
Regards,
Arief Dharmawan
Date: Thu, 8 Jul 1999 08:48:28 +1200
From: Erica Harris EricaH@wairc.govt.nz
Subject: RE: index access
A few things to consider:
1. How many rows in the table versus how many rows where
subl_part_partnum='01-10-003'?
2. Is the distribution of values in subl_part_partnum skewed? If it is,
then you should be collecting histogram statistics for that column.
3. Are you collecting statistics for the table and that index? For big
tables we do 'estimate statistics sample 20 percent'. We also compute
the statistics for indexes.
Hope this helps.
Regards,
Erica
Date: Wed, 7 Jul 1999 13:50:38 -0700
From: "Chalmers, Jeff" Jeff.Chalmers@bactc.com
Subject: RE: index access
Arief,
Check the order of the fields in the index. If the
subl_part_partnum field is not the first field in the index, then the index
will not be used.
Jeff
Date: Thu, 08 Jul 1999 13:02:37 +0000
From: Dave Morgan dave.morgan@calgary.qcdata.com
Subject: Re:index
Hi Arief,
Is SUBL_PART_PARTNUM the first column in the index? If it is not,
Oracle cannot use the index.
HTH
Dave
Mrp_gross_requirements
Date: Thu, 8 Jul 1999 16:40:57 +0200
From: "Worthington, S." S.Worthington@ahpg.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: mrp_gross_requirements
We're having problems getting our MRP jobs to finish before the users start
logging on. It looks like it might be possible to truncate
mrp_gross_requirements before the run, rather than having it delete 1.3
million rows with a 100,000 row loop. Is this possible, or is there
anything else we can try?
I've only just started looking at this, and I don't want to reinvent too
many wheels, so any suggestions welcome. We're on Apps 11.0.2
Thanks, Stu
stuartw@email.com
Memory Estimate needed by non-local user
Date: Thu, 08 Jul 1999 10:57:58 -0400
From: "Michael Porter" MPorter@pgahq.com
To: ORAapps-dba@cpa.qc.ca, oracle@telelists.com, oracledba@theMailingList.net
Subject: Second/Last Try. Memory Estimate Question
I didn't receive one response so I will try one more time to see if anyone
can help me on this...
Where is the amount of ESTIMATED memory needed by each non-local user that
connects via SQL*net that needs to be accounted for in the shared pool
discussed? I know I have seen this somewhere before but, it has escaped
me...
Also, where is it covered that show when database connections are viewed
via OEM 1.6.x (or V$session for that matter) their properties are
explained (e.g. dll's or forms being pointed to)? I seem to have many
connections for each client and some are of the same exact content it
appears...
Oracle 7.3.4.4 / AIX 4.2.1
Thanks
Mike
Date: Thu, 08 Jul 1999 12:23:10 -0400
From: Thomas Jones-Low tjoneslo@softstart.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Second/Last Try. Memory Estimate Question
Michael,
Check the Oracle7 Installation guide for your platform. It was probably
the only hardcopy document sent with the CD's.
Oracle 7 server requires 8464KB + 297KB per user
and SQL*Plus requires 1931KB + 103 KB per user
I'm not sure what you mean for the 2nd part. OracleRBDMS won't know
anything about what the client is doing or how it is doing it, only that
it wants some data. Perhaps if we knew why you needed this?
Thomas Jones-Low JobScheduler for Oracle
tjoneslo@softstart.com
Ph: 802-652-1596 http://www.softstart.com
log_file parameter in Init.ora
Date: Wed, 14 Jul 1999 09:08:37 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: log_file parameter in Init.ora
Hi, Guys I am looking for a guidance for tuning the system.
I am trying to reduce the used space in SGA.
The default value of log_files is 255 which occupied lot of space in SGA by
File Descriptors.
Is it beneficial to reduce this number little higher then the number of log
files I have?
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Wed, 14 Jul 1999 18:57:12 +0400
From: mzaveri@eppco.co.ae
Subject: Re: log_file parameter in Init.ora
Hello,
I agree with you. This should really help to free up sga.
Mehul.
Oracle DBA
EPPCO-DUBAI
Unable to extend index
Date: Mon, 12 Jul 1999 11:03:07 +0300
From: mohammed alshaarawi m_sharawy@tccbce.com
To: "'Ora App'" oraapps-l@cpa.qc.ca
Subject: UNABLE TO EXTEND
hi
I ahve the follwoing error
oracle error-1654:ORA-01654:unable to extend index APPLSYS.FND_LOGINS_U1
by 16 in tablespace FND_X has been detected in FND_SIGNON.AUDIT_USER
thanx
Date: Mon, 12 Jul 1999 08:48:33 -0400
From: "Jim Gehl" JGehl@smtp-gw.lsuc2.lsuc.on.ca
To: oraapps-l@cpa.qc.ca
Subject: Re: UNABLE TO EXTEND
You have to increase your FND_X tablespace.
Jim Gehl
Database Administrator
The Law Society of Upper Canada
Barreau du Haut-Canada
(416) 947-4045
jgehl@lsuc.on.ca
Managing Extents
Date: Tue, 10 Aug 1999 16:12:00 -0300
From: Jeff Preston Jeff.Preston@moncton.org
Subject: DBA: Managing Extents
I've been reading some conflicting ideas about extents, some people say to
try and keep
your number of extents as small as possible, ideally 1 extent per segment.
But in the Advanced Oracle Tuning and Administration, Oracle Press, book it
says
Having a single extent may make an object simple to manage, however
forcing each object in your
database to have just one extent will yield little in the way of
performance improvements, and may
actually hurt performance.
I was wondering what everyone else thought on this topic,
Date: Wed, 11 Aug 1999 20:28:53 MYT-8
From: philip@aleytys.pc.my
To: Jeff Preston Jeff.Preston@moncton.org, oraapps-l@cpa.qc.ca
Subject: Re: DBA: Managing Extents
Back in the Oracle 6 days and before, having too many extents (probably)
did have an impact because of fragmentation and extra dictionary lookups.
However Thomas Kyte (of Oracle Corp) and others recently did some
testing on Oracle 7 and 8 platforms and found that even with several
thousand extents performance degradation was mimimal.
On the other hand I still tend to feel uneasy when there are too many
extents per segment (old habits die hard).
Philip
Date: Wed, 11 Aug 1999 13:07:06 -0400
From: "Dawn M. Duvall" dduvall@mitretek.org
Subject: Re: DBA: Managing Extents
Jeff,
My experience is that this is no longer quite the issue it once was, but
I have experienced dramatic improvement in performance by defragmenting
database objects; particularly indexes.
Dawn
Date: Thu, 12 Aug 1999 17:48:28 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: Managing Extents
Many very small extents will almost always hurt performance. A small extent
is an extent smaller than the multiblock read size, usually 64K. A larger
hurdle for "small" may be the case if your disk farm has a minimum read
size such that even though Oracle would need to go back to the device
multiple times, the device would have cached, let's say 512K, so you get
contiguous reads almost for free. In that case, if the segment is at least
512K in size you probably don't want to have any extent smaller than 512K.
If you have so many extents in your system that the time to scan the list
in memory and read uet$ becomes significant, that will hurt performance.
This is unlikely.
For rollback segments, somewhere in the range of 20 to 40 preallocated
equal extents per rollback segment sized to handle all normal transactions
(making allowance in head room for outliers of extremely large single
commit point transactions) is usually results in optimal throughput.
If insisting on a single extent on JBOD (just a bunch of disks) prevents
you from putting multiple disks in play to service the i/o needs of a large
segment, that will hurt performance.
Other than reducing rollback segment extents below the useful threshold or
preventing JBOD striping, there is probably not a down side to rebuilding
to single extents. It is just that it may be a waste of time. The side
effects of rebuilding, especially in eliminating skewing and thereby
reducing the depth of indexes with monotonically increasing keys,
elimination of migrated rows, and filling in deletion space with rows so
that the data is denser per block far outweighs any improvements due to
reducing extents.
A high number of extents often points an accusing finger at segments that
have other space management problems of significance, so rebuilding high
extent count segments often does result in better performance because of
the side effects.
However, checking for the aforementioned problems in all segments, even
those with a single extent, is the better way to get a handle on what may
need to be rebuilt.
Mark
Maximum Extents - Query 3
Date: Wed, 11 Aug 1999 19:26:33 -0400
From: fushan@gamma.aei.ca
Subject: DBA: max extents
Hi DBAs
Database with version 7.3.4.3 database in Unix Digial machine.
The block size for my database is 8K. According to Oracle, the
max extent of objects (tables, indexes ) for 8K block size database
should be 505. Recently one of my objects has 600 extends. Did
I misunderstand the 505?? any help
Fu-shan Louisa Leong
Oracle DBA, Oracle Financial Application
Andersen Consulting
Date: Wed, 11 Aug 1999 19:34:46 -0400 (EDT)
From: "'Mike Grierson'" mcg@his.com
Subject: Re: DBA: max extents
Oracle said that? I thought the default for that tablespace would have
picked that number....
Mike
Mike Grierson
mcg@his.com
http://www.his.com/~mcg
Date: Wed, 11 Aug 1999 20:05:49 -0400
From: Sudershan virdi virdi@interlog.com
Subject: Re: DBA: max extents
The maxextents value must be set to unlimited
Sudershan
Date: Wed, 11 Aug 1999 22:28:47 -0400
From: fushan@gamma.aei.ca
Subject: Re: DBA: max extents
Hi DBAs
Thanks for your answer. I just found the answer from the book
called "Advanced Tuning and administration" book. For any DB
prior V7.3, the number of maxextent is dependent on the block
size. For V7.3 or later, you can set maxextents to unlimited which
is 2147483645. Very big
Fu-shan Louisa Leong
Oracle DBA, Oracle Financial Application
Andersen Consulting
Autoextend of Datafiles
Date: Fri, 23 Jul 1999 13:35:25 +0530
From: "GopalaKrishnan K" gopalak@wiproge.med.ge.com
Subject: Re: max number of extents
Hi Apps DBAs !
Here is an article about Autoextend of Datafiles
written by Mike Ault !
Regards,
K Gopalakrishnan
Database Administrator
GE Medical Systems Limited,
X Ray (South Asia) Pune-INDIA
Pin:411014 Ph # +91 20 6688693 /4
******************************************* From Mike
Ault*****************************************
Here is an excerpt from a column I wrote for Oreview Magazine:
Information on the Auto-extend Option for the Tablespace Datafiles
A question was posted on the ORASUPP forum concerning where to get
information
from the data dictionary about the auto-extend options for datafiles.
Support's answer was that
the only way to get this information was to query the SYS table FILEXT$.
Unfortunately this table looks like this:
Name
Null? Type
--------------------------------------- ----
FILE#NOT NULL NUMBER
MAXEXTENDNOT NULL NUMBER
INC NOT NULL NUMBER
This structure means that in order to get back to the actual file name and
tablespace
you need to join to several other tables: FILE$, TS$ and V$DBFILE. I suggest
creating a
view based on the select shown in figure 1.
create view dba_file_data as
select
a.name tablespace,a.dflminext min_extents, a.dflmaxext max_extents,
a.dflinit init,a.dflincr next,a.dflextpct pct_increase, d.name datafile,
b.blocks datafile_size, c.maxextend max_extend, c.inc ext_incr
from ts$ a, file$ b, filext$ c, v$dbfile d
where
a.ts#=b.ts# and b.file#=c.file# and b.file#=d.file#
/
Figure 1: Script to Create a Datafile View.
This script creates the view DBA_FILE_DATA which will look like this when
queried:
Name Null? Type
------------------------------- -------- ----
TABLESPACE NOT NULL VARCHAR2(30)
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NOT NULL NUMBER
INIT NOT NULL NUMBER
NEXT NOT NULL NUMBER
PCT_INCREASE NOT NULL NUMBER
DATAFILE VARCHAR2(257)
DATAFILE_SIZE NOT NULL NUMBER
MAX_EXTEND NOT NULL NUMBER
EXT_INCR NOT NULL NUMBER
Supposedly ORACLE8 will have the autoextend information in the DBA_DATA_FILE
view, but
for those of us who have a number of ORACLE7 databases the above view will
come in handy
if we use the auto-extend options. The script should be run as SYS and a
grant made to
public and a synonym created for access of the view.
Michael R. Ault
DMR Consulting Group
The Results People
Associate Director Oracle Practice
(770)-621-0300 X2790
Fax: (770) 939-0310
RevealNet Senior Technical Officer