Index Range Scans and Parallel Nested Loops

From: Kenneth Naim <kennethnaim@xxxxxxxxx>
To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 8 Nov 2010 14:09:48 -0500

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

Index Range Scans and Parallel Nested Loops

Other related posts: