Re: Huge optimization costs with 9.2
Here's a script to show differences in default parameter values between releases. Kind of interesting results. Jared -- parmdiff2.sql -- show init parms that differ between instances -- a parameter is displayed only if it appears -- in both database, and is set to default -- -- the purpose of this is to determine if the default -- value for a parameter has changed between database -- releases. Undocumented parameters are included. -- create_xviews.sql needs to be run first -- for this to work unless you logon as SYS col cinstance1 noprint new_value uinstance1 col cinstance2 noprint new_value uinstance2 prompt prompt Parmdif2f - Compare init.ora parms for 2 Oracle Instances prompt Parameters are displayed only if they appear in prompt both instances and are set to default values prompt prompt This helps explain database behavior changes prompt between releases prompt prompt Instance 1: set feed off term off select '&1' cinstance1 from dual; set term on prompt prompt Instance 2: set term off select '&2' cinstance2 from dual; set term on feed on set line 110 break on name skip 1 col name format a40 col value format a40 col database format a8 head 'DB' spool parmdiff2.txt -- basic parm query --select a.ksppinm name, b.ksppstvl value --from x$ksppi a, x$ksppsv b --where a.indx = b.indx select p.name, p.value, p.database from ( ( select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b where a.indx = b.indx minus select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b where a.indx = b.indx ) union all ( select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b where a.indx = b.indx minus select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b where a.indx = b.indx ) ) p, -- this query used to limit output to parameters -- that both instances have in common, and are -- set to default values ( select name from ( select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b where a.indx = b.indx -- get only defaults that have changed -- if ksppstdf is TRUE, then it is a default value and b.ksppstdf = 'TRUE' union select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b where a.indx = b.indx and b.ksppstdf = 'TRUE' ) p3 group by name having count(*) > 1 ) p2 where p.name = p2.name order by name, value, database / spool off undef 1 2 Richard Foote <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/03/2003 08:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: Huge optimization costs with 9.2 We has problems with another undocumented parameter that changed when we migrated to 9i in August last year. _B_TREE_BITMAP_PLANS change from false to true and caused a number of issues with sub-optimal execution plans. Another possible trap for the unwary ... Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, October 02, 2003 2:54 AM > One of the undocumented init.ora parameters that changed from 8 to 9 is > "_UNNEST_SUBQUERY" (from false to true). You could try if that is the > culprit. Of course, since it is an undocumented parameter, get the blessing > from Oracle support before using it in a production database. > > At 10:09 AM 10/1/2003, you wrote: > >Joan, what is the difference in the plans? What specific feature > >made the difference? Are the values of > >optimizer_index_cost_adj and optimizer_index_caching same on both > >versions? How about histograms? What is with > >db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > >everything same as in 8i? May be setting of those parameters can be > >tweaked to your benefit? > > > >On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > > Kirti, > > > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Befor
Re: Huge optimization costs with 9.2
How many more of these "undocumented" but seemingly crucial optimizer parameters has changed from 8i to 9i? I'd think these are a bit more important to know about. Anyone have a list of these changed parameters? Are they noted in the 9i docs? boss > > We has problems with another undocumented parameter that changed when we > migrated to 9i in August last year. _B_TREE_BITMAP_PLANS change from false > to true and caused a number of issues with sub-optimal execution plans. > > Another possible trap for the unwary ... > > Cheers > > Richard > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, October 02, 2003 2:54 AM > > > > One of the undocumented init.ora parameters that changed from 8 to 9 is > > "_UNNEST_SUBQUERY" (from false to true). You could try if that is the > > culprit. Of course, since it is an undocumented parameter, get the > blessing > > from Oracle support before using it in a production database. > > > > At 10:09 AM 10/1/2003, you wrote: > > >Joan, what is the difference in the plans? What specific feature > > >made the difference? Are the values of > > >optimizer_index_cost_adj and optimizer_index_caching same on both > > >versions? How about histograms? What is with > > >db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > > >everything same as in 8i? May be setting of those parameters can be > > >tweaked to your benefit? > > > > > >On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > > > Kirti, > > > > > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > > > performance is good. After upgrade, one query run time from 2 min to > 12 > > > > hours. Of course, I re-analyzed all tables and indexes. The explain > plan > > > > changed from hash join to nested-loop. All the parameters are same. So > I > > > > have to put optimized_feature_enable=8.1.7 to make run normal as > usual. > > > > I hate to disable the new feature, but no choose. > > > > Wolfgang Breitling > > Oracle7, 8, 8i, 9i OCP DBA > > Centrex Consulting Corporation > > http://www.centrexcc.com > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Wolfgang Breitling > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Richard Foote > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Todd Boss INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
We has problems with another undocumented parameter that changed when we migrated to 9i in August last year. _B_TREE_BITMAP_PLANS change from false to true and caused a number of issues with sub-optimal execution plans. Another possible trap for the unwary ... Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, October 02, 2003 2:54 AM > One of the undocumented init.ora parameters that changed from 8 to 9 is > "_UNNEST_SUBQUERY" (from false to true). You could try if that is the > culprit. Of course, since it is an undocumented parameter, get the blessing > from Oracle support before using it in a production database. > > At 10:09 AM 10/1/2003, you wrote: > >Joan, what is the difference in the plans? What specific feature > >made the difference? Are the values of > >optimizer_index_cost_adj and optimizer_index_caching same on both > >versions? How about histograms? What is with > >db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > >everything same as in 8i? May be setting of those parameters can be > >tweaked to your benefit? > > > >On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > > Kirti, > > > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > > performance is good. After upgrade, one query run time from 2 min to 12 > > > hours. Of course, I re-analyzed all tables and indexes. The explain plan > > > changed from hash join to nested-loop. All the parameters are same. So I > > > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > > > I hate to disable the new feature, but no choose. > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Wolfgang Breitling > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Hi Govind, I tested it. no luck. I killed the session. SQL> alter session set sql_trace=true 2 ; Session altered. SQL> alter session set "_UNNEST_SUBQUERY" = FALSE; Session altered. SQL> alter session set "_ORDERED_NESTED_LOOP" = FALSE; Session altered. SQL> alter session set "_ALWAYS_SEMI_JOIN" = off; Session altered. "pr_view3.prf" 733 lines, 38147 characters Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: CHOOSE 0 LOAD AS SELECT 1420NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_IDENTITY' 96VIEW PUSHED PREDICATE 96 HASH JOIN 96 NESTED LOOPS 96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_MED' 96INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_MED_UNIQUE_TRUNK' (UNIQUE) 96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_DEPARTMENTS' 96INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_DEPARTMENTS_PK' (UNIQUE) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: CHOOSE 0 LOAD AS SELECT 1420NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420 NESTED LOOPS (OUTER) 1420TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_IDENTITY' 96VIEW PUSHED PREDICATE 96 HASH JOIN 96 NESTED LOOPS 96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_MED' 96INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_MED_UNIQUE_TRUNK' (UNIQUE) 96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_DEPARTMENTS' 96INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_DEPARTMENTS_PK' (UNIQUE) 361536 VIEW OF 'PR_ADMINS' 361536 UNION-ALL 361536HASH JOIN 361536 NESTED LOOPS 361536 VIEW 361536 UNION-ALL 0HASH JOIN 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_DEPARTMENTS' 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_HR' 361536HASH JOIN 2688 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_DEPARTMENTS' 361536 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_MED' 361536 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_ADMIN_TYPES_PK' (UNIQUE) 2304 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_ADMIN_GROUPS' 0NESTED LOOPS 1298400 HASH JOIN 2304 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_ADMIN_GROUPS' 1298400 HASH JOIN 1298400 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_SIS' 1824 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_SIS_MAPAUCOLLEGES' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_ADMIN_TYPES_PK' (UNIQUE) 0HASH JOIN 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_ADMIN_GROUPS' 0 MERGE JOIN (CARTESIAN) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_AFFILIATE' 0 BUFFER (SORT) 0 INDEX GOAL: ANALYZED (FULL SCAN) OF 'PR_ADMIN_TYPES_PK' (UNIQUE) 1 VIEW PUSHED PREDICATE 1HASH JOIN 1 NESTED LOOPS (OUTER) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_AFFILIATE' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_AFFILIATE_PK' (UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PR_DEPARTMENTS' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PR_DEPARTMENTS_PK' (UNIQUE) 481 VIEW OF 'PR_ADMINS' 481 UNION-ALL 0 HASH JOIN 0NESTED LOOPS 0 VIEW 0 UNION-ALL 0 HASH JOIN 0TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_DEPARTMENTS' 0TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_HR' 0 HASH JOIN 0TABLE ACCESS GOAL: ANALYZED (FULL)
RE: Huge optimization costs with 9.2
Yes. You have to bounce the database for this to take effect. -Original Message- Joan Hsieh Sent: Thursday, October 02, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Govind, I will test it out today and post the updates, I should set optimize_feature_enable back to 9.2.0 before I test this out, right? JOan [EMAIL PROTECTED] wrote: > > Can you try to generate the query plan with these settings? These are the 9i CBO > Hidden parameters > to generate 8.1.7 like query plans. > > alter session set "_UNNEST_SUBQUERY" = FALSE; > alter session set "_ORDERED_NESTED_LOOP" = FALSE; > alter session set "_ALWAYS_SEMI_JOIN" = off; > > explain plan for > ; > > -Original Message- > Joan Hsieh > Sent: Wednesday, October 01, 2003 2:10 PM > To: Multiple recipients of list ORACLE-L > > this is the explain plan for the 9i, sorry it is long sql. > > Rows Row Source Operation > --- --- > 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) > 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) > 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) > 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 > us) > 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 > us) > 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 > time=1452575 us) >3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 > time=971571531 us) >3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) >3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) >3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 > time=651677 us) >3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 > w=0 time=503807 us)(object id 51394) >3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 > w=0 time=46636 us) >3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 > time=15519 us)(object id 51375) > 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) > 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) > 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) > 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 > us) > 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) > 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) > 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) > 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > w=0 time=903383 us) > 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) > 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) > 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > w=0 time=552179 us) > 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 > time=26292679 us) > 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 > w=0 time=26817559 us)(object id 51357) > 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=228394 us) > 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) > 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) > 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=216366 us) > 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) > 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 > time=52143346 us) > 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 > r=0 w=0 time=353694 us) > 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 > time=115423379 us)(object id 51357) > 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) > 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=289225 us) > 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) > 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 > us) > 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) > 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 > time=0 us)(object id 51357) > 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 > us) > 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) > 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) > 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 > w=0 time=376132 us) > 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 > time=292929 us)(object id 51360) > 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 > w=0 time=4703 us) > 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 > time=1925 us)(object id 51375) > 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) > 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018
Re: Huge optimization costs with 9.2
Govind, I will test it out today and post the updates, I should set optimize_feature_enable back to 9.2.0 before I test this out, right? JOan [EMAIL PROTECTED] wrote: > > Can you try to generate the query plan with these settings? These are the 9i CBO > Hidden parameters > to generate 8.1.7 like query plans. > > alter session set "_UNNEST_SUBQUERY" = FALSE; > alter session set "_ORDERED_NESTED_LOOP" = FALSE; > alter session set "_ALWAYS_SEMI_JOIN" = off; > > explain plan for > ; > > -Original Message- > Joan Hsieh > Sent: Wednesday, October 01, 2003 2:10 PM > To: Multiple recipients of list ORACLE-L > > this is the explain plan for the 9i, sorry it is long sql. > > Rows Row Source Operation > --- --- > 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) > 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) > 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) > 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 > us) > 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 > us) > 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 > time=1452575 us) >3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 > time=971571531 us) >3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) >3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) >3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 > time=651677 us) >3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 > w=0 time=503807 us)(object id 51394) >3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 > w=0 time=46636 us) >3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 > time=15519 us)(object id 51375) > 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) > 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) > 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) > 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 > us) > 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) > 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) > 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) > 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > w=0 time=903383 us) > 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) > 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) > 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > w=0 time=552179 us) > 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 > time=26292679 us) > 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 > w=0 time=26817559 us)(object id 51357) > 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=228394 us) > 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) > 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) > 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=216366 us) > 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) > 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 > time=52143346 us) > 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 > r=0 w=0 time=353694 us) > 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 > time=115423379 us)(object id 51357) > 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) > 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > time=289225 us) > 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) > 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 > us) > 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) > 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 > time=0 us)(object id 51357) > 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 > us) > 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) > 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) > 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 > w=0 time=376132 us) > 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 > time=292929 us)(object id 51360) > 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 > w=0 time=4703 us) > 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 > time=1925 us)(object id 51375) > 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) > 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018 us) > 0 HASH JOIN (cr=3848 r=0 w=0 time=417296 us) > 0 NESTED LOOPS (cr=3848 r=0 w=0 time=351280 us) > 0VIEW (cr=3848 r=0 w=0 time=35
RE: Huge optimization costs with 9.2
Can you try to generate the query plan with these settings? These are the 9i CBO Hidden parameters to generate 8.1.7 like query plans. alter session set "_UNNEST_SUBQUERY" = FALSE; alter session set "_ORDERED_NESTED_LOOP" = FALSE; alter session set "_ALWAYS_SEMI_JOIN" = off; explain plan for ; -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:10 PM To: Multiple recipients of list ORACLE-L this is the explain plan for the 9i, sorry it is long sql. Rows Row Source Operation --- --- 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 us) 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 us) 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 time=1452575 us) 3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 time=971571531 us) 3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) 3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) 3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 time=651677 us) 3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 w=0 time=503807 us)(object id 51394) 3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 w=0 time=46636 us) 3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=15519 us)(object id 51375) 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 us) 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=903383 us) 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=552179 us) 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 time=26292679 us) 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=26817559 us)(object id 51357) 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=228394 us) 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=216366 us) 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 time=52143346 us) 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 r=0 w=0 time=353694 us) 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=115423379 us)(object id 51357) 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=289225 us) 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 us) 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 time=0 us)(object id 51357) 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 us) 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 w=0 time=376132 us) 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 time=292929 us)(object id 51360) 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 w=0 time=4703 us) 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=1925 us)(object id 51375) 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018 us) 0 HASH JOIN (cr=3848 r=0 w=0 time=417296 us) 0 NESTED LOOPS (cr=3848 r=0 w=0 time=351280 us) 0VIEW (cr=3848 r=0 w=0 time=350781 us) 0 UNION-ALL (cr=3848 r=0 w=0 time=349902 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=222856 us) 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0 time=155040 us) 0 TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=123298 us)
RE: Huge optimization costs with 9.2
Joan, Can you post the query in question? Thanks, GOvind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Malden, They all same. I didn't change any parameters after upgrade. The difference in the plans are one used all hash join vs nested loop to join tables. The histograms are all same. db_file_multiblock_read_count is 8, sort_area_size is 1mb, hash_area_size is 40096. optimize_index_caching and optimizer_index_cost is 0/100 all same as 8i. The interesting part is after I changed optimize_feature_enable to 8.1.7. They produced the same explain plan. Joan Mladen Gogala wrote: > > Joan, what is the difference in the plans? What specific feature > made the difference? Are the values of > optimizer_index_cost_adj and optimizer_index_caching same on both > versions? How about histograms? What is with > db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > everything same as in 8i? May be setting of those parameters can be > tweaked to your benefit? > > On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > Kirti, > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > performance is good. After upgrade, one query run time from 2 min to 12 > > hours. Of course, I re-analyzed all tables and indexes. The explain plan > > changed from hash join to nested-loop. All the parameters are same. So I > > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > > I hate to disable the new feature, but no choose. > > > > Joan > > > > Kirtikumar Deshpande wrote: > > > > > > Were tables/indexes anlayzed after the upgrade? > > > > > > - Kirti > > > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > > Hello All > > > > > > > > Version & OS: > > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > > > Problem: > > > > We've captured the sql text and optimization plans for critical sql > > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > > that the cost associated with every sql statement is now HUGE > > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > > via traces, > > > > these statement are noticeably slower per execution. > > > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > > > Thank you in advance. > > > > > > > > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > http://sitebuilder.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Kirtikumar Deshpande > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting services > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > Note: > This message is for the named person's use only. It may contain confidential, > proprietary or legally privileged information. No confidentiality or privilege is > waived or lost by any mistransmission. If you receive this message in error, please > immediately delete it and all copies of it from your system, destroy any hard copies > of it and notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to > monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where > the message states otherwise and the sender is authorized to state them to be the > views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: J
RE: Huge optimization costs with 9.2
Could it be that the optimizer uses different algorithms since 9.2 value would default max permutations to 2000 instead of the 8 -- Oracle9i Database Reference OPTIMIZER_MAX_PERMUTATIONS Parameter type Integer Default value If OPTIMIZER_FEATURES_ENABLE is set to 9.0.0 or higher, then 2000 If OPTIMIZER_FEATURES_ENABLE is set to 8.1.7 or lower, then 8 Parameter class Dynamic: ALTER SESSION Range of values 4 to 8 David Phillips Support DBA Gasper Corporation [EMAIL PROTECTED] BAARF member #30 -Original Message- Sent: Wednesday, October 01, 2003 1:54 PM To: Multiple recipients of list ORACLE-L Yes, they are same. Basically I didn't change any parameter after upgrade. Guang Mei wrote: > > Hi Joan: > > Is your "hash_area_size" parameter the same in both situations? > > Guang > > -Original Message- > Joan Hsieh > Sent: Wednesday, October 01, 2003 11:55 AM > To: Multiple recipients of list ORACLE-L > > Kirti, > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > performance is good. After upgrade, one query run time from 2 min to 12 > hours. Of course, I re-analyzed all tables and indexes. The explain plan > changed from hash join to nested-loop. All the parameters are same. So I > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > I hate to disable the new feature, but no choose. > > Joan > > Kirtikumar Deshpande wrote: > > > > Were tables/indexes anlayzed after the upgrade? > > > > - Kirti > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > Hello All > > > > > > Version & OS: > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > Problem: > > > We've captured the sql text and optimization plans for critical sql > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > that the cost associated with every sql statement is now HUGE > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > via traces, > > > these statement are noticeably slower per execution. > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > Thank you in advance. > > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > http://sitebuilder.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Kirtikumar Deshpande > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Joan Hsieh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the mess
Re: Huge optimization costs with 9.2
this is the explain plan for the 9i, sorry it is long sql. Rows Row Source Operation --- --- 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 us) 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 us) 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 time=1452575 us) 3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 time=971571531 us) 3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) 3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) 3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 time=651677 us) 3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 w=0 time=503807 us)(object id 51394) 3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 w=0 time=46636 us) 3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=15519 us)(object id 51375) 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 us) 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=903383 us) 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=552179 us) 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 time=26292679 us) 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=26817559 us)(object id 51357) 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=228394 us) 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=216366 us) 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 time=52143346 us) 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 r=0 w=0 time=353694 us) 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=115423379 us)(object id 51357) 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=289225 us) 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 us) 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 time=0 us)(object id 51357) 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 us) 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 w=0 time=376132 us) 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 time=292929 us)(object id 51360) 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 w=0 time=4703 us) 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=1925 us)(object id 51375) 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018 us) 0 HASH JOIN (cr=3848 r=0 w=0 time=417296 us) 0 NESTED LOOPS (cr=3848 r=0 w=0 time=351280 us) 0VIEW (cr=3848 r=0 w=0 time=350781 us) 0 UNION-ALL (cr=3848 r=0 w=0 time=349902 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=222856 us) 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0 time=155040 us) 0 TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=123298 us) 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0 time=72769 us) 0 TABLE ACCESS FULL PR_MED (cr=0 r=0 w=0 time=0 us) 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 time=0 us)(object id 51357) 0 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=0 r=0 w=0 time=0 us) 0 NESTED LOOPS (cr=288120 r=0 w=0 time=89293562 us) 6505525 HASH JOIN
Re: Huge optimization costs with 9.2
Malden, They all same. I didn't change any parameters after upgrade. The difference in the plans are one used all hash join vs nested loop to join tables. The histograms are all same. db_file_multiblock_read_count is 8, sort_area_size is 1mb, hash_area_size is 40096. optimize_index_caching and optimizer_index_cost is 0/100 all same as 8i. The interesting part is after I changed optimize_feature_enable to 8.1.7. They produced the same explain plan. Joan Mladen Gogala wrote: > > Joan, what is the difference in the plans? What specific feature > made the difference? Are the values of > optimizer_index_cost_adj and optimizer_index_caching same on both > versions? How about histograms? What is with > db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > everything same as in 8i? May be setting of those parameters can be > tweaked to your benefit? > > On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > Kirti, > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > performance is good. After upgrade, one query run time from 2 min to 12 > > hours. Of course, I re-analyzed all tables and indexes. The explain plan > > changed from hash join to nested-loop. All the parameters are same. So I > > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > > I hate to disable the new feature, but no choose. > > > > Joan > > > > Kirtikumar Deshpande wrote: > > > > > > Were tables/indexes anlayzed after the upgrade? > > > > > > - Kirti > > > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > > Hello All > > > > > > > > Version & OS: > > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > > > Problem: > > > > We've captured the sql text and optimization plans for critical sql > > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > > that the cost associated with every sql statement is now HUGE > > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > > via traces, > > > > these statement are noticeably slower per execution. > > > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > > > Thank you in advance. > > > > > > > > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > http://sitebuilder.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Kirtikumar Deshpande > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting services > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > Note: > This message is for the named person's use only. It may contain confidential, > proprietary or legally privileged information. No confidentiality or privilege is > waived or lost by any mistransmission. If you receive this message in error, please > immediately delete it and all copies of it from your system, destroy any hard copies > of it and notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to > monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where > the message states otherwise and the sender is authorized to state them to be the > views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Huge optimization costs with 9.2
Yes, they are same. Basically I didn't change any parameter after upgrade. Guang Mei wrote: > > Hi Joan: > > Is your "hash_area_size" parameter the same in both situations? > > Guang > > -Original Message- > Joan Hsieh > Sent: Wednesday, October 01, 2003 11:55 AM > To: Multiple recipients of list ORACLE-L > > Kirti, > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > performance is good. After upgrade, one query run time from 2 min to 12 > hours. Of course, I re-analyzed all tables and indexes. The explain plan > changed from hash join to nested-loop. All the parameters are same. So I > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > I hate to disable the new feature, but no choose. > > Joan > > Kirtikumar Deshpande wrote: > > > > Were tables/indexes anlayzed after the upgrade? > > > > - Kirti > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > Hello All > > > > > > Version & OS: > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > Problem: > > > We've captured the sql text and optimization plans for critical sql > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > that the cost associated with every sql statement is now HUGE > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > via traces, > > > these statement are noticeably slower per execution. > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > Thank you in advance. > > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > http://sitebuilder.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Kirtikumar Deshpande > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Joan Hsieh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Wolfgang, you're a genuine cornucopia of useful knowledge. This is another email of yours that I'll have to save for later as "people's exhibit S". On Wed, 2003-10-01 at 12:54, Wolfgang Breitling wrote: > One of the undocumented init.ora parameters that changed from 8 to 9 is > "_UNNEST_SUBQUERY" (from false to true). You could try if that is the > culprit. Of course, since it is an undocumented parameter, get the blessing > from Oracle support before using it in a production database. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Huge optimization costs with 9.2
Title: RE: Huge optimization costs with 9.2 yeah ... and setting _unnest_subquery=true also gave WRONG results when you used a aggregate function in a sub-query without a group by clause. That was a bug ... Raj -Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Subject: Re: Huge optimization costs with 9.2 One of the undocumented init.ora parameters that changed from 8 to 9 is "_UNNEST_SUBQUERY" (from false to true). You could try if that is the culprit. Of course, since it is an undocumented parameter, get the blessing from Oracle support before using it in a production database. At 10:09 AM 10/1/2003, you wrote: >Joan, what is the difference in the plans? What specific feature >made the difference? Are the values of >optimizer_index_cost_adj and optimizer_index_caching same on both >versions? How about histograms? What is with >db_file_multiblock_read_count,sort_area_size and hash_area_size? Is >everything same as in 8i? May be setting of those parameters can be >tweaked to your benefit? > >On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > Kirti, > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > performance is good. After upgrade, one query run time from 2 min to 12 > > hours. Of course, I re-analyzed all tables and indexes. The explain plan > > changed from hash join to nested-loop. All the parameters are same. So I > > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > > I hate to disable the new feature, but no choose. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Huge optimization costs with 9.2
One of the undocumented init.ora parameters that changed from 8 to 9 is "_UNNEST_SUBQUERY" (from false to true). You could try if that is the culprit. Of course, since it is an undocumented parameter, get the blessing from Oracle support before using it in a production database. At 10:09 AM 10/1/2003, you wrote: Joan, what is the difference in the plans? What specific feature made the difference? Are the values of optimizer_index_cost_adj and optimizer_index_caching same on both versions? How about histograms? What is with db_file_multiblock_read_count,sort_area_size and hash_area_size? Is everything same as in 8i? May be setting of those parameters can be tweaked to your benefit? On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > Kirti, > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > performance is good. After upgrade, one query run time from 2 min to 12 > hours. Of course, I re-analyzed all tables and indexes. The explain plan > changed from hash join to nested-loop. All the parameters are same. So I > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > I hate to disable the new feature, but no choose. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Huge optimization costs with 9.2
Hi Joan: Is your "hash_area_size" parameter the same in both situations? Guang -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Joan Kirtikumar Deshpande wrote: > > Were tables/indexes anlayzed after the upgrade? > > - Kirti > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > Hello All > > > > Version & OS: > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > Problem: > > We've captured the sql text and optimization plans for critical sql > > prior to upgrading to 9.2. After the upgrade we have noticed > > that the cost associated with every sql statement is now HUGE > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > via traces, > > these statement are noticeably slower per execution. > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > Thank you in advance. > > > > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kirtikumar Deshpande > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Execution plans would be helpful. If optimizer_index_* parameters are unset, CBO tends to prefer full table access more, which doesn't seem to be your case (but exectution plans are needed in order to be sure in that). As Mladen asked about histograms -> do you use bind variables in your queries? In 8i CBO can't peek bind variable values during hard parse, but in 9i it can, this feature in combination with histograms might cause execution plan change... Did you do the analyzing in 9i exactly the same way and with same tools than in 8i? Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 01, 2003 7:09 PM > Joan, what is the difference in the plans? What specific feature > made the difference? Are the values of > optimizer_index_cost_adj and optimizer_index_caching same on both > versions? How about histograms? What is with > db_file_multiblock_read_count,sort_area_size and hash_area_size? Is > everything same as in 8i? May be setting of those parameters can be > tweaked to your benefit? > > On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > > Kirti, > > > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > > performance is good. After upgrade, one query run time from 2 min to 12 > > hours. Of course, I re-analyzed all tables and indexes. The explain plan > > changed from hash join to nested-loop. All the parameters are same. So I > > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > > I hate to disable the new feature, but no choose. > > > > Joan > > > > Kirtikumar Deshpande wrote: > > > > > > Were tables/indexes anlayzed after the upgrade? > > > > > > - Kirti > > > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > > Hello All > > > > > > > > Version & OS: > > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > > > Problem: > > > > We've captured the sql text and optimization plans for critical sql > > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > > that the cost associated with every sql statement is now HUGE > > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > > via traces, > > > > these statement are noticeably slower per execution. > > > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > > > Thank you in advance. > > > > > > > > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > http://sitebuilder.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Kirtikumar Deshpande > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting services > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > Note: > This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Auth
RE: Huge optimization costs with 9.2
Joan, Can you post the query with the plan in 8.1.7 and 9.2; We ran into certain types of queries that had totally different execution plans and got work-arounds. Thanks, Govind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Joan Kirtikumar Deshpande wrote: > > Were tables/indexes anlayzed after the upgrade? > > - Kirti > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > Hello All > > > > Version & OS: > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > Problem: > > We've captured the sql text and optimization plans for critical sql > > prior to upgrading to 9.2. After the upgrade we have noticed > > that the cost associated with every sql statement is now HUGE > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > via traces, > > these statement are noticeably slower per execution. > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > Thank you in advance. > > > > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kirtikumar Deshpande > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Joan, what is the difference in the plans? What specific feature made the difference? Are the values of optimizer_index_cost_adj and optimizer_index_caching same on both versions? How about histograms? What is with db_file_multiblock_read_count,sort_area_size and hash_area_size? Is everything same as in 8i? May be setting of those parameters can be tweaked to your benefit? On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: > Kirti, > > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, > performance is good. After upgrade, one query run time from 2 min to 12 > hours. Of course, I re-analyzed all tables and indexes. The explain plan > changed from hash join to nested-loop. All the parameters are same. So I > have to put optimized_feature_enable=8.1.7 to make run normal as usual. > I hate to disable the new feature, but no choose. > > Joan > > Kirtikumar Deshpande wrote: > > > > Were tables/indexes anlayzed after the upgrade? > > > > - Kirti > > > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > > Hello All > > > > > > Version & OS: > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > > > Problem: > > > We've captured the sql text and optimization plans for critical sql > > > prior to upgrading to 9.2. After the upgrade we have noticed > > > that the cost associated with every sql statement is now HUGE > > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > > via traces, > > > these statement are noticeably slower per execution. > > > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > > > Thank you in advance. > > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > http://sitebuilder.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Kirtikumar Deshpande > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Joan Kirtikumar Deshpande wrote: > > Were tables/indexes anlayzed after the upgrade? > > - Kirti > > --- Jeff Landers <[EMAIL PROTECTED]> wrote: > > Hello All > > > > Version & OS: > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > > > Problem: > > We've captured the sql text and optimization plans for critical sql > > prior to upgrading to 9.2. After the upgrade we have noticed > > that the cost associated with every sql statement is now HUGE > > compared to its 9.0.1.4 counterpart. Per the statistics being captured > > via traces, > > these statement are noticeably slower per execution. > > > > Anyone experiencing/experienced the same problem with 9.2? > > > > Thank you in advance. > > > > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kirtikumar Deshpande > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Huge optimization costs with 9.2
Were tables/indexes anlayzed after the upgrade? - Kirti --- Jeff Landers <[EMAIL PROTECTED]> wrote: > Hello All > > Version & OS: > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. > > Problem: > We've captured the sql text and optimization plans for critical sql > prior to upgrading to 9.2. After the upgrade we have noticed > that the cost associated with every sql statement is now HUGE > compared to its 9.0.1.4 counterpart. Per the statistics being captured > via traces, > these statement are noticeably slower per execution. > > Anyone experiencing/experienced the same problem with 9.2? > > Thank you in advance. > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
