AnySQL.net

列类型和变量类型不一致引起的性能问题?

    一条不能再简单的SQL引起了性能问题, Where条件中只有一个列, 并且有索引, 这是为什么呢? 请看下面的例子: SQL> CREATE TABLE T_OBJECTS   2  (   3    COL1 NUMBER, COL2 VARCHAR2(20), COL3 VARCHAR2(30)   4  ); Table created. SQL> CREATE INDEX IDX_T_OBJECTS_1 ON T_OBJECTS(COL1); Index created. SQL> CREATE INDEX IDX_T_OBJECTS_2 ON T_OBJECTS(COL2); Index created. SQL> INSERT INTO...

Posted on December 25, 2006 9:19 PM

Query Rewrite的一般理解之六

    MVIEW的刷新也是一个比较难的话题, 尤其是遇到比较复杂的情况下, 如何实现增量刷新, 为什么现在的MVIEW不能实现增量刷新, 一直是我当初在实施MVIEW时遇到的最大问题, 在Oracle中也提供了一个过程可用于分析MVIEW, 这个过程在DBMS_MVIEW这个包中, 过程名为EXPLAIN_MVIEW. 对这个过程有所了解可以帮助你更好地实现MVIEW的应用. 在输出的信息中包括了是否可以实现增量刷新, 同时也列出了这个实体化视图上支持什么样的查询重写(文字匹配, 或一般函义上的重写).     要使用这个功能, 需要建一个名称为MV_CAPABILITIES_TABLE的表, 可以调用@?/rdbms/admin/utlxmv.sql来创建. 这个表的表结构如下:...

Posted on October 14, 2006 10:05 PM

Query Rewrite的一般理解之五

    对于一个给定的SQL, 和现有的MVIEW, 这个SQL可能被Rewrite, 也可能不能被Rewrite, 如何找出这其中的原因呢, 靠经验可以解决一些问题, 不过所花的时间就要长一点了. 其实在Oracle中提供了一个函数可以用于解释为什么某个SQL不能被重写, 这个过程位于dbms_mview这个包中, 过程名为explain_rewrite, 有了这个工具, 可以快速地找出为什么不能被重写, 要使用这个过程, 你需要事先创建一个表REWRITE_TABLE, 可以通过@?/rdbms/admin/utlxrw.sql来创建....

Posted on October 14, 2006 10:03 PM

Query Rewrite的一般理解之四

   可以看到MVIEW在Query Rewrite中的重要性, 要在实际应用中使用, 就得知道它的很多方面, 其中刷新是最主要的: 1, MVIEW日志的建立2, 汇总型的MIVEW的刷新3, JOIN类型的MVIEW的刷新4, 更复杂的MVIEW的刷新5, 分区时的MVIEW的刷新    在这儿我们主要讨论的是如何实现Fast刷新, 否则没有多少意议的. 我们一点一点来看:...

Posted on October 14, 2006 10:00 PM

Query Rewrite的一般理解之三

    在Query Rewrite中大家看到这个技术离不开一样东西, 实体化视图, 简称MVIEW. 这是Oracle在8i中首先推出的技术, MVIEW除了在Query Rewrite中使用外, 还在Master - Slave复制中有很重要的作用, 在这儿我们主要关心Query Rewrite相关的地方, Oracle在Query Rewrite方面越来越强了, 在Oracle 8i中基本上是Text Match的Query Rewrite, 在9i/10g中有很大的更新了, 还支持一般的Query Rewrite(指Text Match以外的), 如可以试一下最后一个SQL语句, 在8i中不能rewrite, 而在9i中却可以:...

Posted on October 14, 2006 9:53 PM

Query Rewrite的一般理解之二

    在Oracle的Query Rewrite中主要有三点, 第一是要使用CBO; 第二是要设置query rewrite enabled参数为TRUE; 第三是要先择设置query rewrite integrity参数的值(stale_tolerated, trusted, enforced). 对于第一点, 我们最好analyze相关的表及索引及MV; 对于第二点,这个参数只有两个值(true, false), 很简单; 对于第三点, 我们先来看Oracle的官方对于这个参数的解释: ENFORCED   Oracle enforces and guarantees consistency and integrityTRUSTED   Oracle allows rewrites using relationships that have been declared, but that are not enforced by...

