| Big performance difference between 8i and 9i [message #65105] |
Fri, 30 April 2004 01:41  |
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  |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
