Home » RDBMS Server » Performance Tuning » Big performance difference between 8i and 9i
Big performance difference between 8i and 9i [message #65105] Fri, 30 April 2004 01:41 Go to next message
Nicola
Messages: 14
Registered: March 2002
Junior Member
Hello all,

I have the same schema in two instances one 8.1.7.4 other 9.2.0.4.
A query runs forever on 8i and normally fast on 9i.
My question is:
I know that different machines (and even the same machine in different times) can give different optimizer's plans but I need to know which area do I need to investigate to fix this problem on 8i.
The reason is that, if I don't understand the reason of this bad performance, I will not be able to handle it in 9i neither (if it will show up even there in the future, according to Murphy laws..)
I gathered statistics in the *same way* in both schemas
(dbms_stats.gather_table_stats with opt 'for all indexed columns. For several columns I calculated histograms as well)
The machines are different. Both are intel pc, but the 8i one is a bi-processor xion with raid 5 disks and 1 giga of ram, while the 9i is a pentium III with 700 mega ram and normal ide disk.
The query is like this:

SELECT V_TABLE_1.F2, V_TABLE_1.F41, V_TABLE_1.F3,
V_TABLE_1.F4, V_TABLE_1.F1,
V_TABLE_106.F316, V_TABLE_106.F870,
V_TABLE_106.F1064, V_TABLE_106.F1213,
V_TABLE_37.F198, V_TABLE_37.F197,
V_TABLE_37.F1065, V_TABLE_37.F855,
V_TABLE_37.F856, V_TABLE_25.X108,
V_TABLE_1.X17, V_TABLE_13.X85,
To_Date(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy')
To_Date_SYS_CONTEXT
FROM V_TABLE_1 V_TABLE_1, V_TABLE_106 V_TABLE_106
, V_TABLE_37 V_TABLE_37
, V_TABLE_25 V_TABLE_25
, V_TABLE_13 V_TABLE_13
WHERE (V_TABLE_106.F1064 = V_TABLE_1.F2) AND
(V_TABLE_37.F198 = V_TABLE_1.F2) AND
(V_TABLE_25.F102 = V_TABLE_1.F2) AND
(V_TABLE_13.F98 = V_TABLE_1.F2) AND (
( V_TABLE_1.F41 = 'S' )
AND ( To_Date(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') >=
v_table_25.f194 )
AND ( To_Date(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') <=
v_table_25.f106 )
AND ( To_Date(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') >=
v_table_13.f87 )
AND ( To_Date(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') <=
v_table_13.f86 )
)
ORDER BY V_TABLE_106.F1213

I don't post the query plan, since it is huge.
Almost any field used in join have index and/or histograms associated.
The final statistis on 8i is:

Statistics
----------------------------------------------------------
8 recursive calls
110520 db block gets
45307943 consistent gets
101636 physical reads
0 redo size
812686 bytes sent via SQL*Net to client
31476 bytes received via SQL*Net from client
247 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3667 rows processed

Note the *enormous* number of consistent get!

In 9i the *same* query has these

statistics
----------------------------------------------------------
2282 recursive calls
0 db block gets
241132 consistent gets
83 physical reads
0 redo size
505469 bytes sent via SQL*Net to client
3183 bytes received via SQL*Net from client
246 SQL*Net roundtrips to/from client
67 sorts (memory)
0 sorts (disk)
3667 rows processed

Thanks very much for any help/tips on this!!!
Bye
Nicola
Re: Big performance difference between 8i and 9i [message #65123 is a reply to message #65105] Tue, 11 May 2004 01:24 Go to previous messageGo to next message
Epe
Messages: 99
Registered: March 2002
Member
Hi Nicola,

Just some ideas :
I think you have to try to find out why there are so many physical reads on your 8i machine. 101636 times physically accessing a disk compared to 83...
A possible reason can be that the data on your raid5 disks might be highly fragmented, while it can be all cleanly "lined up" on the normal ide disk. Did you take an export from the 8i and import the data in 9i ? If so, Oracle might have get rid of the fragmentation while importing...

Success,
Epe
Re: Big performance difference between 8i and 9i [message #65125 is a reply to message #65123] Tue, 11 May 2004 06:29 Go to previous messageGo to next message
Nicola
Messages: 14
Registered: March 2002
Junior Member
Hi Epe

Yes I simply export from 8i and import in 9i.
Which is the best way to monitor fragmentation?
(I will try some google search on this topic..)
Thanks for the idea
Bye
Re: Big performance difference between 8i and 9i [message #65533 is a reply to message #65105] Tue, 19 October 2004 01:12 Go to previous message
rajkumar
Messages: 9
Registered: February 2002
Junior Member
difference between oracle 8i and 9i
Previous Topic: Bequeth connections and memory usage in 8i
Next Topic: URGENT:-Query taking more than 14 hrs.
Goto Forum:
  


Current Time: Thu Mar 28 14:16:24 CDT 2024