-
编辑 删除
9i,enqueue,ORACLE锁争用 查看争用类型
select sid, event, p1, p1raw, chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) type,mod(P1, 16) "MODE"from v$session_waitwhere event = 'enqueue'; 记录在此，...
-
编辑 删除
10GR2后ORACLE对REDO写的增强 commit_write
ORACLE DBA非常有必要简单的了解下。写这个文档，我得出的结论是，我们小事务居多的网站，以后升级到10GR2后，可以考虑改变日志提交方式为batch , wait。这种方式不丢数据，节省了日志浪费，省了更多的redo blocks...
-
编辑 删除
两种分页语句的写法的优劣
SELECT OBJECT_ID, OBJECT_NAME FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM (SELECT /*+ index_rs(t T_I) */OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)WHERE ROWNUM <= 10 ) WH...
-
编辑 删除
CBO，ORACLE，隐含参数，_sort_elimination_cost_ratio的含义
create table t1 as select * from dba_objects where object_id is not null;alter table t1 add constraint t1_pk primary key(object_id);create index t1_ind on t1(object_type);alter session set optimizer_m...
-
编辑 删除
filter操作的hash 碰撞
create table emp( dept_no not null, sal, emp_no not null, padding, constraint e_pk primary key(emp_no))aswith generator as ( sel...
-
编辑 删除
关于查询块query block
explain plan forselect * from wxh_tbd where not exists (select /*+ unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);Explained.select * from table(dbms_xplan.display(null,null...
-
编辑 删除
ORACLE 9I not exists的bug
select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production explain plan fors...
-
编辑 删除
not in 用外连接实现
sys@CRMCN>select * from test; ID NAME---------- ---------- 1 &...
-
编辑 删除
in子查询与表连接是否等价？
Select emp.*from empwhere emp.dept_no in (select dept.dept_nofrom deptwhere dept.dept_group = 1);是否等价与：select emp.*fromemp,deptwheredept.dept_no = emp.dept_noand dept.dept_group = 1;这个重写可...
-
编辑 删除
反连接，最快返回第一条记录
我觉得在HASH JOIN ANTI 中，最快返回第一条记录的条件是，第一个分区对连接完成。select a.* from TEST1 a WHERE not exists(select null from TEST2 b where a.id=b.id);A表为BUILD TABLE,B表来探测，读取第一行...
-
编辑 删除
NOT IN ,NOT EXISTS 区别 11G改变
先看两个查询。select count(*) from wxh_tbd where object_id not in (1,2,3); COUNT(*)---------- 20204select count(*) from wxh_tbd where obje...
-
编辑 删除
DBMS_PARALLEL_EXECUTE 11GR2新特性，并行订正大数据
http://blog.itpub.net/post/7167/495835 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1066555.1 http://www.cnblogs.com/tracy/archive/2011/06/09/2076324...
-
编辑 删除
索引对直接路径加载的影响
TEST表大小1,984M -----------表上无索引，每次操作前flush buffer cacheflush buffer cacheinsert /*+ append */ into test1 select * from test;19999996 rows created.Elapsed: 00:01:19.06 fl...
-
编辑 删除
慎用 ddl_lock_timeout。
ddl_lock_timeout允许DDL等待DML操作结束。ddl_lock_timeout可以手工设置等待的时间。不过这个操作的风险对于存在大并发DML操作的表是有很大风险的。DDL操作基本都在表级别获得类型为6的TM表锁，这个锁的级别为最...
-
编辑 删除
索引的块分裂
select n.name, s.value from v$sysstat s, v$statname n where s.statistic# = n.statistic# and n.name like '%split%'; NAME &nbs...
-
编辑 删除
11gr2，新增hint APPEND_VALUES
sys@SMART>insert /*+ APPEND_VALUES(a) */ into test a values(1,'ss');1 row created.sys@SMART>@locklock ...
-
编辑 删除
关于NULL值在索引里的两个疑惑
create table wxh_tbd as select * from dba_objects;Table created.create index t_s on wxh_tbd(object_id,OBJECT_NAME);Index created.select * from wxh_tbd where object_id is null and object_name>'WXH_...
-
编辑 删除
blevel为1下，索引部分cost的计算
sys@SMART>@tabstatPlease enter Name of Table Owner: sysPlease enter Table Name : wxh_tbd**********************************************************Table Level******************************************...
-
编辑 删除
rebuild索引，违背直觉得实验结果
Prompt ================================promptprompt Test 1.prompt Rebuild before adding extra datapromptprompt ================================sys@SMART>create table t1(n1 number(...
-
编辑 删除
in list查询计算cost
sys@SMART>explain plan for 2 select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id =:1;Explained.sys@SMART>@displayPLAN_TABLE_OUTPUT-------------------------------------...
