| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_IDENTITY'
96 VIEW PUSHED PREDICATE
96 HASH JOIN
96 NESTED LOOPS
96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PR_MED'
96 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_MED_UNIQUE_TRUNK' (UNIQUE)
96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PR_DEPARTMENTS'
96 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_DEPARTMENTS_PK' (UNIQUE)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 NESTED LOOPS (OUTER)
1420 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PR_IDENTITY'
96 VIEW PUSHED PREDICATE
96 HASH JOIN
96 NESTED LOOPS
96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PR_MED'
96 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_MED_UNIQUE_TRUNK' (UNIQUE)
96 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PR_DEPARTMENTS'
96 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_DEPARTMENTS_PK' (UNIQUE)
361536 VIEW OF 'PR_ADMINS'
361536 UNION-ALL
361536 HASH JOIN
361536 NESTED LOOPS
361536 VIEW
361536 UNION-ALL
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_HR'
361536 HASH 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'
0 NESTED 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)
0 HASH 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
1 HASH 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
0 NESTED LOOPS
0 VIEW
0 UNION-ALL
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_HR'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_MED'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
0 NESTED LOOPS
13525 HASH JOIN
24 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
13525 HASH JOIN
13525 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS'
19 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS_MAPAUCOLLEGES'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
481 HASH JOIN
24 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
1443 MERGE JOIN (CARTESIAN)
481 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_AFFILIATE'
1443 BUFFER (SORT)
3 INDEX GOAL: ANALYZED (FULL SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
1420 VIEW PUSHED PREDICATE
1420 HASH JOIN
1420 NESTED LOOPS
1420 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_HR'
1420 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_HR_UNIQUE_TRUNK' (UNIQUE)
1420 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_DEPARTMENTS'
1420 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_DEPARTMENTS_PK' (UNIQUE)
5736800 VIEW OF 'PR_ADMINS'
5736800 UNION-ALL
5736800 HASH JOIN
34080 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
5736800 NESTED LOOPS
5736800 VIEW
5736800 UNION-ALL
5736800 HASH JOIN
310980 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_DEPARTMENTS'
5736800 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_HR'
0 FILTER
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'PR_MED'
5736800 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
0 NESTED LOOPS
0 HASH JOIN
17040 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
19205500 HASH JOIN
19205500 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS'
26980 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS_MAPAUCOLLEGES'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
0 HASH 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)
25 VIEW PUSHED PREDICATE
25 HASH JOIN
25 NESTED LOOPS
25 NESTED LOOPS
25 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_SIS'
25 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_SIS_UNIQUE_TRUNK' (UNIQUE)
25 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_SIS_MAPAUCOLLEGES'
25 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_SIS_MAPAUCOLLEGES_PK' (UNIQUE)
25 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_SIS_COLLEGES'
25 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_SIS_COLLEGES_PK' (UNIQUE)
338125 VIEW OF 'PR_ADMINS'
338125 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 VIEW
0 UNION-ALL
0 FILTER
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_HR'
0 FILTER
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_DEPARTMENTS'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_MED'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PR_ADMIN_GROUPS'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_GROUPS_PK' (UNIQUE)
338125 NESTED LOOPS
338125 HASH JOIN
275 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_ADMIN_GROUPS'
338125 HASH JOIN
338125 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS'
475 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PR_SIS_MAPAUCOLLEGES'
338125 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PR_ADMIN_TYPES_PK' (UNIQUE)
0 HASH 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
Govind.Arumugam_at_alltel.com wrote:
> > 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 > > Govind.Arumugam_at_alltel.com 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 > > <query>; > > > > -----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) > > 42647 NESTED 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) > > 3766 HASH 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) > > 14182756 NESTED 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) > > 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > > w=0 time=903383 us) > > 0 TABLE 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) > > 105448 TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 > > w=0 time=552179 us) > > 14182756 TABLE 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) > > 90384 TABLE 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) > > 50935150 HASH 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) > > 0 INDEX 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) > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 > > time=289225 us) > > 0 MERGE 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) > > 481 NESTED 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) > > 231361 VIEW (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) > > 0 VIEW (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) > > 0 INDEX 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 (cr=287638 r=0 w=0 time=56959607 us) > > 11544 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=1443 r=0 w=0 > > time=22346 us) > > 6505525 HASH JOIN (cr=286195 r=0 w=0 time=30650687 us) > > 6505525 TABLE ACCESS FULL PR_SIS (cr=284752 r=0 w=0 > > time=6657346 us) > > 9139 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=1443 r=0 > > w=0 time=45345 us) > > 0 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=482 r=0 w=0 > > time=16576867 us)(object id 51357) > > 231361 HASH JOIN (cr=10101 r=15 w=0 time=4671565 us) > > 11544 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=1443 r=0 w=0 > > time=52150 us) > > 694083 MERGE JOIN CARTESIAN (cr=8658 r=15 w=0 time=2260463 > > us) > > 231361 TABLE ACCESS FULL PR_AFFILIATE (cr=8177 r=15 w=0 > > time=375547 us) > > 694083 BUFFER SORT (cr=481 r=0 w=0 time=792138 us) > > 1443 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=481 r=0 w=0 > > time=8324 us)(object id 51357) > > 4040 VIEW PUSHED PREDICATE (cr=3185772 r=792 w=0 time=654451906 > > us) > > 4040 HASH JOIN (cr=3185772 r=792 w=0 time=654294108 us) > > 4040 NESTED LOOPS (cr=50731 r=29 w=0 time=670381 us) > > 4040 TABLE ACCESS BY INDEX ROWID PR_HR (cr=46689 r=27 w=0 > > time=534717 us) > > 4040 INDEX UNIQUE SCAN PR_HR_UNIQUE_TRUNK (cr=42649 r=26 w=0 > > time=419348 us)(object id 51382) > > 4040 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=4042 r=2 > > w=0 time=71243 us) > > 4040 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=1 w=0 > > time=27603 us)(object id 51375) > > 16321600 VIEW (cr=3135041 r=763 w=0 time=619837379 us) > > 16321600 UNION-ALL (cr=3135041 r=763 w=0 time=606203283 us) > > 16321600 HASH JOIN (cr=707001 r=170 w=0 time=290238002 us) > > 96960 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=12120 r=2 w=0 > > time=232462 us) > > 16321600 NESTED LOOPS (cr=694881 r=168 w=0 time=244184125 us) > > 16321600 VIEW (cr=690840 r=167 w=0 time=152375256 us) > > 16321600 UNION-ALL (cr=690840 r=167 w=0 time=138600082 us) > > 16321600 HASH JOIN (cr=690840 r=167 w=0 time=108223600 us) > > 884760 TABLE ACCESS FULL PR_DEPARTMENTS (cr=16160 r=2 w=0 > > time=1211703 us) > > 16321600 TABLE ACCESS FULL PR_HR (cr=674680 r=165 w=0 > > time=32969121 us) > > 0 FILTER (cr=0 r=0 w=0 time=2557 us) > > 0 HASH JOIN (cr=0 r=0 w=0 time=0 us) > > 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=0 r=0 w=0 > > time=0 us) > > 0 TABLE ACCESS FULL PR_MED (cr=0 r=0 w=0 time=0 us) > > 16321600 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=4041 r=1 w=0 > > time=38662427 us)(object id 51357) > > 0 NESTED LOOPS (cr=2415920 r=593 w=0 time=288098950 us) > > 0 HASH JOIN (cr=2415920 r=593 w=0 time=288091940 us) > > 48480 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=12120 r=0 w=0 > > time=244840 us) > > 54641000 HASH JOIN (cr=2403800 r=593 w=0 time=215881720 us) > > 54641000 TABLE ACCESS FULL PR_SIS (cr=2391680 r=591 w=0 > > time=55873125 us) > > 76760 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=12120 r=2 > > w=0 time=312667 us) > > 0 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 > > time=0 us)(object id 51357) > > 0 HASH JOIN (cr=12120 r=0 w=0 time=930814 us) > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=12120 r=0 w=0 > > time=288454 us) > > 0 MERGE 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) > > 13525 VIEW PUSHED PREDICATE (cr=8225279 r=86 w=0 time=3350714104 > > us) > > 13525 HASH JOIN (cr=8225279 r=86 w=0 time=3350387793 us) > > 13525 NESTED LOOPS (cr=83228 r=86 w=0 time=1409089 us) > > 13525 NESTED LOOPS (cr=69701 r=84 w=0 time=1137279 us) > > 13525 TABLE ACCESS BY INDEX ROWID PR_SIS (cr=56174 r=83 w=0 > > time=872406 us) > > 13525 INDEX UNIQUE SCAN PR_SIS_UNIQUE_TRUNK (cr=42649 r=83 w=0 > > time=605263 us)(object id 51411) > > 13525 TABLE ACCESS BY INDEX ROWID PR_SIS_MAPAUCOLLEGES > > (cr=13527 r=1 w=0 time=133996 us) > > 13525 INDEX UNIQUE SCAN PR_SIS_MAPAUCOLLEGES_PK (cr=2 r=1 w=0 > > time=57327 us)(object id 51415) > > 13525 TABLE ACCESS BY INDEX ROWID PR_SIS_COLLEGES (cr=13527 r=2 > > w=0 time=156242 us) > > 13525 INDEX UNIQUE SCAN PR_SIS_COLLEGES_PK (cr=2 r=1 w=0 > > time=49119 us)(object id 51413) > > 182925625 VIEW (cr=8142051 r=0 w=0 time=95258575093 us) > > 182925625 UNION-ALL (cr=8142051 r=0 w=0 time=88155847789 us) > > 0 NESTED LOOPS (cr=0 r=0 w=0 time=136686 us) > > 0 NESTED LOOPS (cr=0 r=0 w=0 time=124965 us) > > 0 VIEW (cr=0 r=0 w=0 time=113787 us) > > 0 UNION-ALL (cr=0 r=0 w=0 time=82412 us) > > 0 FILTER (cr=0 r=0 w=0 time=10069 us) > > 0 HASH JOIN (cr=0 r=0 w=0 time=0 us) > > 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=0 r=0 w=0 > > time=0 us) > > 0 TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) > > 0 FILTER (cr=0 r=0 w=0 time=6060 us) > > 0 HASH JOIN (cr=0 r=0 w=0 time=0 us) > > 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=0 r=0 w=0 > > time=0 us) > > 0 TABLE ACCESS FULL PR_MED (cr=0 r=0 w=0 time=0 us) > > 0 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 > > time=0 us)(object id 51357) > > 0 TABLE ACCESS BY INDEX ROWID PR_ADMIN_GROUPS (cr=0 r=0 > > w=0 time=0 us) > > 0 INDEX UNIQUE SCAN PR_ADMIN_GROUPS_PK (cr=0 r=0 w=0 > > time=0 us)(object id 51355) > > 182925625 NESTED LOOPS (cr=8101476 r=0 w=0 time=83292738128 us) > > 182925625 HASH JOIN (cr=8087950 r=0 w=0 time=52376113695 us) > > 148775 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=40575 r=0 w=0 > > time=743515 us) > > 182925625 HASH JOIN (cr=8047375 r=0 w=0 time=17012564710 us) > > 182925625 TABLE ACCESS FULL PR_SIS (cr=8006800 r=0 w=0 > > time=6988566170 us) > > 256975 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=40575 r=0 > > w=0 time=1317097 us) > > 182925625 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=13526 r=0 w=0 > > time=16719626449 us)(object id 51357) > > 0 HASH JOIN (cr=40575 r=0 w=0 time=3174900 us) > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=40575 r=0 w=0 > > time=957411 us) > > 0 MERGE 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) > > > > 8i explain plan from another instance not yet upgrade yet. But this > > explain plain exactly same as 9i instance before upgrade. > > > > Rows Row Source Operation > > ------- --------------------------------------------------- > > 1 LOAD AS SELECT > > 21662 HASH JOIN OUTER > > 21662 HASH JOIN OUTER > > 21662 HASH JOIN OUTER > > 21662 HASH JOIN OUTER > > 21662 TABLE ACCESS FULL PR_IDENTITY > > 3810 VIEW > > 3810 HASH JOIN > > 28 TABLE ACCESS FULL PR_DEPARTMENTS > > 3810 HASH JOIN > > 3810 VIEW PR_ADMINS > > 3810 UNION-ALL > > 3810 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 3810 NESTED LOOPS > > 3811 VIEW > > 3811 UNION-ALL > > 1 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_HR > > 3811 HASH JOIN > > 28 TABLE ACCESS FULL PR_DEPARTMENTS > > 3810 TABLE ACCESS FULL PR_MED > > 3810 INDEX UNIQUE SCAN (object id 41938) > > 0 NESTED LOOPS > > 13790 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 13789 HASH JOIN > > 13789 TABLE ACCESS FULL PR_SIS > > 19 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES > > 0 INDEX UNIQUE SCAN (object id 41938) > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 0 MERGE JOIN CARTESIAN > > 0 TABLE ACCESS FULL PR_AFFILIATE > > 0 SORT JOIN > > 0 INDEX FULL SCAN (object id 41938) > > 3810 TABLE ACCESS FULL PR_MED > > 4221 VIEW > > 4221 HASH JOIN > > 4221 VIEW PR_ADMINS > > 4221 UNION-ALL > > 4221 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 4221 NESTED LOOPS > > 4222 VIEW > > 4222 UNION-ALL > > 4222 HASH JOIN > > 220 TABLE ACCESS FULL PR_DEPARTMENTS > > 4221 TABLE ACCESS FULL PR_HR > > 1 FILTER > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_MED > > 4221 INDEX UNIQUE SCAN (object id 41938) > > 0 NESTED LOOPS > > 1 HASH JOIN > > 12 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 13789 HASH JOIN > > 13789 TABLE ACCESS FULL PR_SIS > > 19 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES > > 0 INDEX UNIQUE SCAN (object id 41938) > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 0 MERGE JOIN CARTESIAN > > 0 TABLE ACCESS FULL PR_AFFILIATE > > 0 SORT JOIN > > 0 INDEX FULL SCAN (object id 41938) > > 4221 HASH JOIN > > 220 TABLE ACCESS FULL PR_DEPARTMENTS > > 4221 TABLE ACCESS FULL PR_HR > > 13789 VIEW > > 13789 HASH JOIN > > 24 TABLE ACCESS FULL PR_SIS_COLLEGES > > 13789 HASH JOIN > > 19 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES > > 13789 HASH JOIN > > 13789 VIEW PR_ADMINS > > 13789 UNION-ALL > > 0 HASH JOIN > > 0 NESTED LOOPS > > 1 VIEW > > 1 UNION-ALL > > 1 FILTER > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_HR > > 1 FILTER > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_MED > > 0 INDEX UNIQUE SCAN (object id 41938) > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 13789 NESTED LOOPS > > 13790 HASH JOIN > > 11 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 13789 HASH JOIN > > 13789 TABLE ACCESS FULL PR_SIS > > 19 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES > > 13789 INDEX UNIQUE SCAN (object id 41938) > > 0 HASH JOIN > > 0 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 0 MERGE JOIN CARTESIAN > > 0 TABLE ACCESS FULL PR_AFFILIATE > > 0 SORT JOIN > > 0 INDEX FULL SCAN (object id 41938) > > 13789 TABLE ACCESS FULL PR_SIS > > 496 VIEW > > 496 HASH JOIN > > 496 HASH JOIN OUTER > > 496 TABLE ACCESS FULL PR_AFFILIATE > > 248 TABLE ACCESS FULL PR_DEPARTMENTS > > 496 VIEW PR_ADMINS > > 496 UNION-ALL > > 0 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 0 NESTED LOOPS > > 1 VIEW > > 1 UNION-ALL > > 1 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_HR > > 1 HASH JOIN > > 0 TABLE ACCESS FULL PR_DEPARTMENTS > > 0 TABLE ACCESS FULL PR_MED > > 0 INDEX UNIQUE SCAN (object id 41938) > > 0 NESTED LOOPS > > 13790 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 13789 HASH JOIN > > 13789 TABLE ACCESS FULL PR_SIS > > 19 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES > > 0 INDEX UNIQUE SCAN (object id 41938) > > 496 HASH JOIN > > 24 TABLE ACCESS FULL PR_ADMIN_GROUPS > > 1488 MERGE JOIN CARTESIAN > > 497 TABLE ACCESS FULL PR_AFFILIATE > > 1488 SORT JOIN > > 3 INDEX FULL SCAN (object id 41938) > > > > Tanel Poder wrote: > > > > > > 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" <ORACLE-L_at_fatcity.com> > > > 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 <jlanders_at_convergys.com> 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: kirtikumar_deshpande_at_yahoo.com > > > > > > > > > > > > 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: ListGuru_at_fatcity.com (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: mladen_at_wangtrading.com > > > > > > > > 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: ListGuru_at_fatcity.com (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: Tanel Poder > > > INET: tanel.poder.003_at_mail.ee > > > > > > 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: ListGuru_at_fatcity.com (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: joan.hsieh_at_tufts.edu > > > > 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: ListGuru_at_fatcity.com (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: <Govind.Arumugam_at_alltel.com > > INET: Govind.Arumugam_at_alltel.com > > > > 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: ListGuru_at_fatcity.com (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: joan.hsieh_at_tufts.edu > > 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: ListGuru_at_fatcity.com (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: <Govind.Arumugam_at_alltel.com > INET: Govind.Arumugam_at_alltel.com > > 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: ListGuru_at_fatcity.com (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: joan.hsieh_at_tufts.edu 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: ListGuru_at_fatcity.com (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).Received on Thu Oct 02 2003 - 13:39:39 CDT
 |
 |