René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Query and current in tkprof

Be sure to also take a look at Basics of tkprof.

Setting up a demonstration environment

A table is created to demonstrate the use of TKPROF. This PL/SQL Script will take care of this.
Ok, now let's give it a try:
alter session set sql_trace=true;
alter session set timed_statistics=true;
select id, ob from test_for_tk_1 where nm='elit magna wisi nulla praesent possim';
When the statement returns, we go to the directory pointed to by user_dump_dest and do a:
tkprof ora01676.trc perf
Then, we open the file perf.prf which was created by tkprof and search for the statement:
select id, ob 
from
 test_for_tk_1 where nm='elit magna wisi nulla praesent possim'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          1           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.49      30.56      37355      75405          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.49      30.56      37355      75405          5           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL TEST_FOR_TK_1
How is this table to be interpreted? First, the statement was parsed and executed once.

query

Query: also known as consistent gets
The TKPROF output includes in his header the following explanation:
query = number of buffers gotten for consistent read
query means: blocks gotten in consistent mode. That could possibly entail reading the rollback segment to reconstruct the data.

current

current: also known as db block gets.
The TKPROF output includes in his header the following explanation:
current = number of buffers gotten in current mode (usually for update)
current mode means: the blocks are read as they are (either in the datafile or still in the buffer cache). Current gets might entail waiting for some data.