Home » RDBMS Server » Performance Tuning » query tuning (oracle 010g)
query tuning [message #593899] Wed, 21 August 2013 13:47 Go to next message
karthikmuthuraman88
Messages: 5
Registered: June 2013
Location: chennai
Junior Member
hi,

am beginner in oracle.anybody can explain what are the steps and easier way to understand query tuning?

thanks in advance Smile
Re: query tuning [message #593900 is a reply to message #593899] Wed, 21 August 2013 13:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
There is a whole book written on this topic.

Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/server.121/e15857/toc.htm
Re: query tuning [message #593907 is a reply to message #593900] Wed, 21 August 2013 21:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
If I had a penny for every time someone asked me to show them an easy way to learn tuning...


There is no easy path to understanding tuning. Consider this: many of us on OraFAQ have been Oracle Database users for 10 years, 20 years, and even 30 years. That's a long time. I personally typed
select 8 from emp;
into a SQL prompt back in 1985 (yep that says 8 not * (I fat fingered my very first SQL statement)). For those of us who have spent these many years working with the Oracle database, we have learned a lot about tuning, yet we might still hesitate to call ourselves Experts at it. There is no short path and it is hard work to get good at it and just as hard to stay good at it given things change so fast.

One of the biggest reasons that tuning is so hard to learn is that there is a CRITICAL MASS OF KNOWLEDGE that must first be attained before one starts to really understand tuning, and there is a wealth of crap out there that anyone interested in acquiring this critical mass of knowledge, must wade through in order to get the gold we seek. Having spent my career reading and learning I understand your frustrations about learning how to tune as I have read most of this crap. Learning to tune takes a lot of time whether you learn vicariously or through direct experience.

For this reason I think it is the duty of those of us who have the experience, to help newcomers focus on the important aspects of tuning so that the best parts of our many years of experience can be digested with in a time frame of an order of magnitude less maybe. Of course this still means best case, two to three years for you to get reasonably good at practical tuning and that is with experienced mentors helping you skip the crap and showing you where the good stuff is, so that you are not wasting your time like we had to do. Use this comment to grasp what is ahead of you if you want to be a good query tuner.

You did mention in your post the term "QUERY TUNING" so I will start you off with a simple question, and a pointer to one of my best posts on OraFAQ (I think anyway). Others can follow suit with their best starting tips if they feel so inclined.

Q: If you have a table with 1 million rows and you want all 1 million rows from it, should you use an index to get all these rows or do a full table scan? Why?

Understanding the WHY of this question will give you an appreciation for the very heart of query tuning.

Good luck. Kevin

Please find attached here the promotional chapter #1 of my new book on SQL TUNING. This chapter (#1) talks about the FILTERED ROWS PERCENTAGE method of Cardinality Based Tuning, and is the full chapter. Do not forget to download the free scripts as well, and if you choose to buy the book make sure to use the coupon code so you get 50% off list price at the noted web address.

[Updated on: Wed, 03 December 2014 13:27]

Report message to a moderator

Re: query tuning [message #593923 is a reply to message #593899] Thu, 22 August 2013 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

You can also buy first book from Stephane Faroult.

Regards
Michel
Re: query tuning [message #593934 is a reply to message #593907] Thu, 22 August 2013 02:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Kevin Meade wrote on Thu, 22 August 2013 03:07
Quote:
If I had a penny for every time someone asked me to show them an easy way to learn tuning...


There is no easy path to understanding tuning. Consider this: many of us on OraFAQ have been Oracle Database users for 10 years, 20 years, and even 30 years. That's a long time. I personally typed
select 8 from emp;
into a SQL prompt back in 1985 (yep that says 8 not * (I fat fingered my very first SQL statement)). For those of us who have spent these many years working with the Oracle database, we have learned a lot about tuning, yet we might still hesitate to call ourselves Experts at it. There is no short path and it is hard work to get good at it and just as hard to stay good at it given things change so fast.

One of the biggest reasons that tuning is so hard to learn is that there is a CRITICAL MASS OF KNOWLEDGE that must first be attained before one starts to really understand tuning, and there is a wealth of crap out there that anyone interested in acquiring this critical mass of knowledge, must wade through in order to get the gold we seek. Having spent my career reading and learning I understand your frustrations about learning how to tune as I have read most of this crap. Learning to tune takes a lot of time whether you learn vicariously or through direct experience.

For this reason I think it is the duty of those of us who have the experience, to help newcomers focus on the important aspects of tuning so that the best parts of our many years of experience can be digested with in a time frame of an order of magnitude less maybe. Of course this still means best case, two to three years for you to get reasonably good at practical tuning and that is with experienced mentors helping you skip the crap and showing you where the good stuff is, so that you are not wasting your time like we had to do. Use this comment to grasp what is ahead of you if you want to be a good query tuner.

You did mention in your post the term "QUERY TUNING" so I will start you off with a simple question, and a pointer to one of my best posts on OraFAQ (I think anyway). Others can follow suit with their best starting tips if they feel so inclined.

Q: If you have a table with 1 million rows and you want all 1 million rows from it, should you use an index to get all these rows or do a full table scan? Why?

Understanding the WHY of this question will give you an appreciation for the very heart of query tuning.

Sadly, this link no longer points to the proper place. Please ignore it. Not sure how that happened. Kevin 22-Jan-2014.
Query tuning actually starts way before writing any SQL. This link shows you the most important thing you can do for query performance.

Good luck. Kevin



That should be made a sticky. Seriously.

I may even just steal it for my office!

[Updated on: Thu, 23 January 2014 03:53] by Moderator

Report message to a moderator

Re: query tuning [message #594001 is a reply to message #593934] Thu, 22 August 2013 07:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Kumar, do you really live in a place called HI-TECH CITY? My mind is awash with flashbacks from MTV and Eon Flux.
Re: query tuning [message #594006 is a reply to message #594001] Thu, 22 August 2013 08:15 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Kevin -

It's always so nice to talk to you Smile

Have a look : Hi-tech City, Hyderabad, Andhra Pradesh, India
Previous Topic: Stats on a partitioned table
Next Topic: dynamic sql choosing wrong index
Goto Forum:
  


Current Time: Thu Mar 28 17:23:30 CDT 2024