DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: interview question

  1. #11
    Join Date
    Apr 2003
    Posts
    32
    http://www.orafaq.net/faqdbapf.htm .... says......

    My query was fine last week and now it is slow. Why?

    The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.

    Some factors that can cause a plan to change are:

    Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
    Has OPTIMIZER_MODE been changed in INIT.ORA?
    Has the DEGREE of parallelism been defined/changed on any table?
    Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
    Have the statistics changed?
    Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
    Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
    Have any other INIT.ORA parameters been changed?

    What do you think the plan should be? Run the query with hints to see if this produces the required performance.

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm not sure that the sort_area_size would affect the execution plan -- certainly it might affect performance in other ways though.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #13
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by slimdave
    I'm not sure that the sort_area_size would affect the execution plan -- certainly it might affect performance in other ways though.
    I'm pretty certain it wouldn't either.

    I think STATISTICS, INDEXES as well as init.ora paramters OPTIMIZER_MODE, DB_FILE_MULTIBLOCK_READ_WRITE and even _small_table_size are the likely candidates. But I'd definatly start at the stats.
    Last edited by grjohnson; 05-06-2003 at 12:38 PM.
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #14
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    "The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available."

    I had to maintain performance on a 15-Billion row database so I stored the optimal execution plans that leveraged the STAR_TRANSFORMATION in the database, then created Java servlet so that from a browser you could pull up both the Optimal baseline plan and the current plan and see if partition elimination and star_trans were chosen. Both new and old were right there for anybody with a browser to examine.

    Additionally there were problems when there was only a single predicate besides the partition key ( time ). Oracle refused to use the efficient bitmap index since it could not AND it with others. All of the compute stats effort in the world would not help. Hints in code is a bad solution also.

    There is an optimizer tweak called optimizer_index_cost_adj that implores the optimizer to choose indexes over scans by artificially lowering the cost of index access paths.

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    oh yes sort_area_size does affect plan

    cut & pasted from doco


    How the CBO Chooses Execution Plans for Join Types

    blah blah blah
    The optimizer also considers other factors when determining the cost of each operation. For example:

    A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area. Sort area size is specified by the initialization parameter SORT_AREA_SIZE.

  6. #16
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by BJE_DBA

    There is an optimizer tweak called optimizer_index_cost_adj that implores the optimizer to choose indexes over scans by artificially lowering the cost of index access paths.
    I've had only MINOR (if any at all) benefit from tweaking these values. I'd be looking else where first.

    Pando, interesting....although I've never come across that being an issue with changing expain plans and I'm always a bit hesitant to trust things from Oracle Manuals... Something to keep in mind though for sure.
    Last edited by grjohnson; 05-06-2003 at 06:09 PM.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by pando
    oh yes sort_area_size does affect plan

    cut & pasted from doco


    How the CBO Chooses Execution Plans for Join Types

    blah blah blah
    The optimizer also considers other factors when determining the cost of each operation. For example:

    A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area. Sort area size is specified by the initialization parameter SORT_AREA_SIZE.
    oh, there we go then. It's in the documentation.

    I wonder what it does when you use automatic pga tuning -- does that mean it would have to look at the available pga at the time of execution to influence the type of sort? I'm on automatice memory management at the moment, and i suspect it's not very good (for DW, anyway)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #18
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    OPTIMIZER_INDEX_COST_ADJ is definitely a useful tool. The initial setting for this value(100) is way too high for an OLTP database. I change it to 20, which is much better balanced. Really low values (like 1-4) will cause some really funky things to happen, BTW. Due to cost rounding, a lot of indexes start looking the same. While Index1 with a cost of 1 vs Index2 with a cost of 4 might be the obvious answer at OICA=20, both Indexes have a cost of 1 at OICA=1, so Index2 may well be chosen. Like I said, things get funky.

    But the default value should always be changed for OLTP, IMHO.

    BTW, here is a quick list of the 8i init.ora params that affect OLTP plans:
    · OPTIMIZER_FEATURES_ENABLE
    · OPTIMIZER_INDEX_CACHING
    · OPTIMIZER_INDEX_COST_ADJ
    · OPTIMIZER_MAX_PERMUTATIONS
    · OPTIMIZER_MODE
    · OPTIMIZER_PERCENT_PARALLEL
    · OPTIMIZER_SEARCH_LIMIT
    · HASH_JOIN_ENABLED
    · ALWAYS_ANTI_JOIN
    · PARTITION_VIEW_ENABLED
    · V733_PLANS_ENABLED
    · HASH_AREA_SIZE
    · HASH_MULTIBLOCK_IO_COUNT
    · SORT_AREA_SIZE
    · SORT_WRITE_BUFFER_SIZE
    · DB_FILE_MULTIBLOCK_READ_COUNT
    · BITMAP_MERGE_AREA_SIZE

    I probably missed a bunch, but this is what I watch.

    But as everyone has said, the usual answer, by far, is that the stats have changed.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #19
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I trust this fact because I did it in practice. Had a query running using nested loop, increased sort_area_size it changed to sort merge join

    I think automatic PGA is only advisable in OLTP...?
    Chris if you turn on event 10053 it shows you all the parameters that affects CBO when calculating the cost

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by grjohnson
    I've had only MINOR (if any at all) benefit from tweaking these values. I'd be looking else where first.
    I found that with optimizer_index_cost_adj, you want less of a tweak and more of a slash.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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