Sql tuning? any Issue?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sql tuning? any Issue?

  1. #1
    Join Date
    Mar 2002
    Posts
    301

    Sql tuning? any Issue?

    Hi,

    I develop some SQL queries for a client in a development environment.
    I should optimize the queries before to roll out, by run it in traceonly mode in production environment.
    For each query, I write 10 comparables queries and I'm waiting for results. How can I choose between differents explain plan?



    DB: Oracle 9i
    OS: Solaris 10
    DB Size: 200 Tera Bytes

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You need to test your sql against production data to be sure that your sql's dont have a performance problem.

    By looking at the plan even seasoned professionals cannot tell which plan is better

    regards
    Hrishy

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    200TB?... is that a Data Warehouse?

    I understand you are tracing the queries in production so...

    1- Look at Explain Plan just to sort out pretty bad stuff lke...
    --- Detect FTS on very large tables
    --- Detect expected star-transformations that are not happening.

    2- Look at Wait Events, that's the whole truth.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Yes it's a big datawarehouse.
    I'm not allowed to run the query on production database but I can do explain plan only on it without runing.
    How can I collect wait event or trace file for such a query?
    How can I decide with plan is the best for the query?

    Bensmail

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    The not much you can do except picking up the lowest cost SQL.
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Quote Originally Posted by malay_biswal
    The not much you can do except picking up the lowest cost SQL.
    The SQL cost numbers are not comparable across two queries, even two versions of the same query. All you can do is make sure you have the right schema and partitioning set up on the database. You want to design a schema so that it will be easy to write good SQL. If it is me I would verify that all foreign keys have a foreign key constraint and an index on the foreign key column. If that isn't the case you might get locking problems when you do updates or inserts.

    If your data warehouse is really 200 Terabytes, then you need a test system with all the same data, even if it runs on lesser hardware. You should also look into compressing some of the larger tables, especially tables that don't get updated very often.
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Code:
    e SQL cost numbers are not comparable across two queries, even two versions of the same query
    That's absolutely correct, but you got 10 sql all give same result(that's my assumption). If that's correct it's always better to pick lower cost number SQL. However low cost does not guarantee execution time will also less.
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bensmail
    I'm not allowed to run the query on production database...
    Do you have any sort of UAT environment? Can you replicate in UAT a subset of whatever you are hittting with your query?

    Experience says your query would be hitting "some" partitions of large tables and "some" small tables. May be you can refresh those objects from Prod into UAT... that would help a little and let you have a feeling about what to expect on Prod.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Nov 2001
    Posts
    335
    Extract statistics from Prod and import it into database where you can test. It will give you an idea about what oracle is going to do in production
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  10. #10
    Join Date
    Mar 2002
    Posts
    301
    Hi all,

    how can I explain the plan of my different queries without run them on the production database?
    I need only execution plan.
    Lower cost don't mean less execution time, so I understand.

    How can I select from the differents execution plan scenarios the right one.
    I'm not allowed to run the query on production environment.

    Regards
    Bensmail

Posting Permissions

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


Click Here to Expand Forum to Full Width