Home » SQL & PL/SQL » SQL & PL/SQL » table corruption?
table corruption? [message #215381] Sun, 21 January 2007 23:20 Go to next message
sajut
Messages: 69
Registered: January 2007
Member
I have a table with 103 and about 150 hundred rows in it. Since yesterday, one of my queries on this table takes too long to execute or not executing at all. Then I copied this table to another table, drop the main table, re created the table and the indexes and now the query is fast.

What could have caused this problem. Was the table or index corrupted? How can I analyze that?
Regards
Re: table corruption? [message #215382 is a reply to message #215381] Sun, 21 January 2007 23:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

I copied this table to another table,
drop the main table,
re created the table and the indexes


Thourgh above procedure you reorganize you table RESET HWM.


regards
Taj
Re: table corruption? [message #215416 is a reply to message #215382] Mon, 22 January 2007 03:49 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
thanks for the reply. and now I understand that I reset the high water mark of the table. But what could have gone for this particular situation
Re: table corruption? [message #215419 is a reply to message #215416] Mon, 22 January 2007 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Generally, performing lots of inserts and deletes on a table with the PCTFREE or PCTUSED set incorrectly will cause the HWM to increase over time.
Re: table corruption? [message #215424 is a reply to message #215419] Mon, 22 January 2007 04:20 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
How can I find other tables which are in the verge of encountering similar problem
Re: table corruption? [message #215453 is a reply to message #215424] Mon, 22 January 2007 06:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT blocks
FROM user_tables
WHERE table_name = 'MY_TABLE'


This will tell you how many blocks are used. Find out the block size by asking the DBA, or typing show parameter block in SQL*Plus - look for db_block_size (I think).

Multiply the two numbers, that's the HWM in bytes.

To work out whether this is reasonable, you can use the VSIZE() function to size individual values in columns.
SELECT sum(vsize(col1)) + sum(vsize(col2)) + ...
from tab

You'll have add in a fudge-factor for PCTFREE and block headers. But if the HWM is more than (say) 50-100% of the VSIZE number, your Full Table Scans will probably be suffering.

Ross Leishman
Re: table corruption? [message #215781 is a reply to message #215453] Tue, 23 January 2007 22:02 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Hi

My database contains about 450 tables. all these tables are with default pctfree values. Since last two or three days, I start experiencing performance problem with some tables or other. What I do is reorganize the table and reset the HWM as i did above. That will solve the problem for 1 day or so and the next table starts giving problem.

How can I fix this problem globally for tables and permanantly
Thanks in advance
Re: table corruption? [message #215784 is a reply to message #215781] Tue, 23 January 2007 22:09 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
If you face performance problem then generate statpack report and findout...or if problem due to table defragmentation the used below method then reorganize your table.

1.create new tablespace.
2.alter table table_name move tablespace_name;
3.alter index index_name rebuild tablespace tablespace_name;

or 
export all tables
drop all tables 
import all tables.


regards
Taj

Re: table corruption? [message #215787 is a reply to message #215381] Tue, 23 January 2007 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I seriously doubt that doing table reorgs is really THE solution.
I stopped doing table reorgs about 6 years ago & have found better things to do with my time.

Sajut,
what is the ratio of INSERTs to DELETEs in your application across the whole collection of 450+ tables?

Which metric at what what value leads you to conclude a reorg is needed or will benefit the application?
Re: table corruption? [message #215831 is a reply to message #215787] Wed, 24 January 2007 01:02 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
dear anacedent
of the 450 tables, 150 are my master tables and there are rarely any inserts/updates/deletion on them and are kept in a different tablespaces. The other 350 tables are my transaction tables. There also only inserts and updates on these tables except for two tables where there inserts and deletes. The ratio of deletion to inserts in these two tables is 1:10
expecting your advice soon
Thanks in advance
Re: table corruption? [message #215985 is a reply to message #215381] Wed, 24 January 2007 09:34 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>There also only inserts and updates on these tables except for two tables where there inserts and deletes.
IMO, a table reorg at best will only help on tables where "heavy" DELETEs occur.
Previous Topic: two users
Next Topic: SQL Hint
Goto Forum:
  


Current Time: Sat Dec 10 10:43:34 CST 2016

Total time taken to generate the page: 0.18486 seconds