gc quiesce

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_TRUNCATE)
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 Received on Tue Jan 22 2008 - 14:26:28 CST