Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Slow performance after upgrading to Oracle 10g (Read 25905 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Slow performance after upgrading to Oracle 10g
05.06.07 at 07:19:15
 
Slow performance after upgrading to Oracle 10g
 
At the moment I am investigating a migrated Oracle 8i to Oracle 10g R2 database that shows poor performance. On the whole the migration is a success but certain jobs show poor performance. Investigating the problem I learned a few things.  
 
Various database parameters may need tuning for correct performance:  
http://www.dba-oracle.com/t_slow_performance_after_upgrade.htm
 
What seems a coincidence is that all SQL that show poor performance make use of aggregate functions. The new optimizer in Oracle 10g uses a new function in the explain plan: window sort. Could it be due to this new function?
 
Inside the window sort I am getting full table scans where I expect indexes to be used. Creating statistics does not help.  
 
I learned that we should not use the RULE based optimizer mode anymore. It has also been removed from the Oracle documentation however trying it out it seems it still works as in Oracle 8i. In fact the window sort is not used and the perfomance is similar to that in 8i. It may therefore be an idea to try out the RULE based optimizer when you encounter this problem. In my previous post I explained how to make the RULE based optimizer the default for the session:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1180949994
 
The SQL I played with:
UPDATE mytable ar
   SET ar.column1 =
         (SELECT cvd.VALUE
            FROM cvo,   cvd,   acn
           WHERE ar.id = acn.id
             AND acn.seq = cvo.acn_seq
             AND cvo.seq = cvd.cvo_seq
             AND cvo.TYPE = 'abc'
             AND cvo.code LIKE '%def%'
             AND cvd.end_date = (SELECT MAX (cvd2.end_date)
                              FROM cvd2
                             WHERE cvd2.cvo_seq = cvo.seq)
                             )
 WHERE ar.id = '1234';

 
It seems the select max is where the problem is.  
 
UPDATE STATEMENT                              
     UPDATE      MYTABLE                              
    INDEX RANGE SCAN      index1                              
    VIEW      SYS.VW1                          
     WINDOW SORT                                      
       HASH JOIN                                              TABLE ACCESS FULL      ACN                                  HASH JOIN                                      
           TABLE ACCESS FULL      CVO                                    TABLE ACCESS FULL      CVD
                                     
Back to top
 
« Last Edit: 05.06.07 at 07:20:21 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #1 - 08.06.07 at 07:36:04
 
I was given some advice by a senior Oracle DBA:
 
Notes on the most common 10g migration performance issues:  
http://www.dba-oracle.com/t_slow_performance_after_upgrade.htm
 
We experience full table scans but expect indexes to be used.
 
He adviced to try:  
- Disabling dynamic sampling (if turned-on)
 - Gather system statistics (workload stats)
http://www.dba-oracle.com/t_dbms_stats_gather_system_stats.htm
- Set optimizer_cost_model back to the 9i value (IO)
 
- Set optimizer_index_cost_adj to a law value, say 20, but better, based on I/O timings:  
http://www.dba-oracle.com/oracle_tips_cost_adj.htm
 
Remember, always set the global instance parms first . . .
 
Try running plan9i.sql to see FTS:
http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #2 - 01.11.07 at 13:24:51
 
I also found these parameters which relieved slow SQL performance  
after a 10g upgrade:
 
  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 type  
activity!
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #3 - 01.11.07 at 13:27:02
 
As an intermediate fix we are not implementing stored outlines where a good execution plan is saved for the most offending SQL statements. Not ideal but it provides hopefully some time to really sort it out.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #4 - 08.06.07 at 07:39:55
 
Setting the value of optimizer_index_cost_adj to 30 started the CBO (Cost Based Optimizer) to favor the use of indexes over full table scans. We are now fine tuning this setting.
 
Another article of interest on 10g performance tuning is this one:
In Dutch:
http://www.whitehorses.nl/preventieve_performance_tuning_in_de_oracle_10g_databa se_1464.1195.html
 
You can use http://babelfish.altavista.com/ to get it translated from Dutch into your own language.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #5 - 30.07.07 at 10:17:33
 
Changing the following parameter from 10g to 8.1.7. should bring the old 8i performance back. It seems to do that but not in all cases.  
 
alter session set OPTIMIZER_FEATURES_ENABLE = '10.2.0.1'  
alter session set OPTIMIZER_FEATURES_ENABLE = '8.1.7'
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #6 - 30.07.07 at 10:20:08
 
It seems that declaring cursors opposed to implicit cursors has an impact.
 
If a statement uses an implicit cursor like this the performance is bad (execution plan 1):
for r in (select * from table)
 
If the select statement is declared and when we use the cursor variable the performance is good (execution plan 2):
for r in my_cursor
 
It seems to be that execution plans are different.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #7 - 10.08.07 at 09:05:36
 
Setting optimizer_index_cost_adj made an incredible difference in a query that did not perform well. Our value for optimizer_index_cost_adj was 5 and we set it to 69 following the outcome of the query below. The value for c5 was used.
 
The query went from 20min to 15 sec. The value change caused the execution plan to be changed which probably led to the increase in performance.
 
select
   a.average_wait                                  c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100           c5
from
  v$system_event  a,
  v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read'
;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Slow performance after upgrading to Oracle 10g
Reply #8 - 01.11.07 at 13:23:54
 
Performance is still not what it should be with the migrated 8i to 10g database. Consulting a knowledgeable DBA the following advice was given:
 
Question:
In my example the sql cost is 189 (from explain plan) where a view operation cost inside the explain plan is calculated as 3500000. I think Oracle is wrongly identifying this execution plan as the cheapest plan. In the expensive view operation full table scans are done but it should be way cheaper to use indexes. Have you seen this before?
 
Answer:
Yes, plenty. See for example:  
http://www.dba-oracle.com/t_slow_performance_after_upgrade.htm
 
Try to do this:
- Gather workload statistics - The 10g CBO requires workload information  
with dbms_stats.gather_system_stats
 
- 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 load.
 
- 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, sometimes  
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 your db_file_multiblock_read_count parameter - The Oracle 10g  
release 2 Performance Tuning Guide (page 14.4) notes:
 
  "The 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.
 
- 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 statistics deficiency  
(i.e. not enough histograms) causing performance issues.  Re-analyze object  
statistics using dbms_stats and make sure that you collect system  
statistics.
 
execute dbms_stats.gather_system_stats('start');
-- wait an hour or so
execute dbms_stats.gather_system_stats('stop');
 
-  Check optimizer parameters - Ensure that you are using the proper  
optimizer_mode (default is all_rows) and check optimal settings for  
optimizer_index_cost_adj (lower from the default of 100) and  
optimizer_index_caching (set to a higher value than the default).
 
- Check I/O timings - Oracle guru David Aldridge has noted that "db file  
sequential reads" (full-scan I/O) can become slower than "db file scattered  
reads" (single block gets) on Linux.  He notes that full-scan access speed  
is aggravated by Oracle willy-nilly block placement in Automated Storage  
Management (ASM) and using bitmap freelists (Automated Segment Storage  
Management).
 
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1