- 博客(18)
- 资源 (13)
- 收藏
- 关注
原创 use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2014-05-31 21:41:11 5171
原创 11.2.0的参数OPTIMIZER_FEATURES_ENABLE会话层与语句层上设置区别;
--下午在测试ventor io的发现OPTIMIZER_FEATURES_ENABLE在会话层级别和语句级别的HINT有差异;先操练下:--加入hint情况,OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.1')SQL> select * from v$version;BANNER------------------------------
2014-05-29 17:12:44 7220
原创 文档performance tuning 第299页有段话先记录下来;
The Query Optimizer 11-23How the Query Optimizer Chooses Execution Plans for JoinsThe query optimizer considers the following when choosing an execution plan:■The optimizer first determines whet
2014-05-29 16:01:41 886
原创 11g自适应游标及statistics_level
--SQL> drop table t1 purge;Table dropped.SQL> SQL> create table t1 as select * from dba_objects;Table created.SQL> create index idx_t1_id on t1(object_id);Index created.SQL> exec dbms_st
2014-05-27 17:44:18 895
原创 SQL运行了,内存中也存在,AWR却查询不到的原因
主要原因:SQL执行过快或者频率过少没有及时捕获造成的SQL> select status from t1 where object_id=120;STATUS-------VALIDVALID--获取SQL_ID为2ay1m2zwrzxtbSQL> @allPLAN_TABLE_OUTPUT------------------------------------
2014-05-21 16:56:45 954
原创 cluster factor(聚簇因子值)计算
SQL> drop table t1 purge;Table dropped.create table t1(n1 number, v2 varchar2(3500), v3 varchar2(3500) );Table created.SQL> create sequence seq001 start with 1;Sequence created.SQL> in
2014-05-14 21:56:31 889
原创 索引列not null与count(*)关系
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/S
2014-05-13 22:14:42 1152
原创 使用to_number,to_char转换的连接列易造成错误的执行计划
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2014-05-12 22:17:50 1117
原创 当push_pred,merge的cost值小于不转换的cost时才会采用
SQL> create table t1 as select * from dba_objects where object_id is not null;Table created.SQL> create index idx_t1_object_id on t1(object_id);Index created.SQL> create view v_t1 as select di
2014-05-11 22:44:59 802
原创 组合索引,前置列与where条件关系
SQL> create table t1 as select * from dba_objects;Table created.SQL> create index idx_z on t1(object_id,owner);Index created.SQL> create index idx_f on t1(owner,object_id);Index created.SE
2014-05-10 22:03:11 2085
原创 等高直方图预估结果集行计算公式
摘自《催华-基于ORACLE的SQL优化SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- N1
2014-05-10 13:25:42 1370
原创 等频直方图预估结果集行计算公式
公式:摘自《催华-基于ORACLE的SQ=>等频(频率)直方图 -->density计算density=1/(2*num_rows*null_adjust)null_adjust=(num_rows-num_nulls)/num_rows-->等值查询,val在low_value and high_value之间,值等于某个endpoint_valuecardinalit
2014-05-10 12:28:13 1201
原创 触发器引起DML性能大降亦可用10046捕获元凶SQL
场景:一些触发器的滥用,造成SQL的DML性能大降,通过10046亦可捕获其中耗能SQL测试:--创建等待插入表T1SQL> create table t1 as select object_id,object_name from dba_objects where 1=2;Table created.--创建触发器遍历的表T2SQL> create table t2 ( 2
2014-05-06 21:58:39 717
原创 当表数据只有部份存在倾斜时,唯一值比例过高的列做直方图效果很差,不适合采用;
SQL> create table t1 as select object_id,object_name from dba_objects where object_id is not null;Table created.SQL> truncate table t1;Table truncated.SQL> insert into t1 select object_id,obj
2014-05-05 21:54:21 1236
原创 bulk collect批量定期插入删除行(续)
create table t1 as select * from dba_objects where object_id is not null; create table t2 as select object_id from t1 where rownum<75201;create index idx_t1_object_id on t1(object_id);根据T2的字段去删除T表的
2014-05-03 16:05:21 855
KFC系列之 - Oracle DBA入门Mysql(下)
2017-12-11
精通Oracle.10g.Pl.SQL编程
2009-03-15
经典linux sed与awk(第二版)
2009-03-13
linux与unix shell 编程(下)
2009-03-04
linux与unix shell编程(上)
2009-03-03
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人