[oracle@rac-node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 10 22:49:39 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create sequence seq_test500
2 minvalue 1
3 maxvalue 999999999999999
4 start with 1
5 increment by 500
6 cache 500;
Sequence created.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE
------------------------------ ------------------------------ ----------
MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1
1.0000E+15 500 N N 500 1
SQL> set line 300
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 1
SQL> /
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 1
-----创建时并不会产生cache
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
1
SQL> /
NEXTVAL
----------
501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 250001
---第一次调用后会CACHE
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
1001
SQL> /
NEXTVAL
----------
1501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 250001
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 2001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 2001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
2001
---正常关闭不会将CACHE清空,将上将执行后的下一个值存为last_number,再次调用 nextval 和上次的连续
SQL> /
NEXTVAL
----------
2501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 252001
--调用一次后,LAST_NUMBER更新为 252001=(CACHE_SIZE* " increment by"+currval - " increment by")。
SQL> alter system flush SHARED_POOL;
System altered.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 252001
----数据字典信息不变
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
252001
--cache缓存在shared pool中,清空后,从数字字典里面取值
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 502001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
252501
SQL> /
NEXTVAL
----------
253001
--上述结果无悬念
SQL> shu abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 502001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
502001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 752001
--shu abort后,结果可想而知。
--正常shutdown的时候,个人理解为,shared pool 里面的信息,会写回数据字典。
--但不是所有的都写.
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.24.25.263 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:26:38
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:27:53
--细看会发现, v$active_session_history并没写入到dba_ash里面。
参考官网:
DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY.
创建个快照试试:
SQL> exec dbms_workload_repository.create_snapshot('TYPICAL');
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.34.40.115 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:37:25
细看,好像少了近三分钟的数据。改下类型试试:
SQL> exec dbms_workload_repository.create_snapshot('ALL');-- ALL
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.38.00.582 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2016-03-10 23:40:05
-----还是不太对劲,动态视图是基于表的。对dictionary cache,library cache,等等各种SGA模块理解还需要加强.....
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 10 22:49:39 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create sequence seq_test500
2 minvalue 1
3 maxvalue 999999999999999
4 start with 1
5 increment by 500
6 cache 500;
Sequence created.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE
------------------------------ ------------------------------ ----------
MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1
1.0000E+15 500 N N 500 1
SQL> set line 300
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 1
SQL> /
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 1
-----创建时并不会产生cache
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
1
SQL> /
NEXTVAL
----------
501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 250001
---第一次调用后会CACHE
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
1001
SQL> /
NEXTVAL
----------
1501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 250001
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 2001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 2001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
2001
---正常关闭不会将CACHE清空,将上将执行后的下一个值存为last_number,再次调用 nextval 和上次的连续
SQL> /
NEXTVAL
----------
2501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 252001
--调用一次后,LAST_NUMBER更新为 252001=(CACHE_SIZE* " increment by"+currval - " increment by")。
SQL> alter system flush SHARED_POOL;
System altered.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 252001
----数据字典信息不变
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
252001
--cache缓存在shared pool中,清空后,从数字字典里面取值
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 502001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
252501
SQL> /
NEXTVAL
----------
253001
--上述结果无悬念
SQL> shu abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 502001
SQL> select seq_test500.nextval from dual;
NEXTVAL
----------
502001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS SEQ_TEST500 1 1.0000E+15 500 N N 500 752001
--shu abort后,结果可想而知。
--正常shutdown的时候,个人理解为,shared pool 里面的信息,会写回数据字典。
--但不是所有的都写.
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.24.25.263 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:26:38
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 599789192 bytes
Database Buffers 167772160 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:27:53
--细看会发现, v$active_session_history并没写入到dba_ash里面。
参考官网:
DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY.
创建个快照试试:
SQL> exec dbms_workload_repository.create_snapshot('TYPICAL');
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.34.40.115 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-10 23:37:25
细看,好像少了近三分钟的数据。改下类型试试:
SQL> exec dbms_workload_repository.create_snapshot('ALL');-- ALL
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_history;
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.38.00.582 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2016-03-10 23:40:05
-----还是不太对劲,动态视图是基于表的。对dictionary cache,library cache,等等各种SGA模块理解还需要加强.....
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27349469/viewspace-2055310/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27349469/viewspace-2055310/