Posted on October 14, 2006 9:50 PM

Query Rewrite的一般理解之一

    Query Rewrite 在数据仓库是是一个非常有用的技术, Tom在<<Effective Oracle by Design>>一书中将实体化视图(MView)称为是数据仓库的索引, 这是再贴切不过的了, 在OLTP中当SELECT语句的所有的字段都在索引中时, Oracle可以不从表读数据, 而直接从索引中获得全部信息, 而Query Rewrite则是通过创建中间表, 让Oracle自动从创建的中间表读取数据, 而不需要从原表读取了, 这个中间表可以是预先join好的或预先计算好的中间结果. 他的使用就和一般的索引同理了, 虽然你指定的还是那个大表, 但oracle可以为你自动识别可以从那个"数据仓库索引"中读取数据. 下面我们可以来看一下最简单的例子: SQL> CREATE TABLE DETAIL_TABLE                                       2  AS SELECT OWNER,OBJECT_NAME FROM sys.dba_objects; Table created.SQL> DESC DETAIL_TABLE Name                               Null?    Type  ---------------------------------- -------- -------------- OWNER                                       VARCHAR2(30) OBJECT_NAME                                ...

Posted on October 14, 2006 9:44 PM

数据库优化中什么是星型转换(Star Transform)?

    在数据仓库中经常查询的SQL总带有下列特征: 几个表进行关联 只有一个数据量巨大的表, 称为事实表 其他的都是编码表, 称为维表 维表和事实表之间有主外键关系     假设有D1(key1),D2(key2),D3(key3),D4(key)四个小的维表和一个事实表F(key1,key2,key3,key4), 那么经常进行的查询将是: SELECT   D1.xxx, D2.xxx, D3.xxx, D4.xxx,   SUM(F.xxx), SUM(F.xxx) FROM F, D1, D2, D3, D4 WHERE F.KEY1=D1.KEY1 AND F.KEY2=D2.KEY2   AND F.KEY3=D3.KEY3 AND F.KEY4=D4.KEY4   AND D1.xxx=? AND D2.xxx=?   AND D3.xxx=?...

Posted on October 11, 2006 10:19 AM

利用维对象来优化数据仓库的高级技巧

    在Oracle的数据仓库(OLAP)中, 实体化视图(MVIEW), 查询重写(Query Rewrite)和维(Dimension)是非常重要的优化手段, 对于前两者我不想在这儿重复讲了, 主要来体验一下维的作用. 要发挥维的作用, 还是需要用到前面两者, 下面是我设计的只有一个维表的最简单的例子. 数据库用户除了connect, resource外, 还要给予Query Rewrite, Create Materialized View, Create Dimension权限.     1, 创建一个维护表. CREATE TABLE TIME_DIM AS   SELECT TO_CHAR(SYSDATE+ROWNUM,'YYYY') F_YEAR,          TO_CHAR(SYSDATE+ROWNUM,'YYYY-Q') F_QUATER,          TO_CHAR(SYSDATE+ROWNUM,'YYYY-MM') F_MONTH,          TRUNC(SYSDATE+ROWNUM,'DD') F_DAY...

Posted on October 9, 2006 11:02 PM

