TKPROF output question

From: <samdba34_at_yahoo.com>
Date: Wed, 24 Oct 2007 11:58:17 -0700
Message-ID: <1193252297.014178.265920@y27g2000pre.googlegroups.com>

Hi

We recently got new storage and are in the process of moving our database to the new faster storage. The data was exported and imported into the new database. But when I run a Pl/Sql procedure in the new database it takes around 34 minutes to cpmplte where as it takes just 5 mts to complete in the original database. The main difference I found between the 2 trace files is in the OVERALL TOTALS FOR ALL NONRECURSIVE  STATEMENTS which for the new instance goes as follows :

BEGIN Load_TABLE(to_date('10/22/2007','mm/dd/yyyy')); END;

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.00       0.00          0          0
0           0
Execute      1    597.67    2038.05       6730   47845804
55676347           1
Fetch        0      0.00       0.00          0          0
0           0

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

---

total        2    597.67    2038.06       6730   47845804
55676347           1

The corresponding value in the original database goes like this

BEGIN Load_TESTFACT(to_date('10/22/2007','mm/dd/yyyy')); END;

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.01       0.14          0          0
0           0
Execute      1     16.32      44.54          0      23183
71           1
Fetch        0      0.00       0.00          0          0
0           0

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

---

total        2     16.33      44.69          0      23183
71           1

The no. of rows being loaded are same and the init parameters are same. In the new database the block size is 32 instead of 16 in the original database.

This change is reflected in the OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS section of the trace file:

OLD :
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        3      0.02       0.41          0          0
0           0
Execute      4     18.19     504.79      45961     163614
558           2
Fetch        0      0.00       0.00          0          0
0           0

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

---

total        7     18.21     505.20      45961     163614
558           2

NEW:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        3      0.00       0.03          0          0
0           0
Execute      4    601.80    2048.31       8430   48003072
55681654           2
Fetch        0      0.00       0.00          0          0
0           0

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

---

total        7    601.80    2048.34       8430   48003072
55681654           2

Can you throw some light on what the section NON-RECURSIVE STATEMENTS means.

Thanks

Sam Received on Wed Oct 24 2007 - 13:58:17 CDT