Search code examples
oraclegroup-bysum

Improving the SQL query processing performance of SUM in Oracle for a huge table

I have a table containing about 1 billion rows with 50 columns and I have a query like this but the processing time is extremely slow, or even no response, due to numbers of SUM functions included. The query is used in direct database query in Oracle Analytics.

When all SUMs and GROUP BY are removed, it takes about 1 min to return result of 1 million rows. With all the SUMs and GROUP BY added, the number of rows becomes about 5k.
As I need to use it in Excel for further analysis, I have to keep the output rows as less as possible to keep it small data size and have processing time not too slow.

Select Col_1,
       Col_2,
       ...,
       Col_10,
       SUM(CASE WHEN col_5 = 1 and Col_6 = a then AMOUNT ELSE 0 END) as AMT_1,
       SUM(CASE WHEN col_5 = 2 and Col_6 = b then AMOUNT ELSE 0 END) as AMT_2,
       ...
       SUM(CASE WHEN col_5 = 6 and Col_6 = d then AMOUNT ELSE 0 END) as AMT_24
from table_a 
where col_1 = 1
      col_5 in (1,2,3,4,5,6)
      col_6 in (a,b,c,d)
Group by Col_1, Col_2,...,Col_10

I have tried to get the query plan with EXPLAIN or add index by CREATE INDEX but unfortunately they are both not supported. it is confirmed that SELECT statement or WITH clause can be used in the system. And I have no other application or system which can access the same database that I need. What can I do to optimise the performance?

Solution

  • You definitely don't want to use indexes for this. Here's a few things to check:

    1. Are you using parallelism (PQ)? With a billion rows, you're going to want a healthy # of PX slaves. Assuming your database server has sufficient CPU, try with this hint:

    SELECT /*+ parallel(16) */ col_1, ....

    1. Make sure you are actually getting the parallel slaves requested. Check gv$session and see if you have twice the # of P0** processes as the DOP requested. If not, ask your DBA if you are being downgraded for some reason. Just because you get a parallel plan doesn't mean you'll get the actual parallel slaves you want at exec time.

    2. Check the explain plan and make sure it's doing a full table scan. You don't want indexes being used here. If you do see an index being used, force a full scan with a hint: SELECT /*+ parallel(16) full(table_a) */

    3. If the table is partitioned, can you add a predicate on the partitioning key (see all_part_key_columns) to prune partitions you don't need?

    4. Ask your DBA or if you can query yourself v$pgastat and check the value for global memory bound. Is it less than 1G? If so, have your DBA raise the pga_aggregate_target (and limit, too, if set and not high enough) until the global memory bound shows its maximum possible value of 1G. This will ensure you get as much memory as you need allocated to those aggregations before using temp space in a workarea. Just be sure you have enough free memory or OS file cache (freeable) on the database host(s). What this does is reduce the amount of time spent on I/O to the temporary tablespace and allow the hash or sort aggregations to take place in physical memory. Or rather, allow more of it to happen in memory than otherwise would. Chances are with 1 billion rows something will spill to disk no matter what, but you want to be as generous as you can be.

    5. Finally, if none of the above works for you, if you have EE and Diagnostics and Tuning option then generate a SQL Monitor report for your sql_id and share it with us:

      SELECT dbms_sql_monitor.report_sql_monitor(sql_id => '82nqt262ycyng', -- replace with your sql_id
                                           report_level => 'ALL') 
  FROM dual;