| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Taking a while to generate a query plan?
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
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_at_seiler.us> 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-lReceived on Fri Jun 01 2007 - 15:50:44 CDT
 |
 |