精华内容
下载资源
问答
  • oracle SQL 星期 算法

    千次阅读 2008-12-03 17:15:00
    SQL操作中可能会遇到这类计算,例如,统计上周一到上周日的订单数量。 计算方法如下:方法: 充分利用trunc函数和next_day函数:next_day(date,day) :给出日期date和星期x(周日 x=1 周一 x=2 周二 x=3 ...周六...

      美国人把周日当成一周的开始周六当成周结束,而我们常常需要将周一当成一周的开始,周日当成一周结束。在SQL操作中可能会遇到这类计算,例如,统计上周一到上周日的订单数量。
      计算方法如下:


    方法:
      充分利用trunc函数和next_day函数:

    next_day(date,'day') :给出日期date和星期x(周日 x=1 周一 x=2 周二 x=3 ...周六=7)之后计算下一 个星期的日期。

    trunc 按照指定的精度进行舍入,注意这个函数是直接截断,和round函数有区别

    例子:
    select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

    ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)

    ----------- ------------ ----------- ------------

    56          -55          55          -55



    计算方法:

    本周:

    周一:trunc(next_day(sysdate - 8, 1) + 1)
    周二:trunc(next_day(sysdate - 8, 1) + 2)
    周三:trunc(next_day(sysdate - 8, 1) + 3)
    周四:trunc(next_day(sysdate - 8, 1) + 4)
    周五:trunc(next_day(sysdate - 8, 1) + 5)
    周六:trunc(next_day(sysdate - 8, 1) + 6)
    周日:trunc(next_day(sysdate - 8, 1) + 7)

    可以看出,要计算某周周一的起始日期,那么就直接使用周一到周日中任何一天的日期date替换这里的sysdate即可计算出日期所在周一和周日。


    应用 :
     统计上周成功订单总数:
    1. select count(*) from orders o where o.gmt_create>=trunc(next_day(sysdate - 81)-6)
    2.  and o.gmt_create<trunc(next_day(sysdate - 81)+1)
    3.  and o.status=5

    补充:JAVA中得到本周一,本周日的方法如下:
     
    1.         Calendar c = Calendar.getInstance();
    2.         c.setFirstDayOfWeek(Calendar.MONDAY);// 指定周一为一周第一天
    3.         c.setTime(new Date());
    4.         c.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);// 本周周一的时间
    5.         c.set(Calendar.HOUR_OF_DAY, 0);
    6.         c.set(Calendar.MINUTE, 0);
    7.         c.set(Calendar.SECOND, 0);
    8.         Date monday = c.getTime();// 当前周一的日期
    very easy.关键就是要设置 c.setFirstDayOfWeek(Calendar.MONDAY);


    bugfix: 之前求周一是trunc(next_day(sysdate - 7, 1) + 1)  这个方法无法避免周日这一天计算错误(周日临界问题)。因此修改为
            trunc(next_day(sysdate - 8, 1) + 1) ,这样就可以按照中国人的习惯 周一~周日 都算 本周



    展开全文
  • 浅析oracle sql_id和sql hash

    千次阅读 2018-09-30 16:31:02
    oraclesql id其实是通过md5算法sql text转化成hash值 所以sql text不变,sql id就不会变,无论在什么版本的oracle数据库中。   md5换算sql text为sql id的脚本: #!/usr/bin/perl -w use Digest::MD5 qw...

    oracle的sql id其实是通过md5算法把sql text转化成hash值

    所以sql text不变,sql id就不会变,无论在什么版本的oracle数据库中。

     

    md5换算sql text为sql id的脚本:

    #!/usr/bin/perl -w
    use Digest::MD5 qw(md5 md5_hex md5_base64);
    use Math::BigInt;
    my $stmt = "select sysdate from dual\0";
    my $hash = md5 $stmt;
    my($a,$b,$msb,$lsb) = unpack("V*",$hash);
    my $sqln = $msb*(2**32)+$lsb;
    my $stop = log($sqln) / log(32) + 1;
    my $sqlid = '';
    my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
    my @chars = split '', $charbase32;
    for($i=0; $i < $stop-1; $i++){
    my $x = Math::BigInt->new($sqln);
    my $seq = $x->bdiv(32**$i)->bmod(32);
    $sqlid = $chars[$seq].$sqlid;
    }

    运行

    $ perl sql_id.pl
    SQL is:
     select sysdate from dual 
    SQL_ID is
     7h35uxf5uhmm1

    数据库中的sql id: 

    SQL> select SQL_TEXT,sql_id from v$sql where SQL_TEXT like 'select sysdate from dual%';
    
    SQL_TEXT                       SQL_ID
    ------------------------------ -------------
    select sysdate from dual       7h35uxf5uhmm1

    sql id是一样的。

     

    9i中sql的标识就是hash value,10g新增sql id

    简言之,9i之前通过hash value标识sql,9i之后通过sql id标识sql

    oracle提供了包来转换sql id和sql hash value。dbms_utility.SQLID_TO_SQLHASH

    SQL> select sql_id,hash_value,dbms_utility.SQLID_TO_SQLHASH(sql_id) convert from v$sql where rownum <9;
    
    SQL_ID        HASH_VALUE    CONVERT
    ------------- ---------- ----------
    0gbrycmp8400u 3934388250 3934388250
    d4p9hk6qvn00z 2914648095 2914648095
    0uq3xcgr1h011 3994550305 3994550305
    3t09d4n4us01a  162267178  162267178
    72y05ahu6401f  878837806  878837806
    19u4dgffqh01n 2640838708 2640838708
    baz7d946qw01t  225312825  225312825
    fr03m9qgnh01v 2672295995 2672295995
    

     

    展开全文
  • Oracle 分页算法汇总

    2010-04-09 18:47:06
    这是我找到的一些关于Oracle分页的算法,大家那里还有没有其他好的算法没?我们大家一起分享一下! [code="sql"] -- Oracle 分页算法一 select * from ( select t.*,rownum rn from (select * from...
    这是我找到的一些关于Oracle分页的算法,大家那里还有没有其他好的算法没?我们大家一起分享一下!
    


    -- Oracle 分页算法一
    select * from (
    select t.*,rownum rn from (select * from help) t
    -- 20 = (currentPage-1) * pageSize + pageSize
    where rownum <= 20
    )
    -- 10 = (currentPage-1) * pageSize
    where rn > 10;

    -- Oralce 分页算法二
    -- 20 = (currentPage-1) * pageSize + pageSize
    select * from help where rownum<=20
    minus
    -- 10 = (currentPage-1) * pageSize
    select * from help where rownum<=10;

    -- 12c最新的分页方式
    -- 返回前3行
    select * from help where 1 = 1 order by info fetch next 3 rows only;
    -- 返回并列排名的行
    select * from help where 1 = 1 order by info fetch next 3 rows with ties;

    -- 分页
    select * from help where 1 = 1 order by info offset 10 rows fetch next 1 rows only;

    -- 返回10%行
    select * from help where 1 = 1 order by info fetch first 10 percent rows only;
    展开全文
  • Oracle SQL优化 总结

    千次阅读 2012-09-25 19:53:00
    Oracle SQL优化 总结 分类: Oracle Performance My Blog Summary 2011-11-24 17:05 6216人阅读 评论(9) 收藏 举报 目录(?)[+] 一.SQL 编写注意事项 SQL 编写的具体注意事项多表关联方式 ...

    Oracle SQL优化 总结

    分类: Oracle Performance My Blog Summary 6216人阅读 评论(9) 收藏 举报

     

    之前的blog中零零散散的整理了一些优化相关的内容,找起来比较麻烦,所以总结一下,查看的时候方便一点。这篇BLog只看SQL 优化的相关的注意事项,数据库优化部分以后有空在整理。

     

    SQL 的优化主要涉及几个方面:

    (1)    相关的统计信息缺失或者不准确

    (2)    索引问题

    (3)    SQL 的本身的效率问题,比如使用绑定变量,批量DML 采用bulk等,这个就考验写SQL的基本功了,这一点也是最主要的一点。

     

    一.SQL 编写注意事项

     1.1 查看SQL

    对于生产环境上的SQL,可以从AWR 或者 Statspack 报告中获取相关的SQL 信息。

     

    这部分参考:

    Oracle AWR 介绍

    http://blog.csdn.net/tianlesoftware/article/details/4682300

     

    statspack安装使用 和report 分析

    http://blog.csdn.net/tianlesoftware/article/details/4682329

     

           查看SQL 的性能怎么样,最直接的工具就是通过执行计划,通过执行计划可以看到SQL 的执行路径,逻辑读,物理读等信息,可以这些信息,可以帮助我们判断SQL 是否还有优化的余地。

     

    1.2 SQL 编写的具体注意事项

           这部分工作是基本功。 在SQL 编写过程中, 避免一些低效的写法,能将SQL的效率提高几倍。 如:

     

    to_char(created,'yyyy') = '2011'

    trunc(created,'y') = to_date('01-jan-2011','dd-mon-yyyy')

     

    与使用TRUNC 相比,使用TO_CHAR 所用的CPU 时间与前者相差一个数量级(即相差12倍)。因为TO_CHAR 必须把日期转换为一个串,这要使用一个更大的代码路径,并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面,TRUNC 只需把后5 个字节设置为1.然后将两个7 字节的二进制数进行比较。因此,如果只是要截断一个DATE 列,你将应该避免使用TO_CHAR。

     

    之前从网上转载了一篇文章,链接如下:

    Oracle SQL的优化

    http://blog.csdn.net/tianlesoftware/article/details/4672023

     

    这是几年前转载的文章,其中内容有些也有误,这里就不更正了。

     

    1.3 多表关联方式

           表之间的关联有如下三种方式:

    (1)    Nested Loop

    Inner table 循环与outer table匹配,这种是表有索引,选择性较好,表之间的差距不大。 ===》两层for 循环,小表匹配大表。

    (2)    Hash John

    小表做hash ,放内存,然后拿大表的每条记录做hash,然后与之前小表的Hash 值匹配。==》大表匹配小表。

    (3)    Sorted Merge Into

    表有序,并且没有索引。

     

    具体参考:

           多表连接的三种方式详解 HASH JOIN MERGE JOINNESTED LOOP

           http://blog.csdn.net/tianlesoftware/article/details/5826546

     

    二. 相关理论说明

    2.1 Oracle 优化器:CBO 和 RBO

    Oracle 的优化器有两种:

                       RBO(Rule-BasedOptimization): 基于规则的优化器

                       CBO(Cost-BasedOptimization): 基于代价的优化器

     

    CBO(Cost Based Optimizer)的思路是让Oracle 获取所有执行计划相关的信息,通过对这些信息做计算分析,最后得出一个代价最小的执行计划作为最终的执行计划。

           从10g开始,Oracle 已经彻底丢弃了RBO。 即使在表,索引没有被分析的时候,Oracle依然会使用CBO。此时,Oracle 会使用一种叫做动态采样的技术,在分析SQL的时候,动态的收集表,索引上的一些数据块,使用这些数据块的信息及字典表中关于这些对象的信息来计算出执行计划的代价,从而挑出最优的执行计划。

     

    当表没有做分析的时候,Oracle 会使用动态采样来收集统计信息,这个动作只有在SQL执行的第一次,即硬分析阶段使用,后续的软分析将不在使用动态采样,直接使用第一次SQL硬分析时生成的执行计划。

     

    相关链接:

    Oracle Optimizer CBO RBO

    http://blog.csdn.net/tianlesoftware/article/details/5824886

     

    Oracle CBO 与 RBO

    http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5709784.aspx

     

    Oracle 分析及动态采样

    http://blog.csdn.net/tianlesoftware/article/details/5845028

     

     

    2.2 软解析和硬解析

    Oracle对此SQL将进行几个步骤的处理过程:

        1、语法检查(syntax check): 检查此sql的拼写是否语法。

        2、语义检查(semantic check): 诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

        3、对sql语句进行解析(prase): 利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。

        4、执行sql,返回结果(execute and return)

     

    其中解析分为:

     Hard Parse: 就是上面提到的对提交的Sql完全重新从头进行解析(当在Shared Pool中找不到时候将会进行此操作),总共有一下5个执行步骤:

              1:语法分析

              2:权限与对象检查

             3: 在共享池中检查是否有完全相同的之前完全解析好的. 如果存在,直接跳过4和5,运行Sql, 此时算soft parse.

             4:选择执行计划

             5:产生执行计划

     

    注:创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。这就是在很多项目中,倡导开发设计人员对功能相同的代码要努力保持代码的一致性,以及要在程序中多使用绑定变量的原因。

     

    Soft Parse: 就如果是在Shared Pool中找到了与之完全相同的Sql解析好的结果后会跳过Hard Parse中的后面的两个步骤。

     

    Oracle SQL的硬解析和软解析

    http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

     

    Oracle 高 Version counts 问题说明

    http://blog.csdn.net/tianlesoftware/article/details/6628232

     

    Oracle SQL Parsing FlowDiagram(SQL 解析流程图)

    http://blog.csdn.net/tianlesoftware/article/details/6625683

     

    那么执行计划放在内存的什么位置,在一下的Blog 有说明:

    Oracle Library cache 内部机制 说明

    http://blog.csdn.net/tianlesoftware/article/details/6629869

     

    与解析相关的一个重要参数:cursor_sharing,它决定什么情况下使用相同的cursor,从某种意义上讲,决定是否需要进行解析,该参数有3个值:

    (1)FORCE

           Allowsthe creation of a new cursor if sharing an existing cursor, or if the cursorplan is not optimal.

     (2)SIMILAR

           Causesstatements that may differ in some literals, but are otherwise identical, toshare a cursor, unless the literals affect either the meaning of the statementor the degree to which the plan is optimized.

     (3)EXACT

           Onlyallows statements with identical text to share the same cursor.

           --只有SQL 语句完全相同的情况下,才会使用相同的cursor,即执行计划。

     

    Oracle cursor_sharing 参数 详解

    http://blog.csdn.net/tianlesoftware/article/details/6551723

     

    2.3 执行计划

    生成SQL的执行计划是Oracle在对SQL做硬解析时的一个非常重要的步骤,它制定出一个方案告诉Oracle在执行这条SQL时以什么样的方式访问数据:索引还是全表扫描,是Hash Join还是Nested loops Join等。 

     

    Oracle 执行计划(Explain Plan) 说明

    http://blog.csdn.net/tianlesoftware/article/details/5827245

     

    Oracle 从缓存里面查找真实的执行计划

    http://blog.csdn.net/tianlesoftware/article/details/6556850

     

    Oracle Recursive Calls 说明  

    http://blog.csdn.net/tianlesoftware/article/details/6561620

     

    我们也可以使用OracleHint 来强制的改变SQL的执行计划,当然Oracle 不建议这么做,因为只要统计信息正确的情况下,CBO 的分析就过一般都是正确的。

     Oracle Hint

    http://blog.csdn.net/tianlesoftware/article/details/5833020

     

    2.4 10053 和 10046 事件

    2.4.1 10053事件

    我们在查看一条SQL的执行计划的时候,只能看到CBO 最终告诉我们的执行计划结果,但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真,如:一个SQL语句,很明显oracle应该使用索引,但是执行计划却没有使用索引。无法进行分析判断。

    10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划,为什么会得到这样的执行计划信息。

     

    对于10053事件的trace文件,我们只能直接阅读原始的trace文件,不能使用tkprof工具来处理,tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。

     

    10053事件有两个级别:

             Level2:2级是1级的一个子集,它包含以下内容:

    Column statistics

    Single Access Paths

    Join Costs

    Table Joins Considered

    Join Methods Considered (NL/MS/HA)

             Level1: 1级比2级更详细,它包含2级的所有内容,在加如下内容:

    Parameters used by the optimizer

    Index statistics

     

    启用10053事件:

    ALTER SESSION SET EVENTS='10053 trace namecontext forever, level 1';

    ALTER SESSION SET EVENTS='10053 trace namecontext forever, level 2';

     

    关闭10053事件:

    ALTER SESSION SET EVENTS '10053 trace namecontext off';

     

     

    说明:

    (1)sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的,explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

    (2)10053只对CBO有效,而且如果一个sql语句已经解析过,就不会产生新的trace信息。

     

    2.4.2 10046 事件:

    10046 事件主要用来跟踪SQL语句,它并不是ORACLE 官方提供给用户的命令,在官方文档上也找不到事件的说明信息。 但是用的却比较多,因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。

     

    10046 事件按照收集信息内容,可以分成4个级别:

    Level 1: 等同于SQL_TRACE 的功能

    Level 4: 在Level 1的基础上增加收集绑定变量的信息

    Level 8: 在Level 1 的基础上增加等待事件的信息

    Level 12:等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。


     --启动10046事件

      SQL>alter session set events‘10046 tracename context forever, level 12’; 


    -- 关闭10046事件

    SQL>alter session set events ‘10046 trace name context off’; 


    也可以使用oradebug 命令来执行10046:

    SYS@anqing1(rac1)> oradebug setmypid

    SYS@anqing1(rac1)> oradebug event 10046trace name context forever,level 8;

    SYS@anqing1(rac1)> oradebug event 10046trace name context off;

    SYS@anqing1(rac1)> oradebugtracefile_name

    /u01/app/oracle/admin/anqing/udump/anqing1_ora_17800.trc

     

    具体的内容参考:

    Oracle oradebug 命令 使用说明

    http://blog.csdn.net/tianlesoftware/article/details/6525628

     

    Oracle 跟踪事件 set event

    http://blog.csdn.net/tianlesoftware/article/details/4977827

     

    Oracle 10053 事件

    http://blog.csdn.net/tianlesoftware/article/details/5859027

          

    Event 10053 执行计划绑定变量 Bind peeking

    http://blog.csdn.net/tianlesoftware/article/details/5544307

     

    Oracle SQL Trace 和 10046 事件

    http://blog.csdn.net/tianlesoftware/article/details/5857023

     

    使用 Tkprof 分析 ORACLE 跟踪文件

    http://blog.csdn.net/tianlesoftware/article/details/5632003

     

    2.5 统计信息

     优化器收集的统计信息包括如下内容:

               1)Table statistics

                            Number of rows

                            Number of blocks

                            Average row length

               2)Column statistics

                            Number of distinctvalues (NDV) in column

                            Number of nulls incolumn

                            Data distribution(histogram)

               3)Index statistics

                            Number of leaf blocks

                            Levels

                            Clustering factor

               4)System statistics

                            I/O performance and utilization

                            CPU performance andutilization

     

     

    统计信息收集如下数据:

    (1)表自身的分析: 包括表中的行数,数据块数,行长等信息。

    (2)列的分析:包括列值的重复数,列上的空值,数据在列上的分布情况。

    (3)索引的分析: 包括索引叶块的数量,索引的深度,索引的聚合因子等。

     

    这些统计信息存放在数据字典里,如:

    (1).  DBA_TABLES

    (2).  DBA_OBJECT_TABLES

    (3).  DBA_TAB_STATISTICS

    (4).  DBA_TAB_COL_STATISTICS

    (5).  DBA_TAB_HISTOGRAMS

    (6).  DBA_INDEXES

    (7).  DBA_IND_STATISTICS

    (8).  DBA_CLUSTERS

    (9).  DBA_TAB_PARTITIONS

    (10).                      DBA_TAB_SUBPARTITIONS

    (11).                      DBA_IND_PARTITIONS

    (12).                      DBA_IND_SUBPARTITIONS

    (13).                      DBA_PART_COL_STATISTICS

    (14).                      DBA_PART_HISTOGRAMS

    (15).                      DBA_SUBPART_COL_STATISTICS

    (16).                      DBA_SUBPART_HISTOGRAMS

     

    统计信息的准确程度,直接决定SQL的效率。 所以需要定期的收集相关对象的统计信息。Oracle 的Statistic 信息的收集分两种:自动收集和手工收集。

     

    Oracle 的Automatic StatisticsGathering 是通过Scheduler 来实现收集和维护的。 Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息:

                (1)Missing statistics(统计信息缺失)

                (2)Stale statistics(统计信息陈旧)

     

    该Job 是在数据库创建的时候自动创建,并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下,job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计信息。

     

    Scheduler Job的 stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False,那么GATHER_STATS_JOB 会中断,而没有收集完的对象将在下次启动时继续收集。

     

    Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下:

    (1)对象的统计信息之前没有收集过。

    (2)当对象有超过10%的rows 被修改,此时对象的统计信息也称为stale statistics。

     

    Oracle Statistic 统计信息 小结

    http://blog.csdn.net/tianlesoftware/article/details/4668723

     

    Oracle 判断 并 手动收集 统计信息 脚本

    http://blog.csdn.net/tianlesoftware/article/details/6445868

     

     

    三.索引

    3.1 索引分类

    索引对DB的性能中起着重要的作用。 Oracle 有如下类型的索引:

    B树索引(默认类型)
    位图索引
    HASH索引
    索引组织表索引
    反转键(reverse key)索引
    基于函数的索引
    分区索引(本地和全局索引)
    位图连接索引

     

    3.2 索引限制

    这部分内容应该放到SQL 编写部分,不过为了强调,还是放到这块。

     

    即使相关字段上有索引,在如下4种情况,也不会走作引:

    (1)    使用不等于操作符(<>、!=)    

    (2)    使用IS NULL 或IS NOT NULL

    (3)    使用函数

    (4)    比较不匹配的数据类型  

     

    所以在SQL 编写过程中,尽量避免以上4种情况。

     

    具体参考:

    Oracle 索引详解

    http://blog.csdn.net/tianlesoftware/article/details/5347098

     

    3.3 索引维护

    这部分内容包括:

    (1)  索引的选择性: distinct/rows, 接近与1,选择性越高,直方图--》数据倾斜。

    (2) 索引的扩展:index segment 是由extents组成,如果extents大于10,可以考虑重建索引。

    (3) 索引碎片:查询index_stats表以确定索引中删除的、未填满的叶子(Leaf)行的百分比 和 height 字段。 如果索引的叶子行的碎片超过10%,或者 index_stats中height > =4, 可以考虑对索引进行重建。

                  select name,height, del_lf_rows, lf_rows,round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent" from index_stats

     

    具体参考:

    索引维护:

    http://blog.csdn.net/tianlesoftware/article/details/5680706

     

    3.4 索引的 Clustering Factor 参数

           这个参数是个神奇的参数,先看一种情况,有的人应该遇到过,就是表的字段上有索引,但根据这个字段做查询时,却发现Oracle并没有使用索引?  并且查询条件没有限制索引。那么Oracle 为什么不走索引? 很可能就是和这个参数值有关。

           可以从dba_indexes 表里查看到每个具体对象的Clustering Factor值:

     select owner,index_name, clustering_factor, num_rows from dba_indexes whereowner='SYS' and index_name='IDX_T_ID';

     

           该参数反应的是数据在物理block的上的连续性,如果该值接近于对象的block数,那么数据在block上的存储就是有序的,如果接近与表的行数,可以反应出数据的存储无序。

           当数据有序时,我们查询一个数据时,可以从一个block里一次性读出,如果无序,那么我们可能就需要读取多个block,这样I/O次数增加,CBO 就会认为这种代价更大,从而选择全表扫描来代替索引。从而导致即使有索引,也不会走。

           但是随着系统使用时间的越长,Clustering Factor值是会越来越大的。解决这个问题的唯一方法就是对表进行move。

     

    具体参考:

    Oracle Index Clustering Factor 说明

    http://blog.csdn.net/tianlesoftware/article/details/6585453

     

    3.5 索引扫描5种类型:

    (1)index unique scan: 查询结果返回一行记录

    (2)index range scan: 查询结果返回多行记录。

     

    (3)index full scan:可能进行全Oracle索引扫描而不是范围扫描,需要注意的是全Oracle索引扫描只在CBO模式下才有效。      CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。

     

    (4)index fast full scan: 与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。

     

    (5)index skip scan: INDEX SKIP SCAN,发生在多个列建立的复合索引上,如果SQL中谓词条件只包含索引中的部分列,并且这些列不是建立索引时的第一列时,就可能发生INDEX SKIP SCAN。这里SKIP的意思是因为查询条件没有第一列或前面几列,被忽略了。

     

    具体参考:

    Oracle 索引扫描的五种类型

    http://blog.csdn.net/tianlesoftware/article/details/5852106

     

    四.绑定变量

    这个也是SQL 编写的基本功.  简单的说,使用绑定变量可以避免进行硬解析,减少对资源的消耗。

     

    Oracle里的所有SQL 语句都是implicitly sharable的。 SQL 在执行之前,要通过一个hash 运算,生成相关的cursor。 如果通过hash 运算之后,发现已经了有对应的cursor,那就可以直接使用之前的cursor 和plan。如果不存在,就需要进行硬解析,而硬解析是一个非常耗资源的操作。需要尽量减少硬解析。

    如何保证每次HASH 运算之后的hash 值都一样,那么这就需要通过绑定变来来实现。

     

    在第一次执行执行SQL 之后,如果使用了绑定变量,那么Oracle 在硬解析的时候,会进行一个叫Peek的操作。 也可以称为偷窥。就是把实际值带进去,帮助产生更加准确的执行计划。比如对应的Peek列上有严重的数据倾斜,假设我们已经对表进行了统计信息收集,oracle 会产生该列的直方图(histogram),在peek的时候,就会根据直方图来决定,是走索引还是走全表扫描更划算。

     

    因此第一执行产生的执行计划肯定是最优的。现在假设数据倾斜有2个值,一个占90%,一个占10%。 我们第一使用10%的值,所以第一次走索引。 那么如果我们以后在绑定时,使用了90%的值,那么这时候,Oracle 还是会使用之前的cursor,继续走索引,此时走索引就不是最优的了。

    这个就是Oracle 10g里绑定变量的一个问题。 在第一次硬解析之后,以后所有的操作都会使用之前产生的cursor 和plan。所以在10g里,对于列上有严重数据倾斜的,最好是不采用绑定变量。

     

    在Oracle 11g里,对这个问题,进行了优化,使用了Adaptive Cursor Sharing,它可以产生多个共享cursor。如果是90%的值,就使用cursor 1,如果是10%的cursor,就使用corsor 2. 在这个转换的过程中还是有可能再次产生硬解析。

     

    Oracle 11g的绑定变量处理过程如下:

           当我们第一去执行一个带有绑定变量的SQL时,Oracle 会进行硬解析,但是硬解析不能确定最优的执行计划,所以这时候有了Peek。 也可以说是偷窥,即把实际值带入,来生成一个selectivity estimate。 然后来选择最优的一个执行计划来执行。

           这是第一次执行SQL语句。以后执行时就会使用已经存在的plan和cursor。 Oracle 通过Adaptive Cursor Sharing特性允许同一个SQL 可以使用多个执行计划。

           在每次执行时,Oracle会根据Peek 的selectivity estimate 值和直方图(如果存在)来判断已经存在的cursor 是否是最优的,如果不是,就重新创建一个child cursor,并讲Bind-Sensitive 标记为Y。

           而且Oracle在SQL 每次执行时,都会收集相关的统计信息,然后根据统计信息进行判断,如果比上次的更好,就在创建一个child cursor,并将Bind-Aware 标记为Y。

           当标记为bind-aware cursor 的cursor在下次执行时,Oracle根据新的bind value 来生成新的plan和cursor,并将原来的cursor标记为非共享,即V$SQL.IS_SHAREABLE 设置为 N,当这种cursor 长期不被使用时, 就会被移出shared SQL area.

           在bind-aware cursor创建新的cursor 之后,如果这个cursor 和之前某个存在的cursor一样,那么Oracle 会对他们进行合并。

           如果在cache里不能找到bind-aware对应的plan,那么就会重新进行一次硬解析,来生成plan 和cursor,如果这个plan 以后被新的cursor 使用,那么Oracle 会将这2个cursor 进行合并。

     

    Oracle 绑定变量 详解

    http://blog.csdn.net/tianlesoftware/article/details/5856430

     

    Oracle 绑定变量 示例

    http://blog.csdn.net/tianlesoftware/article/details/6324243

     

    Oracle 10g 与 11g 绑定变量(Bind Variable) 区别 说明

    http://blog.csdn.net/tianlesoftware/article/details/6591222

     

    对于绑定变量的cursor 能否重用,与Cursor_sharing 参数有很大关系。具体参考:

    Oracle cursor_sharing 参数 详解

    http://blog.csdn.net/tianlesoftware/article/details/6551723

     

    Oracle Library cache 内部机制 说明

    http://blog.csdn.net/tianlesoftware/article/details/6629869

     

     

    五.其他与SQL 性能相关的链接

     

    Oracle Sequence Cache 参数说明

    http://blog.csdn.net/tianlesoftware/article/details/5995051

     

    Oracle bulk 示例

    http://blog.csdn.net/tianlesoftware/article/details/6599003

     

    Oracle Bulk 与 性能优化 说明

    http://blog.csdn.net/tianlesoftware/article/details/6578351

     

    Oracle Pipelined TableFunctions 与 性能优化 说明

    http://blog.csdn.net/tianlesoftware/article/details/6601540

     

    Oracle arraysize 和 fetchsize 参数 与 性能优化 说明

    http://blog.csdn.net/tianlesoftware/article/details/6579913

     

    Oracle 利用 rowid 提升 update 性能

    http://blog.csdn.net/tianlesoftware/article/details/6576156

     

    Oracle Parallel Execution(并行执行)

    http://blog.csdn.net/tianlesoftware/article/details/5854583

     

     

     

    展开全文
  • ORACLE SQL执行步骤

    千次阅读 2009-11-11 13:52:00
    以前理解的 ORACLE sql 的处理过程大致如下: 1.运用HASH算法,得到一个HASH值,这个值可以通过V$SQLAREA.HASH_VALUE 查看 2.到shared pool 中的 library cache 中查找是否有相同的HASH值,如果存在,则无需硬...
  • ORACLE SQL 优化

    千次阅读 2007-12-09 14:39:00
    共享SQL区专门存放用户SQL命令,oracle使用最近最少使用等优先级算法来更新覆盖;数据字典缓冲区(library cache)存放数据库运行的动态信息。数据库运行一段时间后,DBA需要查看这些内存区域的命中率
  • hash join (Oracle里的哈希连接原理) 参考: https://www.cnblogs.com/shangyu/p/6055181.html 转载于:https://www.cnblogs.com/xiaohuizhenyoucai/p/10997815.html...
  • ORACLE SQL语句优化技术分析

    千次阅读 2012-07-04 10:26:13
    ORACLE SQL语句优化技术分析 操作符优化  IN 操作符  用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。  但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用...
  • ——Oracle SQL查询优化 (1) 数据读取方式 1、 Oracle数据读取操作: 1.1数据读取操作类型:  在任何的数据库中,当我们发起一个SQL语句进行数据查询时,SQL执行引擎为了能够返回我们需要的数据,都会进行...
  • ORACLE SQL 优化(二)

    2009-07-27 00:00:00
    ORACLE SQL 优化(二)一、 先介绍一下...共享SQL区专门存放用户SQL命令,oracle使用最近最少使用等优先级算法来更新覆盖;数据字典缓冲区(library cache)存放数据库运行的动态信息。数据库运行一段时间后,DBA需
  • Hibernate中的Oracle分页算法

    千次阅读 2012-08-25 09:43:58
    private static String getLimitString(String sql, boolean hasOffset) { sql = sql.trim(); boolean isForUpdate = false; if (sql.toLowerCase().endsWith(" for update")) { sql = sql.substring(0
  • Oracle SQL语句处理过程

    2009-01-15 15:51:00
    Oracle SQL语句处理过程http://martri.javaeye.com/blog/73029关键字: sql 07年开始,换了一家公司,数据库使用的是ORACLE10G,以前一直使用的是INFORMIX和MSSERVER,感觉ORACLE功能还真强大,比Informi和msserver都...
  • oracle sql执行过程分析

    千次阅读 2011-10-19 21:02:17
    oracle中存在这样两种语句类型:DDL(create、drop、alter、truncate)、DML(insert、update、delete、select)。 DDL语句是从来不会共享使用的,也就是说每次执行都是硬...oracle对DML类sql语句的执行步骤如下:
  • Oracle SQL性能优化

    千次阅读 2011-12-31 00:03:12
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表...
  • Oracle SQL优化之COUNT

    千次阅读 2018-11-20 15:51:44
    先看看Oracle SQL优化中关于COUNT的几条谣言: 1. COUNT(*)比COUNT(列)更慢!项目组必须用COUNT(列),不准用COUNT(*); 2. COUNT(*)用不到索引,COUNT(列)才能用到; 3. COUNT是统计出全表的记录,是吞吐量的操作...
  • oracle sql书写规范

    千次阅读 2014-08-12 10:00:18
    1、sql编写时,大小写一致 2、关键字单占一行,如select、from、where、and、group by、order by等 3、注意行缩进和对齐,建议语句中的关键字右对齐 4、使用空格,SQL语句内的算术运算符、逻辑运算符(AND、OR、...
  • Oracle SQL语句处理过程

    千次阅读 2007-05-06 22:17:00
    07年开始,换了一家公司,数据库使用的是ORACLE10G,以前一直使用的是INFORMIX和MSSERVER,感觉ORACLE功能还真...现结合以前的工作经验和学习的资料,总结ORACLE数据库的一些知识,今天先写Oracle SQL语句处理过程: 
  • Oracle sql语句执行顺序

    千次阅读 2013-07-05 09:53:05
    sql语法的分析是从右到左   一、sql语句的执行步骤: 1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义。 2)语义分析,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。 3...
  • Oracle SQL优化文章不必再看

    千次阅读 2015-11-18 12:32:57
    【重磅干货】看了此文,Oracle SQL优化文章不必再看! 2015-11-18 丁俊 DBAplus社群 听“俊”一席话,胜读十年书。看了这篇由DBA+社群联合发起人丁俊大师(网名:dingjun123)分享的SQL优化大作,其他Oracle...
  • Oracle SQL语句执行步骤

    2014-10-17 09:50:49
    OracleSQL语句执行过程中,Oracle内部解析原理如下: 1、当一用户第一次提交一个SQL表达式时,Oracle会将这SQL进行Hard parse,这过程有点像程序编译,检查语法、表名、字段名等相关信息(如下图),这过程会花...
  • 我们讨论了 MySQL、OracleSQL Server、PostgreSQL 以及 SQLite 实现连接查询的三种算法:嵌套循环连接 Nested Loop Join、哈希连接 Hash Join 以及排序合并连接 Sort Merge Join。了解这些算法的原理和优缺点可以...
  • ORACLE SQL 优化的若干方法详细

    千次阅读 2013-10-09 14:33:42
    ORACLE SQL 优化的若干方法详细 ...共享SQL区专门存放用户SQL命令,oracle使用最近最少使用等优先级算法来更新覆盖;数据字典缓冲区(library cache)存放数据库运行的动态信息。数据库运行一段
  • oracle sql方面 (2013-04-28 14:58:21) 转载▼ 标签: sql oracle it 分类:oracle技能 SQL方面: 对字符串的“sum”——在Oracle中自定义聚集函数的例子:如果利用Oracle的自定义功能实现用户自定义的...
  • oracle SQL语句执行步骤

    2013-02-19 10:39:42
    OracleSQL语句执行过程中,Oracle内部解析原理如下:  1、当一用户第一次提交一个SQL表达式时,Oracle会将这SQL进行Hardparse,这过程有点像程序编译,检查语法、表名、字段名等相关信息(如下图),这过程会花比较...
  • Oracle SQL日期比较和常用日期函数

    万次阅读 2016-04-22 09:21:56
    一、oracle sql日期比较 在今天之前:select * from up_date where update to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') select * from up_date where update to_date('2007-09-07 00:...
  • Oracle SQL的硬解析和软解析

    万次阅读 热门讨论 2010-04-07 16:35:00
    我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析,这里面又分为软解析和硬解析。在Oracle中存在两种类型的SQL语句,一类为 DDL语句(数据定义语言),他们是从来不会共享使用的,也就是每次执行都需要进行...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 70,556
精华内容 28,222
关键字:

oraclesql算法