03 2014 档案

no_merge hint
摘要:This is tested in 10gR2.SQL> select * from v$version;BANNER------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Solaris: Version 10.2.0.5.0 - ProductionNLSRTL Ve... 阅读全文

posted @ 2014-03-31 12:00 kramer 阅读(440) 评论(0) 推荐(0)

优化实例- not use hash to avoid temp space issue
摘要:在展开下面的original sql 和 execution plan之前,要知道这个SQL的问题就在于占用大量的TEMP spaceorignal SQLSELECT roster.IC_N AS icN, roster.WORK_SHIFT_C AS workShiftC, roster.EXTRA_SHIFT_C AS extraShiftC, roster.GENERATED_SHIFT_C AS generatedShiftC, roster.RESERVE_SHIFT_C AS reserveShiftCode, num.STAFF_N AS staffN... 阅读全文

posted @ 2014-03-18 18:03 kramer 阅读(394) 评论(0) 推荐(0)

明日计划
摘要:1. 看这篇blog(http://www.cnblogs.com/kramer/p/3608286.html) ,想办法用hash hint 完善original sql 。2. 看这篇blog (http://www.cnblogs.com/kramer/p/3608343.html),想办法用 hint 去unnest 以及弄明白 max 函数到底会不会导致unnest , max和count等是不是有区别,如果有为什么3. 看Cost Based Oracle Fundamentals-Jonathan Lewis.20064. 看subquery并总结 阅读全文

posted @ 2014-03-18 17:42 kramer 阅读(144) 评论(0) 推荐(0)

优化实例- not in 和 not exists
摘要:客户运行一个SQL,非常慢。于是进行了一下改写。速度飞快,首先看一下原来的SQL。original sqlSQL> explain plan for 2 select count(*) from pnadmin.si_vsl where vsl_status_i = 'A' and to_number(vsl_id_n) not in (select vessel_id from pnadmin.vessel_master);Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUT 阅读全文

posted @ 2014-03-18 17:41 kramer 阅读(1483) 评论(0) 推荐(0)

insert into varchar2(8000)
摘要:在看12c的文档的时候发现varcahr2最大长度是4000 byteVARCHAR2 Data TypeTheVARCHAR2data type specifies a variable-length character string. When you create aVARCHAR2column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you s 阅读全文

posted @ 2014-03-12 17:53 kramer 阅读(702) 评论(0) 推荐(0)

dataguard switchover to physical stnadby
摘要:首先做一系列的checkcheck 当前primary 的 standby redo log是否存在SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- ------------------------------------------------------- --- 3 ONLINE /oracle_asm/standby/it... 阅读全文

posted @ 2014-03-12 15:03 kramer 阅读(336) 评论(0) 推荐(0)

data guard 的redo 传输
摘要:data guard 通过把redo从primary数据库传输到standby数据库并应用在standby数据库来实现自己的功能。 redo 传输是有2种模式1. 同步 sync2. 异步 async同步就是指一个transaction在commit之前必须把redo传输到standby,而异步则是指commit不必等redo传输就可以commit成功。data guard中的redo 传输是通过参数 log_archive_dest_n来配置的,下面的两个配置就对应了同步和异步模式。log_archive_dest_8='SERVICE=itid2 SYNC VALID_FOR=( 阅读全文

posted @ 2014-03-11 15:33 kramer 阅读(482) 评论(0) 推荐(0)

histogram
摘要:首先要知道一个概念selectivit--选择性。选择性是一个row source中可能返回的row的多少。比如一个100行的表,经过查询返回48行,那么selectivity就是0.48。 selectivity对CBO的判断非常重要,简单的说,如果selectivity很大,返回的row占row... 阅读全文

posted @ 2014-03-03 16:00 kramer 阅读(1597) 评论(0) 推荐(0)

导航