Home » RDBMS Server » Performance Tuning » how to find out if a table has dirty blocks in buffers
 how to find out if a table has dirty blocks in buffers [message #136567] |
Thu, 08 September 2005 15:22  |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
I was wondering if there is a way to find out if a table has modified blocks in the buffer cache that have not been flushed to disk. I was able to find out if there are modified buffers but could not come up with a way to be sure what table they were from. This is what I was trying:
SQL> create table try (name char(10)) tablespace users;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select ACTUAL_REDO_BLKS from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
0
SQL> insert into try values ('bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select ACTUAL_REDO_BLKS from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
1
What query can I use and what system tables to confirm that this modified block is from table 'try'?
TIA.
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136642 is a reply to message #136567] |
Fri, 09 September 2005 04:35  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Hi the following article may help:
Oracle provides a number of methods to detect and repair corruption within datafiles:
* DBVerify
* ANALYZE .. VALIDATE STRUCTURE
* DB_BLOCK_CHECKING.
* DBMS_REPAIR.
* Other Repair Methods.
DBVerify
DBVerify is an external utility that allows validation of offline datafiles. In addition to offline datafiles it can be used to check the validity of backup datafiles:
C:>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=100 blocksize=4096
ANALYZE .. VALIDATE STRUCTURE
The ANALYZE command can be used to verify each data block in the analyzed object. If any corruption is detected rows are added to the INVALID_ROWS table:
-- Create the INVALID_ROWS table
SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL
-- Validate the table structure.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;
-- Validate the table structure along with all it's indexes.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;
-- Validate the index structure.
SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE;
DB_BLOCK_CHECKING
When the DB_BLOCK_CHECKING parameter is set to TRUE Oracle performs a walk through of the data in the block to check it is self-consistent. Unfortunately block checking can add between 1 and 10% overhead to the server. Oracle recommend setting this parameter to TRUE if the overhead is acceptable.
DBMS_REPAIR
Unlike the previous methods dicussed, the DBMS_REPAIR package allows you to detect and repair corruption. The process requires two administration tables to hold a list of corrupt blocks and index keys pointing to those blocks. These are created as follows:
BEGIN
DBMS_REPAIR.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => DBMS_REPAIR.repair_table,
action => DBMS_REPAIR.create_action,
tablespace => 'USERS');
DBMS_REPAIR.admin_tables (
table_name => 'ORPHAN_KEY_TABLE',
table_type => DBMS_REPAIR.orphan_table,
action => DBMS_REPAIR.create_action,
tablespace => 'USERS');
END;
/
With the administration tables built we are able to check the table of interest using the CHECK_OBJECT procedure:
SET SERVEROUTPUT ON
DECLARE
v_num_corrupt INT;
BEGIN
v_num_corrupt := 0;
DBMS_REPAIR.check_object (
schema_name => 'SCOTT',
object_name => 'DEPT',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => v_num_corrupt);
DBMS_OUTPUT.put_line('number corrupt: ' || TO_CHAR (v_num_corrupt));
END;
/
Assuming the number of corrupt blocks is greater than 0 the CORRUPTION_DESCRIPTION and the REPAIR_DESCRIPTION columns of the REPAIR_TABLE can be used to get more information about the corruption.
At this point the currupt blocks have been detected, but are not marked as corrupt. The FIX_CORRUPT_BLOCKS procedure can be used to mark the blocks as corrupt, allowing them to be skipped by DML once the table is in the correct mode:
SET SERVEROUTPUT ON
DECLARE
v_num_fix INT;
BEGIN
v_num_fix := 0;
DBMS_REPAIR.fix_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => Dbms_Repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => v_num_fix);
DBMS_OUTPUT.put_line('num fix: ' || TO_CHAR(v_num_fix));
END;
/
Once the corrupt table blocks have been located and marked all indexes must be checked to see if any of their key entries point to a corrupt block. This is done using the DUMP_ORPHAN_KEYS procedure:
SET SERVEROUTPUT ON
DECLARE
v_num_orphans INT;
BEGIN
v_num_orphans := 0;
DBMS_REPAIR.dump_orphan_keys (
schema_name => 'SCOTT',
object_name => 'PK_DEPT',
object_type => DBMS_REPAIR.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => v_num_orphans);
DBMS_OUTPUT.put_line('orphan key count: ' || TO_CHAR(v_num_orphans));
END;
/
If the orphan key count is greater than 0 the index should be rebuilt.
The process of marking the table block as corrupt automatically removes it from the freelists. This can prevent freelist access to all blocks following the corrupt block. To correct this the freelists must be rebuilt using the REBUILD_FREELISTS procedure:
BEGIN
DBMS_REPAIR.rebuild_freelists (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => DBMS_REPAIR.table_object);
END;
/
The final step in the process is to make sure all DML statements ignore the data blocks marked as corrupt. This is done using the SKIP_CORRUPT_BLOCKS procedure:
BEGIN
DBMS_REPAIR.skip_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => DBMS_REPAIR.table_object,
flags => DBMS_REPAIR.skip_flag);
END;
/
The SKIP_CORRUPT column in the DBA_TABLES view indicates if this action has been successful.
At this point the table can be used again but you will have to take steps to correct any data loss associated with the missing blocks.
Other Repair Methods
Other methods to repair corruption include:
* Full database recovery.
* Individual datafile recovery.
* Block media recovery (BMR), available in Oracle9i when using RMAN.
* Recreate the table using the CREATE TABLE .. AS SELECT command, taking care to avoid the corrupt blocks by retricting the where clause of the query.
* Drop the table and restore it from a previous export. This may require some manual effort to replace missing data.
Regds
Girish
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136744 is a reply to message #136567] |
Fri, 09 September 2005 15:38 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
I could not relate the contents of the 'status' to figure it out. Adding the check for class# (=1) seems to work but I could not find enough documentation (even on oracle site for class number). So for the examples I tried, it seems to work but I am not sure if it covers all cases. Queries I tried follow. Let me know if you think this solution does not cover all cases.
SQL> alter system checkpoint;
System altered.
SQL> alter table TRY move;
Table altered.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> alter system checkpoint;
System altered.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> insert into TRY values ('aaa');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
'MODIFIE STATU
-------- -----
MODIFIED xcur
(conclusion: dirty blocks exist)
SQL> alter system checkpoint;
System altered.
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> insert into TRY values ('222');
1 row created.
SQL> commit;
Commit complete.
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
'MODIFIE STATU
-------- -----
MODIFIED xcur
(conclusion: dirty blocks exist)
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136985 is a reply to message #136744] |
Mon, 12 September 2005 10:25 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
I dont understand what you are trying to do here.
There are no columns CLASS OR DIrty in user_objects.
So the above sql is WRONG.
By Theory,
Truncate will reset HWM. THis is known and proved
Truncate will write all dirty blocks to disk.
But there is something over here, when we truncate the table.
I "beleive" , Dirty blocks are NOT written immediately.
They are placed in a checkpoint queue system.
SO only when a checkpoint occurs, the dirty buffers are actually written to Disk.
I still have no proof ( convincing proof) for my above theory and i am working on that.
What i found is for a truncate , there is always a 'local write wait' event.
Please seee the session.
scott@9i > create table try (name char(10)) tablespace users;
Table created.
scott@9i > get get_status
1 SELECT o.data_object_id,
2 bh.dirty,
3 bh.STATUs,
4 count(*)
5 FROM (select * from user_objects where object_name = 'TRY') o,
6 v$bh bh
7 WHERE o.data_object_id = bh.objd
8* group by O.data_object_id,bh.dirty,bh.STATUS
scott@9i > insert into try ( select dbms_random.string('A',10) from dba_extents where rownum < 10000);
1515 rows created.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 1
6369 Y xcur 7
scott@9i > commit;
Commit complete.
scott@9i > @get_status
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 1
6369 Y xcur 7
scott@9i > alter system checkpoint;
System altered.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 8
scott@9i > insert into try ( select dbms_random.string('A',10) from dba_extents where rownum < 10000);
1515 rows created.
scott@9i > commit;
Commit complete.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 3
6369 Y xcur 13
scott@9i > truncate table try;
Table truncated.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6370 N cr 1
6370 Y xcur 1
scott@9i > alter table try move tablespace users;
Table altered.
scott@9i > @get_status
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6371 Y xcur 3
scott@9i > alter system checkpoint;
System altered.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6371 N xcur 3
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 03 02:24:09 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
