strange tkprof output for SQLs - II

From: Danisment Gazi Unal (Unal Bilisim) <dunal_at_unal-bilisim.com>
Date: Wed, 20 Mar 2002 11:01:15 -0800
Message-ID: <F001.0042ED76.20020320110115@fatcity.com>

Hello,

I did 2 tests for PL/SQL and SQL statements. This is the test for SQL. Here are the steps:

SQL > alter session flush shared_pool;
SQL > alter session set sql_trace=true;
SQL > insert into test select * from test;
SQL > alter session set sql_trace=false;

FROM DICTIONARY: SVRMGR> select
SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql where sql_text like 'insert into test select * from test%';

SQL_TEXT                              DISK_READS BUFFER_GET ROWS_PROCE

COMMAND_TY
------------------------------------- ---------- ---------- ----------

---

insert into test select * from test          345       1014
8192          2

1 row selected.

FROM RAW TRACE FILE: PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 hv=895761708 ad='5083d50c'
insert into test select * from test
END OF STMT
PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 .
other recursive statements.
.
. near end of file
EXEC
#1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464

BUFFER GETS IN RAW TRACE FILE:
cr: 597 + 42 = 639
cu: 373 + 2 = 375

Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS

DISK_READS IN RAW TRACE FILE: p: 17 + 328 = 345, which is same as v$sql.DISK_READS.

According to these test, results in dictionary and raw trace files are same. But tkprof formats as below:

insert into test select * from test

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.02         17         42
1           0
Execute      1      0.12       1.86         91        126
356        8192
Fetch        0      0.00       0.00          0          0
0           0

------- ------ -------- ---------- ---------- ---------- ----------

---

total        2      0.12       1.88        108        168
357        8192

DISK_READS = 108
BUFFER GETS = 168 + 357 = 525 Question:

Which one is correct ? Dictionary/raw trace file or tkprof results ?

My comment:

I guess, tkprof substructs child recursive statements from parent user statement ? Why ? This is not a PL/SQL statement ? So, statistics are already not included in parent statement ? I guess statistics in raw trace files are inclusive statistics which include statistics of their child statements according to call orders of kernel calls. But is this expected behavior.

Thanks in advance...

--

Danisment Gazi Unal
http://www.unal-bilisim.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Danisment Gazi Unal (Unal Bilisim)   INET: dunal_at_unal-bilisim.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Mar 20 2002 - 13:01:15 CST