Why a rebuild speeds up my queries.

From: David Sharples <dsharples_at_cerebrussolutions.com>
Date: Wed, 1 Sep 2004 12:21:37 +0100
Message-ID: <EA29A3FCC723674293FD6286D3F0513E8F3E6E@louis.cerebrus.com>

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.

So we ran a statspack snap for the period of the troublesome process, once before a rebuild and once after.

These are the main differences:

Before Rebuild:

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)

Ela Time
-------------------------------------------- ------------ -----------

PX Deq Credit: send blkd                          301,344     167,424
79.49
db file sequential read                         1,370,900      11,840
5.62
PX Deq: Table Q qref                              289,482       9,936
4.72
db file parallel write                              3,214       9,647
4.58
free buffer waits                                   5,364       4,874

2.31

Statistic                                      Total     per Second

per Trans
--------------------------------- ------------------ --------------

table fetch by rowid                       3,866,050        1,159.9
14,812.5
table fetch continued row                    301,956           90.6
1,156.9
table scan blocks gotten                     387,415          116.2
1,484.4
table scan rows gotten                    39,278,682       11,784.8
150,493.0
table scans (direct read)                        669            0.2
2.6
table scans (long tables)                      6,415            1.9
24.6
table scans (rowid ranges)                     6,175            1.9
23.7
table scans (short tables)                     3,285            1.0
12.6
transaction rollbacks                             97            0.0

0.4

After rebuild:

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)

Ela Time
-------------------------------------------- ------------ -----------

PX Deq Credit: send blkd                          203,017      96,311
80.23
db file sequential read                           646,468       5,841
4.87
db file parallel write                              2,762       5,579
4.65
PX Deq: Table Q qref                              300,098       4,874
4.06
free buffer waits                                   2,188       1,977

1.65

Statistic                                      Total     per Second

per Trans
--------------------------------- ------------------ --------------

table fetch by rowid                       4,402,024        2,366.7
19,307.1
table fetch continued row                     15,927            8.6
69.9
table scan blocks gotten                     110,831           59.6
486.1
table scan rows gotten                     9,962,878        5,356.4
43,696.8
table scans (direct read)                        223            0.1
1.0
table scans (long tables)                      8,532            4.6
37.4
table scans (rowid ranges)                     8,532            4.6
37.4
table scans (short tables)                       783            0.4

3.4

The main problems are db file sequential read (but I think that is a symptom, not a cause).

The other area is table scan rows gotten - what can cause the number to change dramatically when the execution plans are the same? Is this row migration? And is this caused by pctfree setting?

Thanks

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 - 06:18:18 CDT