Home » RDBMS Server » Performance Tuning » Table Corruption? Is there as such?
Table Corruption? Is there as such? [message #65114] Tue, 04 May 2004 18:58 Go to next message
Cocoy
Messages: 4
Registered: April 2004
Junior Member
hi,

What could cause a table corruption? in the first place, is there such state? If yes, what are the symptoms?

recently, the DB performance went down. The usual processing time of my script was at 7 mins doing INSERT to 4 tables as some processing to other temp tables went up to 20+ mins. the table size for these 4 tables went up to as much as 3.8 million rows on the average.

we did some analysis and found the table and index to its normal state.

to solve the slowness, we did a reindexing. performance increased for quite sometime but went back to slow state.

we finally decided to do DROP TABLE and recreate it and reload the data and reindex and slowness was gone.

question: What could be the possible causes of such slowness to table access (using transaction SQL statement)? A simple SQL of "Select Count(*) from Table_X" yields result after 2 mins on an 800k records.

thanks and more power!
Re: Table Corruption? Is there as such? [message #65116 is a reply to message #65114] Thu, 06 May 2004 03:51 Go to previous messageGo to next message
Sujit Sarkar
Messages: 40
Registered: September 2003
Member
hi,
perhaps your table had lot many deleted rows (somuch of unused space below high water mark).
We know that delete doesn't reset the high water mark and the deleted rows space was not reclaimed.
we know that whenever we issue a query against such table which has high amount of used space below high water mark then such query takes long time even if it contains less no. of rows.
In your case when you dropped the table and recreted the structure and data, the unused space was reclaimed and slowness was gone.

Thanx & rgrds

Sujit
Re: Table Corruption? Is there as such? [message #65118 is a reply to message #65116] Thu, 06 May 2004 20:06 Go to previous messageGo to next message
JamJam
Messages: 2
Registered: May 2004
Junior Member
Hi sujit,

thanks for the information. what is then the preventive maintenance to reclaim those watermarked rows? recreation of table is not an option here.

thanks!
Re: Table Corruption? Is there as such? [message #65119 is a reply to message #65118] Thu, 06 May 2004 21:11 Go to previous messageGo to next message
Sujit Sarkar
Messages: 40
Registered: September 2003
Member
Hi,
you can reclaim the unused space below high water mark by two ways.
1. You can move the table into a different tablespace by the command
alter table
move tablespace

2. export the table and all dependencies then drop the table and import the table again.

all the best
Hope this would work

Sujit
Re: Table Corruption? Is there as such? [message #65120 is a reply to message #65119] Thu, 06 May 2004 22:04 Go to previous messageGo to next message
JamJam
Messages: 2
Registered: May 2004
Junior Member
hi sujit,

how often should the two option be done? is there a way to avoid such maintenance? in pursuing option 1, how often should i do this? periodically?

thanks alot!
Re: Table Corruption? Is there as such? [message #65135 is a reply to message #65114] Fri, 14 May 2004 10:32 Go to previous messageGo to next message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
When We talk about corruption it is about the
data block .

You will get a error message with file id and the block number , with that you can look for segement/Object and take appropriate action .

-- Data Block Corruption

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <AFN>
and <BL> between block_id AND block_id + blocks - 1

BL - Block number
AFN - File id

it could be table , index , temporray segemnt ....

The other thing is max extnent error related to Table .
When the allocated space for table is full .

Youc an find that out with

-- Objects Reaching to their max extents

select ds.owner||' '||ds.segment_name||' '||ds.segment_Type,ds.extents AS Extents_Filled
from dba_segments ds
where ds.segment_type in ('TABLE','INDEX')
and ds.owner not in('SYS','SYSTEM')
and ( ds.max_extents - ds.extents ) < 100
order by 2

In this you will have to table export of the table ,drop the table , recreate it with assigning more space and then import the data

The corruption could cause beacuse of inert/delete and it happens quiet sometime .... that's why DBA are assigned to deal with .

The slowness could be we don't analyze the tables to keep the statistics upto date .

-- for schema
execute dbms_utility.analyze_schema('USERNAME','ESTIMATE');

analyze table table_name compute statistics ;

Do this regular basis .

executing select count(*) from table_name is not a good idea .

Analyze the table and then execute

select table_name,num_rows from user_table where table_name = 'T'

You can experience the slowness if you ahve database and application same machine .
Re: Table Corruption? Is there as such? [message #65304 is a reply to message #65114] Tue, 27 July 2004 14:45 Go to previous message
Master P
Messages: 6
Registered: July 2004
Junior Member
Are you certain that you had corrupt blocks or was row-resequencing your problem.

Consider a SQL query that retrieves 100 rows using an index:

select
salary
from
employee
where
last_name like 'B%';

This query will traverse the last_name_index, selecting each row to obtain the rows. This query will have at least 100 physical disk reads because the employee rows reside on different data blocks.

Now let's examine the same query where the rows are re-sequenced into the same order as the last_name_index. We see that the query can read all 100 employees with only three disk I/Os (one for the index, and two for the data blocks), resulting in a saving of over 97 block reads.

The degree to which resequencing improves performance depends on how far out of sequence the rows are when you begin and how many rows you will be accessing in sequence. You can find out how well a table's rows match the index's sequence key by looking at the dba_indexes and dba_tables views in the data dictionary.

In the dba_indexes view, we look at the clustering_factor column. If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.
Previous Topic: Database slow down at particular time
Next Topic: filter in explain plan
Goto Forum:
  


Current Time: Thu Mar 28 12:18:57 CDT 2024