Re: Transaction table- Keep Pool
- From: jaysingh1@xxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 05 Aug 2004 11:57:53 -0400
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
-----------------------------------------------------------------
Other related posts:
