Sorry I didn't give index definition in my last mail. 1 SELECT table_name,index_name,column_name,column_length 2 FROM DBA_IND_COLUMNS WHERE index_name=(SELECT object_name FROM DBA_OBJECTS WHERE object_id=1010125) ; 3 SELECT table_name,index_name,column_name,column_length 4* FROM DBA_IND_COLUMNS WHERE index_name=(SELECT object_name FROM DBA_OBJECTS WHERE object_id=951554) ; 5 SQL> @x1 TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_LENGTH -------------------- ------------------------------ ------------------------- ------------- PROFILEDUSER PROFILEDUSER_I02 BUSINESS_COUNTRY_ID 2 TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_LENGTH -------------------- ------------------------------ ------------------------- ------------- EXTENDEDATTRIBUTES ATTRIBUTES_PK PROFILEDUSERID 36 SQL> ----- Original Message ----- From: Niall Litchfield <niall.litchfield@xxxxxxxxx> Date: Thursday, August 5, 2004 11:28 am Subject: Re: Transaction table- Keep Pool > On Thu, 05 Aug 2004 10:31:58 -0400, jaysingh1@xxxxxxxxxxxxx > <jaysingh1@xxxxxxxxxxxxx> wrote: > > > > It is taking 30 sec for such a small volume of records. > > > > call count cpu elapsed disk query > current rows > > ------- ------ -------- ---------- ---------- ---------- ------- > --- ---------- > > Parse 1 0.03 0.05 0 0 > 0 0 > > Execute 1 0.00 0.00 0 0 > 0 0 > > Fetch 3 10.71 33.30 6345 19185 > 0 20 > > ------- ------ -------- ---------- ---------- ---------- ------- > --- ---------- > > total 5 10.74 33.35 6345 19185 > 0 20 > > Well its 20000 LIO for your 20 rows (which seems rather high) but > see below > > > > Rows Row Source Operation > > ------- --------------------------------------------------- > > 20 COUNT STOPKEY > > 20 VIEW > > 20 SORT ORDER BY STOPKEY > > 347 MERGE JOIN CARTESIAN > > 348 NESTED LOOPS > > 4884 TABLE ACCESS BY INDEX ROWID PROFILEDUSER > > 4885 INDEX RANGE SCAN (object id 1010125) > > 5230 TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES > > 9766 INDEX UNIQUE SCAN (object id 951554) > > 347 SORT JOIN > > 1 TABLE ACCESS BY INDEX ROWID COUNTRIES > > 2 INDEX RANGE SCAN (object id 1131957) > > > The 20 rows are actually only there because your sql has one of where > rownum <=20 or where rownum <21 in it. That being the case check out > the asktom here > http://asktom.oracle.com/pls/ask/f?p=4950:8:6693556430011788783::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4105951726381, > specifically Vivek's question quite early on. I have to wonder about > the SQL that is being issued here. Any chance of a peek at it? > > > > > > Elapsed times include waiting on following events: > > Event waited on Times Max. Wait > Total Waited > > ---------------------------------------- Waited ---------- > ------------ > > SQL*Net message to client 3 0.00 > 0.00 > > rdbms ipc reply 6051 0.18 > 2.31 > > global cache freelist wait 6206 0.18 > 3.88 > > file open 4 0.00 > 0.00 > > db file sequential read 6345 0.04 > 14.05 > > global cache cr request 3188 0.21 > 4.87 > > latch free 1 0.02 > 0.02 > > SQL*Net message from client 3 0.27 > 0.32 > > SQL*Net more data to client 1 0.00 > 0.00 > > > ******************************************************************************** > High if you sort this list like this > > d_f_seq_read 14.05 > g_c_cr_r 4.87 > g_c_f_w 3.88 > don't care --- the rest. > > I'd say that this strongly suggests (for me anyway) that the index > access paths aren't that efficient. 1/2 your elapsed time is coming > from the disk reads for the indexes and a further 1/3rd from the fact > that this is LIO on OPS. > > lets have a look at the statement (and I the definitions of > PROFILEDUSER and the index with object id 1010125 on it - and of > EXTENDEDATTRIBUTES and the index with object id 951554) > > I'm off home now, but I predict if you post that info back you'll get > a lot of interesting responses. > > -- > Niall Litchfield > Oracle DBA > http://www.niall.litchfield.dial.pipex.com > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------