关于对象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
------------------------------ ------------------------------ --------------------------- --------
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]
发表回复