Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!128.32.206.55!newsfeed.berkeley.edu!ucberkeley!nntp.kreonet.re.kr!news.maxwell.syr.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: dyou98@aol.com (D.Y.) Newsgroups: comp.databases.oracle.server Subject: Re: performance tuning question Date: 5 May 2002 00:15:04 -0700 Organization: http://groups.google.com/ Lines: 90 Message-ID: References: NNTP-Posting-Host: 12.237.78.230 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1020582904 1942 127.0.0.1 (5 May 2002 07:15:04 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 5 May 2002 07:15:04 GMT Xref: easynews comp.databases.oracle.server:145916 X-Received-Date: Sun, 05 May 2002 00:13:26 MST (news.easynews.com) "Vlad G" wrote in message news:... > "D.Y." wrote in message > news:f369a0eb.0205030751.1b58d9d2@posting.google.com... > > Inserts are relatively fast so that rules out archive, redo, CPU, and I/O > > problems, even though I don't think they would've made things this bad. > > But 10-15 minutes to insert 65K records is still slow by most standards. > > > > There are a couple of possibilities, > > 1, Locking: Other sessions could be updating the same table without > committing > > each update. This only affects updates not inserts. > > This is the only one session updating this table. > > > 2, Cardinality: If you only have a few distinct ID values then each update > > will touch a large number of records. That would explain why creating an > > index on this column only gave you 20% performance gain. If that's the > case > > wouldn't your last update for a certain ID value overwrite all the > previous > > ones? > > > > This is not the case, there shouldn't be duplicates on ID, as the matter of > fact ID becomes PK after update completes. > > > Please post your solution. I'd be interested to know. > > > > No solution so far, seems like my index is not being used due to some > reason, this is the only logical explanation i can find. > If only one session is updating this table and ID is a very selective column, you may have some old statistics on the table as someone else suggested. There are a few things you can try: With table populated run this in sqlplus, analyze table my_table compute statistics; If that doesn't work, change your SQL statements to select /*+ index(my_table ) */ .... (use your temporary index here) update /*+ index(my_table ) */ ... If you want to save some typing, select /* +rule */ ... and update /* +rule */ will achieve the same results. But this could be deprecated in future releases. > Thanks, > Vlad. > > > "Vlad G" wrote in message > news:... > > > Hi there, > > > > > > I'm looking for an advice what configuration parameters I need to look > at > > > first to boost Oracle 8i performance for the following simple scenario. > I'm > > > doing a batch update of a table containing about 65k records. The table > > > structure is rather simple: > > > > > > MY_TABLE: ID, COL1, COL2, COL3 > > > > > > all fields are integers except COL3 which is VARCHAR 2000. There is no > > > primary key set on the table during update. The program is Java and its > > > logic is (in pseudocode): > > > > > > for (each id in update) > > > { > > > if (exists(SELECT * FROM MY_TABLE WHERE ID=id)) > > > UPDATE MY_TABLE SET COL1=val1, COL2=val2, COL3=val3 WHERE ID=id > > > else > > > INSERT INTO MY_TABLE id,val1,val2,val3 > > > } > > > > > > The update is going painfully slow and takes about 6 hours, and as the > > > matter of fact it is about 7 times slower than MS SQL or DB2 on both AIX > and > > > Win2k (can't really tell about MS SQL on AIX ...). This is not a network > > > problem or other environment related issue since load into empty table > using > > > only inserts without selects is pretty fast and takes 10-15 minutes, so > this > > > is full table scans that slow things down. I get 20% performance gain if > > > index is created on ID field which doesn't help a lot. So, what do I > need to > > > look at first? buffers? shared pool? block size? do I need to supply any > > > optimizer hints in my query so the index is actually used? Just want to > get > > > some info before I start poking around... > > > > > > Thanks for any clues. > > > Vladimir.