通过改变SQL来简化工作, 减轻工作量(二)

    Oracle的分析函数功能强大, 可以用于SQL的优化, 往往用普通的SQL需要好几次表扫描的, 用了分析函数后可以一句话轻松搞定. 我们来看一下统计学生成绩的例子吧.     我不想专门建一个成绩表, 就用DBA_OBJECTS来表示吧, 用OBJECT_TYPE来表示班级或年级, 用OBJECT_ID余100的值来作成绩, 为了减少记录显示, 我只查了"TABLE"和"INDEX"两个班级, 通过使用WIDTH_BUCKET函数可以很容易地实现这一要求, 如下所示: ASQL> SELECT CLASS_ID,SCORE_LEVEL,COUNT(*) NROWS     2 FROM(     3 SELECT     4    OBJECT_TYPE CLASS_ID,     5    WIDTH_BUCKET(MOD(OBJECT_ID,100),60,100,4) SCORE_LEVEL     6 FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','INDEX'))     7 GROUP BY CLASS_ID,SCORE_LEVEL...

Posted on September 19, 2006 8:51 PM

通过改变SQL来简化工作, 减轻工作量(一)

    今天接到一个任务, 将在大约二十几个数据库中运行以下的两个SQL来取某个值, 这个表很大, 大约快100个G了, 因此是一件不小的任务. SELECT /*+ FULL(T) PARALLEL(T,4) */  COUNT(*) FROM BIGTABLE T WHERE COL1 < 1000000000   AND ...... / SELECT /*+ FULL(T) PARALLEL(T,4) */  COUNT(*) FROM BIGTABLE T WHERE COL1 > 1000000000   AND ...... /     因此我将这两个SQL进行合并,...

Posted on September 14, 2006 4:32 PM

如何在较长的文本字段上建立有效的索引?

    索引的键值太大的话会影响索引的效率, 还有可能出错. 但有时我们一定要为这字段建索引的话怎么办呢? 我们可以通过计算一个哈希值来实现. 比如有一个字段为NAME, 最长可能有1024个字符, 要在这个列上建一个索引的话, 可以有两种比较好的方法: 增加一个字段, 如HASH_NAME, 用于保存HASH值. 建一个函数索引.     第一种方法需要有一个建在Insert或Update上的触发器来更新HASH_NAME字段, 第二种方法相对简单, 在Oracle的高版本中, 可以用第二种方法. 需要注意的是, 理论上Oracle的这个计算哈希值的算法并不能保证唯一, 因此在查询时需要注意, 需要写两个条件, 如下所示: HASH_NAME = DBMS_UTILITY.GET_HASH_VALUE(:P_NAME) AND NAME=:P_NAME DBMS_UTILITY.GET_HASH_VALUE(NAME) = DBMS_UTILITY.GET_HASH_VALUE(:P_NAME) AND NAME = :P_NAME     另外在数据库升级中,...

Posted on September 13, 2006 11:17 PM

基于分区统计信息的SQL执行计划的性能问题

    在Oracle 9i的CBO优化器中, 会对有绑定变量的SQL在硬分析时做一次Peaking, 这时如果分区表的各个分区的统计信息不一致不同能导致执行计划不同的话, Peaking就有可能出错了. 下面是我做的一个例子(9.2.0.5的库上测试的).     创建一个测试表: SQL> create table t_partplan   2  (   3    col1 number not null,   4    col2 number not null,   5    col3 varchar2(40),   6   ...

Posted on September 8, 2006 2:16 PM

OTune工具提供的SQL优化的信息

    写OTune的原因是因为OPMon的输出的信息不够方便我们进行SQL的优化, 在改进后程序输出了以下几方面的信息.     1, 执行信息 HASH : SQL语句的HASH_VALUE, [HASH: 1116368370] EXEC : 在报告期内SQL被执行的次数, [Exec:1] GETS : 在报告期内SQL引起的逻辑读的块数及其权重比例, [Gets:378663(4.8)] DISK : 在报告期内SQL引起的物理读的块数及其权重比例, [Disk:0(0.0)] SORT : 在报告期内SQL引起的内存排序的次数, [Sort:1] GET/E : SQL每次执行的平均逻辑读块数, [Get/E:378663] DISK/E: SQL每次执行的平均物理读块数, [Disk/E:0] ROW/E : SQL每次执行的返回的平均记录数, [Row/E:0]...

Posted on August 25, 2006 11:29 AM

指导人家用OTop来查找日志生成过量的原因

    有网友问我为什么他们的库每一分种就生成了1G的日志, 这种问题需要查SQL语句, 但如何找出那个SQL语句生成了过量的日志呢? OTop可以轻松地用"-o"选项来显示最近系统中生成日志最多的SQL语句(使用"-o REDO"选项), 命令如下: otop -u system/manager@prod -o redo -q     运行一分钟后按Control+C退出程序运行, 在运行otop的目录中生成一个文件, 检查文件内容, 发现以下信息(以下信息只显示Top Session中的一部份信息): --VAL/S----PCT----OldPrev-----OldCur----NewPrev-----NewCur   2225K  83.20 1425443843 1425443843 1425443843 1425443843   449K  16.80  981151561  981151561 1425443843 2738583441 --VAL/S----PCT----OldPrev-----OldCur----NewPrev-----NewCur   2248K  97.08...

Posted on August 24, 2006 10:29 PM