Xref: alice comp.databases.oracle.server:47812 Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!dispose.news.demon.net!demon!grolier!freenix!wanadoo.fr!not-for-mail From: "Emmanuel Bontemps" Newsgroups: comp.databases.oracle.server Subject: Full scan perf. problem after mig Oracle 7.3.4/8.0.5 Date: Thu, 29 Apr 1999 22:18:40 +0200 Organization: Wanadoo, l'internet avec France Telecom Message-ID: <7gbm7o$ssn$1@wanadoo.fr> X-Trace: wanadoo.fr 925457464 29591 164.138.107.115 (30 Apr 1999 07:31:04 GMT) X-Complaints-To: abuse@wanadoo.fr NNTP-Posting-Date: 30 Apr 1999 07:31:04 GMT X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Lines: 27 Hi, The database work under NT4. Before I migrate it I have meseared the time for full scan (count(*)) on 2 big tables. With 7.3.4 database the time was 58 secondes for table 1, 22 secondes for table2. After migrate with mig80, the time is 137 secondes for table1 and 54 secondes for table2. More than two times slower. An insert stored procedure (a loop on an insert statement) is two time slower with Oracle 8 (2300 record/secons . Anybody have an idea ? Any idea will be welcome. Consider the folowing information : - Optimizer mode was choose on 7.3.4. But they wasn't any statistics on the database. Explicitly optimizer rule with Oracle 8, that solve performance problem with indexed nested loop statements. - init.ora exactly the same between the two version, except, compatible=8.0.4 - I have not measered the sum of physical/logical reads in 7.3.4 - table 1 contain about 8,300,000 small rows (700 Mo), table 2 contain 735,000 bigger records (600 Mo).