RE: Why a rebuild speeds up my queries.
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 12:41:31 -0500
I think Steve Adams's suggestion (Jonathan's book p64) is a superb way to
begin.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 9/20 Hartford, 10/18 New Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Sharples
Sent: Wednesday, September 01, 2004 9:49 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why a rebuild speeds up my queries.
Thanks Cary, migrated rows definitely looks the way to go.=20
I can set a good setting if I was starting from scratch, where I didn't
have fat and skinny rows. The problem I have now is setting a good
value for a table with a mixture of rows on my live systems.
So how does this sound, rebuild the table with default setting of 10 or
20 % so the fat rows don't waste much space and the skinny rows have a
bit of room to grow and maybe some may migrate (that's ok)
Then I do alter table xxx pctfree 60; for example - then new rows will
be able to grow as needed leaving old ones unaffected?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap
Sent: 01 September 2004 14:44
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why a rebuild speeds up my queries.
Dave,
I'm of course nervous basing a diagnostic decision on a statspack =3D
report.
Your extended SQL trace data would give you enough information to =3D
determine
whether the extra PIO blocks are caused by migrated rows, and if so, =3D
exactly
how much time you should expect to save by fixing the migrated rows =3D
problem.
Assuming that your problem is a bad PCTFREE setting, now... A good =3D
sanity
check should be easy. You know how big the rows start out. You know how
=3D
big
the rows end up (you can measure that). And you know the definition of
PCTFREE (from the Concepts guide). You ought to be able to compute a =3D
PCTFREE
number that greatly reduces your pain from its present level, even if =
=3D
the
value you choose isn't perfect (either wastes a little space, or still
produces a few migrated rows).
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 9/20 Hartford, 10/18 New Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
