RE: Why a rebuild speeds up my queries.
- From: "Steve Adams" <steve.adams@xxxxxxxxxxxx>
- To: "'David Sharples'" <dsharples@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 23:49:35 +1000
Hi Dave,
You may want to consider using the MINIMIZE RECORDS_PER_BLOCK command.
If you have Jonathan Lewis' book, "Practical Oracle 8i", the procedure
is explained from page 64.
In short, what the command is minimizing is the number of bits used
in bitmap indexes to represent each block in the table.
Running the MINIMIZE command does a full table scan of the table,
finds the maximum row directory index found in any block, and saves it in
the data dictionary. Subsequent inserts will not allow any more than that
number of rows in any block. Of course, no bitmap indexes can exist
yet on the table when this command is run.
The idea is to work out the number of mature rows that will fit into
each block. Rebuild the table and insert that number of rows into the
first block. Run the MINIMIZE command. Load (the rest of) the data.
However, if using direct load (including ALTER TABLE MOVE) be aware
of bug 2361741 (fixed in 10g) and work around it by putting an extra
row in the first block before running the MINIMIZE command.
If you follow this procedure PCTFREE should be set to just 1 to ensure
that ITL growth will nevertheless be possible, even in a fully populated
block of mature rows.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Sharples
Sent: Wednesday, 1 September 2004 10:48 PM
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
-----------------------------------------------------------------
Other related posts:
