Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    36

    Unanswered: tuning SAP Sql's - very urgent

    Hi All,
    I have a list of SAP sql’s that I need to tune and when I run the db2advis nothings works on those sql’s as they are GUI based. I have run the db2exfmt for all these sql’s. I have the output also but I don’t know how to take things from there. Please if anyone can help me in this it will be a great help. I need to tune all these sql’s ASAP. Please help me.

    DB2 Version - v9.1.0.5 fixpack 5

    AIX - 5.3

    Thanks
    Last edited by db2pro; 04-20-09 at 11:06.

  2. #2
    Join Date
    Feb 2009
    Posts
    114
    db2PRO! follow dummy's advice - DO NOT USE DB2ADVIS! EVER!
    Start by reading up on DB2 access path selection. And, if that is your starting point, this is not going to happen "ASAP" - guaranteed!

  3. #3
    Join Date
    Mar 2009
    Posts
    21
    Thanks dummy1...So, could you please suggest any good material for DB2 access path selection ..so that I can start reading it ..

  4. #4
    Join Date
    Feb 2009
    Posts
    114

  5. #5
    Join Date
    Mar 2009
    Posts
    36
    Thanks a lot db2dummy1...very useful material

  6. #6
    Join Date
    Feb 2009
    Posts
    114
    YW ... we aim to please!

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2dummy1
    db2PRO! follow dummy's advice - DO NOT USE DB2ADVIS! EVER!
    Care to explain?

  8. #8
    Join Date
    Feb 2009
    Posts
    114
    But of course!

    db2advis will recommend indices to optimize those SQL statements presented to it as input. If you submit one SQL statement it will optimize indices for that one SQL - chances are you will get index only access everywhere for that one query. These indices may be absolutely useless for the rest of the workload. In order to have db2advis do proper job (and the jury is still out on how good a job it does), you have to submit the most representative samples of your SQL in one execution - many SQL statements. You have to know what they are or capture them as they are being run. I am yet to find someone who would do a good job at this. Most of the time people send one SQL statement to db2advis, which of course is a waste of time, as explained above, except for that one SQL. My recommendation is to do what db2advis attempts to do, but do it yourself. While you may not have all the SQL that will ever be run on your database, you understand the overall query and update patterns, and your experience and intuition will tell you what indices to define. Unfortunately "experience and intuition" are the two things that db2advis will never have. So, my vote is for basic human intelligence!

    BTW, happy birthday to you, Dr!

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2dummy1
    BTW, happy birthday to you, Dr!
    Thank you, I'm enyoing my last working day as a 49-er.

    I agree, but when only 1 query is choking you database, the 1st thing I do is to feed that query to db2advis. The output (what's in a name) is an advice. Sometimes I follow it up and sometimes not.
    Another thing to unleash db2advis after a system-test and let it swallow the complete package cache! That gives nice indication.

    So your advice NEVER to use db2advis... I say: never on your production machine (I saw it bringing down an instance once) but on your test-machine: yeah, why not?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •