当会话等待redo log buffer 中的信息被写到磁盘的时候就会发生Log file sync waits 等待事件。
通常这是由LGWR的磁盘写入速度慢或者用户的频繁递交引起的。
下面是一些常用的减少log file sync waits 等待时间的方法：
1)调整LGWR以获得更好的磁盘写入吞吐量。
2)如果在短时间段内有很多事务，看有没有可能把这些事务分批处理以减少commit操作。
每个递交动作oracle都会首先去确认相关的redo log 信息以及被写到了磁盘。
3)看是不是有可能使用COMMIT NOWAIT 选项。在Oracle 10g R2中，Oracle增强了COMMIT命令，
增加了WRITE语句来控制redo log文件写入磁盘的方式以提高性能。这个新特性应该只是被用在以下情况：
(1)大事务量导致的redo log 写。
(2)在实例崩溃的时候，可以接受数据的丢失。
(3)处理的事务会引起严重的redo log writes 等待。
COMMIT语句的一些可用选项：
COMMIT;
COMMIT WRITE WAIT; –> The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log.
COMMIT WRITE NOWAIT; –> The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.
COMMIT WRITE BATCH; –> The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log.
COMMIT WRITE IMMEDIATE; –> The commit “prods” the LGWR process by sending a message, so that the redo is written immediately to the redo logs.
| Default COMMIT behavior |
New option |
| IMMEDIATE |
The redo information is written to disk immediately (forcing a disk I/O for each commit). |
BATCH |
Oracle buffers the redo information. The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be “batched”. |
| WAIT |
Oracle does not return from the commit until the commit has completed successfully. |
NOWAIT |
Oracle doesn’t wait for the commit to complete, but returns right away. |
为了避免代码的修改，可以修改COMMIT_WRITE参数文件，参数可以在会话级别或者系统级别设置。
例如：
SQL> ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,NOWAIT’;
你也可以创建一个触发器在运行程序的用户级别设置COMMIT 方式：
SQL> CREATE OR REPLACE TRIGGER sys.global_commit_session_settings AFTER LOGON ON <your application_username>.SCHEMA
BEGIN
execute immediate ‘alter session set COMMIT_WRITE =”IMMEDIATE,NOWAIT”’;
END;
/
4)看看是否可以使用NOLOGGING/UNRECOVERYABLE选项处理事务，以减少redo log 的生成。
Reference:
DOC ID:857576.1
| NUM_LCPUS |
Probably 0 because LPARs not set up |
| NUM_VCPUS |
Same thing |
| AVG_BUSY_TIME |
BUSY_TIME/NUM_CPUS |
| AVG_IDLE_TIME |
IDLE_TIME/NUM_CPUS |
| AVG_IOWAIT_TIME |
IOWAIT_TIME/NUM_CPUS |
| AVG_SYS_TIME |
SYS_TIME/NUM_CPUS |
| AVG_USER_TIME |
USER_TIME/NUM_CPUS |
| BUSY_TIME |
Time equiv of %usr+%sys in sar output |
| IDLE_TIME |
Time equiv of %idle in sar |
| IOWAIT_TIME |
Time equiv of %wio in sar |
| SYS_TIME |
Time equiv of %sys in sar |
| USER_TIME |
Time equiv of %usr in sar |
LOAD |
|
OS_CPU_WAIT_TIME |
Supposedly time waiting on run queues |
RSRC_MGR_CPU_WAIT_TIME |
Time waited coz of resource manager |
PHYSICAL_MEMORY_BYTES |
Total memory in use supposedly |
NUM_CPUS |
Number of CPUs reported by OS |
NUM_CPU_CORES |
Number of CPU sockets on motherboard |
If you want to convert the times (expressed in seconds) back into percentages, then total elapsed time is
e = BUSY_TIME + IDLE_TIME + IOWAIT TIME or
e = SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME since BUSY_TIME = (SYS_TIME+USER_TIME)
e should also be roughly close to elapsed time of report (59.78 min * 60 seconds/min * 8 (NUM_CPUS))
but the report itself takes time to run (actually the snapshots take time as well) and neither the snapshot,
the V$ tables the snapshot is taken from, nor the report itself are AS OF a single consistent point in time.
Anyway at this point you can see, (BUSY_TIME / e) = %busy.
Finally,
CPU used by this session 234,148 65.3 112.4
This is CPU time Oracle reports it used (though we don’t include time spent in SQL*Net code and maybe not all background
processes may be reporting the CPU time they used). It is expressed in centiseconds rather than seconds so
234,148 / (e * 100) will tell you what percent of the total CPU resources on the machine Oracle was keeping busy.
Actually it’s probably easier to look at DB CPU stat since that is already in seconds.
BUSY_TIME – “DB CPU” is the amount of CPU usage that seems to be because of things mostly happening outside the database.
Good news or bad news?
God knows!!!
……
Oracle 10g SQL/PLSQL引入了正则表达式语法。有以下几个内置函数：
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_REPLACE
到了11g，oracle引入了一个新的函数：
REGEXP_COUNT
正则表达式的强大功能就不说了，在Oracle10g之前，很多复杂的字符串操作编程需要在客户端应用中实现。
下面简要介绍这几个函数：
SQL Element Category Description
------------- ---------- -------------------------------------------------------------------------------------------------
REGEXP_LIKE Condition Searches a character column for a pattern.
Use this function in the WHERE clause of a query to return rows matching a regular expression.
The condition is also valid in a constraint or as a PL/SQL function returning a boolean.
The following WHERE clause filters employees with a first name of Steven or Stephen:
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')
REGEXP_REPLACE Function Searches for a pattern in a character column and replaces each occurrence of that pattern with the specified string.
The following function call puts a space after each character in the country_name column:
REGEXP_REPLACE(country_name, '(.)', '\1 ')
REGEXP_INSTR Function Searches a string or substring for a given occurrence of a regular expression pattern (a substring)
and returns an integer indicating the position in the string or substring where the match is found.
You specify which occurrence you want to find and the start position.
The following function call performs a boolean test for a valid email address in the email column:
REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0
REGEXP_SUBSTR Function Searches a string or substring for a given occurrence of a regular expression pattern (a substring)
and returns the substring itself.You specify which occurrence you want to find and the start position.
The following function call uses the x flag to match the first string by ignoring spaces in the regular expression:
REGEXP_SUBSTR('oracle', 'o r a c l e', 1, 1, 'x')
REGEXP_COUNT Function Returns the number of times a pattern appears in a string. You specify the string and the pattern.
You can also specify the start position and matching options (for example, c for case sensitivity).
The following function call returns the number of times that e (but not E) appears in the string 'Albert Einstein',
starting at character position 7 (that is, one):
REGEXP_COUNT('Albert Einstein', 'e', 7, 'c') |
正则表达式功能强大，内容繁多，要想学好正则表达式需要花一定的力气，这是一个开始，有机会做个系列学习。
Refer to:
———
Oracle? Database Advanced Application Developer’s Guide 11g Release 1 (11.1)
Oracle? Database SQL Language Reference 11g Release 1 (11.1)
http://www.opengroup.org/onlinepubs/007908799/xbd/re.html
Oracle Regular Expressions Pocket Reference by Jonathan Gennick, O’Reilly & Associates
Mastering Regular Expressions by Jeffrey E. F. Friedl, O’Reilly & Associates
http://www.adp-gmbh.ch/blog/2005/december/22.html
如果通过以下语句修改了global name，数据库链接将不能被删除，除非改回原来的global name.
ALTER DATABASE RENAME GLOBAL_NAME TO ;
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
58
59
60
61
| SQL> conn scott/scott
已连接。
SQL> create database link link_test connect to strmadmin identified by oracle10g using 'db1.onlyring1.net';
数据库链接已创建。
SQL> drop database link link_test;
数据库链接已删除。
SQL> create database link link_test connect to strmadmin identified by oracle10g using 'db1.onlyring1.net';
数据库链接已创建。
SQL> conn /as sysdba
已连接。
SQL> select global_name from global_name ;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORA10GDB.MYCORP
SQL> alter database rename global_name to ORA10GDB.MYCORP.tests;
数据库已更改。
SQL>
SQL> conn scott/scott
已连接。
SQL> drop database link link_test;
drop database link link_test
*
第 1 行出现错误:
ORA-02024: 未找到数据库链接
SQL> select global_name from global_name ;
GLOBAL_NAME
----------------------------
ORA10GDB.MYCORP.TESTS
SQL> sho user
USER 为 "SCOTT"
SQL> conn / as sysdba
已连接。
SQL> alter database rename global_name to ORA10GDB.MYCORP;
数据库已更改。
SQL> select global_name from global_name ;
GLOBAL_NAME
-------------------
ORA10GDB.MYCORP
SQL> conn scott/scott
已连接。
SQL> drop database link link_test;
数据库链接已删除。 |
如果过global name本来是不包含domain name的，那么利用：
ALETER 语句更改global name 将不能再使用该语句修改回原来的名字。
可以通过修改props$基本表的方法[Metalink DocDI 382994.1]：
1)Take a complete consistent backup of the database
2)Execute the following:
1
2
3
4
5
6
| $sqlplus /nolog
connect / as sysdba
SQL> update props$ set value$ = '<globalname without domain>' where name ='GLOBAL_DB_NAME';
SQL>commit; |
3)Then connect as the schema user that owns the DBLINK and try to drop it.
4)Once the database link is dropped, the global_name can be changed back to the
desired name containing domain part using the alter database rename global_name
statement
Recent Comments