This content has been marked as final. Show 8 replies
Either your session is altered to use rule based optimizer or you have a RULE hint in your query.
one answer - but perhaps not the one for your special situation - could be: missing statistics and no dynamic sampling (OPTIMIZER_DYNAMIC_SAMPLING=0 or a historic value for OPTIMIZER_FEATURES_ENABLE):
Edited by: Martin Preiss on Apr 2, 2013 3:15 PM
-- 22.214.171.124 drop table t; create table t as select rownum id from dual connect by level <= 1000; -- but no statistics gathering alter session set OPTIMIZER_FEATURES_ENABLE='9.2.0'; -- or: alter session set OPTIMIZER_DYNAMIC_SAMPLING=0; explain plan for select * from t; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------- Plan hash value: 1601196873 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T | ---------------------------------- Note ----- - rule based optimizer used (consider using cbo)
Karthick - No. The query is coming from a application so the query and session parameters are fixed. As AWR shows, the query was executed dozens of times and all the plan_hash_values for the same sql_id use CBO, just this one bad apple used the RBO. Why?
Hoek - Yes, I read all of Kerry Osborne's excellent blog entries, that's how I narrowed down the problem to this particular sql_id. But it doesn't talk about WHY a particular query would use the RBO. The most common culprit, bind variable peeking, may cause the plan to flip between 2 CBO plans but it shouldn't cause the RBO to be used.
Martin - That's what I thought too but all objects used in the query do have stats. Since the Oracle auto-stats job/task is running daily, stats are always up-to-date. Here are all the optimizer* parameters in the spfile.
This is really frustrating.
[Oracle Support is looking into it but they usually ask for a 10046 trace and we can't provide that because AWR doesn't capture that and the issue is not reproducible at-will]
optimizer_features_enable 126.96.36.199 optimizer_mode ALL_ROWS optimizer_index_cost_adj 100 optimizer_index_caching 0 optimizer_dynamic_sampling 2 optimizer_secure_view_merging TRUE optimizer_use_pending_statistics FALSE optimizer_capture_sql_plan_baselines FALSE optimizer_use_sql_plan_baselines TRUE optimizer_use_invisible_indexes FALSE
Hmz, I provided the wrong link in my previous post, apologies for that.
A search on Metalink/MOS didn't get me any additional pointers.
Without knowing the exact query, this might be useful to know and/or check anyway: RULE hints are still 'hidden' in the datadictionary tables, see: http://jonathanlewis.wordpress.com/2010/04/13/rule-rules/
and the issue is not reproducible at-willToo bad...
The only additional pertinent data point is that the database was (inadvertently) started in READ ONLY mode and, from AWR data, the bad RBO plan seems to have been chosen right around that time. It was immediately closed and restarted in read write mode but the bad plan persisted for a few more hours after finally, thankfully, aging out of the cache and things have been smooth ever since. Would readonly mode have anything to do with this?
may be this behavior due to an alter system done on you database by an another program or Oracle internal process.
alter system set optimizer_mode=rule;
When I check v$sql , I can see optimizer_mode =RULE.
select distinct optimizer_mode from v$sql
you can check the oracle view v$sql_optimizer_env and you can add clauses on hash_value and sql_id to get the right values of parameter used during execution of this query.
alter system set optimizer_mode=rule;Nope, no changes to system parameters were done.
When I check v$sql , I can see optimizer_mode =RULE.On my system, there are a handful of RULE queries but they are all SYS/SYSTEM ones so that's fine I guess.
you can check the oracle view v$sql_optimizer_env and you can add clauses on hash_value and sql_id to get the right values of parameter used during execution of this query.The bad plan_hash_value has aged out of the v$ views and unfortunately AWR doesn't take a snapshot of this view for historical analyis. AWR also doesn't seem to capture the v$sql_shared_cursor view so we can see why (by inspecting all the mismatch columns in the view) the child cursor was created in the first place!