RE: Move table online and update the indexes at the same time.
- From: "Leonard, George" <GLeonard@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jul 2004 09:55:04 +0200
Hi all
We are having some weird and wonderfulls with our new machine, we use to be
a on E10K, all nice and stable.
They moved the database onto an E25K 2 weeks ago and since then have been
having very weird problems, below is the Sun configuration and also the
Oracle init file.
We have been having CPU spiking if that is what you can call it, load up to
80 on a couple of occasions.
This sometimes happen when the backup/RMAN process starts, sometimes when
Netbackup does a totally independent file system backup.
Any ideas?
Thinking is that some patch is required or something that was just missed?
All help appreciated.
------ Init File
db_name = "pspif1"
instance_name = pspif1
service_names = pspif1
compatible=9.2.0
background_dump_dest = /u02/admin/pspif1/bdump
control_files = ("/u03/oradata/pspif1/controlfile01.pspif1",
"/u04/oradata/pspif1/controlfile02.pspif1", "/u05/oradata/pspif1/
controlfile03.pspif1")
core_dump_dest = /u02/admin/pspif1/cdump
db_block_buffers = 140000
db_block_size = 8192
db_file_multiblock_read_count = 8
hash_area_size = 12000000
java_pool_size = 20000K
job_queue_processes = 10
#job_queue_interval = 30
large_pool_size = 39321600
log_archive_dest_1 = "location=/u02/archive/pspif1"
log_archive_start = true
log_archive_format = pspif1_%t_%s_.arc
#changed by Colin
log_buffer = 2097152
log_checkpoint_interval = 9999999
log_checkpoint_timeout = 1800
max_enabled_roles = 80
# mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
open_cursors = 2000
optimizer_index_caching = 80
optimizer_index_cost_adj = 80
optimizer_mode = choose
#changed by Colin
session_cached_cursors = 150
os_authent_prefix = ""
processes = 2000
remote_login_passwordfile = exclusive
sga_max_size=3000M
shared_pool_size=838860800
sga_max_size=3000M
shared_pool_size=838860800
shared_pool_reserved_size = 60M
pre_page_sga = true
sort_area_retained_size = 2000000
sort_area_size = 2000000
timed_statistics = FALSE
user_dump_dest = /u02/admin/pspif1/udump
AQ_TM_PROCESSES = 1
utl_file_dir = '*'
undo_management=AUTO
undo_retention=2500
undo_tablespace=UNDOTBS1
#db_writer_processes=2
query_rewrite_enabled=true
query_rewrite_integrity=trusted
event="10932 trace name context level 32768"
------ Sun/Solaris Config
Oracle version 9.2.0.4.0
Sun Solaris 8 patch level 117350-02
/etc/system settings:
set shmsys:shminfo_shmmax=8589934592
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=1775
set shmsys:shminfo_shmseg=1675
set semsys:seminfo_semmni=1775
set semsys:seminfo_semmsl=1750
set semsys:seminfo_semmns=65735
set semsys:seminfo_semopm=1475
set semsys:seminfo_semvmx=32767
set semsys:seminfo_semmap=1675
set semsys:seminfo_semmnu=1775
set semsys:seminfo_semume=1005
Other:
72 CPU's
144 Gb Memory
George
________________________________________________
George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank
E-mail:gleonard@xxxxxxxxxxxxx
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----
From: Alexandre Gorbatchev [mailto:agorbatchev@xxxxxxxxxxx]
Sent: Wednesday, July 28, 2004 9:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Move table online and update the indexes at the same time.
Just found DBMS_REPAIR.REBUILD_FREELISTS - seems that it could fill new
freelists with free blocks.
You are right about updates - I didn't make distinctions between update
and insert. In addition there is BLOBs that can be extended, but it's rare
and we have hardly any migrated/chained rows.
Regards,
Alex
From: "Mark W. Farnham" <mwf@xxxxxxxx>@freelists.org on 27-07-2004
08:07 AST
Please respond to oracle-l@xxxxxxxxxxxxx
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
To:
<oracle-l@xxxxxxxxxxxxx>
cc:
Subject:
RE: Move table online and update the indexes at the same time.
Please explain what you believe happens when you change the freelists for
an
existing table.
I do not believe information regarding freelists is stored in individual
blocks, but rather freelists are lists of blocks queued up for new
inserts.
Are you thinking of initial transactions and maximum transactions?
Further, I'm a bit confused that you're having freelist troubles due to
heavy update (unless you include insert in the generic "update" as opposed
to making a distinction between insert, update, and delete.) I suppose
that
if the updates are something like a huge expansion of a row by putting a
giant lob into a column, then freelists might be called for in updates by
row migration and/or row chaining. If you routinely expand rows greatly,
you
like have a different design consideration to consider re-tooling before
you
spend a lot of time reorganizing.
good luck!
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Alexandre Gorbatchev
Sent: Tuesday, July 27, 2004 6:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Move table online and update the indexes at the same time.
Tanel,
Yes, PCTUSED and PCTFREE needs to be corrected as well.
Yes, sure, we do have RAC. But even without RAC we experienced some
problems with low FREELISTS on heavily updated tables/indexes. Changing it
without reorg. would affect only new blocks. We need to change FREELIST
GROUPS and I was planning to set it higher than number of nodes we have
now in anticipation of additional nodes. Thanks for warning of the "side
effect" for setting several freelist groups - we have to research on it.
We can't easily partition some of the tables. I wonder what other options
are besides ASSM.
You've correctly pointed out the problem with IO distribution. That's
another reason for reorganization.
Thanks for your points,
Alex
From: Tanel Põder <tanel.poder.003@xxxxxxx>@freelists.org on 27-07-2004
12:44 ZE3
Please respond to oracle-l@xxxxxxxxxxxxx
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
To:
<oracle-l@xxxxxxxxxxxxx>
cc:
Subject:
Re: Move table online and update the indexes at the same time.
> By the way, when is the lock required - in the beginning or in the end?
I've not tested it, but probably in the end, when you switch your newly
created table instead of the old one.
> I move the table for several reasons:
> 1.) CREATE TABLE ... AS SELECT produces the table which is much smaller
> (sometimes 2-3 times). We are trying to reduce the space usage with it.
So
> it seems the tables are space.
This seems to be an issue of incorrectly configured PCTFREE and PCTUSED.
If
you analyzed the table over time and checked the average free space in
blocks (and rowcounts+rowlens) you might get closer whats the real issue
here. In default configuration the tables PCTUSED is 40 for example,
meaning
that up to 60% of the block contents may remain empty and completely
unused
if the space usage doesn't fall below PCTUSED...
> 2.) Change storage clause - FREELIST, FREELIST GROUP or move to ASSM
(not
> sure, because it seems there are several bugs that we might hit in our
> environment)
You can change FREELISTS online without reorg. You shouldn't normally use
FREELIST GROUPS if you're not in OPS or RAC evnironment. Freelist groups
have the problem, that the processes which PID's map to another freelist
group, don't see free blocks in other freelist groups, thus potentially
wasting space in DML intensive environment. So if you're not seeing heavy
segment header block contention due freelist updates in your database, you
shouldn't consider freelist groups (and even if you see, then there are
other alternatives to consider, like partitioning, etc.)
ASSM - again don't move to it if you don't have a RAC environment (with
continuously changing number of nodes) or you don't have special
conditions
like having rows with extremely varying sizes inserted to your table etc..
> 3.) Physical layout reorganization. Some tables are in wrong
tablespaces.
This doesn't seem like a serious problem, given that your databases
availability is more important (unless you don't have serious bottlenecks
due improperly balanced IO)
Tanel.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
___________________________________________________________________________________________________
The views expressed in this email are, unless otherwise stated, those of the
author and not those
of the FirstRand Banking Group or its management. The information in this
e-mail is confidential
and is intended solely for the addressee. Access to this e-mail by anyone else
is unauthorised.
If you are not the intended recipient, any disclosure, copying, distribution or
any action taken or
omitted in reliance on this, is prohibited and may be unlawful.
Whilst all reasonable steps are taken to ensure the accuracy and integrity of
information and data
transmitted electronically and to preserve the confidentiality thereof, no
liability or
responsibility whatsoever is accepted if information or data is, for whatever
reason, corrupted
or does not reach its intended destination.
________________________________
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
