Re: gc quiesce

On Jan 23, 4:59 am, Mladen Gogala <mgog..._at_yahoo.com> wrote:
> On Tue, 22 Jan 2008 12:26:28 -0800, siska.martin wrote:
> > Dear Usenet members,
>
> > 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
>
> (ROWCOUNT_IN_transient_table_CHANGED_DRASTICALLY_AGAINST_ROWCOUNT_BEFORE_TR­UNCATE)
>
>
>
>
>
> > 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.

And an uneducated guess says bug 5649377 means Oracle doesn't know it is remastering, so keeps trying to. Bug 6742524 looks like someone sees something similar on linux while doing something completely different.

>
> 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.

I noticed a bunch of docs got updated a week ago, perhaps the day approacheth. Or maybe this RAC stuff is just too hard.

Oh holy Townsend, give us a sign!

jg

--
@home.com is bogus.
Basic Flying Rules: "Try to stay in the middle of the air. Do not go
near the edges of it. The edges of the air can be recognized by the
appearance of ground, buildings, sea, trees and interstellar space. It
is much more difficult to fly there."