Are they the same? Based on documents, the second one will call "checkpoint" so does this mean alter system switch logfile contains alter system checkpoint?

The answer is NO.

Try to test on a busy system, alter system switch logfile several times until it rounds to the logfile that is still active, and you will hung at switch logfile. Check the v$Logfile you will see that your next logfile is active.

Solution is to alter system checkpoint and all uncurrent logfile will be inactive.

This definitely indicates that these two are not the same.

[@more@]

Below is my test and show you what’s on earch is the difference. Though not completed, but may help explain a bit.

1. Both command will issue “background checkpoint”.
2. If it’s switch logfile, the background checkpoint can start, but no need to complete before returning to user. If it’s checkpoint, yes, you have to finish background checkpoint.
3. “DBWR thread checkpoint buffers written” indicates “alter system checkpoint”, which is thread checkpoint. All dirty buffer needs to be flush to disk.
4. “DBWR checkpoint buffers written” should related to ckpt process, which is incremental checkpoint. It keeps writing checkpoint queue data.
5. Can’t fully understand “DBWR checkpoints” and “physical writes non checkpoint”.

Check the sysstat for name like ‘%checkpoint’, and do a switch logfile, then do a checkpoint.

eventname	initval	switchlogfile	checkpoint
DBWR checkpoint buffers written	0	0	47
DBWR checkpoints	0	1	2
DBWR thread checkpoint buffers written	0	0	47
DBWR transaction table writes	0	0	11
DBWR undo block writes	0	0	13
background checkpoints completed	0	0	2
background checkpoints started	0	1	2
physical writes non checkpoint	5	5	57

DBWR checkpoint buffers written	668
DBWR checkpoints	2
DBWR parallel query checkpoint buffers written	0
DBWR tablespace checkpoint buffers written	0
DBWR thread checkpoint buffers written	47
background checkpoints completed	2
background checkpoints started	2
physical writes non checkpoint	534

create table cshan_test as select *from dba_objects;

DBWR checkpoint buffers written	680
DBWR checkpoints	2
DBWR parallel query checkpoint buffers written	0
DBWR tablespace checkpoint buffers written	0
DBWR thread checkpoint buffers written	47
background checkpoints completed	2
background checkpoints started	2
physical writes non checkpoint	1554

drop table cshan_test;

DBWR checkpoint buffers written	682
DBWR checkpoints	3
DBWR parallel query checkpoint buffers written	0
DBWR tablespace checkpoint buffers written	0
DBWR thread checkpoint buffers written	47
background checkpoints completed	2
background checkpoints started	2
physical writes non checkpoint	1557

Two seconds later:

DBWR checkpoint buffers written	718
DBWR checkpoints	3
DBWR parallel query checkpoint buffers written	0
DBWR tablespace checkpoint buffers written	0
DBWR thread checkpoint buffers written	47
background checkpoints completed	2
background checkpoints started	2
physical writes non checkpoint	1566

alter system checkpoint;

DBWR checkpoint buffers written	801
DBWR checkpoints	4
DBWR parallel query checkpoint buffers written	0
DBWR tablespace checkpoint buffers written	0
DBWR thread checkpoint buffers written	129
background checkpoints completed	3
background checkpoints started	3
physical writes non checkpoint	1592

Hope this can help.

ITPub博客

difference between alter system checkpoint and alter system switch logfile

最新文章