in a test system:
*) enable tracing
*) run most poorly performing SQL
*) tkprof trace files to create a set of baseline tkprof files
*) set optimizer_index_cost_adj to 10
*) rerun most poorly performing SQL
*) tkprof trace files to create 1st set changed tkprof files
*) repeat general process as needed (i.e. setting pga_aggregate_target to
high enough value to avoid the excessive context switching, MetaLink Bug
3199732)
I also found this related issue on MetaLink:
Bookmark Go to End
Doc ID: Note:258945.1
Subject: Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and
Resolving
Type: TROUBLESHOOTING
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 02-DEC-2003
Last Revision Date: 07-JAN-2005
PURPOSE
The Oracle Cost Based Optimizer is continually been enhanced. These
enhancements were designed to improve performance but in some cases can
cause a minority of queries to perform no better or worse than before. This
article discusses some of the potential problems you may encounter when
moving SQL Queries from Oracle 8i to Oracle 9i. It mainly centres on changes
within the Cost Based Optimizer.
SCOPE & APPLICATION
DBAs and Application Designers
Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
Subquery Unnesting Changes
Oracle 9i enabled subquery unnesting by default, controlled by the
<Parameter:OPTIMIZER_FEATURES_ENABLE>.
If this was set to >= 9.0.0. then subquery unnesting would be enabled.
This means that on Oracle9i, queries containing subqueries are likely to be
unnested when they were not in Oracle8i.
In most cases unnesting subqueries provides performance enhancements, but in
some cases unnesting can produce a sub-optimal plan (or other factors, such
as missing or innaccurate statistics, can produce a poor plan making the
unnesting seem a bad option).
If an application has 8i to 9i upgrade related query tuning problem, please
consider if subqueries are involved
Enhanced subquery unnesting
Diagnosing Subquery Unnesting issues:
Look for subqueries in the query text
Examine the explain plan and determine if the subquery has been unnested
Compare the current explain plan with the plan from the earlier version to
see if the subquery is being handled differently
Disable subquery unnesting and see if that resolves the problem
To workaround subquery unnesting issues, you may disable unnesting in one of
the following ways:
Set <Parameter:UNNEST_SUBQUERY> (underscore) = FALSE
alter session set "_UNNEST_SUBQUERY" = false;
If _unnest_subquery = false does not help then you may also need to set
_always_semi_join = off (if the subquery is an IN or EXISTS) or
_always_anti_join = off (if the subquery is a NOT IN or NOT EXISTS)
alter session set "_ALWAYS_SEMI_JOIN" = off;
alter session set "_ALWAYS_ANTI_JOIN" = off;
Set <Parameter:OPTIMIZER_FEATURES_ENABLE> to a value less than "9.0.0"
This is a static parameter and cannot be changed on the fly. It has to be
set in one of the instance initialisation files (e.g. initSID.ora).
Set <Parameter:UNNEST_NOTEXISTS_SQ> (underscore) = OFF for not exists
subqueries.
Use a /*+ NO_UNNEST */ hint in the subquery
Rewrite the query to modify or remove the subquery
Please note that the decision to unnest a subquery is not costed in
Oracle9i. The decision to unnest a subquery is taken based on a set of
heuristics (rules) before the query is optimized.
RELATED DOCUMENTS
<Note:258167.1> Upgrading from 8.1.X to 9.X - Potential Query Tuning Related
Issues
<Note:144967.1> Manipulating the access path of queries involving subqueries
<Note:199070.1> Optimizing statements that contain views or subqueries
@ INTERNAL <Note:214074.1> SubQuery Unnesting
@ INTERNAL <Note:250646.1> SubQuery Unnesting - IN SubQuery
@ INTERNAL <Note:258228.1> SubQuery Unnesting - NOT IN SubQuery
@ INTERNAL <Note:258676.1> SubQuery Unnesting - EXISTS SubQuery
-----Original Message-----
From: Daniel Woodruff/EmCare
[mailto:oracledba-ezmlmshield-x29908082.[Email address protected]
Sent: Tuesday, June 28, 2005 2:44 PM
To: LazyDBA Discussion
Subject: Moving from rule based to cost based optimizer? Huge load on
system.
Oracle 9.2 on HP/UX 11.i
Database size 35 Gig.
Users about 120.
We are going through the process of trying to move from RULE based
optimizer to CHOOSE optimizer. I have set the instance in CHOOSE mode,
but when I did that and there were statistics we had a huge load problem.
This problem does not show up in our development system. I have tried
adjusting the two optimizer parameters as shown. I did a analyze using the
dbms_stats of all tables and indexes and we still have load issues when
statistics are available and also if these are set.
optimizer_index_caching=90
optimizer_index_cost_adj=50
The load on our systems goes from 50% to 100% and users slow down and stop
working.
Any ideas?/
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page