Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Your opinion please :) explanation of my point of view

RE: Your opinion please :) explanation of my point of view

From: <Kip.Bryant_at_Vishay.com>
Date: Thu, 26 Feb 2004 09:51:58 -0800
Message-id: <01L71YHUG0HK91WH1C@Mail.Vishay.com>


Hi Juan,

I have to agree with Don's point of view. I think it took a lot of courage to open yourself to criticism like this. I think a lot of us would probably like a cookbook to follow but don't expect to ever find it ("it depends" being the "answer" far too often). I'm strictly a "school of hard knocks" DBA and what little I know I've learned the hard way. I've found that off and on I've had to just "put a stake in the ground" regarding where to start the tuning process for DBA's who report to me just to stir things up. I don't expect them to agree with me (it's not about "my way or the highway") but I do expect them to work methodically and put assumptions to the test.  

I guess all this makes me an "iggorant PHB"...

Kip

|Let me chime in my 2 cents here. I printed and reviewed your document and
|noted a few things that I will discuss with my co-DBA. I liked the article
|because in a short document you summarized a number of things that were
|important in your experience. Some of them seem relevent to what I am doing
|so I will spend some time to check them out. As far as you posting it, I
|personally try to pass on tips and tricks as I learn them and I appreciate
|that behavior in others.

|As what we do is often a competitive business I have not often encountered
|people personally who freely give away valuable information which they
|struggled to gain. The top-level professionals who answer questions here are
|doing 'pro-bono' work for which I am grateful. It's the same advice that
|people pay them for. In real life I'm beginning to think it's pretty
|remarkable when somebody says, "Come on over, I'll show you how it works."
|The lists are very valuable to me. I'm not in a position to travel to
|conferences, seminars, and attend workshops to improve my skills and don't
|work for an employer who values (or pays for) this kind of stuff.

|I think you are pretty brave for posting your 'baby.' I have been holding
|my breath because I thought it might provoke a lot of disagreement or
|outright disparagement of your effort. At this point I don't know enough to
|say if what you have posted is altogether technically accurate but
|I always check 'free' advice anyway.

|You are correct, I think, in the way some people approach books. I am
|struggling to understand some of the features in Oracle and have quite an
|extensive library of Oracle books. Most of them I haven't read cover to
|cover. I tend to read them a chapter, or even a paragraph at a time
|depending on what my issue is. I download a lot of papers, "how-too's",
|tips and tricks.

|The value of the books on the market to me is to answer the bigger questions
|of why and when (or how much) instead of what and how. Its always helpful to
|me when somebody says, "On an OLTP system is should be X and on a DS system
|it should be Y." It gives me a point at which to start looking. The oracle
|books usually provide the exact syntax describing how a feature or function
|is implemented but don't answer the question, for me, "Is this an appropriate
|thing to do in a small datawarehouse environment..." I know, "It depends."

|So, thanks for posting!

|Don Freeman
|Oracle 9i OCP

|-----Original Message-----
|From: oracle-l-bounce_at_freelists.org
|[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Cachito Reyes
|Pacheco
|Sent: Thursday, February 26, 2004 9:05 AM
|To: oracle-l_at_freelists.org
|Subject: Re: Your opinion please :) explanation of my point of view

|Thanks for your opinion.

|Now if you think "kill lazy and bad dbas", I can't do too much about this.
|I think every one likes his job, and a paper like this can wake up his
|interest in reading to a bad dba, and help to introduce a good dba who is
|starting to read.

|Yes this is true, the idea is to introduce to all this people to tunning.
|I think if every one would read the Oracle document as they should, 50% of
|the book would be useless. If you take randomly a good tunning book and seek
|the same in Oracle tunning documentation, 90% is in the Oracle
|documentation, at least!!

|But even when you read and got an OCP, you need some guide about what
|you should check first, better and simple explanations, for example about
|CURSOR_SPACE_FOR_TIME there is not a clear explanation about when to use
|them.
|You need a guide that introduce to tunning. to make you understand what is
|important and what is the most important.

|----- Original Message -----
|From: "Igor Neyman" <ineyman_at_perceptron.com>
|To: <oracle-l_at_freelists.org>
|Sent: Thursday, February 26, 2004 9:53 AM
|Subject: RE: Your opinion please :) explanation of my point of view

|> If "newbie DBA" doesn't like to read, he/she'd better be in different
|> business...
|>
|> Igor Neyman, OCP DBA
|> ineyman_at_perceptron.com
|>
|>
|>
|> -----Original Message-----
|> From: oracle-l-bounce_at_freelists.org
|> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Cachito Reyes
|> Pacheco
|> Sent: Thursday, February 26, 2004 7:48 AM
|> To: oracle-l_at_freelists.org
|> Subject: Re: Your opinion please :) explanation of my point of view
|>
|> 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_at_audit-commission.gov.uk>
|> To: <oracle-l_at_freelists.org>
|> 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_at_freelists.org
|> [mailto:AC.GWIA.oracle-l_at_freelists.org]
|> Sent: Wed 25/02/2004 18:41
|> To: jreyes_at_dazasoftware.com; oracle-l_at_freelists.org
|> 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_at_freelists.org
|> put 'unsubscribe' in the subject line.
|> --
|> Archives are at http://www.freelists.org/archives/oracle-l/
|> FAQ is at http://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_at_freelists.org
|> put 'unsubscribe' in the subject line.
|> --
|> Archives are at http://www.freelists.org/archives/oracle-l/
|> FAQ is at http://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_at_freelists.org
|> put 'unsubscribe' in the subject line.
|> --
|> Archives are at http://www.freelists.org/archives/oracle-l/
|> FAQ is at http://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_at_freelists.org
|> put 'unsubscribe' in the subject line.
|> --
|> Archives are at http://www.freelists.org/archives/oracle-l/
|> FAQ is at http://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_at_freelists.org
|put 'unsubscribe' in the subject line.
|--
|Archives are at http://www.freelists.org/archives/oracle-l/
|FAQ is at http://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_at_freelists.org
|put 'unsubscribe' in the subject line.
|--
|Archives are at http://www.freelists.org/archives/oracle-l/
|FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Feb 26 2004 - 12:14:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US