Home » RDBMS Server » Performance Tuning » tuning question
tuning question [message #116364] Tue, 19 April 2005 11:55 Go to next message
Bruce Carson
Messages: 38
Registered: February 2005
Location: Nova Scotia, Canada
Member

sql tuning

What would be the top 5 tuning techniques when using sql?
Re: tuning question [message #116367 is a reply to message #116364] Tue, 19 April 2005 12:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
1. Read the Oracle Concepts Guide
2. Read the Oracle Application Developer Fundamentals Guide
3. Read the Performance Tuning Guide
4. Make sure you are using the latest Oracle Release Version
5. Read Effective Oracle By Design by Tom Kyte

Re: tuning question [message #116369 is a reply to message #116367] Tue, 19 April 2005 12:53 Go to previous messageGo to next message
Bruce Carson
Messages: 38
Registered: February 2005
Location: Nova Scotia, Canada
Member

wow ... i knew that would be the answer. Laughing

just looking for some quick hits but thank you for the response
i better take a course in speed reading first.

Bruce.
Re: tuning question [message #116401 is a reply to message #116364] Tue, 19 April 2005 17:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yeah, I wish I could read a lot faster myself...

Actually a couple quick general things might be:

a) use sql instead of plsql whenever possible
b) use analytic functions
c) use inline views
d) try more than one method and test their plans/stats
e) think about the queries you need to run when creating your tables in the first place

[Updated on: Tue, 19 April 2005 17:03]

Report message to a moderator

Re: tuning question [message #116524 is a reply to message #116401] Wed, 20 April 2005 09:02 Go to previous messageGo to next message
pscjhe
Messages: 38
Registered: April 2005
Member
As to "Tuning SQL" my top 5 would be

1. Understand what this SQL wants to do

2. Understand physical objects underlying SQL 
   Particularly indexes, statistics 

3. Understand what each version of Oracle can offer
   Particularly the improvements in Optimizer (CBO), SQL, 
   indexes.

4. Use explain plan, autotrace, tkprof and runstats to compare
   Different execution plan's and its runtime statistics  

5. Cheat (:-
   asktom.oracle.com
Re: tuning question [message #116529 is a reply to message #116524] Wed, 20 April 2005 09:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to add.
>>runstats to compare

runstats is cool.
and should be available in asktom.oracle.com



Re: tuning question [message #116677 is a reply to message #116529] Thu, 21 April 2005 06:29 Go to previous message
Bruce Carson
Messages: 38
Registered: February 2005
Location: Nova Scotia, Canada
Member

Thanks to everyone for the tips !
I'm new to this user forum and so far like it alot
Previous Topic: Analyze table&Index
Next Topic: Avoiding Cursors.
Goto Forum:
  


Current Time: Tue Sep 22 07:41:19 CDT 2020