1常见视图
select sid from v$mystat where rownum=1;(当前会话id)
select sid,type,id1,id2,request,lmode,block from v$lock where type in ('TM','TX');(查看锁对象)
--block=1 说明它阻塞了其他会话
select machine from v$session where sid in(158);(查看访问的主机名称)
select object_name ,subobject_name from dba_objects where object_id = 157;(查看对象名称对应产看锁的id1)
create table t(x int) partition by range(x) (partition p1 values less than(10),partition p2 values less than(maxvalue));(创建分区表)
set autotrace trace exp;(在sqlplus里面展现执行计划)
set tarce off;(关闭执行计划)
select /*+ dynamic_sampling(t 0) */ from * from t where id =1;（禁止动态采样）
EXEC DBMS_STATS.gather_table_stats('ios_ah','t',cascade =>true);（对表进行分析）
EXEC DBMS_STATS.gather_table_stats('ios_ah','t1',cascade =>true,method_opt=>'for all indexed columns');（对索引列分析）
select * from t1 where id in(select /*+ dynamic_sampling(t2 0) cardinality(t2 10000) */ id from t2 where name ='AA');(禁止动态采样,同事使用hint)这种方式通过hash join semt关联表,因为数据量以为比较大
select * from t1 where id in(select /*+ dynamic_sampling(t2 0) cardinality(t2 1) */ id from t2 where name ='AA');以netsted loop join嵌套循环的方式关联表,因为数据量它以为就是1条综上所述cardinality的数量直接影响关联的方式
explain plan for select * from t1,t2 where t1.id = t2.id; select * from table(dbms_xplan.display);（查看执行计划的另外一个方式）
EXEC DBMS_STATS.gather_table_stats('ios_ah','t2',cascade =>true);（对表进行分析）select index_name,clustering_factor from user_indexs
where table_name = 'T'(查看一个表急促因子的值)
select index_name,ditinct_keys from user_indexs where
table_name = 'T'（查看索引的）重复次数