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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------
