sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库对象SMON_SCN_TIME使用介绍

时间:2016-11-28 21:45   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库对象SMON_SCN_TIME的介绍。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 关于对象SMON_SCN_TIME

关于对象SMON_SCN_TIME,今天某个网友遇到这个问题,smon对该表的delete操作非常消耗资源,其实关于该表,以前我也遇到过一次,一次远程协助的恢复 遇到异灵事件
SQL> SELECT owner, TABLE_NAME, num_rows, blocks, empty_blocks, avg_row_len
  2    FROM dba_tables
  3   WHERE TABLE_NAME = 'SMON_SCN_TIME';

OWNER        TABLE_NAME            NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
------------ ------------------- ---------- ---------- ------------ -----------
SYS          SMON_SCN_TIME              938        167            0        1095


SQL> SELECT owner, object_name, object_id, object_type
  2    FROM dba_objects
  3   WHERE object_name = 'SMON_SCN_TIME';

OWNER        OBJECT_NAME           OBJECT_ID OBJECT_TYPE
------------ -------------------- ---------- -------------------
SYS          SMON_SCN_TIME               576 TABLE

该表的功能可以通过如下例子来进行展示:
SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    1414451

SQL> SELECT scn_to_timestamp(1414451) FROM dual;

SCN_TO_TIMESTAMP(1414451)
---------------------------------------------------------------------------
05-DEC-11 11.52.21.000000000 PM

SQL> !DATE

Mon DEC  5 23:52:37 PST 2011

SQL> SET pagesize 100
SQL> SELECT dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."SMON_SCN_TIME"
   (    "THREAD" NUMBER,
        "TIME_MP" NUMBER,
        "TIME_DP" DATE,
        "SCN_WRP" NUMBER,
        "SCN_BAS" NUMBER,
        "NUM_MAPPINGS" NUMBER,
        "TIM_SCN_MAP" RAW(1200),
        "SCN" NUMBER DEFAULT 0,
        "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD")

SQL> SELECT owner, object_name, object_id, object_type
  2    FROM dba_objects
  3   WHERE object_name = 'SMON_SCN_TO_TIME';

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS                            SMON_SCN_TO_TIME                      574 CLUSTER

SQL> SELECT dbms_metadata.get_ddl('CLUSTER','SMON_SCN_TO_TIME','SYS') FROM dual;

DBMS_METADATA.GET_DDL('CLUSTER','SMON_SCN_TO_TIME','SYS')
--------------------------------------------------------------------------------

  CREATE CLUSTER "SYS"."SMON_SCN_TO_TIME"  (
        "THREAD" NUMBER )
  PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
  PARALLEL (DEGREE 1 INSTANCES 1)

该表的最大记录为1440条,即是保存5天的记录,为什么这么说呢?应该该表记录是通过smon进程来进行维护的,每5分钟写一条记录,如下:
SQL> !DATE

Mon DEC  5 23:56:29 PST 2011

SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       943

SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       943

SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       945

SQL> !DATE

Tue DEC  6 00:05:47 PST 2011

###### 我们可以看到,10分钟过去了,多了2条记录。######
今天网友遇到的问题是,对于该表的delete操作,过于频繁,且非常消耗资源,希望能想办法去解决这个问题,
我们来看看正常情况下的执行计划:
SQL> SET LINES 150

SQL> DELETE FROM smon_scn_time
           WHERE THREAD = 0 AND SCN = (SELECT MIN (SCN)
                                         FROM smon_scn_time
                                        WHERE THREAD = 0);

1 ROW deleted.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2779095807

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | ROWS  | Bytes | Cost (%CPU)| TIME     |
------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                       |     1 |    15 |    48   (0)| 00:00:01 |
|   1 |  DELETE                      | SMON_SCN_TIME         |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| SMON_SCN_TIME         |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SMON_SCN_TIME_SCN_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE           |                       |     1 |     8 |            |          |
|   5 |      TABLE ACCESS CLUSTER    | SMON_SCN_TIME         |   938 |  7504 |    46   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN      | SMON_SCN_TO_TIME_IDX  |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("THREAD"=0)
   3 - access("SCN"= (SELECT MIN("SCN") FROM "SMON_SCN_TIME" "SMON_SCN_TIME" WHERE
              "THREAD"=0))
   6 - access("THREAD"=0)


Statistics
----------------------------------------------------------
         42  recursive calls
          5  db block gets
        172  consistent gets
          0  physical reads
       1920  redo SIZE
        667  bytes sent via SQL*Net TO client
        638  bytes received via SQL*Net FROM client
          3  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

从我这里来看,正常情况下这个语句的消耗为172+42+5=219个逻辑读,还有一个memory的排序。由于该表记录通常比较小,那我们可以考虑让其走index full scan,我们知道这种情况下是多块读的,
如下,我创建一个复合index:
SQL> CREATE INDEX scn_thread_smon ON SMON_SCN_TIME(scn,thread);

INDEX created.

SQL> analyze INDEX scn_thread_smon compute statistics;

INDEX analyzed.

SQL> DELETE FROM smon_scn_time
           WHERE THREAD = 0 AND SCN = (SELECT MIN (SCN)
                                         FROM smon_scn_time
                                        WHERE THREAD = 0);

