asked on

performance issue with million record table

There is one table with 4Million rows, running one query is causing performance issue.
Please help on how to resolve it.

SQL> select count(*) from psp.psp_adjustment_lines_history
2 /

COUNT(*)
---------
4193690

Below query is causing performance issue:

SELECT a.summary_line_id,
MAX (a.distribution_date) latest_distribution_date
FROM psp.psp_adjustment_lines_history a
GROUP BY a.summary_line_id

Operation        Optimizer       Cost      Cardinality      Bytes
SELECT STATEMENT      ALL_ROWS       39746        140014      1680168
HASH(GROUP BY)             39746        140014      1680168
TABLE ACCESS(FULL) PSP.PSP_ADJUSTMENT_LINES_HISTORY
ANALYZED       31841 4193690      50324280

Please suggest how to increase the performance.

Sujith

try this.

SELECT  /*+ PARALLEL(a) */ a.summary_line_id,
         MAX (a.distribution_date) latest_distribution_date
 FROM    psp.psp_adjustment_lines_history a
  GROUP BY a.summary_line_id

Open in new window

cnagarjuna

ASKER

a) After this hint the query cost is 1472, it reduced alot. Thank you. It is possible to reduce further.
b) What is this PARALLEL about.
c) One more query is also causing performance issue.
SELECT sl.summary_line_id,
      MAX(d.DISTRIBUTION_DATE) latest_distribution_date
FROM        apps.psp_summary_lines sl
,apps.psp_distribution_lines_history d
,apps.psp_payroll_controls pc
WHERE sl.SUMMARY_LINE_ID=d.SUMMARY_LINE_ID
AND sl.payroll_control_id = pc.payroll_control_id
AND pc.payroll_action_id NOT IN (SELECT pa.payroll_action_id FROM apps.pay_payroll_actions pa)
GROUP BY sl.SUMMARY_LINE_ID

SQL> select count(*) from psp_distribution_lines_history
2 /

COUNT(*)
---------
14768679

Operation      Optimizer      Cost      Cardinality      Bytes
SELECT STATEMENT      ALL_ROWS      8140      31053      1180014
HASH(GROUP BY)            8140      31053      1180014
TABLE ACCESS(BY INDEX ROWID) PSP.PSP_DISTRIBUTION_LINES_HISTORY      ANALYZED      3      12      168
NESTED LOOPS            7828      31053      1180014
NESTED LOOPS            504      2536      60864
NESTED LOOPS(ANTI)            193      62      930
TABLE ACCESS(FULL) PSP.PSP_PAYROLL_CONTROLS      ANALYZED      183      29511      265599
INDEX(UNIQUE SCAN) HR.PAY_PAYROLL_ACTIONS_PK      ANALYZED      0      24536      147216
TABLE ACCESS(BY INDEX ROWID) PSP.PSP_SUMMARY_LINES      ANALYZED      5      41      369
INDEX(RANGE SCAN) PSP.PSP_SUMMARY_LINES_N1      ANALYZED      2      41
INDEX(RANGE SCAN) PSP.PSP_DISTRIBUTION_LINES_HIST_N5      ANALYZED      2      14

cnagarjuna

ASKER

Any update on this issue.

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

It will be good if you run the statistics.
In fact you do not use indexes, but the server
will know more about the table.

Also try to increase sort_area and large_pool in the SGA.
You have many sort tasks in the query: MAX, GROUP BY.
So there will be more RAM place for sorting.

cnagarjuna

ASKER

1) We have already gathered stastics on the problamatic tables.
2) how to know the sort_area, large_pool_area of the SGA.

Actually there is one view, containing 4 uninions. These two queries are part of that view.

Now in production, because of this view it is failing saying 'snapshot too old'.