ORACLE-MLOG high water

2011-04-22  来源:本站原创  分类:Database  人气:81 

Step 1 Check the SQL part of the statspack report.
MLOG $ _table_name "table, this table is" table_name "the log table, oracle table using log tables to track changes in

If the mlog $ table hwm too high will cause the number of records in the table despite the small, but doing full table scan still had a very high physical read (mlog $ table The reason why there will be a high hwm, it should be because certain period of time because of network outages or other causes of data synchronization fails, the historical record has been kept in the mlog $ table, then sync recovery, oracle will automatically delete the history off, but did not reduce hwm). Therefore, high system IO address the key issue is to reduce the materialized views associated mlog $ table hwm.

Step 2 You can use the following statement to estimate the MLOG $ table if there is a high hwm
select segment_name, bytes/1024/1024 from user_segments where segment_name like 'MLOG $%'
If you check out the MLOG table space occupied by the great, it shows that this MLOG table exists HWM.

Approach:
1 to oracle user host.
(2) to connect to the database.
sqlplus "/ as sysdba"

3 will extend the service node JOB to 10 hours, and manually execute a JOB.
JOB user login database with implementation, refresh the group find the JOB number and name.
select job, rname from user_refresh;
 JOB: job number  RNAME: refresh group name
4. Modify the refresh interval time job for 10 hours.
SQL> BEGIN
DBMS_REFRESH.CHANGE (
name => 'SYNC_GROUP', - the first step in the refresh group name query
next_date => SYSDATE,
interval => 'sysdate + 36000/86400');
END;
5 Manual implementation of the business node job.
SQL> exec dbms_job.run (354); - the first step in the query JOB No.
SQL> commit;

6 high level cleaning MLOG table.
To "MLOG $ _table_name" table, for example. High water removal operation requires two session with the database connection management node, it can be done with the cleanup operation.

7 operate in the first session, on "table_name" table plus an exclusive lock to prevent other people to update the table (corresponding mlog $ table will no longer have change).
- Session 1
SQL> LOCK TABLE table_name IN EXCLUSIVE MODE;

8 In the second session in operation, "table_name" table of records to a temporary table "TEMP_MLOG_table_name" in.
- Session 2
SQL> CREATE TABLE TEMP_MLOG_table_name AS SELECT * FROM MLOG $ _table_name;

9 In the second session in operation, with truncate empty "MLOG $ _table_name" table, reducing the table hwm.
- Session 2
SQL> TRUNCATE TABLE MLOG $ _table_name;

10 In the second session in operation, the temporary table record plug it back "MLOG $ _table_name" table, then drop off the temporary table "TEMP_MLOG_table_name".
- Session 2
SQL> INSERT INTO MLOG $ _table_name SELECT * FROM TEMP_MLOG_table_name;
SQL> DROP TABLE TEMP_MLOG_table_name;
SQL> COMMIT;

11 in the first session in operation, the implementation of rollback, the release of "table_name" table exclusive locks.
- Session 1
SQL> ROLLBACK;

12. To restore the business database synchronization JOB intervals of time, and manually perform a JOB.
When you are done, mlog $ table hwm is set, the system uses a significant reduction in IO, disk IO peak rate back to normal.

