Oracle TKPROF Hidden/Undocumented Parameters
While I was examining with tkprof, I noticed that there are 2 undocumented parameters (Oracle 11gR2): verbose and diag. Let’s take a look at them.
Verbose: If you set verbose=y, tkprof will provide some extra information on output files. It adds “SQL Text addres(s)” and “SQL Text Hash Value” lines for each query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL ID: 3g7sxtj9d6zd3 Plan Hash: 742841275 select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 9 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.00 0.00 0 9 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE SQL Text addres: 7dea7d18 SQL Text Hash Value: 1389591971 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 GROUP BY (cr=3 pr=0 pw=0 ... |
It also adds a summary part to the end of the output file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Oracle command counts: create table 23 insert 14 select 185 update 17 delete 53 create sequence 3 create view 1 create procedure 2 alter procedure 2 alter session 64 commit 1 pl/sql execute 26 Oracle commands with error during parse: None found. |