When Parallel is Wrong

The Lead Developer and I have been working very, very hard on tuning the main process that produces much of the revenue for our company. The tuning exercise has been very successful but we’ve run into some challenging on the pre-processing that occurs for each week to build the mart that the newly tuned process runs on afterwards. Due to this, I’ve been granted some time to monitor the build process a little closer than previously and although I have a lot of tuning to do, some of the largest challenges comes in the form of queries that the analysts have to execute as part of the final checks before we toggle over to the newly created mart.

These statements all have the same where clause, two different tbl_keys, (12 or 48), pulling the entire table, (each table between 90-380GB) and then ordering the data by the i_id. They all have the same parallel hints, just different tables substituted into the statements. The reason for the sort is that the data is then pulled into an application to finish the final step. The application is incapable of dealing with this type of large sorts, so they decided to do it internal to the database. The final data is used to verify that the correct data is present in the mart of the members that were fed into the primary database that the mart is based off of for the timeline.

select /*+ full(t) parallel (t 8) */ t.*
from tbl_summary t WHERE (tbl_key=12 and usr_id=0)
 
order by i_id ;

The above statement is going to result in 16 parallel slaves, pulling the entire data set for user class=0 vs. a specific id, for the last 12 months of data, then sort it by the usr_id. Note the first hint- FULL. Realize, as this data is across multiple partitions, we will not see a “partition list single” in the explain plan, but “px iterator” for the database to correctly identify the individuals that meet the criteria for each partition. Once it completes this step, then the challenge comes in sorting all that data and the problem with the second hint- PARALLEL.

I love parallel. I am thrilled with the performance gains I can attain when hashing large tables when accessing multiple partitions. I do not love parallel when someone decides to put a group by or order by into the statement. If any sorting has to occur on the statement, I’ve been seeing some odd behavior in about 80% of the executions. It appears that when sorting, it’s not Oracle sorting the data all at once, but sorting one parallel slave’s data set at a time. This creates an escalation in temp space usage that can become insanely difficult for Oracle to process efficiently and performance degrades immensely.

This is what occurred with the statements for the analysts.
With Parallel:
select /*+ full(t) parallel (t 8) */ t.*
 
from tbl_summary t WHERE (tbl_key=12 and usr_id=0)
order by i_id ;

Elapsed Time Breakdown
SQL Time (sec) 47,951.17
PL/SQL Time (sec) 0.00
Java Time (sec) 0.00

Activity By Time
Elapsed Time (sec) 47,951.17
CPU Time (sec) 9,734.81
Wait Time (sec) 38,216.35

Activity By Waits
Remaining Waits(20.9%)
User I/O Waits(58.8%)
CPU(20.3%)

I decided to query the temp usage, as I knew the above query would need to sort the ib_id and would impact the performance of the parallel execution.  The first and last sql_id in the table are the queries in question with parallel, which the one owned by kpedersen is my run at it without parallel:

Now after seeing the waits for temp, the long operations, (441 minutes left on the sort in temp…) I decide to say enough it enough and attempt the execution without the hints- just plain ol’ query of this huge table, nothing more, (see above in the table, showing my temp usage for the single process, as “Mine”.) Fifteen minutes, yes, that’s right, fifteen minutes and I had the first rows back on my screen:

SQL Text-
select t.* from tbl_summary t
WHERE (tbl_key=12 and usr_id=0) order by i_id;

DETAILS on Without Parallel:
Elapsed Time Breakdown
SQL Time (sec) 945.25
PL/SQL Time (sec) 0.00
Java Time (sec) 0.00

Activity By Time
Elapsed Time (sec) 945.25
CPU Time (sec) 370.67
Wait Time (sec) 574.58

Activity By Waits
Remaining Waits(1.1%)
User I/O Waits(59.7%)
CPU(39.2%)

Moral:
Carefully test any parallel processing when sorting is involved. You may not receive performance benefits as expected.