ORACLE查询优化及gather

查询优化手段和gather_plan_statistics hint：

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息，具体使用方法如下： 如果在statistics_level参数设置为ALL，或者执行的sql使用了gather_plan_statistics hint，则在sql执行后，会在v sqlplanstatisticsall视图中查到SQL的执行统计信息，例如逻辑读，物理读，基数等等。这些数据对于性能诊断有着非常大的帮助。同时v sql_plan中的执行计划，与通过EXPLAIN PLAN得到的执行计划相比，前者是oracle执行sql时真正使用的执行计划，而后者则可能不是真正的执行计划；同时有的时候，执行过的sql使用了绑定变量，而oracle在解析sql时通常会进行绑定变量窥探，这个时候我们不能使用EXPLAIN PLAN来得到那个sql的执行计划，就算得到的跟那个sql的真实的执行计划是不一样的，所以有时我们更愿意直接从v$sql_plan中得到执行计划。 下面结合tom的文章实例讲一下这个hint得用法：原文：http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html ——————————————————————————— 一个查询调优的例子： SQL> create table t  as  select case when mod(rownum,200000) = 0 then 5              else mod(rownum,4)          end X,         rpad( ‘x’, 100, ‘x’ ) data    from dual  connect by level <= 1000000  /    –此处为了迷惑优化器而制造了分布不均的数据（skew data）  SQL> create index t_idx on t(x); Index created. SQL> exec dbms_stats.gather_table_stats( user, ‘T’ ); PL/SQL procedure successfully completed. SQL> select x, count(*)    from t   group by x   order by x  /  X   COUNT(*) ———————————  ———————————          0     249995          1     250000          2     250000          3     250000          5          5 SQL> select /*+ gather_plan_statistics */         count(data)    from t   where x = 5; / –查看上面查询执行计划 SQL>  select *    from table(          dbms_xplan.display_cursor( format=> ‘allstats last’ )          )  /    PLAN_TABLE_OUTPUT —————————————————————————————————— SQL_ID  cdwn5mqb0cpg1, child number 0 —————————————————————————————————— select /*+ gather_plan_statistics */         count(data)    from t   where x = 5 Plan hash value: 2966233522 ——————————————————————————————————————————————————————————————————————————— | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | ——————————————————————————————————————————————————————————————————————————— |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.08 | |   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.08 | |*  2 |   TABLE ACCESS FULL| T    |      1 |    200K|      5 |00:00:00.08 | ——————————————————————————————————————————————————————————————————————————— Predicate Information (identified by operation id): ———————————————————————————————————————————————————————————————————————————    2 - filter(“X”=5) 20 rows selected. –可以看到执行计划走了全表扫描并且E-Rows，即预估基数为200K而A-rows，即实际返回基数为5差距相当大（a huge difference）。原因就是优化器对X列上分布不均的数据没有察觉。 –此时，我们需要提供X列上的直方图（histogram）让优化器掌控全面的基数信息。 SQL> select histogram     from user_tab_columns    where table_name = ‘T’      and column_name = ‘X’; HISTOGRAM ————— NONE SQL> exec dbms_stats.gather_table_stats( user, ‘T’, no_invalidate=>false ); SQL> select histogram     from user_tab_columns    where table_name = ‘T’      and column_name = ‘X’; HISTOGRAM ————— FREQUENCY —-此处是用NO_INVALIDATE参数使之前的依赖游标失效，保证下次运行该查询执行硬解析（hard parse） –再次执行前面的查询操作，查看运行时计划信息 PLAN_TABLE_OUTPUT —————————————————————————————————— SQL_ID  cdwn5mqb0cpg1, child number 0 —————————————————————————————————— select /*+ gather_plan_statistics */         count(data)    from t   where x = 5 Plan hash value: 1789076273 ———————————————————————————————————————————————————————————————————————— | Id | Operation                    | Name  | Starts | E-Rows | A-Rows | ———————————————————————————————————————————————————————————————————————— |  0 | SELECT STATEMENT             |       |      1 |        |      1 | |  1 |  SORT AGGREGATE              |       |      1 |      1 |      1 | |  2 |   TABLE ACCESS BY INDEX ROWID| T     |      1 |    182 |      5 | |* 3 |    INDEX RANGE SCAN          | T_IDX |      1 |    182 |      5 | ———————————————————————————————————————————————————————————————————————— ————– |   A-Time   | ————– |00:00:00.01 | |00:00:00.01 | |00:00:00.01 | |00:00:00.01 | Predicate Information (identified by operation id): ————————————————————————————————————————————————————————————————————————    3 - access(“X”=5) 21 rows selected. –此时执行计划走的是索引范围扫描且E-Rows已经很接近A-Rows，执行时间也大大缩减。 –这里你可能会犯嘀咕：咋第一次收集表信息时没有生成直方图？ 看下tom的原话: This change is likely due to the default value of the METHOD_OPT parameter used with DBMS_STATS. In Oracle Database 10g, the METHOD_OPT parameter defaults to a SIZE AUTO. After you ran a query, the database remembered the predicates and updated a dictionary table, SYS.COL_USAGE .Then,thenexttimeyouranDBMSSTATStogatherstatisticsonthesetables,DBMSSTATSqueriedthattabletofindoutwhatcolumnsshouldhavehistogramscollectedautomatically,basedonpastqueryworkload.Itlookedatyourpredicatesandsaid,“Hmm,thesecolumnsarecandidatesforhistogramsbasedonthequeriestheendusershavebeenrunning.”听起来很神奇吧？事实却是如此！△补充1：关于管道函数dbmsxplan.displaycursor(format=>‘allstatslast′)要解释下几个参数的取值含义：—functiondisplaycursor(sqlidvarchar2defaultnull,—cursorchildnointegerdefault0,—formatvarchar2default‘TYPICAL′)——−sqlid:指定SQLID取值为V SQL.SQL_ID, V SESSION.SQLID,或者V SESSION.PREV_SQL_ID，如果不指定则默认为最后执行语句SQL_ID   —   - cursor_child_no: 指定sql游标的子号，取值为V SQL.CHILDNUMBER或者inV SESSION.SQL_CHILD_NUMBER,V$SESSION.PREV_CHILD_NUMBER， 此参数只有指定sql_id情况下才有效。如果不指定，则指定sql_id下所有子游标都显示。   —   - format:   —         指定输出列，值取自视图：v$sql_plan_statistics_all.   —   —        IOSTATS:   假设基本计划统计在SQL执行时已收集（使用gather_plan_statistics提示或设置statistics_level参数为ALL） —               此格式将展示所有游标执行的（或仅最后执行游标）IO统计。   —        MEMSTATS:  —   假设PGA内存管理开启（例如：pga_aggregate_target参数设置为非0值），此格式允许展示内存管理统计  （例如：操作执行模式，内存使用，溢出到磁盘字节数）   —        ALLSTATS: 指定’IOSTATS MEMSTATS’的快捷命令   —   —        LAST:    —              此格式就是指定仅显示最后执行sql的统计   —   —        Also, the following two formats are still supported for backward   —        compatibility: 另外以下2个参数为了向后兼容而保留   —   —        ‘RUNSTATS_TOT’:  Same as ‘IOSTATS’, i.e. displays IO statistics   —                         for all executions of the specified cursor.   —        ‘RUNSTATS_LAST’: Same as ‘IOSTATS LAST’, i.e. displays the runtime   —                         statistics for the last execution of the cursor.   —   权限要求:              必须具有：SELECT ON V SQLPLANSTATISTICSALL,V SQL, 和 V SQLPLAN权限。△补充2：还可以使用另一路径查询SQL运行时执行计划：HR@orcl>select/∗+gatherplanstatisticsempplan∗/count(1)fromemployees;COUNT(1)———−107HR@orcl>SELECTSQLID,CHILDNUMBERFROMV SQL  WHERE SQL_TEXT LIKE '%emp_plan%' and SQL_TEXT NOT LIKE '%V SQLSQLIDCHILDNUMBER————−————2c5q2d8489ybt0HR@orcl>SELECT∗FROMTABLE(DBMSXPLAN.DISPLAYCURSOR(‘2c5q2d8489ybt′,0,′ALLIOSTATSLAST′));PLANTABLEOUTPUT———————————————————————————————————————————–SQLID2c5q2d8489ybt,childnumber0————————————−select/∗+gatherplanstatisticsempplan∗/count(1)fromemployeesPlanhashvalue:3580537945—————————————————————————————————————————−|Id|Operation|Name|Starts|E−Rows|Cost(—————————————————————————————————————————−|0|SELECTSTATEMENT||1||1(100)||1|00:00:00.02|1|1||1|SORTAGGREGATE||1|1|||1|00:00:00.02|1|1||2|INDEXFULLSCAN|EMPEMAILUK|1|107|1(0)|00:00:01|107|00:00:00.02|1|1|—————————————————————————————————————————−QueryBlockName/ObjectAlias(identifiedbyoperationid):————————————————————−1−SEL 1    2 - SEL 1/EMPLOYEES@SEL 1 Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ———————————————————————————————————————————– ———————————————————–    1 - (#keys=0) COUNT(*)[22] 已选择25行。

—————————————

Dylan    Presents.