RE: Why a rebuild speeds up my queries.
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 10:44:10 -0400
Dave,
Hasve you considered a little known feature called "minimize rows
per block"? First copy all the existing data off to a temporary=20
table using CTAS. Now, mock up some data that's "fully populated",
and see how many rows fit into a block. Now, once you've got that,
"ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;". That will
guarantee that no more than that number of rows will ever go=20
into any single block on your table. Now, delete the mocked up data,
and copy your data back in from the temporary table. Now, no block
in your table will ever attempt to hold more than that number of blocks.
You could set PCTFREE to 0, and it wouldn't make a difference. Oracle
will not store more than that number of rows per block.
Yes, the minimize records per block feature was originally intended
for use w/ bitmap indexes, but it can be used in this was as well.
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David Sharples
Sent: Wednesday, September 01, 2004 9:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why a rebuild speeds up my queries.
As an extra too this, here is a table we rebuilt
ACCOUNT_ID NOT NULL VARCHAR2(35)
ACCOUNT_TYPE NOT NULL NUMBER(3)
CALL_DIVERT_ABS_FEAT_TS NUMBER(11)
CALL_DIVERT_ABS_FEAT_1H NUMBER(7)
CALL_DIVERT_ABS_FEAT_4H NUMBER(7)
CALL_DIVERT_ABS_FEAT_8H NUMBER(7)
CALL_DIVERT_ABS_FEAT_24H NUMBER(7)
CALL_DIVERT_ABS_FEAT_1W NUMBER(7)
THREE_WAY_CALL_ABS_FEAT_TS NUMBER(11)
THREE_WAY_CALL_ABS_FEAT_1H NUMBER(7)
THREE_WAY_CALL_ABS_FEAT_4H NUMBER(7)
THREE_WAY_CALL_ABS_FEAT_8H NUMBER(7)
THREE_WAY_CALL_ABS_FEAT_24H NUMBER(7)
THREE_WAY_CALL_ABS_FEAT_1W NUMBER(7)
EXP_CALL_XFER_ABS_FEAT_TS NUMBER(11)
EXP_CALL_XFER_ABS_FEAT_1H NUMBER(7)
EXP_CALL_XFER_ABS_FEAT_4H NUMBER(7)
EXP_CALL_XFER_ABS_FEAT_8H NUMBER(7)
EXP_CALL_XFER_ABS_FEAT_24H NUMBER(7)
EXP_CALL_XFER_ABS_FEAT_1W NUMBER(7)
CONF_CALL_ABS_FEAT_TS NUMBER(11)
CONF_CALL_ABS_FEAT_1H NUMBER(7)
CONF_CALL_ABS_FEAT_4H NUMBER(7)
CONF_CALL_ABS_FEAT_8H NUMBER(7)
CONF_CALL_ABS_FEAT_24H NUMBER(7)
CONF_CALL_ABS_FEAT_1W NUMBER(7)
ADVICE_CHARGE_ABS_FEAT_TS NUMBER(11)
ADVICE_CHARGE_ABS_FEAT_1H NUMBER(7)
ADVICE_CHARGE_ABS_FEAT_4H NUMBER(7)
ADVICE_CHARGE_ABS_FEAT_8H NUMBER(7)
ADVICE_CHARGE_ABS_FEAT_24H NUMBER(7)
ADVICE_CHARGE_ABS_FEAT_1W NUMBER(7)
REVERSE_CHARGE_ABS_FEAT_TS NUMBER(11)
REVERSE_CHARGE_ABS_FEAT_1H NUMBER(7)
REVERSE_CHARGE_ABS_FEAT_4H NUMBER(7)
REVERSE_CHARGE_ABS_FEAT_8H NUMBER(7)
REVERSE_CHARGE_ABS_FEAT_24H NUMBER(7)
REVERSE_CHARGE_ABS_FEAT_1W NUMBER(7)
MESS_SERV_ABS_FEAT_TS NUMBER(11)
MESS_SERV_ABS_FEAT_1H NUMBER(7)
MESS_SERV_ABS_FEAT_4H NUMBER(7)
MESS_SERV_ABS_FEAT_8H NUMBER(7)
MESS_SERV_ABS_FEAT_24H NUMBER(7)
MESS_SERV_ABS_FEAT_1W NUMBER(7)
VOICE_MAIL_ABS_FEAT_TS NUMBER(11)
VOICE_MAIL_ABS_FEAT_1H NUMBER(7)
VOICE_MAIL_ABS_FEAT_4H NUMBER(7)
VOICE_MAIL_ABS_FEAT_8H NUMBER(7)
VOICE_MAIL_ABS_FEAT_24H NUMBER(7)
VOICE_MAIL_ABS_FEAT_1W NUMBER(7)
On account creation, everything is populated with 0 apart from
account_id and account_type
As time goes on the others will get proper values, up to the column
length, so the row can grow massively.
I'm tempted to set pctfree to be like 80,but this just *seems* bad - any
suggestions
Thanks
Dave
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Sharples
Sent: 01 September 2004 13:48
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
-----------------------------------------------------------------
Other related posts:
