/************物件檢查點的概念****************/
即僅把與物件所屬的dirty block自buffer cache 的checkpoint queue寫入到資料檔案中

/*************檢視與物件相關的檢查點*********/
SQL> select * from v$statname where name like '%object%' and STATISTIC#=108;

STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
       108 DBWR object drop buffers written                                          8 658143835

/*********檢視物件檢查點的引數值*************/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         0

/********建立測試表*********/
SQL> create table t_object_checkpoint(a int);

Table created

/*******建立表不產生物件檢查點********/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         0
/*******插入資料**********/
SQL> insert into t_object_checkpoint values(1);

1 row inserted

/************插入資料不產生物件檢查點************/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         0

SQL> commit;

Commit complete

/***同上commit********/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         0

SQL> delete from t_object_checkpoint;

1 row deleted

/******delete也不產生物件檢查點********/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         0


SQL> truncate table t_object_checkpoint;

Table truncated

/***truncate產生物件檢查點********/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         2


SQL> drop table t_object_checkpoint;

Table dropped

/****drop table 產生物件檢查點*********/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4



/*********建立另一個測試表**********/
SQL> create table t_ckpt(a int);

Table created

/****檢視物件檢查點資訊*****/
SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

SQL> alter table t_ckpt add b int;

Table altered

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

SQL>
SQL> ---未新增表記錄前的ddl不產生物件檢查點
SQL>
SQL>
SQL>
SQL> ---插入表記錄
SQL> insert into t_ckpt values(1);

insert into t_ckpt values(1)

ORA-00947: not enough values

SQL> insert into t_ckpt values(1,2);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table t_ckpt add c int;

Table altered

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

SQL> --插入表記錄後的ddl也不產生物件檢查點
SQL>
SQL>
SQL> update t_ckpt set c=10;

1 row updated

SQL> commit;

Commit complete

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

/****alter table rename column不產生物件檢查點*********/
SQL> alter table t_ckpt rename column c to c_new;

Table altered

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

SQL> create index idx_ckpt on t_ckpt(a);

Index created

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         4

SQL> drop index idx_ckpt;

Index dropped

SQL> select value from v$sysstat where STATISTIC#=108;

     VALUE
----------
         6

SQL> ---drop index會產生物件檢查點

object checkpoint物件檢查點小記

相關文章