| 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
Sure.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:920429344869#21857295401638
SELECT * FROM (
SELECT e.userstatusid,
P.processed_by,
P.last_name,
P.first_name,
P.company_name,
c.countryname,
e.customerid,
TO_CHAR(e.LASTUPDATEDATE,'dd MON, yy ')||'AT
'||TO_CHAR(e.LASTUPDATEDATE,'hh24:mi ')||'GMT' formattedlastupdatedate,
P.userid,
business_country_id
FROM (SELECT * FROM PROFILEDUSER WHERE hsbc_user_category IN
('GIB','HIBM')) P,
(SELECT customerid,userstatusid,profileduserid,lastupdatedate FROM
EXTENDEDATTRIBUTES WHERE lastupdatedate >= SYSDATE-30) e,
COUNTRIES c
WHERE P.profileduserid= e.profileduserid AND P.business_country_id = c.countryabbrev AND p.business_country_id ='GB'
> 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
> -----------------------------------------------------------------
>
-- 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:47:57 CDT
 |
 |