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: Query Tuning Question

Re: Query Tuning Question

From: <rgaffuri_at_cox.net>
Date: Thu, 12 Jun 2003 13:12:50 -0700
Message-ID: <F001.005B0A63.20030612124123@fatcity.com>


has anythign changed in the table? inserts, updates, deletes? if so considering doing a move on the table to rebuild it and possibly rebuilding the indexes in question.

have you gather statistics lately? Is it using the same plan it was using a fwe weeks ago?

>
> From: "Meng, Dennis" <[EMAIL PROTECTED]>
> Date: 2003/06/12 Thu PM 03:54:59 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Query Tuning Question
>
> Hi fellow DBAs,
> This is kind of the follow-up of my last E-mail on wait event.
> I have a query that is taking hours to complete and the plan looks ok. While one of
> the tables is huge (267mil rows) it is being accessed using one of its indexes.
> I recorded some stats from v$session_wait while the query is running to see which
> segment is query is hanging up on and the result is the big table with 267mil rows.
> Funny thing is, according to the user community, this query took only minutes to run
> couple of weeks ago.
> What could be the cause of this wait? When index is being used, oracle will go
> directly to the data block and retrieve the data, which should be very efficient
> correct?
>
>
> TIA
>
> Dennis
>
>
>
>
>

Hi
fellow DBAs,
This
is kind of the follow-up of my last E-mail on wait event. I have
a query that is taking hours to complete and the plan looks ok. While one of the tables is huge (267mil rows) it is being accessed using one of its indexes.
I
recorded some stats from v$session_wait while the query is running to see which segment is query is hanging up on and the result is the big table with 267mil rows.
Funny
thing is, according to the user community, this query took only minutes to run couple of weeks ago.
What
could be the cause of this wait? When index is being used, oracle will go directly to the data block and retrieve the data, which should be very efficient correct?
<SPAN
class=101284618-12062003> 
<SPAN
class=101284618-12062003> 
<SPAN
class=101284618-12062003>TIA
<SPAN
class=101284618-12062003> 
<SPAN
class=101284618-12062003>Dennis
  Received on Thu Jun 12 2003 - 15:12:50 CDT

Original text of this message

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