[oracle@ORAX8PPD1 trace]$ cat backdbpp1_ora_1542848_QUERY_AUDIT2.prf
TKPROF: Release 19.0.0.0.0 - Development on Wed Oct 26 19:29:30 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: backdbpp1_ora_1542848_QUERY_AUDIT2.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
********************************************************************************
SQL ID: a65s3v20t1jan Plan Hash: 821094412
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01")
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE */ :"SYS_B_02" AS C1, CASE WHEN
LOWER("AUDITMESSA0_"."SA_ID")=:"SYS_B_03" AND "AUDITMESSA0_"."EVENT_TIME">=
TIMESTAMP:"SYS_B_04" AND "AUDITMESSA0_"."EVENT_TIME"<TIMESTAMP:"SYS_B_05"
THEN :"SYS_B_06" ELSE :"SYS_B_07" END AS C2 FROM (SELECT /*+
NO_PARALLEL("AUDITMESSA0_") FULL("AUDITMESSA0_")
NO_PARALLEL_INDEX("AUDITMESSA0_") */ * FROM
"AUDIT_PERSISTENCE"."AUDIT_MESSAGETEST" SAMPLE BLOCK (:"SYS_B_08" ,
:"SYS_B_09") SEED (:"SYS_B_10") "AUDITMESSA0_" WHERE
TBL$OR$IDX$PART$NUM("AUDIT_PERSISTENCE"."AUDIT_MESSAGETEST",:"SYS_B_11",
:"SYS_B_12",:"SYS_B_13",ROWID) IN (:"SYS_B_14",:"SYS_B_15",:"SYS_B_16",
:"SYS_B_17",:"SYS_B_18",:"SYS_B_19",:"SYS_B_20",:"SYS_B_21",:"SYS_B_22",
:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",:"SYS_B_27"))
"AUDITMESSA0_") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 1 0.02 0.12 65 191 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.14 65 191 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 377 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 RESULT CACHE 2vk2kvfmb67ug9bcnt8xkwd3zt (cr=191 pr=65 pw=0 time=125958 us starts=1)
1 1 1 SORT AGGREGATE (cr=191 pr=65 pw=0 time=125854 us starts=1)
440 440 440 PARTITION COMBINED ITERATOR PARTITION: KEY KEY (cr=191 pr=65 pw=0 time=196005 us starts=1 cost=130 size=102871556 card=3025634)
440 440 440 TABLE ACCESS SAMPLE AUDIT_MESSAGETEST PARTITION: KEY KEY (cr=191 pr=65 pw=0 time=113289 us starts=14 cost=130 size=102871556 card=3025634)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 9 0.00 0.00
Disk file operations I/O 3 0.00 0.00
asynch descriptor resize 1 0.00 0.00
db file sequential read 65 0.01 0.08
gc cr grant 2-way 30 0.00 0.01
gc current grant 2-way 6 0.00 0.00
IPC group service call 1 0.00 0.00
********************************************************************************
select auditmessa0_.id as id1_0_,
auditmessa0_.additions as additions2_0_,
auditmessa0_.emulator_domain as emulator_domain3_0_,
auditmessa0_.emulator_user_id as emulator_user_id4_0_,
auditmessa0_.emulator_username as emulator_username5_0_,
auditmessa0_.error as error6_0_,
auditmessa0_.event_action as event_action7_0_,
auditmessa0_.event_category as event_category8_0_,
auditmessa0_.event_desc as event_desc9_0_,
auditmessa0_.metadata as metadata10_0_,
auditmessa0_.ip_address as ip_address11_0_,
auditmessa0_.legal_entity_id as legal_entity_id12_0_,
auditmessa0_.message_set_id as message_set_id13_0_,
auditmessa0_.object_type as object_type14_0_,
auditmessa0_.schema_version as schema_version15_0_,
auditmessa0_.sa_id as sa_id16_0_,
auditmessa0_.status as status17_0_,
auditmessa0_.temp_legacy_id as temp_legacy_id18_0_,
auditmessa0_.event_time as event_time19_0_,
auditmessa0_.user_agent as user_agent20_0_,
auditmessa0_.actor_user_id as actor_user_id21_0_,
auditmessa0_.actor_username as actor_username22_0_
from audit_messagetest auditmessa0_
where auditmessa0_.event_time >= to_timestamp('Jan 01 09:40:08 2022', 'Mon DD HH:MI:SS YYYY')
and auditmessa0_.event_time < to_timestamp('Oct 23 09:40:09 2022', 'Mon DD HH:MI:SS YYYY')
and lower(auditmessa0_.sa_id) = '8a00861780ce9dba0180fe6e971c058c'
order by auditmessa0_.event_time desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.05 0.17 65 255 0 0
Execute 2 0.06 0.79 5 2176 0 0
Fetch 134 1.95 55.26 0 0 0 132
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 138 2.06 56.23 70 2431 0 132
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 377
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
66 66 66 PX COORDINATOR (cr=1088 pr=2 pw=0 time=27800884 us starts=1)
0 0 0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=66885 size=545219427 card=605127)
0 0 0 SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us starts=0 cost=66885 size=545219427 card=605127)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=62907 size=545219427 card=605127)
0 0 0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=62907 size=545219427 card=605127)
0 0 0 PX BLOCK ITERATOR PARTITION: 32 1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=62907 size=545219427 card=605127)
0 0 0 TABLE ACCESS FULL AUDIT_MESSAGETEST PARTITION: 6496 6177 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=62907 size=545219427 card=605127)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
PGA memory operation 21 0.00 0.00
reliable message 5 0.28 0.29
enq: KO - fast object checkpoint 4 0.00 0.00
oracle thread bootstrap 24 0.02 0.12
enq: PS - contention 88 0.01 0.06
latch: shared pool 8 0.00 0.03
PX Deq: reap credit 68119 0.02 0.74
PX Deq: Join ACK 128 0.01 0.11
PX Deq: Parse Reply 128 0.01 0.11
SQL*Net message to client 136 0.00 0.00
SQL*Net message from client 136 14.76 27.64
PX Deq: Execute Reply 1376 1.40 53.67
resmgr:cpu quantum 13 0.00 0.02
IPC send completion sync 216 0.10 0.23
PX Deq: Signal ACK EXT 128 0.10 0.25
latch: parallel query alloc buffer 2 0.01 0.01
PX Deq: Slave Session Stats 128 0.00 0.00
Disk file operations I/O 3 0.00 0.00
gc cr multi block grant 1 0.00 0.00
db file parallel read 1 0.00 0.00
gc cr multi block mixed 1 0.00 0.00
latch free 2 0.00 0.00
********************************************************************************
SQL ID: 0bzyrc5yb4m22 Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid,
current_user)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22,:23)
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 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 3 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
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL AUD$ (cr=0 pr=0 pw=0 time=154 us starts=1)
********************************************************************************
SQL ID: b3853arjnybzv Plan Hash: 0
INSERT INTO AUDSYS.AUD$UNIFIED (AUDIT_TYPE, SESSIONID, PROXY_SESSIONID,
OS_USER, HOST_NAME, TERMINAL, INSTANCE_ID, DBID, AUTHENTICATION_TYPE,
USERID, PROXY_USERID, EXTERNAL_USERID, GLOBAL_USERID, CLIENT_PROGRAM_NAME,
DBLINK_INFO, XS_USER_NAME, XS_SESSIONID, ENTRY_ID, STATEMENT_ID,
EVENT_TIMESTAMP, ACTION, RETURN_CODE, OS_PROCESS, TRANSACTION_ID, SCN,
EXECUTION_ID, OBJ_OWNER, OBJ_NAME, CLIENT_IDENTIFIER, NEW_OWNER, NEW_NAME,
OBJECT_EDITION, SYSTEM_PRIVILEGE_USED, SYSTEM_PRIVILEGE, AUDIT_OPTION,
OBJECT_PRIVILEGES, ROLE, TARGET_USER, EXCLUDED_USER, EXCLUDED_SCHEMA,
EXCLUDED_OBJECT, CURRENT_USER, ADDITIONAL_INFO, UNIFIED_AUDIT_POLICIES)
values
(:AUDIT_TYPE, :SESSIONID, :PROXY_SESSIONID, :OS_USER, :HOST_NAME, :TERMINAL,
:INSTANCE_ID, :DBID, :AUTHENTICATION_TYPE, :USERID, :PROXY_USERID,
:EXTERNAL_USERID, :GLOBAL_USERID, :CLIENT_PROGRAM_NAME, :DBLINK_INFO,
:XS_USER_NAME, :XS_SESSIONID, :ENTRY_ID, :STATEMENT_ID, :EVENT_TIMESTAMP,
:ACTION, :RETURN_CODE, :OS_PROCESS, :TRANSACTION_ID, :SCN, :EXECUTION_ID,
:OBJ_OWNER, :OBJ_NAME, :CLIENT_IDENTIFIER, :NEW_OWNER, :NEW_NAME,
:OBJECT_EDITION, :SYSTEM_PRIVILEGE_USED, :SYSTEM_PRIVILEGE, :AUDIT_OPTION,
:OBJECT_PRIVILEGES, :ROLE, :TARGET_USER, :EXCLUDED_USER, :EXCLUDED_SCHEMA,
:EXCLUDED_OBJECT, :CURRENT_USER, :ADDITIONAL_INFO, :UNIFIED_AUDIT_POLICIES)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 3 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
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL AUD$UNIFIED (cr=0 pr=0 pw=0 time=140 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 6 0.00 0.00
log file sync 1 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.05 0.17 65 255 0 0
Execute 2 0.06 0.79 5 2176 0 0
Fetch 134 1.95 55.26 0 0 0 132
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 138 2.06 56.23 70 2431 0 132
Misses in library cache during parse: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 245 0.00 0.00
SQL*Net message from client 245 14.76 34.87
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
PGA memory operation 40 0.00 0.00
Disk file operations I/O 3 0.00 0.00
db file sequential read 51 0.00 0.04
gc cr grant 2-way 31 0.00 0.01
reliable message 5 0.28 0.29
enq: KO - fast object checkpoint 4 0.00 0.00
oracle thread bootstrap 24 0.02 0.12
enq: PS - contention 88 0.01 0.06
latch: shared pool 8 0.00 0.03
PX Deq: reap credit 68119 0.02 0.74
PX Deq: Join ACK 128 0.01 0.11
PX Deq: Parse Reply 128 0.01 0.11
PX Deq: Execute Reply 1376 1.40 53.67
resmgr:cpu quantum 13 0.00 0.02
IPC send completion sync 216 0.10 0.23
PX Deq: Signal ACK EXT 128 0.10 0.25
latch: parallel query alloc buffer 2 0.01 0.01
PX Deq: Slave Session Stats 128 0.00 0.00
gc cr multi block grant 1 0.00 0.00
db file parallel read 1 0.00 0.00
gc cr multi block mixed 1 0.00 0.00
latch free 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.01 0.01 0 0 6 2
Fetch 1 0.02 0.12 65 191 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.04 0.14 65 191 6 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 9 0.00 0.00
Disk file operations I/O 3 0.00 0.00
asynch descriptor resize 1 0.00 0.00
db file sequential read 65 0.01 0.08
gc cr grant 2-way 30 0.00 0.01
gc current grant 2-way 6 0.00 0.00
IPC group service call 1 0.00 0.00
log file sync 1 0.00 0.00
3 user SQL statements in session.
2 internal SQL statements in session.
5 SQL statements in session.
********************************************************************************
Trace file: backdbpp1_ora_1542848_QUERY_AUDIT2.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
2 internal SQL statements in trace file.
5 SQL statements in trace file.
4 unique SQL statements in trace file.
71606 lines in trace file.
86 elapsed seconds in trace file.
[oracle@ORAX8PPD1 trace]$