Re: Index Rebuilds

From: Terry Sutton <terrysutton_at_usa.net>
Date: Fri, 28 Jul 2006 18:22:57 -0700
Message-ID: <003b01c6b2ae$00146cb0$3c03a8c0@TerrySutton>

What is the result of this query?
> select count(*)
> from
> objects where type = 'live'

I.e., how many rows have type = 'live'?

--Terry

> LBLKS: 66653
> BlVL: 3
> CF: 679426
> NROWS: 829734
> AVG_DATA_BLK/KEY: 2
>
> Coalescing the index got the lblks to 11k and the desired plan
> however, the performance is still under water. 10046 trace on the sql
> is
>
> select count(*)
> from
> objects where type = 'live'
>
>
> call count cpu elapsed disk query current
rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----

---

> Parse 1 0.01 0.00 0 0 0
0
> Execute 1 0.00 0.00 0 0 0
0
> Fetch 2 10.39 31.07 65484 69693 0
1
> ------- ------ -------- ---------- ---------- ---------- ---------- ----

---

> total 4 10.40 31.08 65484 69693 0
1
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 5
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE
> 832154 INDEX FAST FULL SCAN IX_TYPE_LASTMODIFIED (object id 25015)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
Waited
> ----------------------------------------
 Waited ---------- ------------
> SQL*Net message to client 2 0.00
0.00
> db file sequential read 536 0.00
0.23
> db file scattered read 5518 0.09
25.62
> SQL*Net message from client 2 22.65
22.65
>

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

Received on Fri Jul 28 2006 - 20:22:57 CDT