ITPub博客

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,反之如果外

DB2和 Oracle的并发控制（锁）比较[@more@]http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512niuxzh/#author

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使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用

http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/dm-0401gupta/index.html[@more@]

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字

oracle 10g 成功安装在solaris 10上[@more@]vi /etc/system set shmsys:shminfo_shmmax=4294967295 （给oracle最大内存设为2G） set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=10 set semsys:seminfo_semmni=110 set semsys:seminfo_semmns=310 2. 创建用户和组 # groupadd dba #useradd –g dba –d /export/home/oracle –m oracle #passwd oracle (改oracle用户密码) 设定oracle用户的环境参数 #su – oracle $vi .profile 加入内容： ORACLE_BASE=/export/home/oracle ORACLE_HOME=$ORACLE_B

AIX使用异步I/O(aio)提高系统性能[@more@]如果是同步I/O,当一个I/O操作执行时,应用程序必须等待,直到此I/O执行完. 相反,异步I/O操作在后台运行,I/O操作和应用程序可以同时运行,提高了系统性能. 使用异步I/O会提高I/O流量,如果应用是对裸设备进行操作,这种优势更加明显. 因此像数据库,文件服务器等应用往往会利用异步I/O,使得多个I/O操作同时执行. 1. 如何知道是否需要异步I/O? * 执行命令:"vmstat # #" 如果"wa"值超过25%. * 执行命令:"iostat # #" 如果"%tm_act"值超过35%. 2. 查询有几个AIO服务

一个表连接SQL语句的优化过程[@more@]需要优化的语句如下select count(*) from t_addr_list tl , t_addr_group tg where tl.group_id=tg.group_id and tg.user_id=to_number('24886');开始执行时间在5秒左右一.设置参数SQL> SET AUTOTRACE ON 打开执行计划和统计信息SQL> SET TIMING ON打开语句执行的时间二.查询所用到的两个表的行数SQL> select count(*) from t_addr_list; COUNT(*)---------- 1963920已用时间: 00: 00: 07.07SQL> select count(*) from t_addr_group where tg.user_id=to_number('24885'); COUN

AIX下，为什么非adm组成员不能使用sar命令[@more@]AIX下，为什么非adm组成员不能使用sar命令 很多人发现普通用户不能使用sar命令，IBM给的解决方法是将普通用户加到adm组中。但原因是什么呢？使用truss命令，很快就能自己找到答案了。$ sar 1 1sar: The file access permissions do not allow the specified action.注解：这说明普通用户不能使用sar命令。$ truss sar 1 1execve("/usr/sbin/sar", 0x2FF22C0C, 0x2FF22C1C) argc: 3sbrk(0x00000000) = 0x200036F4sbrk(0x0000000C)

use oraperf_lio[@more@]$ unzip oraperf_lio.zip$ sqlplus -s "/ as sysdba"SQL> @oraperf_packagePackage created.SQL> @oraperf_lio.plbPackage body created.Run it like this: The 10 means, 10 samples and the 2 means, at 2 second interval.SQL> set serveroutput onSQL> execute oraperf.analyze_lio(10,2);Logical I/O Analyzer - Version 1.0.1 AlphaCopyright (c) 2003, OraPerf.com. All rights reserved.Connecting to Linuxi386/Linux-2.0.34-8.1.0 Version 9.2.0.1.0TOTAL LIO ROWGET CHAIN UP

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

管理分区表[@more@]create table BILL_MONTHFEE_ZERO(SERV_ID NUMBER(20) not null,BILLING_CYCLE_MONTH NUMBER(6) not null,DATE_TYPE NUMBER(1),ACC_NBR VARCHAR2(80))partition by range (BILLING_CYCLE_MONTH)(partition p_200407 values less than (200407)tablespace TS_ZIKENstorage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),partition p_200408 values less than (200408)tablespace TS_ZIKENstorage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0));create index i

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;

再加脚本[@more@]-- Remark: This is an incomplete procedure.9i and 10gcreate or replace procedure DCas cursor current_event is select 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, b.logon_time, b.process, b.sql_hash_value, b.saddr, b.mod

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