ITPub博客

hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading或者ordered可以控制多表之前的连接顺序----------------创建4个测试表create table t1 as select * from dba_objects;create...

----------------------创建测试表create table t1 ( part_col not null, id  not null, small_vc, padding )partition by range(part_col) ( partition p0200 v...

SQL> select /*+ use_concat */ * from hao2 where object_id=1 or object_id=2 or object_id=3;执行计划----------------------------------------------------------Plan hash value: 2739802529---------------...

create table hao1 as select * from dba_objects;create table hao2 as select * from dba_objects;create table hao3 as select * from dba_objects;create index hao3idx on hao3(object_id);create index hao2id...

create table wxh_tbd1 as select * from dba_objects;create table wxh_tbd2 as select * from dba_objects;create index ind_oi on wxh_tbd1(object_name);create index t_22 on wxh_tbd1(object_id);create inde...

create table wxh_tbd1 as select * from dba_objects;create table wxh_tbd2 as select * from dba_objects;create index t_1 on wxh_tbd1(object_name);create index t_2 on wxh_tbd1(object_id);create index t_3...

select * from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);对于如上查询，在10G以前，执行计划的结果只可能为(我们只考虑hash join)：--------------------------------------------------------------------------...

最近出了一个问题，由于新增分区后，导入了表上一个分区的统计信息做为新分区的统计信息，一条SQL走错了。类似如下的查询select * from a where b=1 and c>sysdate-7。由于c列是个日期型。导入的统计信息记录的最大...

一个比较著名的FIRST_ROWS vs. ALL_ROWS的问题:1)select 操作执行了索引查询2)但是当select作为dml操作的一部分的时候(例如：insert .. select ),cbo忽略了这个索引oracle对于dml操作，内总总会倾向与all_rows模式...

http://blog.china.com/u/071030/89253/201007/6618907.html 在RAC环境中，Oracle使用GRD（Global Resource Service）来记录各个RAC节点的资源信息，具体通过GCS（Global Cache Service...

GoalIt is possible that we need to set statistics manually, for example, - when it takes a long time to gather stats for a big table- when we want to force a specific execution planBut, it is not simp...

create table wxh_tbd as select * from dba_objects;update wxh_tbd set object_id=10000000 where object_id=100;create index t_1 on wxh_tbd(object_id);----------------分析表begin  dbms_stats.gather_t...

11gr2，V$OPEN_CURSOR增加cursor_type字段。一直非常多的人对v$open_cursor里记录的内容是被打开的游标还是被cache的游标争论不已。从11GR2新增字段CURSOR_TYPE来看，v$open_cursor包含了以上两个之外，还包含了其他...

ops$admin@CRMG>explain plan for  2  create index ttt on aaaa(id);Explained.ops$admin@CRMG>@displayPLAN_TABLE_OUTPUT------------------------------------------------------------------------...

create or replace function display_raw (rawval raw, type varchar2)return varchar2is cn number; cv varchar2(32); cd date; cnv nvarchar2(32); cr rowid; cc char...

shrink space分两个阶段：1）通过把segment后面的数据delete，然后insert到segment的前面，这个操作的事务单位比较小，在这个过程中即使把进程取消，也不会有问题下次可以继续上一次的操作。而不是很多人理解的，取...

-------------------SESSION 1,SID=3252create table wxh_tbd tablespace apollo_data as select a.* from dba_objects a ,dba_objects b where rownum<50000000;Table created.alter table wxh_tbd modify OBJECT_N...

11G后library cache latch已经消失，被library cache mutxt替代。library cache latch的数量是比cpu_count值大的最小质数相同的子latch。@>show parameter cpuNAME           &n...

select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Productio...

上次跟大家分享的时候，我说如果有DDL没执行完，会导致所有对它的操作被hang在library cache lock上。可是前几天CRM库上在执行DDL期间，后台绝大多数的等待是library cache pin。把这个错误纠正一下。SESSION 1:执行...