Post reply

Poor Performance : Select count(*) takes 30 Mins ? Why ?

  • December 4, 2008 at 6:13 pm

    #198446

    Our ERP System (JDE) uses Oracle 8i as our database .

    We found that the performance of query table is getting poor .

    Select count(*) takes 30 Mins (select count(*) from hqprddta.F0911) to get the output !

    How can I solve this ?

    Thanks a lot !

    Geoffrey

  • December 4, 2008 at 6:45 pm

    #908034

    No chance of using indexes... it's gonna return the whole table. How many rows are in the table? Also, are you sure it's a table and not a view?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 4, 2008 at 7:00 pm

    #908035

    The Record Count is 9777594 !

    It's table !

  • December 4, 2008 at 7:15 pm

    #908038

    Sorry... I missed the fact that you were doing a count on the table... not returning rows... Got a PK or any decent indexes on the table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 4, 2008 at 7:19 pm

    #908039

    Nope, just blame it on Oracle.

  • December 4, 2008 at 7:40 pm

    #908043

    The table has PK and 13 indexes !

  • December 5, 2008 at 4:47 am

    #908155

    Please do this, get and post execution plan for both "select count(*)..." and "select count(your-pk-column)..."

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply