Oracle DB 12.1.0.2
RAC 2 nodes
Solaris 11
Hello Team,
We have 1 downstream database on which extract and replicat for golden gate is running. We are planning to to CDC from Oracle to a big data platform.
We noticed the capture is taking very long time. I have generated the trace file for this session and saw most of time is SQL net and select (37 minutes).
Please find below TKPROF entry:
TKPROF: Release 12.1.0.2.0 - Development on Mon May 31 11:03:00 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Trace file: dware11_ora_8404_LOGMNR_TRACE.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
(STARTSCN => 326674508624, ENDSCN => 326675265403,
OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION+ DBMS_LOGMNR.NO_SQL_DELIMITER+DBMS_LOGMNR.NO_ROWID_IN_STMT+ DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
Error encountered: ORA-00928
********************************************************************************
SQL ID: 85wska2dgav1a Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_2_seq_26535.5674.1073986835',
OPTIONS => DBMS_LOGMNR.NEW); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.06 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.06 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.00 0.00
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 4 0.00 0.00
log file sequential read 1 0.05 0.05
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: ffkx1nqwkwzkt Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_2_seq_26539.5167.1073987545',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.04 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.01 0.01
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: fq9wzdrhahxzc Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_2_seq_26542.6576.1073987919',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.03 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.00 0.00
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: g5papyu6w6jya Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_1_seq_31425.6887.1073985279',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.05 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.05 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.01 0.02
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.03 0.03
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 0uxh7136yzc57 Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_2_seq_26532.6304.1073985791',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.03 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.00 0.00
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 7d9nz188vzb4s Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_1_seq_31426.6147.1073985961',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.05 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.05 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.00 0.00
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.05 0.05
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.60 0.60
********************************************************************************
SQL ID: aybwc6ygf2mx5 Plan Hash: 0
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+RECO/DWARE1/ARCHIVELOG/2021_05_31/thread_1_seq_31428.6955.1073987547',
OPTIONS => DBMS_LOGMNR.ADDFILE); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.33 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.33 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
KSV master wait 6 0.00 0.00
ASM file metadata operation 3 0.00 0.00
Disk file operations I/O 3 0.00 0.00
log file sequential read 1 0.33 0.33
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 49.16 49.16
********************************************************************************
SQL ID: 22w1fx2wn3qru Plan Hash: 0
BEGIN DBMS_LOGMNR.START_LOGMNR; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.53 0.53
********************************************************************************
SQL ID: 5ybfkfw06nv2n Plan Hash: 0
DECLARE opstring_in VARCHAR2(31) := :1; session_num_in NUMBER :=
:2; logmnr_did_inout NUMBER := :3; logmnr_uid_inout NUMBER := :4;
num_uids_in NUMBER := :5; BEGIN
sys.logmnr_krvrdluid3(opstring_in, session_num_in,
logmnr_did_inout, logmnr_uid_inout, null, null,
null, num_uids_in); :3 := logmnr_did_inout; :4 :=
logmnr_uid_inout; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: dcjx58t6yaqyv Plan Hash: 2750348802
SELECT LU.LOGMNR_DID, LU.LOGMNR_UID, LD.SESSION#
FROM
SYSTEM.LOGMNR_UID$ LU, SYSTEM.LOGMNR_DID$ LD WHERE LD.SESSION# > 2147483647
AND LU.LOGMNR_DID = LD.LOGMNR_DID ORDER BY 1, 2, 3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 7 7 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 7 7 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT ORDER BY (cr=7 pr=7 pw=0 time=7903 us cost=7 size=13 card=1)
0 0 0 HASH JOIN (cr=7 pr=7 pw=0 time=7886 us cost=6 size=13 card=1)
0 0 0 NESTED LOOPS (cr=7 pr=7 pw=0 time=7883 us cost=6 size=13 card=1)
0 0 0 NESTED LOOPS (cr=7 pr=7 pw=0 time=7880 us cost=6 size=13 card=1)
1 1 1 STATISTICS COLLECTOR (cr=6 pr=6 pw=0 time=7180 us)
1 1 1 TABLE ACCESS FULL LOGMNR_UID$ (cr=6 pr=6 pw=0 time=7165 us cost=4 size=7 card=1)
0 0 0 INDEX RANGE SCAN LOGMNR_DID$_PK (cr=1 pr=1 pw=0 time=698 us cost=1 size=0 card=1)(object id 1299)
0 0 0 TABLE ACCESS BY INDEX ROWID LOGMNR_DID$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=6 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED LOGMNR_DID$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=6 card=1)
0 0 0 INDEX RANGE SCAN LOGMNR_DID$_PK (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 1299)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 2 0.00 0.00
db file scattered read 1 0.00 0.00
********************************************************************************
SQL ID: 8ggw94h7mvxd7 Plan Hash: 0
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
SQL ID: 4cjxdycqgwvx0 Plan Hash: 1410080983
select value
from
system.logmnr_parameter$ where name = 'READ_BUFFERS' and session# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED LOGMNR_PARAMETER$ (cr=1 pr=1 pw=0 time=817 us cost=1 size=1081 card=1)
0 0 0 INDEX RANGE SCAN LOGMNR_PARAMETER_INDX (cr=1 pr=1 pw=0 time=814 us cost=1 size=0 card=1)(object id 1347)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
********************************************************************************
SQL ID: b56nuu9nrx1ju Plan Hash: 535362191
select 0 logmnr_uid, con_id pdb_id
from
v$containers where name <> 'PDB$SEED' and open_mode like 'READ%' order by
pdb_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=0 pr=0 pw=0 time=60 us cost=1 size=15 card=1)
1 1 1 FIXED TABLE FULL X$CON (cr=0 pr=0 pw=0 time=41 us cost=0 size=15 card=1)
********************************************************************************
SQL ID: 2b0xbyh2b5n4s Plan Hash: 2327622893
select 0 as AsOfSCNwrp_col, 0 as AsOfSCNbas_col, (select
substr(pr.value$,1,64) as DBTimeZone_col
from
sys.props$ pr where pr.name = 'DBTIMEZONE') as DBTimeZone_col,
ko.metadata as FDO_col, ko.length as FDOLen_col from
sys.kopm$ ko where ko.name = 'DB_FDO'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL PROPS$ (cr=2 pr=0 pw=0 time=34 us cost=2 size=28 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=11 us cost=1 size=111 card=1)
1 1 1 INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=7 us cost=0 size=0 card=1)(object id 679)
********************************************************************************
SQL ID: 1p5cusfqt3w03 Plan Hash: 1410080983
select value
from
system.logmnr_parameter$ where name = 'DICTIONARY_CACHE_SIZE' and session#=
:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED LOGMNR_PARAMETER$ (cr=1 pr=0 pw=0 time=9 us cost=1 size=1081 card=1)
0 0 0 INDEX RANGE SCAN LOGMNR_PARAMETER_INDX (cr=1 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 1347)
********************************************************************************
SQL ID: 5dt9w7dmjqp7a Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 9.49 9.49
********************************************************************************
SQL ID: 1mu8hgsxyx2rk Plan Hash: 3748306801
SELECT *
FROM
V$LOGMNR_CONTENTS WHERE TABLE_NAME = 'TABLENOTEXIT'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2255.46 2272.54 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2255.46 2272.54 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 FIXED TABLE FULL X$LOGMNR_CONTENTS (cr=0 pr=0 pw=0 time=2218859601 us cost=0 size=11113 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 43 0.00 0.00
SQL*Net message to client 1 0.00 0.00
get branch/thread/sequence enqueue 7 0.00 0.00
KSV master wait 112 0.00 0.05
ASM file metadata operation 56 0.00 0.00
log file sequential read 293 0.39 16.80
enq: WL - contention 4 0.00 0.00
SQL*Net message from client 1 659.72 659.72
********************************************************************************
SQL ID: a3uqbmwnkfv18 Plan Hash: 0
DECLARE table_rule_count NUMBER := 0; BEGIN BEGIN select count(1)
into table_rule_count from system.logmnr_gt_tab_include$; EXCEPTION
when others then table_rule_count := 0; END; :1 :=
table_rule_count;END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 7y03axgp9xsjv Plan Hash: 150745652
SELECT COUNT(1)
FROM
SYSTEM.LOGMNR_GT_TAB_INCLUDE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=65 us)
0 0 0 TABLE ACCESS FULL LOGMNR_GT_TAB_INCLUDE$ (cr=0 pr=0 pw=0 time=6 us cost=2 size=0 card=1)
********************************************************************************
SQL ID: 4u366ybrxar6t Plan Hash: 0
declare l_count integer := 0;begin select count(1) into l_count from
system.logmnr_gt_xid_include$; :1 := l_count; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 87shmyqssu2wg Plan Hash: 2056569297
SELECT COUNT(1)
FROM
SYSTEM.LOGMNR_GT_XID_INCLUDE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=6 us)
0 0 0 TABLE ACCESS FULL LOGMNR_GT_XID_INCLUDE$ (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.01 0.01 0 0 0 0
Execute 11 0.01 0.62 0 0 0 8
Fetch 1 2255.46 2272.54 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 2255.49 2273.17 0 0 0 8
Misses in library cache during parse: 7
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 68 0.00 0.00
SQL*Net message to client 12 0.00 0.00
SQL*Net message from client 12 659.72 753.33
KSV master wait 154 0.01 0.12
ASM file metadata operation 77 0.00 0.00
log file sequential read 300 0.39 17.34
SQL*Net break/reset to client 2 0.00 0.00
get branch/thread/sequence enqueue 7 0.00 0.00
enq: WL - contention 4 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 3
Fetch 9 0.00 0.00 8 13 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.00 0.01 8 13 0 7
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
db file sequential read 3 0.00 0.00
db file scattered read 1 0.00 0.00
11 user SQL statements in session.
11 internal SQL statements in session.
22 SQL statements in session.
********************************************************************************
Trace file: dware11_ora_8404_LOGMNR_TRACE.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
11 user SQL statements in trace file.
11 internal SQL statements in trace file.
22 SQL statements in trace file.
22 unique SQL statements in trace file.
1118 lines in trace file.
3024 elapsed seconds in trace file.
How can I tune my DB for faster performance?
Log read rate is 22G/h.
45 G archives generated per hour
Thanks,
Roshan