-
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.
-
I'm not sure that the sort_area_size would affect the execution plan -- certainly it might affect performance in other ways though.
-
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
-
"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.
-
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.
-
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
-
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)
-
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
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|