Re: Index question
Date: Tue, 25 Aug 2009 19:29:40 +0200
Message-ID: <4a941f85$0$13356$426a74cc_at_news.free.fr>
<stevedhoward_at_gmail.com> a écrit dans le message de news: 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)
Yes I tend to think about an index corruption in the min/max values. Try to rebuild the index.
Regards
Michel
Received on Tue Aug 25 2009 - 12:29:40 CDT