Forum Stats

  • 3,769,601 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Redo Stats on Custom table - Performance

User910243567
User910243567 Member Posts: 620 Silver Badge
edited Jan 29, 2018 7:53PM in General Database Discussions

Hello All,

We have table with nearly 50 million rows and growing with 150k rows each month. We are seeing performance issues lately on program which is using this table, Does redoing stats on custom table will help little. While we are checking other issues in the program.

Thanks

Tagged:
AndrewSayer

Comments

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 29, 2018 2:35PM
    User910243567 wrote:Hello All,We have table with nearly 50 million rows and growing with 150k rows each month. We are seeing performance issues lately on program which is using this table, Does redoing stats on custom table will help little. While we are checking other issues in the program.Thanks

    No, you are shooting in the dark. Please turn on the light, where is the time going in your critical processes? Can you share the execution plans? The wait statistics? A tkprof would be brillient.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 29, 2018 2:42PM
    User910243567 wrote:Hello All,We have table with nearly 50 million rows and growing with 150k rows each month. We are seeing performance issues lately on program which is using this table, Does redoing stats on custom table will help little. While we are checking other issues in the program.Thanks

    Read, Fire, AIM!

    http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888

    Please refer to URL above & be sure to provide the details requested:
    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof


  • User910243567
    User910243567 Member Posts: 620 Silver Badge
    edited Jan 29, 2018 3:04PM

    Yes I will be checking the tkprof and share it here, The reason i have asked about redoing stats as their is considerable difference in run time in 2 different database instances.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 29, 2018 3:57PM
    User910243567 wrote:Yes I will be checking the tkprof and share it here, The reason i have asked about redoing stats as their is considerable difference in run time in 2 different database instances.

    Sure, but statistics are used to determine execution plans. If you haven't looked at the execution plan and decided it's doing something wrong (and that means you know theirs a better way of doing it), then there's no point in looking at statistics.

    There could be any number of problems and causes of those problems, without seeing what's going on (where your time is going) then it's silly to just make up possible solutions

    1) they might not help

    2) they might take a long time to implement

    3) they might make things worse

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 29, 2018 4:20PM
    User910243567 wrote:Yes I will be checking the tkprof and share it here, The reason i have asked about redoing stats as their is considerable difference in run time in 2 different database instances.

    WAIT a minute!

    There was no mention of 2 different database instances in the first post.

    When you observe different results you can be 100% certain that something is different.

    From each database provide necessary details listed below

    0) actual SQL statement

    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof

    AndrewSayer
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Jan 29, 2018 7:53PM

    Are the "performance issues" a result of poor Execution Plans ? 

    If so, are the "poor Execution Plans" a result of insufficient or stale statistics ?

    Hemant K Chitale

This discussion has been closed.