11 Replies Latest reply on Dec 8, 2009 1:32 PM by pparkko

    Steps for SQL Tunning

    553495
      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 ?
        • 1. Re: Steps for SQL Tunning
          535723
          See the following link:
          http://www.dba-oracle.com/art_sql_tune.htm
          • 2. Re: Steps for SQL Tunning
            480544
            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'
            • 3. Re: Steps for SQL Tunning
              BluShadow
              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.
              • 4. Re: Steps for SQL Tunning
                480544
                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.
                • 5. Re: Steps for SQL Tunning
                  94799
                  "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.
                  • 6. Re: Steps for SQL Tunning
                    William Robertson
                    I think that must mean holistic in the redneck sense of the word.
                    • 7. Re: Steps for SQL Tunning
                      480544
                      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.
                      • 8. Re: Steps for SQL Tunning
                        Billy~Verreynne
                        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?
                        • 9. redneck?
                          537728
                          wots a redneck?
                          • 10. Re: redneck?
                            William Robertson
                            > wots a redneck?

                            If only there was an online dictionary.
                            • 11. Re: Steps for SQL Tunning
                              pparkko
                              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