This post is more than 5 years old
256 Posts
0
534
Increasing Threads on a Full Table Scan
Michael Barrett just pushed this out to the Oracle Community email distribution list. I thought I would see if we can get an answer here:
Hi,
I have a application team within one of my customer trying to get a baseline for their system performance (Oracle, running on Sun Sparc/Solaris, VxVm and VPLEX/VNX). They are performing full table scans (all read), then adding additional threads to see if the test will scale. They are seeing small improvements, approx. 10%, by adding a second thread, but adding additional threads doesn’t change performance significantly. Our array is performing well, but the view of performance on the array is about the same, no matter how many threads we get. The only indication is an increase in ABQL.
So my question: How much could we expect the performance of a full table scan to increase as we increase threads? By increasing threads, are we performing more table scans or trying to make one table scan go faster? Is it possible to scale a table scan with multiple threads?
To me, multi-threading a table scale doesn’t seem like a great way to test system performance, but I could be wrong.
Regards,
Mike Barrett
Senior Systems Engineer
EMC2
8820 Columbia 100 Pkwy
Columbia, Maryland 21045
C: (240) 401-2599
email: michael.barrett@emc.com
jeff_browning
256 Posts
0
October 25th, 2013 05:00
My off-the-cuff response would be that increasing the threads for a full table scan would improve performance if the individual server process doing the scan was the bottleneck in some way. If the operation is bottlenecked on something else (like mechanical disk, for example), then increasing the number of CPU threads may do very little. It is very environment and workload specific in other words.