Re: Transaction table- Keep Pool

From: <jaysingh1_at_optonline.net>
Date: Thu, 05 Aug 2004 11:57:53 -0400
Message-id: <3dd5ab53dd8f3b.3dd8f3b3dd5ab5@optonline.net>

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_at_gmail.com> Date: Thursday, August 5, 2004 11:28 am Subject: Re: Transaction table- Keep Pool

> On Thu, 05 Aug 2004 10:31:58 -0400, jaysingh1_at_optonline.net
> <jaysingh1_at_optonline.net> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 05 2004 - 10:54:53 CDT