Eine wenig bekannte aber interessante Option des TKPROFs ist „pdbtrace“.
tkprof kann so die formatierte Ausgabe erzeugen ohne direkt Zugriff auf die Trace-Files auf Fileebene des Datenbank-Servers zu haben.
select value from v$diag_info where name='Diag Trace';
–> Trace-Directory ermittlen
tkprof <input_tracefile> <output_textfile> <options> pdbtrace=<DB-user><password>/@ora_connectstring
Beispiel:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';
EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace => true);
select user from dual;
EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace => false);
tkprof ..._my_trace_id.trc /tmp/tkprof_output.txt pdbtrace=...
[]$ more /tmp/tkprof_output.txt
TKPROF: Release 19.0.0.0.0 – Development on Tue Apr 20 12:09:42 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: CDB1_ora_11507_my_trace_id.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: 0g5n01r51t3f1 Plan Hash: 0
BEGIN DBMS_SESSION.SET_SQL_TRACE(sql_trace => true); END;
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 2 184 0 1
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 1 0.00 0.00 2 184 0 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
SQL ID: ahc9t761kxw30 Plan Hash: 1388734953
select user
from
dual
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