Hi
I have a query that runs well on 10.2.0.2, but under 10.2.0.3 the plan changes
and I'm not sure why.
As far as I can see the init.ora parameters are the same, and statistics (dbms_stats) are up to date for the objects on both systems. Checking aux_stats$ shows that system stats were gathered a while ago, and are set to the same for both instances.
The hardware spec is the same for both servers too.
The performing query uses a hash join, whilst the poor performance uses a merge c artesian.
GOOD PERFORMANCE
==================
SQL> ;
1 SELECT
2 PART.PART_NO ,
3 ICERPT.RPV_SERVICE_REPAIR.TECHNICIAN
4 FROM
5 PART ,
6 ICERPT.RPV_SERVICE_REPAIR,
7 RPV_EXTERNALS_ANALYSIS,
8 EQUIPMENT
9 WHERE
10 ( ICERPT.RPV_SERVICE_REPAIR.SERIAL_NUMBER (+)= RPV_EXTERNALS_ANALYSIS.SERIAL_NO )
11 AND ( RPV_EXTERNALS_ANALYSIS.ID_EQUIPMENT = EQUIPMENT.ID_EQUIPMENT )
12* AND ( EQUIPMENT.FK_PART = PART.ID_PART )
Execution Plan
----------------------------------------------------------
Plan hash value: 3167271130
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 276K| 17M| | 6734 (2)| 00:01:21 |
|* 1 | HASH JOIN | | 276K| 17M| | 6734 (2)| 00:01:21 |
| 2 | VIEW | index$_join$_001 | 12786 | 162K| | 84 (2)| 00:00:02 |
|* 3 | HASH JOIN | | | | | | |
| 4 | INDEX FAST FULL SCAN| AK_PART | 12786 | 162K| | 49 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| PK_PART | 12786 | 162K| | 34 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 276K| 13M| 7496K| 6645 (2)| 00:01:20 |
| 7 | TABLE ACCESS FULL | MV_SERVICE_REPAIR_1 | 219K| 4923K| | 3655 (1)| 00:00:44 |
|* 8 | HASH JOIN | | 276K| 8095K| 8072K| 2065 (3)| 00:00:25 |
| 9 | TABLE ACCESS FULL | MV_EXTERNALS_ANALYSIS_2 | 275K| 4841K| | 461 (2)| 00:00:06 |
| 10 | INDEX FAST FULL SCAN| X_EQ_ID4PART4MODEL | 612K| 7177K| | 494 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EQUIPMENT"."FK_PART"="PART"."ID_PART")
3 - access(ROWID=ROWID)
6 - access("SERIAL_NUMBER"(+)="SERIAL_NO")
8 - access("ID_EQUIPMENT"="EQUIPMENT"."ID_EQUIPMENT")
POOR PERFORMANCE
==================
SQL> ;
1 SELECT
2 PART.PART_NO ,
3 ICERPT.RPV_SERVICE_REPAIR.TECHNICIAN
4 FROM
5 PART ,
6 ICERPT.RPV_SERVICE_REPAIR,
7 RPV_EXTERNALS_ANALYSIS,
8 EQUIPMENT
9 WHERE
10 ( ICERPT.RPV_SERVICE_REPAIR.SERIAL_NUMBER (+)= RPV_EXTERNALS_ANALYSIS.SERIAL_NO )
11 AND ( RPV_EXTERNALS_ANALYSIS.ID_EQUIPMENT = EQUIPMENT.ID_EQUIPMENT )
12* AND ( EQUIPMENT.FK_PART = PART.ID_PART )
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 390K| 24M| | 18E (1)| |
|* 1 | HASH JOIN RIGHT OUTER | | 390K| 24M| 6888K| 18E (1)| |
| 2 | TABLE ACCESS FULL | MV_SERVICE_REPAIR_2 | 201K| 4522K| | 3214 (1)| 00:00:39 |
|* 3 | HASH JOIN | | 390K| 15M| 13M| 18E (1)| |
| 4 | INDEX FAST FULL SCAN | X_EQ_ID4PART4MODEL | 608K| 7126K| | 490 (3)| 00:00:06 |
| 5 | MERGE JOIN CARTESIAN | | 5154M| 144G| | 18E (1)| |
| 6 | TABLE ACCESS FULL | MV_EXTERNALS_ANALYSIS_1 | 390K| 6489K| | 643 (2)| 00:00:08 |
| 7 | BUFFER SORT | | 13187 | 167K| | 18E (1)| |
| 8 | TABLE ACCESS BY INDEX ROWID | PART | 13187 | 167K| | 18E (1)| |
| 9 | BITMAP CONVERSION TO ROWIDS| | | | | | |
| 10 | BITMAP INDEX FULL SCAN | X_PT_OWNED_BY | | | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SERIAL_NUMBER"(+)="SERIAL_NO")
3 - access("ID_EQUIPMENT"="EQUIPMENT"."ID_EQUIPMENT" AND "EQUIPMENT"."FK_PART"="PART"."ID_PART")
RELEVANT INIT.ORA for BOTH
=======================
compatible=10.2.0
db_block_checksum=FALSE
db_block_size=8192
db_file_multiblock_read_count=16
fast_start_mttr_target=300
hash_area_size=131072
open_cursors=5000
optimizer_index_caching = 0
optimizer_secure_view_merging = FALSE
pga_aggregate_target=1G
processes=100
query_rewrite_enabled=FALSE
sga_target=1G
star_transformation_enabled=FALSE
RAW TRACE FILE INFO
===================
Good performance
PARSING IN CURSOR #1 len=429 dep=0 uid=0 oct=3 lid=0 tim=3330749294122 hv=1615984331 ad='93a4bc8'
SELECT
ice.PART . PART_NO ,
ICERPT . RPV_SERVICE_REPAIR . TECHNICIAN
FROM
ice.PART ,
ICERPT . RPV_SERVICE_REPAIR ,
icerpt.RPV_EXTERNALS_ANALYSIS ,
ice.EQUIPMENT
WHERE
( ICERPT . RPV_SERVICE_REPAIR . SERIAL_NUMBER (+)= icerpt.RPV_EXTERNALS_ANALYSIS . SERIAL_NO )
AND ( icerpt.RPV_EXTERNALS_ANALYSIS . ID_EQUIPMENT = ice.EQUIPMENT . ID_EQUIPMENT )
AND ( ice.EQUIPMENT . FK_PART = ice.PART . ID_PART )
END OF STMT
PARSE #1:c=50000,e=54313,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=3330749294113
BINDS #1:
..
..
STAT #1 id=1 cnt=11731 pid=0 pos=1 obj=0 op='HASH JOIN (cr=35141 pr=17536 pw=0 time=17149202 us)'
STAT #1 id=2 cnt=12800 pid=1 pos=1 obj=0 op='VIEW index$_join$_001 (cr=77 pr=33 pw=0 time=153260 us)'
STAT #1 id=3 cnt=12800 pid=2 pos=1 obj=0 op='HASH JOIN (cr=77 pr=33 pw=0 time=153249 us)'
STAT #1 id=4 cnt=12800 pid=3 pos=1 obj=24522 op='INDEX FAST FULL SCAN AK_PART (cr=43 pr=28 pw=0 time=58774 us)'
STAT #1 id=5 cnt=12800 pid=3 pos=2 obj=24521 op='INDEX FAST FULL SCAN PK_PART (cr=34 pr=5 pw=0 time=52724 us)'
STAT #1 id=6 cnt=11731 pid=1 pos=2 obj=0 op='HASH JOIN RIGHT OUTER (cr=35064 pr=17503 pw=0 time=16950543 us)'
STAT #1 id=7 cnt=219191 pid=6 pos=1 obj=196259 op='TABLE ACCESS FULL MV_SERVICE_REPAIR_2 (cr=32174 pr=16618 pw=0 time=14725668 us)'
STAT #1 id=8 cnt=9083 pid=6 pos=2 obj=0 op='HASH JOIN (cr=2890 pr=885 pw=0 time=1491154 us)'
STAT #1 id=9 cnt=276642 pid=8 pos=1 obj=134665 op='TABLE ACCESS FULL MV_EXTERNALS_ANALYSIS_1 (cr=2073 pr=854 pw=0 time=836535 us)'
STAT #1 id=10 cnt=9084 pid=8 pos=2 obj=30543 op='INDEX FAST FULL SCAN X_EQ_ID4PART4MODEL (cr=817 pr=31 pw=0 time=117675 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=30543 tim=3330772740888
WAIT #0: nam='SQL*Net message from client' ela= 5329797 driver id=1650815232 #bytes=1 p3=0 obj#=30543 tim=3330778070750
XCTEND rlbk=0, rd_only=1
Poor Performance
==============
PARSING IN CURSOR #6 len=429 dep=0 uid=0 oct=3 lid=0 tim=256657748180 hv=1615984331 ad='820a71f8'
SELECT
ice.PART . PART_NO ,
ICERPT . RPV_SERVICE_REPAIR . TECHNICIAN
FROM
ice.PART ,
ICERPT . RPV_SERVICE_REPAIR ,
icerpt.RPV_EXTERNALS_ANALYSIS ,
ice.EQUIPMENT
WHERE
( ICERPT . RPV_SERVICE_REPAIR . SERIAL_NUMBER (+)= icerpt.RPV_EXTERNALS_ANALYSIS . SERIAL_NO )
AND ( icerpt.RPV_EXTERNALS_ANALYSIS . ID_EQUIPMENT = ice.EQUIPMENT . ID_EQUIPMENT )
AND ( ice.EQUIPMENT . FK_PART = ice.PART . ID_PART )
..
..
..
XCTEND rlbk=0, rd_only=1
STAT #6 id=1 cnt=1092 pid=0 pos=1 obj=0 op='HASH JOIN RIGHT OUTER (cr=22565 pr=16694 pw=0 time=40554760 us)'
STAT #6 id=2 cnt=201366 pid=1 pos=1 obj=156360 op='TABLE ACCESS FULL MV_SERVICE_REPAIR_2 (cr=14575 pr=14564 pw=0 time=7692962 us)'
STAT #6 id=3 cnt=1091 pid=1 pos=2 obj=0 op='HASH JOIN (cr=7990 pr=2130 pw=0 time=32368250 us)'
STAT #6 id=4 cnt=608117 pid=3 pos=1 obj=30543 op='INDEX FAST FULL SCAN X_EQ_ID4PART4MODEL (cr=2133 pr=2119 pw=0 time=1237405 us)'
STAT #6 id=5 cnt=14389915 pid=3 pos=2 obj=0 op='MERGE JOIN CARTESIAN (cr=5857 pr=11 pw=0 time=14528723 us)'
STAT #6 id=6 cnt=1092 pid=5 pos=1 obj=134666 op='TABLE ACCESS FULL MV_EXTERNALS_ANALYSIS_2 (cr=83 pr=3 pw=0 time=17231 us)'
STAT #6 id=7 cnt=14389915 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=5774 pr=8 pw=0 time=133714 us)'
STAT #6 id=8 cnt=13187 pid=7 pos=1 obj=24520 op='TABLE ACCESS BY INDEX ROWID PART (cr=5774 pr=8 pw=0 time=105611 us)'
STAT #6 id=9 cnt=13187 pid=8 pos=1 obj=0 op='BITMAP CONVERSION TO ROWIDS (cr=1 pr=0 pw=0 time=76 us)'
STAT #6 id=10 cnt=2 pid=9 pos=1 obj=194341 op='BITMAP INDEX FULL SCAN X_PT_I_POPULAR (cr=1 pr=0 pw=0 time=70 us)'
Anyone able to help ?
Many thanks