High Executions Of Statement "delete from smon_scn_time..."
In this Document
Symptoms |
Cause |
Solution |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]Information in this document applies to any platform.
SYMPTOMS
A delete from smon_scn_time is performing excessive gets and executions as viewed from AWR report:
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s)
205,501,888 30,508 6,736.00 54.05 9,733.97 61,180.96 delete from smon_scn_time where...
The AWR report shows the following SQL with an excessive amount of executions:
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0);
CAUSE
There are inconsistencies between the indexes and table smon_scn_time.The delete statement deletes the oldest rows from smon_scn_time to clear space for new rows. SMON wakes up every 5 minutes and checks how many on-disk mappings we have--the max is 144000.
The new mappings are then added for the last period (since SMON last updated), and if this is over 144000, SMON will then issue the delete statement:
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)
There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does not free enough mappings, then there will be multiple executions.
What happens is due to the inconsistency between the table and indexes the delete returns zero rows; so the delete statement is executed continuously to reduce the smon_scn_time below the maximum 14400 mappings.
When table smon_scn_time is analyzed we see the inconsistency:
SQL> analyze table smon_scn_time validate structure cascade;
analyze table smon_scn_time validate structure cascade
*
ERROR at line 1 :
ORA-01499: table/Index Cross Reference Failure - see trace file
SOLUTION
To implement the solution, please execute the following steps:
1. Ensure you have a usable backup in case of failures
2. Drop and recreate the indexes on table smon_scn_time
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;
相關文章
- Elasticsearch Java High Level REST Client(Delete API)ElasticsearchJavaRESTclientdeleteAPI
- DG vs Storage in High Availability -- From OracleAIOracle
- DELETE from DBA_TABLESPACES IN 10Gdelete
- mysql 的delete from where 子查詢的一些限制MySqldelete
- Statement
- JS - if else and else if statementJS
- v$sqlarea_parse_calls與executions與session_cached_cursors關係SQLSession
- GCC編譯遇到“a label can only be part of a statement and a declaration is not a statement”問題GC編譯
- delete input 與 delete all inputdelete
- delete OBSOLETE 與 delete expirieddelete
- Statement (操作 SQL 語句)SQL
- jdbc Statement和PrepareStatement操作JDBCREST
- deletedelete
- delete 和 delete [] 的真正區別delete
- JDBC入門(一):Statement物件JDBC物件
- JDBC - Statement物件 - executeBatch()和executeUpdate()JDBC物件BAT
- statement 、prepareStatement的用法和解釋REST
- Understanding the CREATE DATABASE Statement (69)Database
- delete與delete[]需要注意的地方delete
- MySQL:You must reset your password using ALTER USER statement before executing this statement.MySql
- FormData delete()ORMdelete
- URLSearchParams delete()delete
- JavaScript deleteJavaScriptdelete
- DELETE STATISTICSdelete
- rman 中delete 與delete force 的區別delete
- high wait forAI
- Oracle vs PostgreSQL Develop(16) - Prepared StatementOracleSQLdev
- 追溯 MySQL Statement Cancellation TimerMySql
- Import all grant statement of users in mysql schema !ImportMySql
- Mysql Prepared statement needs to be re-preparedMySql
- prepare statement cache size influence databaseDatabase
- JDBCTM 指南:入門4 - Statement (轉)JDBC
- Step 7: Issue the CREATE DATABASE Statement (65)Database
- statement, session , transaction ,consistency 等概念Session
- HiSpider(Hitask) is a fast and high performance spider with high speedIDEASTORM
- MySQL Delete PHPMySqldeletePHP
- WeakMap delete() 方法delete
- WeakSet delete() 方法delete