Re: Transaction table- Keep Pool
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 5 Aug 2004 16:28:46 +0100
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
-----------------------------------------------------------------
Other related posts:
