Re: SQL Tuning
Date: Wed, 3 Jul 2013 11:39:43 -0400
Message-ID: <CAE-dsOL=U8+7Q8ZfnR5i1Lk2WYwzKx-VSgUj2sww7PHqLfJ5XA_at_mail.gmail.com>
that is a great quote. I am tempted to put in my email with a link to it in the docs.
to be fair, I have found that teaching developers just a little bit about SQL tuning saves me alot of time. Basically I try to get them to click the little button sql developer that does an auto-trace. I tell them to add up 'consistent reads' and 'db block gets'. If they are writing a query that returns the same thing and one has a lower number than these two values than another that query is better. I tell them to just focus on that and I will take care of everything else.
I also give them a very brief explanation of the explain plan and tell them how to read. I tell them that is a tree twisted to its side. Developers almost universal understand trees from their computer science classes. I tell them you walk the tree by looking for the line that is farthest to the right and on top. Then I tell them how to read it by walking up the tree. I pretty out a few simple plans. One full table scan, one index read, one 2 table join with a nested loop. I use a pen and number which operation goes first and then at the bottom, I have typed up in english what is going on
oracle is first going to the index. oracle uses the index based on what is in your where clause. We then look at the fields in the index. the line below it shows that oracle is then going to the data blocks. This is because you have more fields in your 'select' (I dont add extra fields to the where clause that are not in the index. keeps it simple). then see above that where it says nested loop. Oracle opens up a loop and 'for each row' it checks this other tables index based on the where clause.
I add a few more things. I also repeat this stuff over and over again and turn it into a stump speech. I think DBAs make sql tuning too complex for developers and throw in too many edge cases. Developers than get confused. Think about it. If you are going to learn how to code in whatever language they are doing, you would want to start with simple stuff. I find that repetition is important because when something is new and different its hard to remember. I think alot of DBAs make a mistake and tell developers something 1 time, then RTFM them. Repetition is very important.
I tell them that everything I say is correct until I tell them it isnt. If its not correct, I will handle them and give them the correct solution and tell them to implement it. Sometimes I get a few guys who find the problem solving fun and like to learn more, most of the time they just take what I give them. Which is fine also. They have deadlines too.
I find that if developers just know a few basic things, it saves me alot of time and they really appreciate the direction and that I keep it very simple.
On Wed, Jul 3, 2013 at 11:21 AM, Hemant K Chitale <hemantkchitale_at_gmail.com>wrote:
> From the 12c SQL Tuning Guide
> · Because of the existence of GUI-based tools, it is possible to
> create applications and administer a database without knowing SQL. However,
> it is impossible to tune applications or a database without knowing SQL.
>
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 03 2013 - 17:39:43 CEST