-
i have a table named test.
which occupy around 400m.
i run two query as the following:
select * from test order by a;
select /*+PARALLEL(test,3)*/ * from test order by a;
for no parallel one , if data is inside SGA, it will read from SGA, for parallel one, even if data is inside SGA, it still read from disk.
am i right?
the reason i am asking this is because
i run both query several times, each time
i run first one, i didn't notice much disk activity.
but when i run second one, i notice a lot of disk activity.
also , i notice that parallel one is actually SLOWER than the
one without parallel.
why?
-
Can you check the database buffer before and after the first query?
Did you check the explain plan for both?
If there is an index on a, the optimiser might choose to do a full index scan, hence the faster return. (this is especially true for aggregrate functions)
The parallel hint may be forcing the optimiser to choose a full table scan. The overhead of splitting and recombining the query results for parallel might actually make you machine CPU bound (not knowing the no of CPUs you have) and slow the whole process down, or if they are hitting the same disk I/O bound??.
Even with enough CPU, parallel queries work better with larger datasets.
Put up the explain plan for both and we can see what is happening, also set AUTOTRACE on, so we grab the stats as well.
Have Fun
-
Everything roobaron said is true. I might also add that 3 is an extremely small degree of parallelism. There is a lot of overhead to making a query parallel. One cannot expect a sufficient return on that investment with only 3 threads. I would up that to 5 * #processors as a start.
- Chris
-
How many CPUs does the box have?
Is the data file striped across many disks?
-
roobaron:
Can you check the database buffer before and after the first query?
tamilselvan:
i have 8 cpu and data is striped by two disk
chrisrlong:
don't you think i should start with
5*#cpu which is 40 degree
-
When the data is striped across 2 disks, more than 2 Parallel Query Processes is a waste. In fact, They will slow down the entire process.
Parallel Query Execution Plan =
Minimum ( number of CPUs or number of data files on which the table is striped)
Also remember that the number of extents created for the object should be the degree of parallelism. For example, you want to create an index with degree of parallelism 4. Assume that the index data file is striped across 4 disks. And the box has 8 CPUs. In order to use Parallel index creation, the object must be created with atleast 4 extents of any size. Then only you would gain the performance of parallelism, because each Parallel Query Process uses one extent, and later they will be merged.