ITPub博客

Contention for Shared Pool and Library Cache Latches—Parsing[@more@]select a.*, sysdate-b.startup_time days_old from v$sysstat a, v$instance b where a.name like ’parse%’;STATISTIC# NAME CLASS VALUE DAYS_OLD---------- ------------------------- ----- ---------- ---------- 230 parse time cpu 64 33371587 4.6433912 231 parse time elapsed 64 63185919 4.6433912 232 parse count (total) 64 2137380227 4.6433912

select a.total_waits, b.sum_of_sleepsfrom (select total_waits from v$system_event where event = ’latch free’) a, (select sum(sleeps) sum_of_sleeps from v$latch) b;[@more@]

http://www.oracle.com/technology/global/cn/pub/articles/project_lockdown/index.html[@more@]

Oracle IN&EXISTS与NOT IN&NOT EXISTS 的优化原则的讨论[@more@]uestion:-----------------------------------------------------------1.EXISTS的执行流程 select * from t1 where exists ( select null from t2 where y = x )可以理解为: for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loop对于in 和 exists的性能区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外

SQL*Plus的Copy命令[@more@]参考http://www.itpub.net/showthread.php?s=&threadid=225749&highlight=copy在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy Command便是其中之一。SQL*Plus Copy Command通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。在实际运行环境中若能恰当地选择使用SQL*Plus Copy Command可以有效地提高数据复制的性能。下面将简要介绍SQL*Plus Copy Command使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用

9个重要的视图 [@more@]Ref: http://blog.itpub.net/post/7923/49453 1）v$lock给出了锁的信息，如type字段， user type locks有3种：TM，TX，UL，system type locks有多种，常见的有：MR，RT，XR，TS等。我们只关心TM，TX锁。当TM锁时，id1字段表示object_id；当TX锁时，trunc(id1/power(2,16))代表了回滚段号。lmode字段，session持有的锁的模式，有6种：0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) request字段，process请求的锁的模式，取值范围与lmode相同。ctime字

v$session_wait和v$session使用[@more@]set linesize 3000set pagesize 31spool c:wait_session.lstselect a.sid, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2, a.p2raw, a.p3text, a.p3, a.p3raw, a.wait_time, a.seconds_in_wait, a.state, b.serial#, b.username, b.osuser, b.paddr, to_char(b.logon_time,'YYYY-MM-DD HH24:MI:SS'), b.process, b.sql_hash_value, b.saddr, b.module, b

sysman密码重置[@more@]sysman不是oracle用户，因此不能用alter user 子句修改密码。修改方法是以oem所属用户登陆，然后运行该用户下的存储过程：smp_maintenance.RESET_SYSMAN 该过程将sysman的密码重置为oem_temp.例如，如果存放oem的用户是oem,密码也是oem在sqlplus 中执行：SQL> conn oem/oem;SQL>exec smp_maintenance.RESET_SYSMAN;

oracle database links and sequence[@more@]SQL> conn sys/sys@orcl as sysdbaConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as SYSSQL> select SEQ_TEST.NextVal@local_db from dual; NEXTVAL---------- 1SQL>

logoff trigger for the purpose of collecting historical performance data during logoffs[@more@]create table system.session_event_history tablespace <name>storage (freelist groups <value>)initrans <value>as select b.sid, b.serial#, b.username, b.osuser, b.paddr, b.process, b.logon_time, b.type, a.event, a.total_waits, a.total_timeouts, a.time_waited, a.average_wait, a.max_wait, sysdate as l

Changing the DBID and Database Name[@more@]Changing the DBID and Database NameThe following steps describe how to change the DBID of a database. Optionally, you can change the database name as well.Ensure that you have a recoverable whole database backup. Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example: SHUTDOWN IMMEDIATESTARTUP MOUNTInvoke the DBNEWID utility on the command line, specifying a valid user with the SYSD

free buffer waits [@more@]The free buffer waits is latency related wait event. Obtain the SQL statement that is associated with the free buffer waits event using the hash value supplied by the CURRENT_EVENT cursor: select hash_value, address, piece, sql_text from v$sqltext where hash_value = <cursor hash value> order by piece;

library cache pin [@more@]The library cache pin wait event is related to contention for library cache objects.1. Look up the name of the object that the pin is for using the P1RAW value supplied by the CURRENT_EVENT cursor:select kglnaobj from x$kglob where inst_id = userenv('instance') and kglhdadr = <cursor P1RAW>;2. Find out who is blocking, the statement that is executing, and the mode in which the object is being held using the P1RAW value supplied by the CURRENT_EVENT

buffer busy waits [@more@]The buffer busy waits event is related to read/read, read/write, and write/write contention.1. Establish the name of the object that the buffer lock contention is for using the P1 and P2 values supplied by the CURRENT_EVENT cursor:2. select segment_name, partition_name from dba_extents where <cursor P2> between block_id and (block_id + blocks - 1) and file_id = <cursor P1>;3. Find out the type of block using the P1 and P2 values p

enqueue [@more@]enqueue The enqueue wait event is related to transaction locking.1. Discover the requested lock type and mode by deciphering the P1 value supplied by the CURRENT_EVENT cursor.select chr(bitand(<cursor P1>,-16777216)/16777215) || chr(bitand(<cursor P1>,16711680)/65535) lock_type, mod(<cursor P1>,16) lock_mode from dual;2. Determine the name of the object the enqueue is for using the ROW_WAIT_OBJ# supplied by the CURRENT_EVENT cursor:sele

direct path write [@more@]The direct path write wait event is related to direct write operations.1. Ascertain the name of the object the session is writing to using the P1 and P2 values supplied by the CURRENT_EVENT cursor:select segment_name, partition_name from dba_extents where <cursor P2> between block_id and (block_id + blocks - 1) and file_id = <cursor P1>;2. You can get a rough idea of what the session is doing from the type of database file it writes to. If

direct path read [@more@]The direct path read wait event is related to direct read operations that read data into the session’s PGA.1. Find out the name of the object the session is reading from using the P1 and P2 values supplied the CURRENT_EVENT cursor:select segment_name, partition_name from dba_extents where <cursor P2> between block_id and (block_id + blocks - 1) and file_id = <cursor P1>;2. You can get a rough idea of what the session is doing from the type

latch free[@more@]latch free The latch free wait event is related to concurrency and serialization.1. Get the name of the latch that is being competed for using the P2 value supplied by the CURRENT_EVENT cursor:select name from v$latchname where latch# = <cursor P2>;2. Obtain the SQL statement that is associated with the latch free event using the hash value supplied by the CURRENT_EVENT cursor:select hash_value, address, piece, sql_text from v$sqltext where hash_value = &

db file sequential read and db file scattered read [@more@]db file sequential read and db file scattered read The db file sequential read and db file scattered read are I/O related wait events.1. Determine the object name and partition name (if applicable) using the P1 and P2 values supplied by the CURRENT_EVENT cursor:select segment_name, partition_name from dba_extents where <cursor P2> between block_id and (block_id + blocks - 1) and file_id = <cursor P1>;Querying the

Options for Repairing Data Block Corruption[@more@]Oracle provides different methods for detecting and correcting data block corruption. One method of correction is to drop and re-create an object after the corruption is detected. However, this is not always possible or desirable. If data block corruption is limited to a subset of rows, another option is to rebuild the table by selecting all data except for the corrupt rows.Yet another way to manage data block corruption is to use the DBMS_REPAI