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: Why a rebuild speeds up my queries.

RE: Why a rebuild speeds up my queries.

From: David Sharples <dsharples_at_cerebrussolutions.com>
Date: Wed, 1 Sep 2004 14:31:29 +0100
Message-ID: <EA29A3FCC723674293FD6286D3F0513E8F3E84@louis.cerebrus.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Sharples Sent: 01 September 2004 13:48
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Richard Foote Sent: 01 September 2004 13:22
To: oracle-l_at_freelists.org
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_at_cerebrussolutions.com> To: <oracle-l_at_freelists.org>
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Sep 01 2004 - 08:53:18 CDT

Original text of this message

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