Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

logminer performance - tkprof

RobeenApr 13 2021

Oracle DB 12.1.0.2
AIX

Hello Team,
I would like some help regarding tuning of logminer.
I have genrated tkprof for the logminer session. Could you please advise if SGA needs to be increased?
Below is the tkprof output.


TKPROF: Release 12.1.0.2.0 - Development on Mon Apr 12 18:22:15 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Trace file: PMTG2_ora_28901588_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 statements encountered a error during parse:

BEGIN DBMS_LOGMNR.ADD_LOGFILE(  LOGFILENAME => '/app/PMTG/db/apps_st/data/log01a.dbf',  OPTIONS => DBMS_LOGMNR.NEW);>; END;
WAIT #48584632
Error encountered: ORA-06550
--------------------------------------------------------------------------------
(STARTSCN => 315616632428, ENDSCN => 281474976710655,
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: 6jqss82q7zm7m Plan Hash: 0

BEGIN DBMS_LOGMNR.ADD_LOGFILE(  LOGFILENAME => 
  '/app/PMTG/db/apps_st/data/log01a.dbf',  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.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  ----------  ------------
  SQL*Net break/reset to client                   1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       10.84         17.18
  Disk file operations I/O                        3        0.00          0.00
  log file sequential read                        1        0.00          0.00
********************************************************************************

SQL ID: 512z11a3ctzms Plan Hash: 0

BEGIN DBMS_LOGMNR.ADD_LOGFILE(  LOGFILENAME => 
  '/app/PMTG/db/apps_st/data/log02a.dbf',  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.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                        3        0.00          0.00
  log file sequential read                        1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        6.03          6.03
********************************************************************************

SQL ID: 60zv80k24k39w Plan Hash: 0

BEGIN DBMS_LOGMNR.ADD_LOGFILE(  LOGFILENAME => 
  '/app/PMTG/db/apps_st/data/log03a.dbf',  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.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                        3        0.00          0.00
  log file sequential read                        1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       76.68         76.68
********************************************************************************

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.01          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          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        1.70          1.70
********************************************************************************

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          0          6          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          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=6 pr=0 pw=0 time=382 us cost=3 size=52 card=1)
         0          0          0   NESTED LOOPS  (cr=6 pr=0 pw=0 time=361 us)
         0          0          0    NESTED LOOPS  (cr=6 pr=0 pw=0 time=358 us cost=2 size=52 card=1)
         0          0          0     TABLE ACCESS FULL LOGMNR_UID$ (cr=6 pr=0 pw=0 time=356 us cost=2 size=26 card=1)
         0          0          0     INDEX RANGE SCAN LOGMNR_DID$_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 2256227)
         0          0          0    TABLE ACCESS BY INDEX ROWID LOGMNR_DID$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

********************************************************************************

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: 3797659971

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          0          1          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
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 LOGMNR_PARAMETER$ (cr=1 pr=0 pw=0 time=30 us cost=1 size=1081 card=1)
         0          0          0   INDEX RANGE SCAN LOGMNR_PARAMETER_INDX (cr=1 pr=0 pw=0 time=29 us cost=1 size=0 card=1)(object id 250231)

********************************************************************************

SQL ID: asvzxj61dc5vs Plan Hash: 2595202071

select timestamp, flags 
from
 fixed_obj$ where obj#=: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          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
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  TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=3 pr=0 pw=0 time=25 us cost=2 size=18 card=1)
         1          1          1   INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=15 us cost=1 size=0 card=1)(object id 248621)

********************************************************************************

SQL ID: afx304d90ps3z Plan Hash: 3313858376

select rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, analyzetime, 
  samplesize, avgspc_flb, flbcnt, flags 
from
 tab_stats$ where obj#=: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          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
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  TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=3 pr=0 pw=0 time=13 us cost=2 size=38 card=1)
         1          1          1   INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=2 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 248623)

********************************************************************************

SQL ID: 96g93hntrzjtr Plan Hash: 841937906

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
  spare1, spare2, avgcln 
from
 hist_head$ where obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         16          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         16          0           4

Misses in library cache during parse: 0
Optimizer mode: RULE
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  TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=4 pr=0 pw=0 time=28 us)
         1          1          1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=3 pr=0 pw=0 time=20 us)(object id 221)

********************************************************************************

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: 1
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=68 us cost=1 size=14 card=1)
         1          1          1   FIXED TABLE FULL X$CON (cr=0 pr=0 pw=0 time=41 us cost=0 size=14 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: 1
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=41 us cost=2 size=28 card=1)
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=8 us cost=1 size=111 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)(object id 351)

********************************************************************************

SQL ID: 1p5cusfqt3w03 Plan Hash: 3797659971

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: 1
Misses in library cache during execute: 1
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 LOGMNR_PARAMETER$ (cr=1 pr=0 pw=0 time=6 us cost=1 size=1081 card=1)
         0          0          0   INDEX RANGE SCAN LOGMNR_PARAMETER_INDX (cr=1 pr=0 pw=0 time=4 us cost=1 size=0 card=1)(object id 250231)

********************************************************************************

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     61.85     146.83          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     61.85     146.83          0          0          0           0

Misses in library cache during parse: 1
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=146837202 us cost=0 size=11113 card=1)


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                       2        0.00          0.00
  SQL*Net message from client                     2       22.16         32.40
  Disk file operations I/O                       19        0.00          0.00
  get branch/thread/sequence enqueue              3        0.00          0.00
  log file sequential read                       22        0.13          0.44
********************************************************************************

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=13 us)
         0          0          0   TABLE ACCESS FULL LOGMNR_GT_TAB_INCLUDE$ (cr=0 pr=0 pw=0 time=7 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=5 us)
         0          0          0   TABLE ACCESS FULL LOGMNR_GT_XID_INCLUDE$ (cr=0 pr=0 pw=0 time=2 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        6      0.00       0.00          0          0          0           0
Execute      6      0.00       0.01          0          0          0           4
Fetch        1     61.85     146.83          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13     61.85     146.85          0          0          0           4

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                       30        0.00          0.00
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     8       76.68        150.90
  SQL*Net break/reset to client                   3        0.00          0.00
  log file sequential read                       25        0.13          0.44
  get branch/thread/sequence enqueue              3        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.00       0.00          0          0          0           0
Execute     17      0.00       0.00          0          0          0           3
Fetch       15      0.00       0.00          0         34          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       46      0.00       0.00          0         34          0          13

Misses in library cache during parse: 6
Misses in library cache during execute: 4

    6  user  SQL statements in session.
   14  internal SQL statements in session.
   20  SQL statements in session.
********************************************************************************
Trace file: PMTG2_ora_28901588_LOGMNR_TRACE.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       6  user  SQL statements in trace file.
      14  internal SQL statements in trace file.
      20  SQL statements in trace file.
      20  unique SQL statements in trace file.
     430  lines in trace file.
     270  elapsed seconds in trace file.

Comments
Post Details
Added on Apr 13 2021
0 comments
212 views