Re: gc quiesce
- From: Mladen Gogala <mgogala@xxxxxxxxx>
- Date: 23 Jan 2008 12:59:14 GMT
On Tue, 22 Jan 2008 12:26:28 -0800, siska.martin wrote:
Dear Usenet members,(ROWCOUNT_IN_transient_table_CHANGED_DRASTICALLY_AGAINST_ROWCOUNT_BEFORE_TRUNCATE)
As a part of testing we tried to setup an Oracle 10g(v10.2.0.3) 2-
instance RAC environment hosted on Microsoft Windows 2003 Server ( x64
editioin, SP2 ).
Both machines have 4CPUs (Xeon's @ 2.66GHz) and 4GB of RAM. The are
hosted in VMWare environment.
Storage is implemented using shared hard drives managed by ASM.
What are we trying to achieve is to run an ETL load composed of PL/SQL
jobs in a DSS environment in order to see how the resource consumption
will be balanced through the instances. PL/SQL code runs without a
glitch on non-RAC environment.
Now the problem.
PL/SQL code blocks it's execution usually in a random INSERT statement (
block in other statements were not observed ) by generating a lot of
'gc quiesce' wait events. However, no blocking sessions were discovered
in gv$session.
PL/SQL code consists of more jobs executed in sequence. Each job has
structure according to this template :
TRUNCATE temp_log_table1;
TRUNCATE temp_log_table2;
TRUNCATE transient_table;
INSERT ALL
WHEN fatal_error_occurs THEN INTO temp_log_table1 (columns) VALUES
(values)
WHEN nonfatal_error_occurs THEN INTO temp_log_table2 (columns) VALUES
(values)
WHEN NOT fatal_error_occurs THEN INTO transient_table (columns) VALUES
(values)
SELECT
columns
FROM
source_tables;
COMMIT;
IF
THEN
RECOMPUTE_STATS_ON_TRANSIENT_TABLE;
END IF;
MERGE INTO target_table trg USING
(
SELECT
columns
FROM
transient_table
) src ON (merge_clause)
WHEN NOT MATCHED THEN INSERT INTO (trg.columns) VALUES (src.values) WHEN
MATCHED THEN UPDATE SET trg.columns = src.values
COMMIT;
INSERT INTO log_table1
SELECT * FROM tmp_log_table1
INSERT INTO log_table2
SELECT * FROM tmp_log_table2
COMMIT;
Sometimes the execution blocks when INSERTing data to dictionary during
stats recomputing, sometimes it is the first multi table insert ( in
randomly choosed job in the sequence ) that causes the problem.
Any idea what causes this random 'gc quiesce' event blocks ?
Yours faithfully,
Martin Siska
To "quiesce" database means to suspend it for a brief period of time.
In RAC context, GC quiesce probably means to quiesce a resource DB.
This is an educated guess, only. If this table is heavily in use,
the instance you're inserting into may be pulling huge numbers of
blocks from the other nodes and becoming their master. You are waiting
for remastering. Make sure that all inserts are being done on the same
instance.
Look at the list of bugs in the heavenly patchset, the one that will be
released week after the judgment day, 10.2.0.4. The list of bug fixes is
available in the doc id: 401436.1.
Also, dump systemstate, level 12 when you observe such waits, send it to
Oracle Corp. and work with them. Using HANGANALYZE might not be a bad idea
in this situation.
K. Gopalakrishnan, the author of the RAC book might be having to say about
that, as well. I don't have his book right here and I can't look into it.
I looked into Gopal and Kirti's "Oracle10g Wait Event" book, but there is
nothing in there.
--
Mladen Gogala
http://mgogala.freehostia.com
.
- Follow-Ups:
- Re: gc quiesce
- From: joel garry
- Re: gc quiesce
- References:
- gc quiesce
- From: siska . martin
- gc quiesce
- Prev by Date: Re: A potential bug (infinite loop) in Oracle: querying v$access
- Next by Date: Re: ORA-27072: skgfdisp: I/O error
- Previous by thread: gc quiesce
- Next by thread: Re: gc quiesce
- Index(es):
Relevant Pages
|
