Other Oracle database topics - How to begin with tuning of SQL statements

Author

How to begin with tuning of SQL statements

sam

2005-03-30, 9:40 am

Hi

I want to know how to begin with sql tuning.I mean to say reading
Explain plan output and modifying sql accordingly.

Secondly how to know the value of snap id while generating statspack
report.

Any help will be appreciated.

sameer

HansF

2005-03-30, 9:40 am

On Wed, 30 Mar 2005 01:51:29 -0800, sam wrote:

>
> I want to know how to begin with sql tuning.I mean to

Go to the documentation and look for a manual titled
"Performance Tuning Guide and Reference".

sam

2005-03-30, 9:40 am

Hi

Thanks for the response.I dont need the manual.I need the practical
guidance if you can provide that.From where to begin and things to
remember while doing that.

sameer

Rauf Sarwar

2005-03-30, 9:40 am

sam wrote:
> Hi
>
> Thanks for the response.I dont need the manual.I need the practical
> guidance if you can provide that.From where to begin and things to
> remember while doing that.
>
> sameer

You would need the manual to start off. Tuning is a broad subject and
cannot be summarized in just one post. Start with the manual and a test
database. Visit this NG, metalink and http://tahiti.oracle.com
frequently. If you have a specific question within tuning e.g. how do I
tune such and such query then you can post it here and someone will be
able to answer you.

As for statspack snap id, It's all listed in
ORACLE_HOME/rdbms/admin/spdoc.txt or ORACLE_HOME/rdbms/doc/spdoc.txt

Regards
/Rauf

HansF

2005-03-30, 9:40 am

On Wed, 30 Mar 2005 02:12:51 -0800, sam wrote:

> Hi
>
> Thanks for the response.I dont need the manual.I need the practical
> guidance if you can provide that.From where to begin and things to
> remember while doing that.
>
> sameer

Many books have been written about 'where to begin', and I couldn't even
begin to summarize them. Perhaps people (much smarter than I) would do so.

Short of some descriptve information (are you using CBO, which database
version, why are you tuning, what are you trying to tune, what access and
authority do you have, etc.) anything else I could suggest here would be a
stab in the dark.

I suggested the manual since I thought you wanted help on (using
Oracle9i R2 doc references):

- where to start, [after understanding what can be done] (Chapter 20)
- setting up & reading the explain plan (Chapter 9)
- setting up & using statspack (Chapter 21)
- modifying SQL [assuming that is what is required] (Chapter 6)
- value of SNAP_ID (Chapter 21, under "How Statspack Works")
and possibly
- what is tuneable (Chapters 13-19, depending on your installed config)

I guess I misunderstood, and for that I apologize.

(If you were interested in reading, I'd also recommend the manual
"Performance Planning", specifically the chapter "Emergency Performance
Techniques" might be more suitable. I've found that not reading may be a
reason for possible performance issues.)

SO ...

Practical guidance: Upgrade to Oracle Database 10g and set it on
Automatic Tuning. Or install the Enterprise Manager, set up the Tuning
Pack, use the screen cams (otn > EM > Tutorials: Database Performance)

/Hans

Frank van Bortel

2005-03-30, 9:40 am

HansF wrote:
> On Wed, 30 Mar 2005 02:12:51 -0800, sam wrote:
>
>
>
>
> Many books have been written about 'where to begin', and I couldn't even
> begin to summarize them.

I smell a "How to get started for dummies" in the making... :)
--
Regards,
Frank van Bortel

HansF

2005-03-30, 9:40 am

On Wed, 30 Mar 2005 13:57:07 +0200, Frank van Bortel wrote:

> I smell a "How to get started for dummies" in the making... :)

Interesting ... Collaboration?
/Hans

joel-garry@home.com

2005-03-30, 7:34 pm

>I need the practical
guidance if you can provide that

I think Lawson's book is a good introduction to that.

http://www.oraclemagician.com/art.html

jg
--
@home.com is bogus.
What time is it?

Sponsored Links