Tuning SQL in Oracle

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jag
    #1

    Tuning SQL in Oracle

    I have some fairly complex queries (joins to 20 tables etc) that run
    fine using the rule based optimizer, but simply die using the CBO. Can
    anyone offer any hints or point me to a book, web resource, etc. that
    will help me tune these correctly using the CBO? I'm using /*+ Rule*/
    as a stopgap measure because we're running 8.1.7 right now, but our
    move to 9i is coming soon, so I really need to get these running
    efficiently using cost based optimization.

    Thanks in advance for any help you can offer.

    Tags: None
    • LKBrwn_DBA
      #2
      Re: Tuning SQL in Oracle


      Hint:

      Analyze all tables and indexes.

      Amazon.com
      http://www.amazon.com/exec/obidos/ASIN/0130123811/qid=1059590327/sr=2-3-

      /ref=sr_2_3/103-8017330-2186264

      --
      Posted via http://dbforums.com

        Comment

        • jag
          #3
          Re: Tuning SQL in Oracle

          On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
          <member30625@db forums.comwrote :
          >
          >Hint:
          >
          >Analyze all tables and indexes.
          That's what got me into this. :) My code starting dragging on our
          production database right after a analyze had taken place. Apparently
          we were right under a certain level of data before the analyze was run
          that the new stats took us past. At that point I guess the CBO chose a
          very bad access path.
          >http://www.amazon.com/exec/obidos/AS...590327/sr=2-3-
          >/ref=sr_2_3/103-8017330-2186264
          Thanks. I just ordered one. This book is a couple of years old though.
          Has the CBO changed much for 9i?

            Comment

            • Jim Kennedy
              #4
              Re: Tuning SQL in Oracle

              Hard to know if we don't see the explain plan and tkprof outputs.(and the
              sql)
              Jim

              "jag" <7bf79m4s02@sne akemail.comwrot e in message
              news:n89givo5c0 nihtkrbetgm5vbj ek400udrf@4ax.c om...
              On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
              <member30625@db forums.comwrote :
              >

              Hint:

              Analyze all tables and indexes.
              >
              That's what got me into this. :) My code starting dragging on our
              production database right after a analyze had taken place. Apparently
              we were right under a certain level of data before the analyze was run
              that the new stats took us past. At that point I guess the CBO chose a
              very bad access path.
              >
              Amazon.com
              http://www.amazon.com/exec/obidos/ASIN/0130123811/qid=1059590327/sr=2-3-

              /ref=sr_2_3/103-8017330-2186264
              >
              Thanks. I just ordered one. This book is a couple of years old though.
              Has the CBO changed much for 9i?

                Comment

                • Daniel Roy
                  #5
                  Re: Tuning SQL in Oracle

                  Even if you analyzed all your tables and indices, one more issue you
                  might want to consider is to create histograms for the (indexed)
                  columns heavily skewed. They can greatly improve the performance with
                  the CBO sometimes.

                  Daniel
                  On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
                  <member30625@db forums.comwrote :
                  >

                  Hint:

                  Analyze all tables and indexes.
                  >
                  That's what got me into this. :) My code starting dragging on our
                  production database right after a analyze had taken place. Apparently
                  we were right under a certain level of data before the analyze was run
                  that the new stats took us past. At that point I guess the CBO chose a
                  very bad access path.
                  >
                  Amazon.com
                  http://www.amazon.com/exec/obidos/ASIN/0130123811/qid=1059590327/sr=2-3-

                  /ref=sr_2_3/103-8017330-2186264
                  >
                  Thanks. I just ordered one. This book is a couple of years old though.
                  Has the CBO changed much for 9i?

                    Comment

                    • jag
                      #6
                      Re: Tuning SQL in Oracle

                      On Thu, 31 Jul 2003 00:34:56 GMT, "Jim Kennedy"
                      <kennedy-down_with_spamm ers@no_spam.com cast.netwrote:
                      >Hard to know if we don't see the explain plan and tkprof outputs.(and the
                      >sql)
                      >Jim
                      Of course. I wouldn't want to subject anyone else to that. One is
                      enough. :)

                      That's why I was just asking for general hints and book
                      recommendations . Fortunately using + RULE has bought me enough time to
                      allow me to get some studying in. I suspect I'll have a lot better
                      knowledge of the CBO and Oracle internals once I'm done and that's a
                      great thing.

                      My sincere thanks to everyone responding.

                        Comment

                        Previous template Next
                        Working...