Re: long running select min(timestamp) query

From: <bobdurie_at_gmail.com>
Date: Wed, 30 Jan 2008 09:12:58 -0800 (PST)
Message-ID: <f9bd3e5a-f60a-462d-887e-d735b3c96854@i12g2000prf.googlegroups.com>

I'm afraid i don't know how to calculate what the top waits are - i've seen references to it, but nothing conclusive. That udump file i had contains MANY waits, is there a tool that will go through it and summarize?

I think i finally figured out how to get the xplan, although i'm not sure what it tells me:
SQL> select

  2     /*+
  3             gather_plan_statistics
  4             ordered use_nl(events) index(events)
  5     */
  6     min(eventdate)
  7  from
  8     events

  9 ;

MIN(eventdate)

---

22-JAN-08 08.44.55.912000 AM SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT

---

SQL_ID 797v1v1tbvp6s, child number 0

---

select /*+ gather_plan_statistics ordered use_nl(events) inde x(events) */

 min(eventdate) from events

Plan hash value: 116994577

---

PLAN_TABLE_OUTPUT

---

| Id  | Operation                  | Name             | Starts | E-

Rows | A-Rows | A-Time | Buffers | Reads |

---

|   1 |  SORT AGGREGATE            |                  |      1 |
1 | 1	 |00:14:53.92 |     118K|    118K|
|   2 |   INDEX FULL SCAN (MIN/MAX)| EVENTS_EVENTDATE |      1 |

516K|

PLAN_TABLE_OUTPUT

---

 1 |00:14:53.92 | 118K| 118K|

---

14 rows selected.

This indicates that there are tons of estimated rows - i'm not sure what else this really tells me... i'm still digging on the other responses, thanks for all your prolonged support! Received on Wed Jan 30 2008 - 11:12:58 CST