关于对象SMON_SCN_TIME
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 关于对象SMON_SCN_TIME
|
1 2 3 4 5 6 |
关于对象SMON_SCN_TIME，今天某个网友遇到这个问题，smon对该表的delete操作非常消耗资源， 其实关于该表，以前我也遇到过一次，那是一次数据库的恢复， 详见链接: <a href="http://www.killdb.com/2011/07/07/encounter-a-supernatural-event-in-a-remote-assistance-for-recover.html" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">一次远程协助的恢复 遇到异灵事件</a> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
|
1 |
该表的功能可以通过如下例子来进行展示： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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) |
|
1 2 |
该表的最大记录为1440条，即是保存5天的记录，为什么这么说呢？应该该表记录是通过smon进程来进行维护的， 每5分钟写一条记录，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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条记录。###### |
|
1 2 |
今天网友遇到的问题是，对于该表的delete操作，过于频繁，且非常消耗资源，希望能想办法去解决这个问题， 我们来看看正常情况下的执行计划： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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 |
|
1 2 3 |
从我这里来看，正常情况下这个语句的消耗为172+42+5=219个逻辑读，还有一个memory的排序。 由于该表记录通常比较小，那我们可以考虑让其走index full scan，我们知道这种情况下是多块读的， 如下，我创建一个复合index： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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 |
|
1 2 3 4 |
我们可以看到，创建复合索引以后，逻辑读降低为14，虽然多了3个memory排序。 由于这个表是由smon来进行维护操作的，所以网友说在创建复合index时，会处于等待状态， 其实我们可以通过如下方式来停止smon去维护SMON_SCN_TIME表： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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 |
|
1 2 |
我们可以看到，设置event 12500以后，该表的记录不再发生变化了。 下面我们手工将其delete清空。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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 |
|
1 |
当然，ok以后还检查下相关index是否正常。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
|
1 |
补充下，还可以参考oracle提供的方式，通过如下顺序将相关index drop然后重建： |
|
1 2 3 4 5 6 |
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; |
|
1 2 3 4 5 6 7 |
另外大家可以参考如下几个mos文档，里面有相关的描述： <span style="color: #0000ff;"> 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] </span> |
2 Responses to “关于对象SMON_SCN_TIME”
http://www.oracledatabase12g.com/archives/smon-update-smon-scn-time.html
这算撞衫吗?
to maclean: 哈哈 没看过你以前的这篇。
Leave a Reply
You must be logged in to post a comment.