1 ROW deleted.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3568929938

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |                 |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  DELETE                       | SMON_SCN_TIME   |       |       |            |          |
|*  2 |   INDEX RANGE SCAN            | SCN_THREAD_SMON |     1 |    15 |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE             |                 |     1 |     8 |            |          |
|   4 |     FIRST ROW                 |                 |     1 |     8 |     2   (0)| 00:00:01 |
|*  5 |      INDEX FULL SCAN (MIN/MAX)| SCN_THREAD_SMON |     1 |     8 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("SCN"= (SELECT MIN("SCN") FROM "SMON_SCN_TIME" "SMON_SCN_TIME" WHERE
              "THREAD"=0) AND "THREAD"=0)
   5 - FILTER("THREAD"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          9  db block gets
          4  consistent gets
          0  physical reads
        188  redo SIZE
        668  bytes sent via SQL*Net TO client
        638  bytes received via SQL*Net FROM client
          3  SQL*Net roundtrips TO/FROM client
          4  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
我们可以看到,创建复合索引以后,逻辑读降低为14,虽然多了3个memory排序。

由于这个表是由smon来进行维护操作的,所以网友说在创建复合index时,会处于等待状态,其实我们可以通过如下方式来停止smon去维护SMON_SCN_TIME表:

SQL> SHOW USER
USER IS "SYS"

SQL> ALTER system SET events '12500 trace name context forever, level 10';

System altered.

SQL> !DATE

Tue DEC  6 00:09:04 PST 2011

SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       945

SQL> !DATE

Tue DEC  6 00:10:04 PST 2011

SQL> !DATE

Tue DEC  6 00:10:32 PST 2011

SQL> !DATE

Tue DEC  6 00:10:57 PST 2011

SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       945
我们可以看到,设置event 12500以后,该表的记录不再发生变化了。

下面我们手工将其delete清空。
SQL> SELECT COUNT(*) FROM SMON_SCN_TIME;

  COUNT(*)
----------
       947

SQL>  analyze TABLE SMON_SCN_TIME compute statistics;

TABLE analyzed.

SQL> SELECT owner, TABLE_NAME, num_rows, blocks, empty_blocks, avg_row_len
  2    FROM dba_tables
  3   WHERE TABLE_NAME = 'SMON_SCN_TIME';

OWNER          TABLE_NAME            NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
-------------- ------------------- ---------- ---------- ------------ -----------
SYS            SMON_SCN_TIME              947        167           88        1123

SQL> ALTER system SET events '12500 trace name context forever, level 10';

System altered.

SQL> DELETE FROM SMON_SCN_TIME;

947 ROWS deleted.

SQL> commit;

Commit complete.

SQL> analyze TABLE SMON_SCN_TIME compute statistics;

TABLE analyzed.

SQL> SELECT owner, TABLE_NAME, num_rows, blocks, empty_blocks, avg_row_len
  2    FROM dba_tables
  3   WHERE TABLE_NAME = 'SMON_SCN_TIME';

OWNER         TABLE_NAME           NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
------------- ------------------ ---------- ---------- ------------ -----------
SYS           SMON_SCN_TIME               0        167           88           0

SQL> TRUNCATE cluster SMON_SCN_TO_TIME;

Cluster truncated.

SQL> analyze TABLE SMON_SCN_TIME compute statistics;

TABLE analyzed.

SQL> SELECT owner, TABLE_NAME, num_rows, blocks, empty_blocks, avg_row_len
  2    FROM dba_tables
  3   WHERE TABLE_NAME = 'SMON_SCN_TIME';

OWNER     TABLE_NAME         NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
--------- ---------------- ---------- ---------- ------------ -----------
SYS       SMON_SCN_TIME             0          0            7           0

当然,ok以后还检查下相关index是否正常。

SQL> SELECT owner, index_name, index_type, STATUS
  2    FROM dba_indexes
  3   WHERE TABLE_NAME = 'SMON_SCN_TIME';

OWNER                          INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ ------------------------------ --------------------------- --------
OracleоSYS                            SCN_THREAD_SMON                NORMAL                      VALID
SYS                            SMON_SCN_TIME_TIM_IDX          NORMAL                      VALID
SYS                            SMON_SCN_TIME_SCN_IDX          NORMAL                      VALID

SQL> SELECT owner, index_name, index_type, STATUS
  2    FROM dba_indexes
  3   WHERE TABLE_NAME = 'SMON_SCN_TO_TIME';

OWNER                          INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ ------------------------------ --------------------------- --------
SYS                            SMON_SCN_TO_TIME_IDX           CLUSTER                     VALID

补充下,还可以参考oracle提供的方式,通过如下顺序将相关index drop然后重建:

CONNECT / AS sysdba
DROP INDEX smon_scn_time_scn_idx;
DROP INDEX smon_scn_time_tim_idx;
CREATE UNIQUE INDEX smon_scn_time_scn_idx ON smon_scn_time(scn);
CREATE UNIQUE INDEX smon_scn_time_tim_idx ON smon_scn_time(time_mp);
analyze TABLE smon_scn_time validate STRUCTURE cascade;

另外大家可以参考如下几个mos文档,里面有相关的描述:

How to Resolve ORA-08102 Reported on Table SMON_SCN_TIME [ID 978502.1]
LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
High Executions Of Statement "delete from smon_scn_time..." [ID 375401.1]
How to map SCN with Timestamp before 10g  [ID 365536.1]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库对象SMON_SCN_TIME使用介绍

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1338.html

Oracle研究中心

关键词:

SMON_SCN_TIME使用笔记

Oracle SMON_SCN_TIME