Menu
▾
▴
#15 regular oracle statistic updates
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
Logged In: YES
user_id=23294
Sounds interesting. Does this apply to 10g as well?
Logged In: YES
user_id=55360
Yes.
Oracle provides some packages /include in every shipment)
which have to be called.
Juergen
Logged In: YES
user_id=23294
Ok then, I'm interested. Anything that can speed things up is
welcome.
Logged In: YES
user_id=55360
Attached a archive which does maintain oracle statistics
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?
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.
Logged In: YES
user_id=87038
Thanks - As far as I realize, the maintenance of general
statistics is automatic in 10g - use the manager console and
use the index optimizer for specific issues (e.g. if you have
lots of rows)