Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!nntp2.aus1.giganews.com!nntp.giganews.com!news-hub.cableinet.net!blueyonder!amsnews01.chello.com!news01.chello.se!newsfeed1.bredband.com!bredband!uio.no!newsfeed1.uni2.dk!news1!darth-vader.mobilixnet.dk!10.0.0.1.MISMATCH!newsreader!not-for-mail From: kennethkoenraadt@no-spam.hotmail.com Newsgroups: comp.databases.oracle.server Subject: Re: Suggestions please - Query loading 98% Date: Thu, 10 Oct 2002 18:22:36 GMT Organization: Orange A/S Lines: 102 Message-ID: <3da5c067.1321740@news.mobilixnet.dk> References: NNTP-Posting-Host: ras-10-123.mobilixnet.dk X-Trace: jarjarbinks.mobilixnet.dk 1034274189 97172 212.97.240.123 (10 Oct 2002 18:23:09 GMT) X-Complaints-To: usenet@jarjarbinks.mobilixnet.dk NNTP-Posting-Date: Thu, 10 Oct 2002 18:23:09 +0000 (UTC) X-Newsreader: Forte Free Agent 1.21/32.243 Xref: newsfeed1.easynews.com comp.databases.oracle.server:163762 X-Received-Date: Thu, 10 Oct 2002 11:22:03 MST (news.easynews.com) Hi BN, I see at least 4 things that could explain the high load : 1) There's a (possible huge) sort in your execution plan. Is your sort_area_size high enough or do you experience large disk sorts ? 2) You delete a big part of the rows once a month. That could, under some conditions, make your indexes very inefficient. Try the following select segment_name,bytes from dba_segments where segment_type = 'INDEX' and segment_name in (select index_name from dba_indexes where table_name = 'STORE_MESSAGE'); and note the result. Then rebuild the indexes on STORE_MESSAGE and rerun the query above. If some of the indexes take much less space up now, your indexes are probably quite skewed. The short-term solution is to rebuild the indexes regularly. The long-term to reconsider the table design (i.e. partitioning). 3) There is, AFAIK, no index on the MSG_STATUS column, though it is in the WHERE clause of your subquery. That column begs for a Bitmax index. 4) Since your table is high-volatile : Do you have recent, reliable statistics on it ? - Kenneth Koenraadt On 10 Oct 2002 10:22:07 -0700, vze33dv2@verizon.net (BN) wrote: >Greetings, > >Oracle Database is 8044 runing on IBM RS6000 BOX. 24X7 system OLTP > >I see the following query run many times, and loading the system (cpu and io). > >SELECT MSG_LEN,MSG_NUM >FROM STORE_MESSAGE WHERE MSG_STATUS = 'WAITING' >AND LAST_UPDATE_TIME = (SELECT MIN(LAST_UPDATE_TIME) >FROM STORE_MESSAGE WHERE MSG_STATUS = 'WAITING' ) AND ROWNUM = 1 >/ >Execution Plan >---------------------------------------------------------- > 0 >SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=42) > 1 0 > COUNT (STOPKEY) > 2 1 > TABLE ACCESS (BY INDEX ROWID) OF 'STORE_MESSAGE' (Cost=4 Card=1 Bytes=42) > 3 2 > INDEX (RANGE SCAN) OF 'IND_SM_STORE_MESSAGE' (NON-UNIQUE) (Cost=3 Card=1) > 4 3 > SORT (AGGREGATE) > 5 4 > TABLE ACCESS (FULL) OF 'STORE_MESSAGE' (Cost=1749 Card=23094 Bytes=369504) > > >desc STORE_MESSAGE > Name Null? Type > ------------------------------- -------- ---- > MSG_NUM NOT NULL NUMBER(6) > SERVICE_ID NOT NULL NUMBER(3) > SERVICE_VERSION NOT NULL NUMBER(3) > MSG_STATUS VARCHAR2(10) > FAIL_COUNT NUMBER(2) > LAST_UPDATE_TIME DATE > DATE_CREATED DATE > MSG_LEN NUMBER(5) > FCIF_MSG LONG > >CONSTRAINT_NAME COLUMN_NAME TYPE >----------------------------- ----------------------------- ------ >SYS_C001341 MSG_NUM Check >CK1_STORE_MESSAGE MSG_STATUS Check >SYS_C001342 SERVICE_ID Check >SYS_C001343 SERVICE_VERSION Check >PK_STORE_MESSAGE MSG_NUM PK >FK_STORE_MESSAGE SERVICE_ID FK > SERVICE_VERSION FK >service_id and service_version are indexed. >It is insert and update intensive. >This table has 100K rows. The column msg_status is >MSG_STATUS COUNT(*) >---------- ---------- >FAILED 625 >SUCCEEDED 63686 >WAITING 5029 > >We delete all the succuuded once a month. >I appreciate your suggestions . >Regards & Thanks >BN