摘要：
最近有尝试做SQL优化、一直不得要领，请逛到这里的高人提供一些优化实例（最好是有优化历程说明），先谢谢了！oracle 的优化器: oracle 有两种优化器：基于规则的优化器(rbo/rule based optimizer)和基于代价的优化器(cbo/cost based optimizer).较典型的问题有: 有时、表明明建有索引,但查询过程显然没有用到相关的索引,导致查询过程耗时漫长,占用资源巨大,问题到底出在哪儿呢？按照以下顺序查找,基本上能发现原因所在。首先,我们要确定数据库运行在何种优化模式下,相应的参数是：optimizer_mode。oracle v7 以来缺省的设臵应是&q 阅读全文
随笔分类 - Oracle性能诊断
Oracle如何根据物化视图日志快速刷新物化视图
2011-10-13 15:32 by Tracy., 625 阅读, 收藏, 编辑
摘要：
===========================================================作者: yangtingkun(http://yangtingkun.itpub.net)发表于: 2005.03.05 23:49分类: ORACLE 出处: http://yangtingkun.itpub.net/post/468/20584--------------------------------------------------------------- Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle如何通过一个物化视图日志就可以支持 阅读全文
Asktom: Thanks for the question regarding "Partitioning question", version 8.1.7
2011-10-12 14:47 by Tracy., 313 阅读, 收藏, 编辑
摘要：
Brian -- Thanks for the question regarding "Partitioning question", version 8.1.7Submitted on 12-Oct-2001 13:16 Central time zoneTom's latest followup | Bookmark | BottomLast updated 8-Oct-2011 6:53You AskedLet's say we have a table of documents with key of doc_id. This table conta 阅读全文
Oracle:Commit Enhancements in Oracle 10g Database Release 2
2011-09-23 15:21 by Tracy., 283 阅读, 收藏, 编辑
摘要：
Commit Enhancements in Oracle 10g Database Release 2In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation. This can improve performance, but it should only b 阅读全文
Asktom:get_stat_val函数显示SQL执行I/O相关数据的方法。
2011-09-23 13:44 by Tracy., 835 阅读, 收藏, 编辑
摘要：
Hi Tom, You mentioned this in one of your responses. <<So, a procedure that has been running for 10 hours and finished in your window would dump ALL of its cpu time into your window.>> Could you please clarify if this logic of dumping the time in the snapshot at the end of the procedure 阅读全文
Oracle:索引列压缩的分析对比
2011-09-21 13:39 by Tracy., 3388 阅读, 收藏, 编辑
摘要：
首先，我们结合index_stats得到的索引分析数据看一下，在不同索引列压缩情况下的效果。然后统一总结实验效果。1.创建测试用表t_compress_indexcreate table t_compress_index as select * from all_objects;2.不使用索引压缩技术创建索引sec@secooler> create index idx_t_compress_index on t(owner,object_type,object_name);Index created.sec@secooler> analyze index idx_t_compres 阅读全文
Oracle:在Oracle中使用锁进行并发控制
2011-09-20 14:04 by Tracy., 875 阅读, 收藏, 编辑
摘要：
现在主流的数据库管理系统中，都支持多个事务同时执行，这样提高了数据库管理系统的运行效率。试想如果只允许一个事务运行，而这个事务又需要很长的时间，那么其他的用户必须一直等待该事务结束，效率何其低下。执行不同的事务虽然可以提高性能，但是有可能会破坏数据的完整性，所以我们必须在性能和数据完整性之间进行权衡。那么什么是并发控制呢？并发控制是数据库管理系统协调多个运行事务的行为。首先了解一下，并发控制中经常遇到的三个问题。 脏读 我们通过一个products表来解释一下什么是脏读，该products表有一列quantity(数量)，现在的值为20。假如现在有两个事务T1和T2，它们都是要更新qu... 阅读全文
Oracle:反向键索引
2011-09-16 11:22 by Tracy., 4670 阅读, 收藏, 编辑
摘要：
2、显式为主键列建立反向键索引 2.1 反向键索引的原理和用途 我们知道Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况，默认的B-Tree索引并不理想。这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的I/O读写次数和索引树的层级数成正比，也就是说，一棵具有5个层级的B-Tree索引，在最终读取到索引数据时最多可能发生多达5次I/O操作。因而，减少索引的层级数是索引性能调整的一个重要方法。 如果索引列的数据以严格的有序的方式插入，那么B-Tree索引树将变成一棵不对称的&quo 阅读全文
案例编号 0005---index range scan, index full scan, out-of_date statistics
2011-09-02 15:53 by Tracy., 443 阅读, 收藏, 编辑
摘要：
案例编号: 0005关键词: index range scan, index full scan, out-of_date statistics正文:出问题的SQL:SELECT * FROM ZORYZYZ.VD_TLOWVM_KSLGL WHERE member_id = :1 AND status IN (:2) ORDER BY member_id, id当前的执行计划(index full scan):-----------------------------------------------------------------------------------| Id ... 阅读全文
Index Full Scan && Index Range Scan
2011-09-02 10:50 by Tracy., 9221 阅读, 收藏, 编辑
摘要：
Index Full Scan reads the whole index, in an ordered manner (i.e. it "walks" the index from the lowest value to the highest value).(Index Fast Full Scan reads the whole index, without bothering about ordering, it just reads all the index leaf blocks using multiblock reads -- similar to a T 阅读全文
物化视图快速刷新不支持标准外联接写法
2011-09-01 15:40 by Tracy., 1213 阅读, 收藏, 编辑
摘要：
发现对于REFRESH FAST ON COMMIT物化视图，并不支持标准外连接的写法，而Oracle特有的(+)方式则没有问题。Oracle对于标准外联接的写法支持的并不好，类似的bug已经不是第一次碰到了。SQL> CREATE TABLE T_P (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));表已创建。SQL> CREATE TABLE T_F (ID NUMBER PRIMARY KEY, FID NUMBER);表已创建。SQL> CREATE MATERIALIZED VIEW LOG ON T_P 2 WITH ROWID 阅读全文
Asktom:Tom对Consitent Get的解释及分析
2011-08-31 16:27 by Tracy., 318 阅读, 收藏, 编辑
摘要：
You AskedTom:create table test( a int);beginfor i in 1..10000 loopinsert into test values (i);end loop;end;set autotrace onselect count(0) from test;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 ... 阅读全文
Oracle:TOM实验DBMS_LOCK
2011-08-24 08:58 by Tracy., 1362 阅读, 收藏, 编辑
摘要：
创建一个带主键的表，和一个触发器，触发器会防止两个或多个会话同事插入相同的值。这个触发器使用DBMS_UTILITY.GET_ HASH_VALUE 来计算主键的散列值，得到一个0～1 073 741 823 之间的数（这也是Oracle 允许我们使用的锁ID 号的范围）。然后使用DBMS_LOCK.REQUEST 根据这个ID 分配一个排他锁（也称独占锁，exclusive lock）。一次只有一个会话能做这个工作。grant execute on dbms_lock to scott;create table demo (x int primary key);create or repla 阅读全文
Oracle:物化视图的PCT特性(二)
2011-08-23 16:11 by Tracy., 1238 阅读, 收藏, 编辑
摘要：
上一篇文章介绍了PCT的概念和优点，本文详细介绍一下PCT的限制。物化视图的PCT特性（一）：http://blog.itpub.net/post/468/21406物化视图的分区变化跟踪特性（PCT）具有以下的限制条件：1.物化视图参考的基表中至少有一个是分区的；2.分区表必须是范围分区或复合分区；3.分区键必须由单列组成；4.物化视图必须包含基表的分区列或分区标志；5.如果物化视图包含GROUP BY语句，则分区列或分区标志必须出现在GROUP BY语句中；6.数据修改只能发生在分区表中；7.兼容性设置COMPATIBLE必须在9.0.0.0.0以上；8.物化视图不能参考远端表、视图或外连 阅读全文
Oracle:物化视图的PCT特性(一)
2011-08-23 16:07 by Tracy., 822 阅读, 收藏, 编辑
摘要：
Oracle的物化视图从9i开始支持了PARTITION CHANGE TRACKING（PCT）功能。本文简单描述一下PCT的概念及PCT的优点。物化视图的PCT特性（二）：http://blog.itpub.net/post/468/21639PCT是基于分区的修改跟踪，如果基表进行了分区，Oracle可以知道物化视图中的每条记录会被基表中的哪个或哪几个分区所影响。PCT带来的优点主要体现在两个方面上：刷新和查询重新。当基表发生DROP PARTITION或TRUNCATE PARTITION操作后，物化视图仍然可以执行快速刷新。而且即使不执行快速刷新，Oracle也不会将这个物化视图中所 阅读全文
创建其他用户下的物化视图报错权限不足
2011-08-23 15:39 by Tracy., 2514 阅读, 收藏, 编辑
摘要：
在创建一个其他用户的物化视图时报错，错误信息为：ORA-01031: 权限不足。以前写过几篇文章，介绍物化视图创建所需要的权限：建立物化视图所需权限（一）：http://yangtingkun.itpub.net/post/468/50672建立物化视图所需权限（二）：http://yangtingkun.itpub.net/post/468/50707建立物化视图所需权限（三）：http://yangtingkun.itpub.net/post/468/50838建立物化视图所需权限（四）：http://yangtingkun.itpub.net/post/468/51163但是目前碰到的问 阅读全文
Oracle:如何计算索引的大小
2011-08-23 10:29 by Tracy., 4617 阅读, 收藏, 编辑
摘要：
上次因为创建索引失败，原因是TEMP临时表空间满，经过测试，索引创建需要的临时表空间大概是索引的大小，所以在执行alter index index_name rebuild online nologging parallel 5语句前，要计算出创建索引需要的临时段的大小。实验如下：SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 阅读全文
在Oracle中怎样查看空间索引的存储空间
2011-08-23 09:54 by Tracy., 4022 阅读, 收藏, 编辑
摘要：
本文只讨论Oracle中最常见的索引，即是B-tree索引。本文中涉及的数据库版本是Oracle8i。 一. 查看系统表中的用户索引 在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。 一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。select count(*) from dba_indexeswhere tablespace... 阅读全文
db file scattered read和sequential read.
2011-08-22 16:41 by Tracy., 839 阅读, 收藏, 编辑
摘要：
1. db file scattered read-DB 文件分散读取这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适的索引，我们可能需要检查这些数据表已确定是否进行了正确的设置。然而这个等待事件不一定意味着性能低下，在某些条件下Oracle 会主动使用全表扫描来替换索引扫描以提高性能，这和访问的数据量有关，在CBO 下Oracle 会进行更为智能的选择，在RBO 下Oracle 更倾向于使用索引。因为全表扫描被置于LRU 阅读全文
非空闲等待事件之：db file scattered read(转)
2011-08-22 16:20 by Tracy., 582 阅读, 收藏, 编辑
摘要：
当会话发布一个读入多个数据块的IO请求时提交该事件。ORACLE会话等待多个连续的数据块(由初始参数DB_FILE_MULTIBLOCK_READ_COUNT指定)从磁盘读入到SGA中。该事件是在全表扫描或索引快速全局扫描时发生的典型事件。初始化参数db_file_multiblock_read_count(MBRC)决定了读取数据块的最大数目，有两种情况会造成多块读取操作被分成几次操作：>> MBRC因子不能跨越整个区，如果一个区包含10个块，MBRC因子=8，那么多块读取会发布两次读取调用，一次8个数据块，一次2个数据块。>> 要读取的多块有，有一部分已在CACHE 阅读全文