Re: Your opinion please :) explanation of my point of view
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Feb 2004 08:48:16 -0400
Thanks for your opinion, tellme what do you think, about this.
Point by point
1) About the set of checklist, I agree, but the goal is to introduce to
tunning, in the market there are several excellent tunning books, (including
the documentation where is almost all), but people don't read them.
I decided something short, a checklist explaining very very briefly what you
can tune, the most important, the first steps, for newbies, now if you want
more information, you can go to documentation.
For example about statistics, I had two pages of a BRIEF explanation, but no
body is going to read all that, because instead of 8 pages it will be 60
pages, it will be preferable to read the documentation, but several
questions you should had read some moment, shows people don't like to read,
people who never took the time to read neither concept manual, so I decided
to explain the most important, briefly.
For example, about statistics, the explanation is very short
1.1 Statistics
The optimizer works based on statistics, if they are old, or inaccurate you'
ll get a wrong execution plan.
You must recalculate statistics every time an important change had happened
in it:
· Periodically, based on normal changes in a database
· After importing a big amount of data
· When distinct values in primary columns change
· After creating indexes and table
1.1.1 Test database
If this is the first time you get statistics, you must remember some
database has fixed execution plans, or any other consideration can cause a
statistics recalculation cause serious problems, as general rule do it first
in ha test database, before doing in the production database.
1.1.2 Recalculating statistics
Oracle recommends DON'T USE ANALYZE to gather statistics, the package USE
DBMS_STATS, this package get more statistics, specially for new features.
Don't execute DBMS_STATS on SYS schema.
This command has dozens of options, like parallel execution, etc. etc. you
must read them.
To gather all statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS();
To gather statistics in a schema:
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('ADM','COMPUTE');
To gather statistics in a schema, more precisely, the one we use, because
our database is a small database:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'ADM',
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');
What you understand after this is
-optimizer needs statistics
-changing statistics can cause you problems, first use a test database
-use dbms_stats not analyze
That's all, and I think this is the basic thing you have to understand, I
know dbas (not ocps) who don't know that simple fact," they have to
recalculate statistics periodically". Because their database are small and
comparing with sql server it takes a similar amount of time to process, they
don't see it as a problem or a mistake.
2)About the optimizer_index_caching I read a lot, because we have a problem,
we solved setting this parameter, about this parameter I say:
"This parameter sets the possibility to find blocks accessed through an
index in memory.
Default value is 0, 0 means that no one block accessed through index will be
found in memory THIS IS FALSE. A value of 90% is advisable"
Could you giveme an example how could I explain it better.
3) About
"but pays no attention to individual business processes (or indeed
development modules)."
Could you giveme an example what for example I could add ,
But I'm not explaining in this paper things like ORDERED hint, because for a
newbie, first he have to do the basic things, once he had done all of them,
if he continue having problems, then the correct way is to read a more
serious document.
Because if you give advanced points, he will try them first, is like special
hints, I think you heard about them, they are not in documentation because
it could cause problems to new dbas, because they could try to use them to
solve problem, when the reality is they can be used only exceptionally. but
there are book out there once you get the enough experience you could buy
them.
Now if you want another point of view, or a better idea about this, you are
welcome :).
----- Original Message -----
From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 25, 2004 7:29 PM
Subject: RE: Your opinion please :)
hi
I have to admit I don't like the paper much. it looks like a set of
checklists with little or no reasoning behind them. for example the
suggestions on optimizer_index_caching have just start at x and work from
there. where x is apparently an arbitrary number.
my deeper problem is that the article seems to suggest that it is possible
to tune systems but pays no attention to individual business processes (or
indeed development modules). I disagree, the logical unit for tuning is the
thing that runs badly, the logical time to do it is when writing the code
for it. setting db wide parameters is sticking plaster on the titanic.
perhaps.
Niall
-----Original Message-----
From: AC.GWIA.oracle-l@xxxxxxxxxxxxx [mailto:AC.GWIA.oracle-l@xxxxxxxxxxxxx]
Sent: Wed 25/02/2004 18:41
To: jreyes@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc:
Subject: Your opinion please :)
Hi I took sometime to do a paper, to help newbies in tunning, this is not to
become they expert, but at least they know there are statistics in the
database
If you get some time, and give some opinion about it, like the previous
mistake I did with IN function, you are welcome.
Or something important I'm missing (for newbies dbas of course).
Did you remember the first time you had a tunning problem, before the ocp,
and you didn't knew where to start to check,and lost long time, changing
some database parameter to see if that improved something, the idea is to
save that step.
http://www.geocities.com/juancarlosreyesp/101basictunningonOracle9i.pdf
Juan Carlos Reyes Pacheco
OCP
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
**********************************************************************
This email contains information intended for
the addressee only. It may be confidential
and may be the subject of legal and/or
professional privilege. Any dissemination,
distribution, copyright or use of this
communication without prior permission of
the sender is strictly prohibited.
**********************************************************************
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
