Oracle 10g Migration:Oracle Database Tips by Donald Burleson
slow performance after upgrade to Oracle10g
I just upgraded to Oracle
10g and I'm seeing very bad SQL performance. I had
to set optimizer_features_enable=9.0.5. What can I do
to fix Oracle10g upgrade & migration performance tuning problems?
Oracle has improved the
cost-based Oracle optimizer in 9.0.5 and again in 10g, so you need
to take a close look at your environmental parameter settings
(init.ora parms) and your optimizer
statistics. I have complete directions in my book "Oracle
Tuning - The Definitive Reference", but here are some notes.
Oracle tips for
10g migration. and
Oracle 11g upgrade
|For Oracle 10g performance problems,
call BC at 800-766-1884. We are expert at Oracle10g
performance problems and we know many tricks that are not
published by Oracle for achieving optimal tuning.
First, make sure
to see these important notes on
_optimizer_cost_based_transformation issues when doing 10g
Also note that 9i has many
optimizer bugs, notably issues that are corrected with
when using query re-write.
Properly configured, Oracle 10g should always faster
than earlier releases, both for PL/SQL and SQL,
so it is likely that any slow performance after an Oracle 10g
upgrade is due to initialization parameter settings or incomplete
Oracle 10g Optimizer Issues
automated statistics gathering caused problems because the default for
estimate_percent is auto_sample_size and the default for
method_opt is "for all columns size auto".
- In 10g - avoid auto_sample_size: auto_sample_size fixed
in 11g - only works when you specify estimate_percent =
- Histograms in 10g cause troubles because predicates with binds
did not work (bind peeking issue)
Oracle 10g migration due diligence
Oracle 10g is the world's most flexible and
complex database, and upgrading to Oracle 10g is very tricky.
Prior to putting your Oracle 10g upgrade into production, it's a
best practice to obtain an independent
health check to identify sub-optimal configuration settings.
Remember that there were many
optimizer enhancements and bug fixed in Oracle 10g and you can use the
view to display specific optimizer features by release.
Reasons for sub-optimal Oracle 10g performance
For complete insurance against bad performance
after a 10g upgrade, see
Tuning: The Definitive Reference".
for 10g optimization bugs - See MOSC Note 469972.1, note
240764.1, note 466181.1 and note 337096.1.
workload statistics - The 10g CBO requires workload
Selectively disable dynamic sampling -
Dynamic sampling is not for every database. Dynamic
sampling default levels change between releases, and you may
want to turn-off dynamic sampling, depending on your database
Re-set optimizer costing - Consider unsetting your
CPU-based optimizer costing (the 10g default, a change from
9i). CPU costing is best of you see CPU in your top-5
timed events in your STATSPACK/AWR report, and the 10g default
of _optimizer_cost_model=cpu will include CPU costs,
invoking more full scans, especially in tablespaces with large
blocksizes. To return to your 9i CBO I/O-based costing,
set the hidden parameter "_optimizer_cost_model"=io
Verify deprecated parameters - you
need to set optimizer_features_enable = 10.2.0.2 and
optimizer_mode = FIRST_ROWS_n (or ALL_ROWS for a warehouse, but
remove the 9i CHOOSE default).
- - Re-check
parameter - The
Oracle 10g release 2 Performance Tuning Guide (page 14.4) notes:
value of db_file_multiblock_read_count is set to the
maximum allowed by the operating system by default. However,
the optimizer uses mbrc=8 for costing.
Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
- Verify quality of CBO statistics - Oracle 10g
does automatic statistics collection and your original
customized dbms_stats job (with your customized
parameters) will be overlaid. You may also see a
not enough histograms) causing
performance issues. Re-analyze object statistics using dbms_stats and make sure that you collect system statistics.
changes to dbms_stats for more details.
-- wait an hour or so
Check optimizer parameters -
Ensure that you are using the proper optimizer_mode (the
new default is
all_rows instead of choose) and check optimal settings for
(lower from the default of 100) and optimizer_index_caching
(set to a higher value than the default).
- Check I/O timings - This
guy has noted that in Linux
2.6 using ASM, "db file scattered
reads" (full scan I/O) can become slower than "db file
sequential reads" (usually single block gets) because of
non-contiguous data block placement on disk. He
notes that full-scan access speed is aggravated by Oracle
willy-nilly block placement in Automated Storage Management
(ASM) and the use of bitmap freelists (Automated Segment Storage
- Check Cartesian Merge Joins -
Unnecessary cartesian merge joins have been reported as a
source of performance issues.
Disabling Cartesian Merge Joins is possible but is
discouraged. It's always best to look for the root
cause instead of using undocumented or hidden parameters as
a workaround. The warning below about modification of
undocumented parameters should be taken very seriously.
To find the exact root cause of the changed SQL
performance, start by collecting the execution plans and TKPROF
output for the 10g default and again after setting
optimizer_features_enable). Compare the plans and then see
how you might adjust initialization parameters and CBO statistics
(using dbms_stats) to replicate the optimal SQL execution plan.
- If you have a SQL statement which
has poor performance after a 10g upgrade, try temporarily adding a
/*+ rule */ hint to see if the problem is related to sub-optimal
If the RULE hint optimizes the SQL,
remove the RULE hint and adjust your statistics until it replicates
the execution plan.
DBA Reports on 10g upgrade
shows some parameters which relieved
slow SQL performance after a 10g upgrade by George Johnson:
After our upgrade from 9206 to
10201, we ended up with these parameters making the biggest
difference to our slow query performance. The optimizer_index_cost_adj
figure was arrived at after about 2 days of testing various
optimizer_secure_view_merging = false
_gby_hash_aggregation_enabled = FALSE
optimizer_index_cost_adj = 50
optimizer_index_caching = 0
_optimizer_cost_based_transformation = OFF
We were told by one Oracle guy
that if your DB is not a warehouse and it's used batch and OLTP,
the bottom four parameters should be set in 10g, without
question to ensure the Warehouse components do not affect OLTP
should only change underscore undocumented parameters at the direct
request of Oracle technical support.
notes other 10g upgrade issues with advanced SQL optimization in
subquery un-nesting and outer hash joins:
"I have noticed after upgrading
from 9i to 10g, the biggest impacts for query performance were -
(1) subquery unnesting (mostly, unnesting EXISTS to HASH JOIN
(2)right outer hash join (smaller table in an outer join *is*
used as the hash table, unlike what was happening in pre-10g
In some cases (till 10.1.0.4) we would have to hint the
sub-query with NO_UNNEST to avoid the hash join semi. . .
The only ways to get rid of
this problem (ora-979 is omitted if it is raised in inline view)
is to use the hint NO_MERGE in 9i, and depending on 10g version
and testmerge table columns - setting the
'_OPTIMIZER_COST_BASED_TRANSFORMATION' to off on 10g."
by Shervin Sheidaei notes a similar performance issues after a 10g
upgrade and a change to
Some queries after upgrade from
9i to 10g may have performance issue which means they may run
slower in 10g.
For figuring out whether or not performance issue is because of
new enhanced features in 10g please turn off the following
options and run query in 10g.
alter session set
"_optimizer_cost_based_transformation" =off; (Disable subquery
unnesting and view merging -- New 10g optimizer feature).
alter session set "_gby_hash_aggregation_enabled" = FALSE;
(Disable Hash group by aggregation -- New 10g optimizer
More information on the impact of hash aggregation on
performance and the expected effect of disabling GROUP BY
aggregation by setting
_gby_hash_aggregation_enabled to FALSE is
David Aldridge has concerns about
disk read performance when using ASM and ASSM, noting that
placement on the disk and competing I/O requests can impede
scattered read access times (as the read-write head thrashes):
"Does the issue of the slowing of one read
matter when other process are being equally served at the same
time? That's a question at the very heart of i/o scheduling (and
queuing theory, which is what all this is about) — what is
meant by "equally served"?
If it means that the scheduler
finishes with one read request and then immediately moves the
disk heads to satisfy another, then moves the heads back to
satisfy another request from the first process then that might
seem equitable, but it's exactly analogous to a single check-in
handling multiple check-ins at the airport at the same time.
Consider passenger A and passenger B, both
waiting to be served. To check in each passenger takes five
minutes, so passenger A is checked in in five minutes and
passenger B waits for five minutes then gets checked in and is
gone after a total wait of ten minutes.
If, in an effort to be equitable to both
parties, the check-in agent flits between the two then the total
time to check them both in is now eleven minutes (taking into
account a total latency of one minute due to walking between the
desks), and they both wait the full eleven minutes to be
finished. Not equitable at all!"
This shop notes an ODBC transformation problem causing
unnecessary large-table full-table scans and driving-up CPU
We were able to identify (via SQLT reports) an issue with VARCHAR2
Essentially the ADO.net application sends NVARCHAR2 data through
ODBC to the Oracle database. The data columns in the Oracle database
are defined as VARCHAR2.
There is a conversion going on that converts the column data in the
database to NVARCHAR2 causing full table scans.
We were able to programmatically change the application to explicitly
say data in string is VARCHAR thus eliminating the overhead of the
conversion and the full table scans. This drastically reduced
the overhead and dropped the CPU utilization in half. We also do not
see any long running items in OEM.
The question for Oracle
WHY is the database converting the column data to nvarchar2
(using the SYS_OP_C2C function) when it should know that the data type
is varchar2 and should process the Unicode appropriately?
we see is the exact same query coming through as varchar2 in some
cases and nvarchar2 in other cases.
Expert 10g performance support
You can also telephone me for
performance consulting, to quickly solve your upgrade related
performance tuning slowdowns.
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.