Index Range Scans and Parallel Nested Loops
From: Kenneth Naim <kennethnaim_at_gmail.com>
Date: Mon, 8 Nov 2010 14:09:48 -0500
Message-ID: <AANLkTimF0BTnOsHWfoEMrYGahhnPp-DsTaQbAJfchLUH_at_mail.gmail.com>
I've been tuning several long running queries on an EE database without the partitioning option. I've taken advantage of parallel nested loops queries to speed up access to large number of rows (20 million) out of a large table (150 million). The indexed column has approximately 160 distinct dates with the same number of row per date (approximately). When the query is run with a range of 16 weeks with parallelism of 16 the query runs for about 2 hours and 16 out of 32 threads that are spawned are active with a wait event of db file sequential read. However when i try to process a single week only 1 thread (out of 32) is active with db file sequential read and also takes about 2 hours implying that each thread is processing one value.
Date: Mon, 8 Nov 2010 14:09:48 -0500
Message-ID: <AANLkTimF0BTnOsHWfoEMrYGahhnPp-DsTaQbAJfchLUH_at_mail.gmail.com>
I've been tuning several long running queries on an EE database without the partitioning option. I've taken advantage of parallel nested loops queries to speed up access to large number of rows (20 million) out of a large table (150 million). The indexed column has approximately 160 distinct dates with the same number of row per date (approximately). When the query is run with a range of 16 weeks with parallelism of 16 the query runs for about 2 hours and 16 out of 32 threads that are spawned are active with a wait event of db file sequential read. However when i try to process a single week only 1 thread (out of 32) is active with db file sequential read and also takes about 2 hours implying that each thread is processing one value.
Is there a way i can have multiple threads process the single value without partitioning and without doing a full table scan. I assume the partitioning option is also required for partitioned indexes? Is using the total number of session waits for db file sequential read an accurrate guestimate of how many nested loops were performed? Would reducing this number by the the number of chained rows fetched during the session/query make trhe number more accurate?
Thanks,
Ken Naim
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 08 2010 - 13:09:48 CST