Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Transaction table- Keep Pool

Re: Transaction table- Keep Pool

From: <jaysingh1_at_optonline.net>
Date: Fri, 06 Aug 2004 09:42:53 -0400
Message-id: <3f1acf73f18e5d.3f18e5d3f1acf7@optonline.net>


Niall

Really appreciate your response.

We have indexes on all predicate columns in the where clause. Explain plan looks nice.

What we are thinking is that it is taking more time for any PIO so it 1) May be index fragmentation
2) is because of Hardware inefficiency ( We use SUN Storage for 8i 2 Node OPS - Raw device)

Folks recommended that moving to EMC will give significan improvement with respect to IO.

> On Thu, 05 Aug 2004 11:49:34 -0400, jaysingh1_at_optonline.net
> <jaysingh1_at_optonline.net> wrote:
> > Sure.
> >
> >
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:920429344869#21857295401638
> I didn't intend to suggest you asked tom, but as usual the FIRST_ROWS
> hint he suggests is a worthwhile suggestion (you only want the first
> 20 rows...).
>
> >
> > 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'
> > and e.userstatusid in ('5')
> > ORDER BY e.LASTUPDATEDATE desc
> > )
> > WHERE ROWNUM <=20
>
> I rewrote this (I hope correctly ) as
>
> select * from(
> select ...
> from
> PROFILEDUSER p,
> EXTENDEDATTRIBUTES e,
> COUNTRIES c
> WHERE p.profileduserid= e.profileduserid AND
> P.business_country_id = c.countryabbrev AND
> p.business_country_id ='GB' AND
> e.userstatusid in ('5') AND
> p.hsbc_user_category in(GIB','HIBM') and
> e.lastupdate >= sysdate - 30
> order by e.lastupdate)
> where rownum <= 20;
>
>
> Oracle doesn't seem to think that there are any useful indices for any
> of your filter criteria.
>
> I'm thinking along the lines of a concatenated index for
> EXTENDEDATTRIBUTES on (profileduserid,userstatusid and lastupdate)
> -
> it sounds like this index might be more generally useful (what user
> had what status when could be a common question, but you would know
> that).
>
> I also wonder how selective hsbc_user_category is, but that sounds
> like the sort of column that might get heavily skewed.
>
> One other comment. I wonder if there is a subtle bug as well. You are
> asking for the oldest 20 records (from the last 30 days) that satisfy
> various criteria. So if you run this query at 9am then you get the
> first 20 records that were last updated after 9am 30 days ago, if you
> run it at 5:30pm you get the first 20 records that were last updated
> after 5:30pm 30 days ago. Do you want this or do you want lastupdate
> to be >= trunc(sysdate) - 30 ?
>
>
>
>
> --
> 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 Fri Aug 06 2004 - 08:40:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US