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 Go to next message
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 Go to previous messageGo to next message
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 #136657 is a reply to message #136642] Fri, 09 September 2005 07:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nile,
ARe you looking for dirty blocks or corrupt blocks ( as Girish has explained)?
Re: how to find out if a table has dirty blocks in buffers [message #136659 is a reply to message #136657] Fri, 09 September 2005 07:44 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
oops... sorry... the doc was all for corrupt blocks.

Re: how to find out if a table has dirty blocks in buffers [message #136664 is a reply to message #136567] Fri, 09 September 2005 08:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is a lot to consider here ( delayed block cleanout etc).
So this may not be Very accurate.
I will continue my test and keep posted.
Thanks and Regards.
scott@9i > create table try (name char(10)) tablespace users;

Table created.

scott@9i > colum object_name format a10
scott@9i > select owner,object_name,object_id from  dba_objects where owner='SCOTT' and object_name='TRY';

OWNER                          OBJECT_NAM  OBJECT_ID
------------------------------ ---------- ----------
SCOTT                          TRY              6338

scott@9i > select count(*) from v$bh where DIRTY='Y' and objd=6338;

  COUNT(*)
----------
         3

scott@9i > insert into  try values ('bbb');

1 row created.

scott@9i > select count(*) from v$bh where DIRTY='Y' and objd=6338;

  COUNT(*)
----------
         8

scott@9i > commit;

Commit complete.

scott@9i > select count(*) from v$bh where DIRTY='Y' and objd=6338;

  COUNT(*)
----------
         8

scott@9i > alter system checkpoint;

System altered.

scott@9i > select count(*) from v$bh where DIRTY='Y' and objd=6338;

  COUNT(*)
----------
         0

Re: how to find out if a table has dirty blocks in buffers [message #136671 is a reply to message #136567] Fri, 09 September 2005 08:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
My first thought was to look in the v$sysstat or v$sesstat using the stats described in v$statname. But these I think are not at the individual object level but more counts of activity.

I don't know anything about v$bh, but at the top of the reference page for it it says

"This is a Real Application Clusters view. This view gives the status and number of pings for every buffer in the SGA"

Yet mine is populated with data, even though I'm not running RAC. Is this a contradiction? What am I missing?
Re: how to find out if a table has dirty blocks in buffers [message #136672 is a reply to message #136664] Fri, 09 September 2005 08:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and by checkpoint, the buffers are not flushed always. sometimes they are just cleaned (Look into v$bh.status). you need a very pristine environment to test these~
Re: how to find out if a table has dirty blocks in buffers [message #136675 is a reply to message #136672] Fri, 09 September 2005 08:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes Martin.
It is says it is a RAC view,
but is still populating in a non-rac database.

Re: how to find out if a table has dirty blocks in buffers [message #136678 is a reply to message #136675] Fri, 09 September 2005 08:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and i got the tip from here
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2170006092044
search for v$bh.dirty
Re: how to find out if a table has dirty blocks in buffers [message #136680 is a reply to message #136567] Fri, 09 September 2005 08:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks Nile for the question and Mahesh for the information. I just ran a script and saw it in action. I also saw when I compared a regular insert of 10,000 rows to a direct path insert the number of dirty blocks in first case showed at 192 while in the second case at only 6, which makes sense because the direct path would bypass buffer cache.
Re: how to find out if a table has dirty blocks in buffers [message #136688 is a reply to message #136567] Fri, 09 September 2005 09:12 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Also, watch out. According to below link I think we should use data_object_id not object_id from dba_objects:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4433887271030#14939077627283

And I just did a test that seems to confirm this. V$bh.objd is the data_object_id, not the object_id. Confusion is that these two start off the same, but data_object_id will change if segment changes via things like move, truncate, and other.

[Updated on: Fri, 09 September 2005 09:18]

Report message to a moderator

Re: how to find out if a table has dirty blocks in buffers [message #136690 is a reply to message #136688] Fri, 09 September 2005 09:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yep!. true.
I was just about to post the same.
The script i used was a behaving a little bizare. using data_object_id fixed it.


Re: how to find out if a table has dirty blocks in buffers [message #136710 is a reply to message #136567] Fri, 09 September 2005 11:16 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
Thanks a bunch for the information. I really appreciate it. I was trying out the queries from v$bh and things work correctly with data_object_id indicating if there are dirty blocks present. I am still confused about one thing though. Here is a test I tried:

SQL> create table try (name char(10)) tablespace users;
Table created.

SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192310

SQL> insert into TRY values ('aaa');
1 row created.
SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.

SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0

----> verified: on disk 1 row present - 'aaa'

SQL> insert into TRY values ('bbb');
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
---------- --------------
1192310 1192310

SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1

----> indicates dirty blocks present i.e data on disk is not accurate
----> verification: on disk 1 row present - 'aaa'



SQL> alter system checkpoint;
System altered.

SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0

----> indicates NO dirty blocks i.e data on disk is accurate
----> verification: on disk 2 rows - 'aaa', 'bbb'



SQL> truncate table TRY;
Table truncated.

SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192311
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1 (seems to indicate dirty blocks present)

----> indicates dirty blocks present i.e data on disk is not accurate
----> verification: on disk 0 rows

As I verified that data on disk contains no rows, data_object_id seems to indicate otherwise... am I missing something??

TIA
Re: how to find out if a table has dirty blocks in buffers [message #136711 is a reply to message #136710] Fri, 09 September 2005 11:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
did you check the status too?
v$bh.status. You may see a difference.

[Updated on: Fri, 09 September 2005 11:21]

Report message to a moderator

Re: how to find out if a table has dirty blocks in buffers [message #136714 is a reply to message #136567] Fri, 09 September 2005 11:37 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
same thing when I try 'alter table TRY move'. data_object_id indicates that there are dirty blocks present but data on disk seems ok...

----> verification: on disk 1 row - '111'

SQL> select object_id, data_object_id from user_objects where object_name='TRY';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192312

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
---------- --------------
1192310 1192313

SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');

COUNT(*)
----------
0

SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');

COUNT(*)
----------
1

----> verification: on disk 1 row - '111' (data_block_id indicates data on disk is not accurate????)
Re: how to find out if a table has dirty blocks in buffers [message #136715 is a reply to message #136567] Fri, 09 September 2005 11:45 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
>> did you check the status too? v$bh.status.
sorry...looks like my prev post crossed yours. I will try out status and post the results
Re: how to find out if a table has dirty blocks in buffers [message #136724 is a reply to message #136567] Fri, 09 September 2005 12:44 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yes, look at status. Also look at the class# column, which indicates which type of block is in there. I think you should look for a value of 1 meaning data or index blocks. Other blocks include things such as segment headers and undo blocks.
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 Go to previous messageGo to next message
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 #136951 is a reply to message #136567] Mon, 12 September 2005 07:57 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Looks good...I saw a list for the class# values on asktom, and 1 was the only data or index block. Rest was undo blocks or system blocks or segment headers.

So, now that we at least have a pretty good answer, one that will provide at the least an estimate, what is your goal in all this?
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
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


Re: how to find out if a table has dirty blocks in buffers [message #137026 is a reply to message #136567] Mon, 12 September 2005 18:05 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
>> I dont understand what you are trying to do here.
>>There are no columns CLASS OR DIrty in user_objects.
Sorry, the SQL should have looked like this:
SQL> select 1 from v$bh where class# = 1 and dirty = 'Y' and objd=(select data_object_id from user_objects where object_name = 'TRY1' and data_object_id
is not null);
(tried it on a bunch of cases and it seems to work but I can't be 100% sure as I would need to read more about 'class#' and 'status' about which there is not too much info even in ora docs.

>> Truncate will reset HWM. THis is known and proved
>> Truncate will write all dirty blocks to disk.
After reading your comment, I read about HWMs and things are clearer now. Thanks. Since the HWM is reset, there would be no data to read whether checkpoint is done or not.
Re: how to find out if a table has dirty blocks in buffers [message #137358 is a reply to message #136567] Wed, 14 September 2005 16:37 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
partitioned tables have partitions dealt with separately so the query is:
SQL> select 1 from v$bh where class# = 1 and dirty = 'Y' and objd IN (select data_object_id from user_objects where object_name = 'TRY1' and data_object_id
is not null);


Also here are the class# values:

1 Data/Index GC_DB_LOCKS
2 Sort blocks No PCM lock held for these blocks
3 Save UNDO Blocks GC_SAVE_ROLLBACK_LOCKS
4 Segment Headers GC_SEGMENTS
5 Save UNDO Headers GC_ROLLBACK_LOCKS
6 Free List Groups GC_FREELIST_GROUPS
7 System UNDO segment hdr None
8 System UNDO blocks GC_ROLLBACK_LOCKS
7+(2n) User UNDO segment hdr n GC_ROLLBACK_SEGMENTS
7+(2n+1)User UNDO segment GC_ROLLBACK_LOCKS

Re: how to find out if a table has dirty blocks in buffers [message #137488 is a reply to message #136567] Thu, 15 September 2005 06:36 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
What is your source on those class# values? I had seen something similar.

Also, what is are the bits after you list what the class# stands for? For example, GC_SEGMENTS? Is that just a global constant variable declared somewhere? Do you know where?
Re: how to find out if a table has dirty blocks in buffers [message #137673 is a reply to message #136567] Fri, 16 September 2005 01:41 Go to previous message
nile
Messages: 13
Registered: September 2005
Junior Member
Info is from metalink. I am not sure about what each of the values mean. I still posted the values as they may be of help to others. It is hard to find the documentation on this.
Previous Topic: Oracle9i Function Base index?
Next Topic: Execution plan misleads me (merged cross-posts)
Goto Forum:
  


Current Time: Fri Dec 09 13:19:36 CST 2022