RE: Why a rebuild speeds up my queries.
- From: "David Sharples" <dsharples@xxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 14:38:35 +0100
Hi Riyaj,
I think you got the number the other way round,
The table scan row gotten went down to 9M from 39M, table scans (short
tabkes)went down as well (the other one you are correct with).
I know the explain plans are the same because I looked with 10046 -
there are no small tables in the queries, all tables involved have
around 6million rows.
We are using bind variables through oci, e.g.
UPDATE fld_feature_buckets SET call_divert_abs_feat_ts =3D :i_call
_divert_abs_feat_ts, call_divert_abs_feat_1h =3D :i_call_divert_ab
s_feat_1h, call_divert_abs_feat_4h =3D :i_call_divert_abs_feat_4h
Thanks!
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Riyaj Shamsudeen
Sent: 01 September 2004 14:17
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why a rebuild speeds up my queries.
David
In addition to Richard's comments: Between before and after
rebuilds, table scans(short tables) have gone up from 783 to 3285,
whereas table fetch by rowid has gone down from 4.4M to 3.9M. Table scan
rows gotten also has gone up from 9M to 39M. I think, your process,
after the table rebuild is scanning at least one smaller table using
FTS too. I guess, only 10046 event trace will identify this correctly.
BTW, are these SQLs use bind variables or are they literal variables ?
PS: Not sure, how you are looking at the explain plan, but in few cases,
sqlplus autotrace and sqltrace explain plans do not match.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Sharples
Sent: Wednesday, September 01, 2004 7:48 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why a rebuild speeds up my queries.
Hi Richard thanks for the snippet about dbms_stats, yes we do use that.
Next time I will use analyze and see what that is.
As for pctfree, the tables are set to the default of 10. I think this
is the problem because the rows start of as 'skinny' ones with all
values being 0, then they get updated constantly with true values,
numbers up to 10 digits, varchar2 up to 20, so they then become fat and
must migrate.
Do you know of a good link where it tells me how to set pctfree
properly?
Oracle docs are good but I find them to tell me more what it is about
that what it should be.
Thanks very much
Dave
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Richard Foote
Sent: 01 September 2004 13:22
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Why a rebuild speeds up my queries.
Hi David,
Your "table fetch continued row" count has certainly plummeted so
something's changed there.
When you say dba_tables shows nothing, you don't by any chance use
dbms_stats to analyze as chain_cnt is only calculated with the "old"
analyze command (else you just get a 0).
In which case, yes, your pctfree is buggered.
Just a thought.
Cheers
Richard
----- Original Message -----
From: "David Sharples" <dsharples@xxxxxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, September 01, 2004 9:21 PM
Subject: Why a rebuild speeds up my queries.
Hi,
I have a process that overtime slowly gets slower and slower (execution
plans are the same)
A rebuild of the table / index fixes this and makes it go quick again. I
know that we shouldn't need to rebuild things, so I need to know what is
wrong in my setup which is causing this.
The setup is this: oracle 9.2.0.4 on Solaris
Running queries against hashed partitioned table which never get deleted
them, they only get inserted into and then updated a fair amount.
We think it is due to row migration / chained rows but chain count from
dba_tables showed nothing after an analyze.
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
-- Attached file included as plaintext by Ecartis --
-- Desc: Signature
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any
review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
