Re: Index question

From: <stevedhoward_at_gmail.com>
Date: Tue, 25 Aug 2009 09:57:32 -0700 (PDT)
Message-ID: <759c8035-99e5-466c-9677-78619bcc6b55_at_34g2000yqi.googlegroups.com>

On Aug 25, 12:48 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> Trace them and post execution plans.
> Maybe an index corruption.
>
> Regards
> Michel

Thanks, Michel. Below is the 10046 trace of both (no errors in the trace file).

---

select min(jrn_seq) from xwc.xwc_change_history where jrn_seq between 1019514060 and 1019514080

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.01       0.01          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.01          1          4
0           1

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

---

total        4      0.01       0.02          1          4
0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=1 pw=0 time=14558 us)
      1   FIRST ROW  (cr=4 pr=1 pw=0 time=14532 us)
      1    PARTITION HASH ALL PARTITION: 1 8 (cr=4 pr=1 pw=0
time=14526 us)
      1     INDEX RANGE SCAN (MIN/MAX) XWC_CHG_HIST_JRN_GBL PARTITION:

1 8 (cr=4 pr=1 pw=0 time=14509 us)(object id 40380)

---

select jrn_seq from xwc.xwc_change_history where jrn_seq between 1019514060 and 1019514080 order by 1

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.02          2         32
0           9

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

---

total        4      0.00       0.03          2         32
0           9

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation

-------  ---------------------------------------------------
      9  SORT ORDER BY (cr=32 pr=2 pw=0 time=26677 us)
      9   PARTITION HASH ALL PARTITION: 1 8 (cr=32 pr=2 pw=0 time=98
us)
      9    INDEX RANGE SCAN XWC_CHG_HIST_JRN_GBL PARTITION: 1 8 (cr=32

pr=2 pw=0 time=26562 us)(object id 40380) Received on Tue Aug 25 2009 - 11:57:32 CDT