#15 regular oracle statistic updates

closed-works-for-me
nobody
None
5
2005-05-31
2005-02-26
lofoten
No

I have noticed, that the compiere installation does not
setup oracle statistics for schema "COMPIERE". From a
performance point of view this is harmfull.
I have some scripts which do create/recreate oracle
statistics.

If there is any interest i can adopt them to the
compiere way "RUN_Oraclestatistics.sh" and upload them
here.

regards

Juergen

Discussion

1 2 > >> (Page 1 of 2)
  • Alin Vaida
    Alin Vaida
    2005-02-26

    Logged In: YES
    user_id=23294

    Sounds interesting. Does this apply to 10g as well?

     
  • lofoten
    lofoten
    2005-02-26

    Logged In: YES
    user_id=55360

    Yes.

    Oracle provides some packages /include in every shipment)
    which have to be called.

    Juergen

     
  • Alin Vaida
    Alin Vaida
    2005-02-26

    Logged In: YES
    user_id=23294

    Ok then, I'm interested. Anything that can speed things up is
    welcome.

     
  • lofoten
    lofoten
    2005-02-28

    Logged In: YES
    user_id=55360

    Attached a archive which does maintain oracle statistics

     
  • lofoten
    lofoten
    2005-02-28

     
  • lofoten
    lofoten
    2005-03-09

    Logged In: YES
    user_id=55360

    Has anybody used the scripts?

    Juergen

     
  • Logged In: YES
    user_id=835286

    I am not a DBA, so I do not really understand what the
    scripts do.

    1) What are statistics and how do they improve performance?
    (Please do not refer me to a document on the oracle
    technical sight as I have browsed all those - I would be
    much happier with a simple one-liner in laymans terms - like
    don't teach me how to build and operate a rocket, just tell
    me that a rocket flies into space)

    2) I have installed and run the scripts today but did not
    see any increase in performance. Does it need to be run
    frequently over time before having any effect?

    3) Is the script safe to run while users are actively
    working with the compiere database?

     
  • lofoten
    lofoten
    2005-03-12

    Logged In: YES
    user_id=55360

    Hi kkalice

    1)
    Oracle has a pice of software , called the optimizer, which
    translates a sql statement to a executable entity in order
    to fetch some data. The optimizer does decisions like "shall
    i use a index in order to get a set of requested data, or
    might it be better to inspect the table sequentially and
    therefore bypass any indexes."

    Both ways to get the data deliver the same resultset, but
    with different execution times. (selecting 1 row from 1
    million is faster via index, selecting 700.000 from 1
    million is faster via walking the table sequentially)

    The oracle optimizer does need some information about the
    number of rows in tables and indexes to get to a fastest
    exection plan.
    The informations about the number of rows in tables and
    indexes are the statistics.

    2 ) At least the statistics shall be generated after
    importing the initial data.
    The frequency of update depends on your compiere/datase
    usage. If you do enter many new items you should update
    frequently to make sure that the statisitcs reflect your
    database usage. If you mostly browse the update rate can be
    reduced (e.g once a week/month).

    You will notice performance benefits if your database has
    accumulated some data, as a empty database is fast under
    every condition.

    3)
    Yes it is save. It might degrade the response time while
    generating the computations, but do not affect the data
    themself.

    regards
    Juergen

     
  • Logged In: YES
    user_id=835286

    1)
    I see.
    Thanks for the very understandable explanation.
    Why can't oracle write it like that?

    2+3)
    OK, I will give it a try and install it.
    I think your default cron settings will do fine.

     
  • Jorg Janke
    Jorg Janke
    2005-05-31

    • status: open --> closed-works-for-me
     
1 2 > >> (Page 1 of 2)