| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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 -----------------------------------------------------------------Received on Thu Aug 05 2004 - 10:25:09 CDT
 |
 |