High elapsed/cpu ratio

From: <ebwright_at_gmail.com>
Date: Mon, 10 Sep 2007 11:07:16 -0700
Message-ID: <1189447636.545188.240490@w3g2000hsg.googlegroups.com>

I'm working on tuning a particular query and have gotten the explain plan as pretty as possible, low cost, etc. However, when running the query (an insert into table select ... statement), I find the following information in the tkprof trace file:

call     count       cpu    elapsed       disk      query
current        rows

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

Parse        1      0.02       0.02          0          0
0           0
Execute      1    170.59    2289.34     870183   16953935
49           5
Fetch        0      0.00       0.00          0          0
0           0

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

total        2    170.61    2289.37     870183   16953935
49           5

The cpu spent less than 3m executing the query, but it took nearly 40m for results to be retrieved and inserted. This was done in a development environment with little to no other users (so, no locking issues), fresh statistics on tables and indexes, and an empty target table that was truncated immediately before processing.

The one issue I know that could cause problems is that the tables being accessed are in a very large (~500 GB) tablespace. This is not something that can be changed at this time.

Thanks,
Eric Received on Mon Sep 10 2007 - 13:07:16 CDT