FreeLists / oracle-l / Re: Taking a while to generate a query plan?

["Don Seiler" <don@xxxxxxxxx>]

OK I've run 10053 level 1 and 10046 level 12 traces just to be
prudent.  Pasted below.

The query has an OR, and each field in the OR has a global nonunique
index on it.  The query is crafted so as to span two of the monthly
partitions.  The table is partitioned on disposal_time.  disposal_time
is also the second field in a two-field global primary key.

I'm curious as to the query plan, where in each of those OR field
indexes brings back 40K-50K rows, which are then pared down to the end
total of 184.  If I am indeed envisioning that correctly, that would
explain the high fetch count.

I'm wondering what the impact of global vs local is in this case.
Would a compound index on (src_addr, disposal_time) behave better?

********************************************************************************

SELECT   event_id, record_type, src_addr, dest_addr, src_route, dest_route,
        submit_time, disposal_time, disposal_type, REFERENCE, msg_length,
        priority, request_def, product_id, receipt_req, description, ROWID
   FROM foo
  WHERE (src_addr = '7155555555' OR dest_addr = '7155555555')
    AND product_id LIKE '00000200000'
    AND record_type LIKE '%'
    AND disposal_time >= to_date('03/07/2007', 'MM/DD/YYYY')
    AND disposal_time <= to_date('04/06/2007', 'MM/DD/YYYY')
ORDER BY disposal_time DESC

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       14      4.38       4.38      33190      34397          0         184
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      4.38       4.39      33190      34397          0         184

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 826

Rows     Row Source Operation
-------  ---------------------------------------------------
   184  SORT ORDER BY (cr=34397 pr=33190 pw=0 time=4388878 us)
   184   CONCATENATION  (cr=34397 pr=33190 pw=0 time=935633 us)
    53    TABLE ACCESS BY GLOBAL INDEX ROWID foo PARTITION: ROW
LOCATION ROW LOCATION (cr=15275 pr=15192 pw=0 time=118868 us)
 39299     INDEX RANGE SCAN foo_DEST_ADDR (cr=258 pr=258 pw=0
time=518081 us)(object id 174704)
   131    TABLE ACCESS BY GLOBAL INDEX ROWID foo PARTITION: ROW
LOCATION ROW LOCATION (cr=19122 pr=17998 pw=0 time=366133 us)
 49395     INDEX RANGE SCAN foo_SRC_ADDR (cr=343 pr=343 pw=0
time=649649 us)(object id 174703)


Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                      14        0.00          0.00
 db file sequential read                     33190        0.00          1.67
 SQL*Net message from client                    14        0.02          0.06
********************************************************************************



On 6/1/07, Don Seiler <don@xxxxxxxxx> wrote:
> Interesting situation.  Running 10.2.0.2 EE.  This situation can be
> duplicated on our production and 3 development instances.
>
> We have a table with monthly partitions.  I'm told that "all of a
> sudden (today)" new queries are taking their time running against
> their table.  Once they are run the first time, successive attempts
> are fine.  When I say "new queries" I mean basically the same query
> with a literal value changed in the predicate (yes I've discussed bind
> vars already).
>
> We were able to duplicate this by flushing the shared pool, and not
> flushing the buffer cache.  Based on this, I'm inclined to believe
> that data/index I/O was not a major factor in the time spent waiting.
> The query plan itself looked great, low cardinality and cost, using
> the desired indexes properly.
>
> We wait sometimes as long as 30 seconds before the query comes back.
>
> Where might one look to investigate what is happening during this time?
>
> --
> Don Seiler
> oracle blog: http://ora.seiler.us
> ultimate: http://www.mufc.us

--
Don Seiler
oracle blog: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l