[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

performance issue with million record table

Posted on 2008-11-20
6
Medium Priority
?
946 Views
Last Modified: 2012-05-05
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.




0
Comment
Question by:cnagarjuna
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 23002550
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

0
 

Author Comment

by:cnagarjuna
ID: 23002884

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      

0
 

Author Comment

by:cnagarjuna
ID: 23004408
Any update on this issue.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 27

Accepted Solution

by:
sujith80 earned 1500 total points
ID: 23004662
Dont look at the cost.
Did it reduce the execution time?
Looking at what you are expecting out of the query I dont think there could be any other alternatives.
( unless you have an index on summary_line_id and distribution_date and you have a very VALID reason to have that index)

And for the second query try this.
SELECT  /*+ PARALLEL(d) */ 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     NOT EXISTS (SELECT  pa.payroll_action_id  FROM apps.pay_payroll_actions pa
                                          WHERE pa.payroll_action_id = pc.payroll_action_id  )
  GROUP BY sl.SUMMARY_LINE_ID

Open in new window

0
 
LVL 48

Expert Comment

by:schwertner
ID: 23007298
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.
0
 

Author Comment

by:cnagarjuna
ID: 23019268

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'.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question