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 -- //www.freelists.org/webpage/oracle-l