Help with SQL Trace

From: Larry Lutz <larry.j.lutz_at_gmail.com>
Date: Wed, 8 Feb 2006 09:42:21 -0500
Message-ID: <abc34f0e0602080642s131cb57dqfbf1bca997124502@mail.gmail.com>

The SQL below is simple since dual is a one row table. The plan is as exected. However, I would expect the logical and physical blocks read to be 2 at the most, and the elapsed time to be less than a second. Twenty minutes is obscure. Any one know how or if this can be possible? It came straight out of a production system:

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') Process_End_Time from dual

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----------

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

Parse 1 0.00 0.00 0 0 0 0
Execute 2 1271.80 1318.39 475439 21600279 2563509 1
Fetch 2 0.00 0.00 0 3 0 1

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

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

total 5 1271.80 1318.39 475439 21600282 2563509 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 111 ()

Rows Execution Plan

0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'DUAL'

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 08 2006 - 08:42:21 CST