相关文章
  • In-depth understanding of the oracle of the high water level (HWM) 2011-05-12

    Transfer http://tolywang.itpub.net/post/48/307529 Speaking of HWM, we must first briefly talk about the logical storage management ORACLE we know, ORACLE stored on the logical sub-4 size: tablespace, segment, area and block. (1) block: the smallest s

  • the oracle delete and truncate 2011-07-29

    Original Address: http://apps.hi.baidu.com/share/detail/33669918 Code department has gone wrong, the last is very slow query response page, the page update is very slow, the developer submitted a SQL statement to me, Looked at, this statement is very

  • oracle truncate and delete in 2011-07-05

    Code department has gone wrong, the last is very slow query response page, the page update is very slow, the developer submitted a SQL statement to me, Looked at, this statement is very simple, update tbl set ... = ... where mobile =.. and sp_id =..,

  • .net连接oracle的3种实现方法 2015-03-14

    这篇文章介绍了.net连接oracle的3种实现方法,有需要的朋友可以才可以一下 方式1:直接利用.NET的oracle驱动连接 安装Oracle客户端驱动包 找到服务器端安装路径:E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN下tnsnames.ora,把该文件覆盖客户端驱动目下该文件 配置客户端驱动 1.打开数据源管理 2.选择系统DSN,点击[添加] 3.弹出的界面中选择Oracle in OraDB10g_1驱动,弹出Oracle ODBC Dri

  • oracle high water line (turn) 2011-03-04

    High-water line experiment: - Create table test3 SQL> create table test3 as 2 select * from dba_objects where 1 = 2; Table created - View the table allocated block, district size SQL> SELECT segment_name, segment_type, blocks - the number of allocat

  • Oracle high-water line (high water mask) in a different section of the advance management mode 2011-03-29

    As we all know, Oracle marks the high water line below the block had been Oracle format, popular thing about that is that the high-water line of the block are used by Oracle. Usually perform insert operation, when the high-water line when the block i

  • ORACLE-MLOG high water 2011-04-22

    Step 1 Check the SQL part of the statspack report. MLOG $ _table_name "table, this table is" table_name "the log table, oracle table using log tables to track changes in If the mlog $ table hwm too high will cause the number of records in t

  • Oracle database to use snapshot snapshot 2010-02-22

    oracle database snapshot is a table that contains a local or remote database on one or more tables or views in the query results. Here in my bureau in the development of Xiangfan City, 170 Hua Fei reminder system, speed up queries using the snapshot

  • Oracle Learning Notes (12) 2010-03-04

    80. Table type 1. Heap organized table (heap organized table) The most basic type of the database table. To the heap, which is the easiest way for storage. 2. Index-organized table (index organized table) These tables stored in accordance with the in

  • Oracle truncate table, delete, drop table differences 2010-03-13

    Oracle truncate table, delete, drop table differences Keywords: truncate table, delete, and the difference between drop table TRUNCATE TABLE is functionally and not with the same WHERE clause of the DELETE statement: Both delete all the rows in the t

  • How to set Oracle Events to track database 2010-03-18

    Oracle Events event is an important diagnostic tool and problem-solving approach, often through Events or change the settings to screen the behavior of Oracle; There are four types of Events: Immediate dumps Conditional dumps Trace dumps Events that

  • Oracle table space recovery 2010-03-26

    Oracle data file shrink 1. View the data files, including the use of content: data file size, has been used space, free space, hwm information select /*+ ordered use_hash(a,b,c) */ a.file_id,a.file_name,a.filesize, b.freesize, (a.filesize-b.freesize)

  • [Order] sql statement some practical tips for oracle 2010-03-30

    Finishing a book read long ago, and forgot what this was, now contributed. 1) In the select statement, use conditional logic 1select ename, sal, 2 case when sal <= 2000 then 'UNDERPAID' 3 when sal> = 4000 then 'OVERPAID' 4 else 'OK' 5 end as status

  • Oracle 9i.10g flashback query Overview 2010-04-21

    Oracle 9i flashback query Overview 1, Oracle 9i flashback query feature In Oracle 9i before the operation if the user error data, in addition to not fully restored, there is no good solution. Oracle 9i flashback query provided by a new package DBMS_F

  • Plan of Implementation of ORACLE - Basics 2010-05-24

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access the data At the physical level Oracle reads blocks of data. The smallest amount

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • See Oracle event of the function and event list 2010-06-10

    View function, you can use spooling, the output saved to a file. declare err_msg varchar2 (240); begin dbms_output.enable (1000000); for err_num in 10000 .. 10999 loop err_msg: = sqlerrm (-err_num); if err_msg not like '% Message' | | err_num | | 'no

  • Oracle study notes the cursor and the trigger 5 2010-07-05

    Cursor: Implicit Cursor:% FOUND,% NOTFOUND,% ROWCOUNT 1. % FOUND used, only a line or in the DML statements affect more rows,% FOUND attribute it to return TRUE. The following example demonstrates the use of% FOUND: begin update employees2 set first_

  • Oracle Error List (3) 2010-07-31

    ORA-09751: pw_attachPorts: Server call failed pws_attach ORA-09752: pw_attachPorts: port_allocate failure ORA-09753: spwat: invalid process ID ORA-09754: sppst: the process of sent to sppst invalid number ORA-09755: osngpn: port configuration failed

  • oracle SQL tuning of access (transfer) 2010-08-13

    [ORACLE access method: 1) The full table scan (Full Table Scans, FTS) To achieve full table scan, Oracle reads all rows in the table, and check whether each line to meet the WHERE statement constraints. Oracle assigned to the table in order to read e