OTN Discussion Forums : Steps for SQL Tunning ...

Reply to this Thread

Search Forum

Back to Thread List

Replies: 11 - Pages: 1 - Last Post: Dec 8, 2009 5:32 AM Last Post By: pparkko

vijay3

Posts: 27
Registered: 12/27/06

Steps for SQL Tunning
Posted: Jan 30, 2007 2:50 AM

How to tune any SQL Query.

Let's take an example..

I have a SQL which is taking huge time, what steps do i need to follow to tune any query.

If i am database developer then ?

If i am database DBA then ?

Posts: 13
Registered: 09/25/06

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 2:55 AM

in response to: vijay3

See the following link:
http://www.dba-oracle.com/art_sql_tune.htm

graham_oakes

Posts: 231
Registered: 01/04/06

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:03 AM

in response to: i2

sorry but that link sucks. Any advice to start playing with kernel parameters and initialization parameters before tuning a single SQL statement is madness.

When creating a new db fair enough, take time to make sure the kernel parameters, disks, initialization parameters etc are setup correctly. But to do so after a system is up and running is a serious step. You need to be 100% sure that changing a kernel parameter is going to have the desired affect before doing so as it needs a server reboot.

if you want to learn how to tune SQL got to asktom.oracle.com and search for 'tuning'

BluShadow

Posts: 12,877
Registered: 09/21/05

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:13 AM

in response to: vijay3

To be honest, there's a lot to consider when coming to tune your SQL.

I attended a 3 day SQL Tuning course at Oracle to gain the basic understanding of what needs doing. There's no way that can be detailed in single points here on a forum.

graham_oakes

Posts: 231
Registered: 01/04/06

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:36 AM

in response to: BluShadow

I couldn't agree more, I've been on the same course - and that just starts you off. I think it's all about knowledge and understanding, the more you know and understand, the better you're able to tune.

The only advice I can think of is reading, testing, reading, testing and then reading some more. The oracle docs are always a good place to start
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm
and for a really good understanding of the optimizer read Jonathan Lewis's book
http://www.amazon.com/s/ref=nb_ss_gw/104-3255760-8610312?url=search-alias%3Daps&field-keywords=cost-based+oracle+fundamentals&Go.x=2&Go.y=8&Go=Go

from a developer's view I would go to asktom as said previously.

padders

Posts: 2,217
Registered: 04/18/99

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:37 AM

in response to: i2

"Remember, you must ALWAYS start by holistic SQL tuning by holistic methods"

"If you have SQL that frequently tests SQL, creating an index on NULL values"

Genius.

William Robertson

Posts: 6,652
Registered: 06/17/98

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:48 AM

in response to: padders

I think that must mean holistic in the redneck sense of the word.

graham_oakes

Posts: 231
Registered: 01/04/06

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:54 AM

in response to: William Robertson

I was having a private bet with myself, how many posts after a burleson link before the redneck link appears! I guessed about 8 but you got there way before that.

Billy Verreynne

Posts: 9,122
Registered: 05/27/99

Re: Steps for SQL Tunning
Posted: Jan 30, 2007 3:57 AM

in response to: William Robertson

Awe sheez.. Again. Bashing Mr Burleson. Come on!

You guys know that is NOT acceptable!! You very well know the rules of when it comes to Burleson-bashing.

So last warning. DO NOT LET IT HAPPEN AGAIN!!

So next time around, remember.. Billy goes first! Okay?

Elidas

Posts: 189
Registered: 10/05/06

redneck?
Posted: Jan 30, 2007 7:04 AM

in response to: vijay3

wots a redneck?

William Robertson

Posts: 6,652
Registered: 06/17/98

Re: redneck?
Posted: Jan 30, 2007 7:07 AM

in response to: Elidas

> wots a redneck?

If only there was an online dictionary.

pparkko

Posts: 42
Registered: 10/07/99

Re: Steps for SQL Tunning
Posted: Dec 8, 2009 5:32 AM

in response to: vijay3

This thread is priceless - thank you for a good laugh!

About the original question - it is indeed a vast subject.
After 10 years working with Oracle you're off to a good start.
But .. just when you think you are beginning to understand something - carpet is yanked below your feet

BR,
Pasi

Pages: 1

Back to Thread List

Legend
	Guru : 2500 - 1000000 pts
	Expert : 1000 - 2499 pts
	Pro : 500 - 999 pts
	Journeyman : 200 - 499 pts
	Newbie : 0 - 199 pts
	Oracle ACE Director
	Oracle ACE Member
	Oracle Employee ACE
	Helpful Answer (5 pts)
	Correct Answer (10 pts)