【Maclean Liu技术分享】拨开Oracle优化器迷雾探究Histogram之秘

【Maclean Liu技术分享】拨开Oracle CBO 优化器迷雾, 探究Histogram直方图之秘,讲座文档正式版已上传


http://t.askmaclean.com/thread-2172-1-1.html

预计时长: 1.5个小时

适合参与成员: 对于性能调优和CBO优化器有兴趣的同学,或急于提升SQL调优技能的同学。

教学视频已上传 , 收看请猛击下面的地址:

 

 

 

【Maclean Liu技术分享】Histogram直方图技术演示脚本如下:

【Maclean Liu技术分享】Histogram直方图技术演示脚本.txt (9.93 KB, 下载次数: 65)

讲座材料presentation 当前正式版本下载:

【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘 20130429.pdf (1.11 MB, 下载次数: 3646)

 
 

 

Oracle BYPASS_UJVC HINT提示说明

本文地址:https://www.askmaclean.com/archives/bypass_ujvc.html

 

 

  • 本资料中设计到的 BYPASS_UJVC 提示是没有在oracle公司的语句件中记载的提示语语句。
  • 不支持使用BYPASS_UJVC提示,或者不能从安全性的角度来使用。
  • 指定BYPASS_UJVC 提示,发生了不太恰当的操作时,也无法执行修正。
  • 使用BYPASS_UJVC提示时,需要参考本资料,将其变更为不使用提示语语句的处理。
  • 为了删除BYPASS_UJVC提示,需要在实际环境中进行充分的验证。

 

可以更新的结合视图与ORA-1779

[参考] 样本数据

BYPASS_UJVC1

 

create or replace view sample_v1 as

  select empno,ename,d.deptno,dname,loc

  from emp e,dept d

  where e.deptno = d.deptno (+)

  order by 1;

 

BYPASS_UJVC2

 

可以更新的结合视图 (Updatable Join View)

BYPASS_UJVC3

 

更新视图时的注意事项

  • 对于视图的DML操作的限制中,应用如下基准。
    • 视图的定义中使用包含SET或者DISTINCT运算符、GROUP BY语句或者group函数查询时,无法对其视图的实际表插入、更新、或者删除行。
    • 视图的定义中使用了WITH CHECK OPTION,从实际表中无法选择行时,无法通过视图对实际表的行进行插入或者更新。
    • 没有DEFAULT语句的NOT NULL列会在视图中被省略,无法通过视图对实际表的行进行插入或者更新。
    • 制成视图时如果使用DECODE(deptno, 10, “SALES”, …)类似式子的话,就无法通过视图对实际表的行进行插入或者更新。

 

更新可以更新的视图时的注意事项

 

  • 视图的各列需要映射单一表的列。
  • 视图中,不能包含以下内容。
    • 集合运算符
    • DISTINCT运算符
    • 总计功能或者分析功能
    • GROUP BY、ORDER BY、MODEL、CONNECT BY或者START WITH语句
    • SELECT列表中的收集式
    • SELECT列表中的副查询
    • WITH READ ONLY指定的副查询
    • 结合 (有例外 … <下章会谈到>)
  • 可以更新的视图包含疑似的列或者式子时,使用参考这些疑似列或者式子的更新语句时,无法更新实际环境中的表的行。
  • DELETE语句中,通过结合,制成保存多个key的表的话,不管视图是否指定WITH CHECK OPTION来制成,都会从FROM语句中所指定的最开始的表中删除。

 

 

通过结合视图更新时的注意事项

 

  • DML语句仅仅会影响结合基础的1个表。
  • UPDATE语句
    • 所以可以更新的结合视图的列都需要对保存key的表的列进行映射
    • 在视图的定义中,使用WITH CHECK OPTION语句时,所有的结合列以及重复表的列都无法更新。
  • DELETE语句
    • 结合中保存key的表只有一个时,无法在结合视图中删除行。
    • 这个保存可以的表可以使用FROM语句来重复。
    • 视图的定义中,使用了WITH CHECK OPTION语句,重复保存key表示,无法从视图中删除行。
  • INSERT语句
    • INSERT语句中,请不要参考没有保存key的表。
    • 结合视图的定义中,使用WITH CHECK OPTION语句时,无法使用INSERT语句

 

 

可以更新的结合视图的确认方法

 

SQL> select table_name,column_name,updatable,insertable,deletable
  2  from user_updatable_columns, user_views
  3  where view_name = table_name;

TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ --- --- ---
SAMPLE_V1                      EMPNO                          YES YES YES
SAMPLE_V1                      ENAME                          YES YES YES
SAMPLE_V1                      DEPTNO                         NO  NO  NO
SAMPLE_V1                      DNAME                          NO  NO  NO
SAMPLE_V1                      LOC                            NO  NO  NO

BYPASS_UJVC4

 

ORA-01779

ORA-01779

[JP] 无法对没有保存key的表的列进行映射。

[EN] cannot modify a column which maps to a non key-preserved table

结合的结果

  • 可以更新保存key的表的列
  • 对于不是保存key的表的列进行更新时,就会发生ORA-01779

BYPASS_UJVC5

 

 

保存key的表 (Key-Preserved Table)

保存key的表是指什么 ….

所有表的key都是结合的结果key,其表就会变成保存key的表换言之,保存key的表就是指结合之后依旧保存了key的表。

 

BYPASS_UJVC6

 

样本 #1

BYPASS_UJVC7

样本 #2

BYPASS_UJVC8

样本 #3

BYPASS_UJVC9

 

样本#4:UPDATE语句内的子查询

BYPASS_UJVC10

 

 

[参考] 样本#4event 10046

 

Update (
select empno,ename,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno (+) order by 1)
set ename='ORACLE' where empno=7369
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          3          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          3          3           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58  (SCOTT)
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  EMP (cr=3 pr=0 pw=0 time=595 us)
      1   NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=154 us)
      1    TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=113 us)
      1     INDEX UNIQUE SCAN PK_EMPNO (cr=1 pr=0 pw=0 time=56 us)(object id 51936)
      1    INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=27 us)(object id 51934)
update /*+ BYPASS_UJVC */ (
select empno,ename,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno (+) order by 1)
set dname='ACCOUNTING' where empno=7499
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          1           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          1           1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58  (SCOTT)
Rows     Row Source Operation
-------  ---------------------------------------------------
0  UPDATE  DEPT (cr=3 pr=0 pw=0 time=199 us)
1   NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=59 us)
1    TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=35 us)
1     INDEX UNIQUE SCAN PK_EMPNO (cr=1 pr=0 pw=0 time=20 us)(object id 51936)
1    INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=10 us)(object id 51934)

本文地址:https://www.askmaclean.com/archives/bypass_ujvc.html

[参考] ORA-01779以外的ORA错误

  • ORA-01732
    • [JP] 这个视图中数据操作无效
    • [EN] data manipulation operation not legal on this view
    • INSERTABLE是对NO表执行INSERT语句时发生的错误
      • 即使指定列,也无法插入NULL

 

  • ORA-01752
    • [JP] 无法从多个表的视图中删除
    • [EN] cannot delete from view without exactly one key-preserved table
    • DELETABLE是对NO表执行DELETE语句时发生的错误

 

BYPASS_UJVC提示

 

制成优化的执行计划时,对UJVC(Updatable Join View) 测试进行bypass的提示语句

 

BYPASS_UJVC11

 

BYPASS_UJVC 提示的利用的悬念点

 

  • 没有document化的提示语句
    • 不支持对象
  • 更新的对象行数不同
    • 基于对象行数的处理中,可能会产生业务逻辑错误
  • 更新了意料之外的数据
    • 由于视图化,可能会更新之前隐藏的数据

 

更新的对象行数不同

BYPASS_UJVC12

 

 

[参考] event 10046 的结果

 

update /*+ BYPASS_UJVC */ sample_v1 set loc = 'BOSTON‘ where deptno = 20
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          3          0           0
Execute      1      0.00       0.00          0          8          7           5
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0         11          7           5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58  (SCOTT)
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  DEPT (cr=8 pr=0 pw=0 time=2301 us)
      5   SORT ORDER BY (cr=8 pr=0 pw=0 time=1600 us)
      5    HASH JOIN OUTER (cr=8 pr=0 pw=0 time=1437 us)
      5     TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=218 us)
      1     INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=24 us)(object id 51934)

 

本文地址:https://www.askmaclean.com/archives/bypass_ujvc.html

更新了意料之外的数据

BYPASS_UJVC13

 

面向废止BYPASS_UJVC提示的指导线

 

简介迁移的指导线

  • 指定对应地址
  • 在每个批量处理的模型中执行
  • à高效使用 Real Application Testing
  • 改写对应地址

1.使用MERGE语句

  • 将从源表中搜索到数据结果记录在USING语句中,通过在ON语句中记录这个结果的结合条件就可以不使用UJV来记录

2.使用○ WHERE EXISTS语句

  • 通过在WHERE EXISTS语句中记录结合条件,就可以改写不使用视图以及inline视图的SQL语句
  • 比MERGE语句的SQL语句稍微复杂一点

3.使用△Instead of 触发器

 

Oracle Real Application Testing 概要

BYPASS_UJVC14

  • Database Replay
    • 吞吐量的性能测试
    • 修正应用同时执行性的问题
  • SQL Performance Analyzer
    • SQL的响应时间测试
    • 指定SQL重定向以及调优

本文地址:https://www.askmaclean.com/archives/bypass_ujvc.html

Database Replay概要

  • 利用正式数据库的负载进行性能测试
    • 维持正式环境中特有的时机以及事务的依赖性、思考时间
  • 测量与测试事务的吞吐量
  • 指定应用的可扩展性,新功能的并行性
  • 执行Oracle Database 9iR2以及10gR2更新

 

BYPASS_UJVC改写对应地址

方法 内容 平均 是否推荐
案1 使用MERGE语句 将从源表中搜索到的结果记录在USING语句中,将这个见过的结合条件记录在ON语句中,就可以不使用UJV来记录 直观上来说,便于理解,并且可以改写成简单的SQL语句
案2 使用WHERE EXISTS语句 通过在WHERE EXISTS语句记录结合条件,就可以不使用视图以及inline视图来改写SQL语句 SQL语句比MERGE语句要复杂
案3 使用Instead of 触发器 使用BEFORE触发器更新 使用起来太复杂,不现实

 

使用MERGE语句

MERGE语句

从源表中搜索数据,根据条件不同,可以在一个SQL语句中对对象表执行插入与更新

MERGE INTO 表名1
  USING 表名2 |副查询
  ON ( 结合条件 )
WHEN MATCHED THEN
  UPDATE SET 列名1 = 值1, 列名2 = 值2, ・・・
WHEN NOT MATCHED THEN
  INSERT [( 列名1, 列名2, ・・・ )] VALUES ( 值1, 值2, ・・・ );

 

MERGE语句的注意事项

  • 无法成为Fine-grained access control的对象
  • 使用INTO语句的话,就可以在更新或者插入对象的表、视图中进行指定。对视图映射数据时,需要更新视图。
  • 必须保证ON语句的对象列的一致性

 

使用MERGE语句 (样本)

 

BYPASS_UJVC15

 

[参考] event 10046 的结果

使用了结合视图的BYPASS_UJVC 提示的更新

 

update /*+ BYPASS_UJVC */
  (
  select empno,ename,d.deptno,dname,loc
  from emp e,dept d
  where e.deptno=d.deptno)
set loc = 'BOSTON' where ename = 'SMITH'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          8          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          8          3           1
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  DEPT (cr=8 pr=0 pw=0 time=899 us)
      1   NESTED LOOPS  (cr=8 pr=0 pw=0 time=366 us)
      1    TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=306 us)
      1    INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=46 us)(object id 53317)

 

使用了MERGE语句 的更新

merge into dept d
  using (select deptno from emp where ename = 'SMITH' ) v
  on ( v.deptno = d.deptno)
  when matched then update set loc='BOSTON'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          9          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.03          0          9          2           1
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  MERGE  DEPT (cr=9 pr=0 pw=0 time=0 us)
      1   VIEW  (cr=9 pr=0 pw=0 time=0 us)
      1    NESTED LOOPS  (cr=9 pr=0 pw=0 time=0 us)
      1     NESTED LOOPS  (cr=8 pr=0 pw=0 time=0 us cost=5 size=29 card=1)
      1      TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1)
      1      INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 83537)
      1     TABLE ACCESS BY INDEX ROWID DEPT (cr=1 pr=0 pw=0 time=0 us cost=1 size=20 card=1)

 

所有WHERE EXISTS语句 (样本)

使用了结合视图的BYPASS_UJVC 提示的更新

SQL> update /*+ BYPASS_UJVC */
  2    (
  3      select empno,ename,d.deptno,dname,loc from emp e,dept d
  4      where e.deptno=d.deptno)
  5  set loc = 'BOSTON' where ename = 'SMITH';
1 row updated.

 

通过WHERE EXISTS语句的改写

SQL> update dept d
  2    set loc = 'BOSTON'
  3    where exists (
  4      select * from emp e
  5      where e.deptno=d.deptno and e.ename = 'SMITH')
  6  ;
1 row updated.

 

通过WHERE EXISTS语句进行的改写

 

update dept d
  set loc = 'BOSTON'
  where exists (
    select * from emp e
    where e.deptno=d.deptno and e.ename = 'SMITH')
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.05          0          0          0           0
Execute      1      0.00       0.00          0          8          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.05       0.06          0          8          2           1
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  DEPT (cr=8 pr=0 pw=0 time=0 us)
      1   NESTED LOOPS  (cr=8 pr=0 pw=0 time=0 us cost=6 size=20 card=1)
      1    SORT UNIQUE (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1)
      1     TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1)
      1    INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=0 size=11 card=1)(object id 83537)
其他改写
BYPASS_UJVC16
总结
  • BYPASS_UJVC提示语句
    • BYPASS_UJVC提示是没有被文件化的提示语句。
    • 因为存在安全性的风险,所以尽可能不要使用这个来修正应用会比较好。
    • 11g无视了这个提示语句,导致了ORA-01779错误发生。
  • ORA-1779错误
    • 对于不是保存key的表的列进行更新时发生
    • 可以利用Real Application Testing (付费Option)进行高效指定对应SQL语句。
  • 回避BYPASS_UJVC提示
    • 使用MERGE语句
    • 使用WHERE EXISTS语句
    • (使用Instead of 触发器)

 


【CBO Optimizer优化器】IX_SEL索引选择率

ix_sel – Index selectivity 索引选择率是Oracle中CBO 基于成本优化器重要的参考指标 ,反应了符合谓词条件 通过索引主导列访问表上数据行的比例。(ix_sel – Index selectivity. Fraction of the table rows accessed by the indexes leading column in order to meet the predicate supplied. (10053)。

 

注意仅仅leading column即索引的主导列用作计算ix_sel

举一个简单的计算ix_sel的例子:

SQL> create index ind_maclean on sh.sales( prod_id,CUST_ID,TIME_ID);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SH’,’SALES’,cascade=>true,method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

 

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever ,level 1;
Statement processed.
SQL> explain plan for select * from sh.sales where prod_id=13 and CUST_ID=987;

Explained.

 

 

 

 
BEGIN Single Table Cardinality Estimation
—————————————–
Column (#1): PROD_ID(NUMBER)
AvgLen: 4.00 NDV: 72 Nulls: 0 Density: 0.013889 Min: 13 Max: 148
Column (#2): CUST_ID(NUMBER)
AvgLen: 5.00 NDV: 5828 Nulls: 0 Density: 1.7159e-04 Min: 2 Max: 100989
Table: SALES Alias: SALES
Card: Original: 924076 Rounded: 2 Computed: 2.20 Non Adjusted: 2.20
—————————————–
END Single Table Cardinality Estimation
—————————————–
Access Path: TableScan
Cost: 414.20 Resp: 414.20 Degree: 0
Cost_io: 389.00 Cost_cpu: 215902675
Resp_io: 389.00 Resp_cpu: 215902675

www.askmaclean.com
Access Path: index (RangeScan)
Index: IND_MACLEAN
resc_io: 5.00 resc_cpu: 37017
ix_sel: 2.3831e-06 ix_sel_with_filters: 2.3831e-06
Cost: 5.00 Resp: 5.00 Degree: 1

 

 

 

ix_sel= 1/ (72*5828)=2.3831e-06

 

对于 Equality predicates 且变量可见(硬绑定或 绑定可窥视) IX_SEL=1 / (NDV1* NDV2*..)

 

同样变量可见情况下>、<开放范围 IX_SEL=(MAX- 代入的范围值) / (MAX-MIN)

 

 

 

而变量不可见(cursor_sharing=FORCE、_optim_peek_user_binds=false)的情况:

 

1、Equality predicates 等式谓词情况下,IX_SEL一般等于列的Density

 

2、 对于> <大于、小于的开放范围谓词 ix_sel一般恒等于0.009,  对于 (object_id>:i and object_id<:b;)的闭包则恒等于 0.0045

例如:

select count(*) from test where object_id>:i

Access Path: index (IndexOnly)
Index: TEST_IDX
resc_io: 3.00 resc_cpu: 160764
ix_sel: 0.009 ix_sel_with_filters: 0.009
Cost: 3.02 Resp: 3.02 Degree: 1
Best:: AccessPath: IndexRange Index: TEST_IDX
Cost: 3.02 Degree: 1 Resp: 3.02 Card: 3869.30 Bytes: 0

 
select count(*) from test where object_id>:i and object_id<:b

Access Path: index (IndexOnly)
Index: TEST_IDX
resc_io: 2.00 resc_cpu: 84043
ix_sel: 0.0045 ix_sel_with_filters: 0.0045
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange Index: TEST_IDX
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 193.47 Bytes: 0

ix_sel的 0.009和0.0045 都是写死在代码里的常数值,具体可以参考下表:

 

/* defaults */
 
/* Default selectivities are set low to
 
keep cost values low for future resource limiter use
keep cost values low for permutation cutoff in kko
Defaults are used for bind variables, general expressions and
 
unanalyzed tables, except for equality where defaults are not
 
needed for bind variables.
 
*/
 
#define KKEDSREL 0.05 /* default selectivity for < <= > >= */
#define KKEDSEQ 0.01 /* default selectivity for = */
#define KKEDSNE 0.05 /* default selectivity for != */
#define KKEDSDF 0.05 /* default selectivity for all other ops */
#define KKEDSIRL 0.009 /* default selectivity for relation on indexed col */
#define KKEDSBRL 0.009 /* def sel for relation with bind var on index col*/
#define KKEDSIEQ 0.004 /* default selectivity for = on indexed col */
#define KKEDMBR 8 /* default multiblock read factor */
#define KKEDMBW 8 /* default multiblock write factor */
#define KKEDFNR 100.0 /* default – fixed table cardinality */
#define KKEDFRL 20 /* default – fixed table row length */
#define KKEDDNR 2000.0 /* default – remote table cardinality */
#define KKEDDRL 100 /* default – remote table avg row length */
#define KKEDDNB 100 /* default – default # of blocks */
#define KKEDDSC 13.0 /* default – default scan cost */
#define KKEDILV 1 /* default – default index levels */
#define KKEDILB 25 /* default – number of index leaf blocks */
#define KKEDLBK 1 /* default – number leaf blocks/key */
#define KKEDDBK 1 /* default – number of data blocks/key */
#define KKEDKEY 100 /* default – number of distinct keys */
#define KKEDCLF (KKEDDNB*8) /* default – clustering factor */
#define KKECRI 1.5 /* remote table access cost increase factor */
#define KKECFSC 1.0 /* fixed table scan cost */
#define KKECFNB 0 /* fixed table number of blocks */
#define KKECMXB 15 /* maximum byte length for normalization */
#define KKECBBS 256.0 /* base for byte sequence normalization */
#define KKECSPC ‘ ‘ /* space byte value */
#define KKECSPD 86400.0 /* seconds per day */
#define KKESROH 10.0 /* sort per row overhead in bytes */
#define KKESAUT 0.75 /* sort area utilization */
#define KKESROP 0.10 /* sort row overhead percent */
#define KKESRML 2.0 /* sort run multiple */
#define KKESTP 0x01 /* single table predicate */
#define KKETEQ 0x02 /* equi join */
#define KKETBCPJ 0x04 /* Cartesian product join */
#define KKESOK 0x08 /* input swap ok */
#define KKESWP 0x10 /* inputs swapped */
#define KKEEQP 0x20 /* equipartitioned */
#define KKELKNWC 0x01 /* LIKE no wild card */
#define KKELKTWC 0x02 /* LIKE trailing wild card */
#define KKELKEWC 0x04 /* LIKE embedded wild card */
#define KKELKLWC 0x08 /* LIKE leading wild card */
#define KKELKOWC 0x10 /* LIKE only wild card */

 

 

 

实践Oracle 性能调优-从诊断到解决

 

本文永久地址: https://www.askmaclean.com/archives/实践oracle-性能调优-从诊断到解决.html

前言

  • 本资料中,包含oracle数据库的系统中产生性能问题时,将对调查原因到完成调优为止,进行指导。
    • 本资料面向对数据库具有基相关知识的人士。
    • 因此,发生性能问题时的对策,仅仅讲解确认数据库是否有性能问题的方法,最多讲解处理性能问题的1个方法,但这也不是绝对的
  • 请作为系统发生性能问题时的参考资料来灵活使用

发生了系统性能问题

发生了系统性能问题

 

解决的顺序

为了解决问题,需要处理与考虑方法

  1. 指定出现性能问题的地方
  2. 执行调优

性能问题解决步骤

确认DB性能问题

获得DB内部的统计信息,确认其是否发生了性能问题

DB内部的统计信息是指

性能统计信息(ex. 资源使用情况以及发生待机的信息)

执行各SQL时的详细信息

<方法>

Statspack(8i~)

AWR报告(10g~)

※Enterprise Edition中需要Diagnostic Pack选项

SQL 追踪(主要在测试环境中使用)

 

<参考>其他的推荐事先获得的信息

OS统计信息

  -CPU统计信息

-磁盘统计信息

-内存统计信息

-网络统计信息

-运行进程统计信息

OS中的统计信息因为含有包含oracle以外的部分的服务器整体的资源信息

所以即使原因可能出在DB中,也可以获得

性能参考体系

分析需要了解Statspack是什么

在使用DB内部的统计信息进行分析之前,先需要获得统计信息

理解各个方法的概要

<方法>

Statspack(8i~)

AWR报告(10g~)

※Enterprise Edition中需要Diagnostic Pack选项

SQL追踪(主要在测试环境中使用)

 

Statspack(Statistics Package)

Statspack 的意思是

从Oracle 8i 安装的oracle标准的性能分析工具

使用Statspack 的话

某段期间内,还可以确认oracle的运行情况

基本的Statspack使用流程

  1. 安装之后,可以定期获得统计信息
  2. 发生问题时,会将累积的统计信息生成报告
  3. 比较现有的报告(必要的话还可以同时比较过去的报告)进行分析

 

确认Statspack 的的信息,确认在执行SQL时,到底是什么原因导致了性能恶化

 

Statspack机制

Statspack是两个不同时点分别作为snapshot来记录,仅仅从差异中输出报告

statspack

灵活地输出报告

输出报告时,可以指定使用任何snapshot,可以灵活地变更输出范围

statspack1

参考>通过Statspack获得的信息(9iR2,10g,11g)

leve水平(获得的信息详细程度)设定参考基准

一般正常运行时默认为Level 5,如果感觉到“最近变得有点慢了”的时候,可以遵循oracle技术支持的意见调整为Level 6Level 7、或者Level 10(高负荷)

 

Snapshot level 收集数据
基本统计信息 建议信息 SQL统计信息 SQL详细信息 段信息 latch详细信息
Level 0
Level 5
Level 6
Level 7
Level 10

 

Statspack 使用顺序

Statspack的使用顺序如下所示

Step1 安装・设定

首次使用Statspack时,可以创建执行用的用户,同时可以设定密码与表空间

Step2  获得snapshot

根据情况不同自动获得snapshot level,或者调整snapshot level

Step3 创建报告

选择开始与终止的snapshot 输入文件名

使用了Statspack的脚本已经在导入了DB的环境中准备好了,只要连接到DB,就可以执行脚本

 

执行安装脚本

Statspack相关的操作全部都是通过执行用用户(PERFSTAT)来执行的,所以需要创建执行安装脚本的PERFSTAT用户

1.通过SQL*Plus,作为拥有SYSDBA权限的用户来连接DB

2.执行安装脚本(spcreate.sql)

# 以SYSDBA权限连接到SQL*Plus

SQL> connect sqlplus / as sysdba

# 执行安装・脚本

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

 

设定密码与表空间

设定PERFSTAT 用户的密码、在Statspack中所使用的默认表空间以及临时表空间

1.执行脚本,PERFSTAT用户的密码,默认表空间,临时表空间

# 设定执行用户的密码

在perfstat_password中输入值:输入password

# 设定默认表空间

在default_tablespace中输入值:输入表空间名

# 设定临时表空间

在temporary_tablespace中输入值:输入表空间名

默认表空间推荐多设定300MB的冗余

 

获得snapshot

用PERFSTAT用户连接到DB,通过snapshot来获得统计信息

1.通过PERDSTAT用户执行statspack.snap

2.必要的话可以通过i_snap_level => 来设定获得信息的详细程度(level)(用户没有指定level时,以默认值的level5来获得)

#获得 snapshot

SQL> execute statspack.snap(I_snap_level => 6);

l记录重复snapshot,通过在某个时间点的snapshot,作为报告来输出,可以获得特定期间内的DB统计信息

 

报告的创建

1.通过PERDSTAT用户来执行spreport.sql

SQL> @?/rdbms/admin/spreport.sql  # 创建报告脚本を执行

    ~省略~                                    Snap

Instance   DB Name   Snap Id    Snap Started               Level   Comment

————  —————  ———–  ——————————  ———  —————

orcl1         ORCL                  1    17 10月 2008 12:17            5

                                            11    20 10月 2008 09:29             5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

在begin_snap中输入值:

 

输入snapshot ID

指定开始、终止获得统计信息范围的时间点的snapshot ID,输出报告

1.参考Snap Id,输入起始snapshot以及终止snapshot ID

2.输入报告名

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# 输入起始snapshot ID

请在begin_snap中输入值: 输入Snap Id 例:1

# 输入终止snapshot ID

请在end_snap中输入值: 输入Snap Id 例:11

#输入 报告名

请在report_name中输入值: 输入报告名>

删除snapshot

获得的性能统计信息只要不明确地删除,无论何时都会被保留,根据不同情况可能会挤压储存区域

因此,请删除不需要的统计信息

1.通过PERFSTAT用户来执行sppurge.sql

2.指定想删除的范围的起始于终止时点,指定snapshot ID

SQL> @?/rdbms/admin/sppurge.sql   #执行删除的脚本

Specify the Lo Snap Id and Hi Snap Id range to purge

 

Statspack 实际的报告输入例

 

 

STATSPACK report for
Database      DB Id    Instance  Inst Num         Startup Time      Release      RAC
~~~~~~~~  -------------- ------------ -------------   -----------------------    --------------
    1196126054    orcl1              1   17-10月-08 11:40    11.1.0.7.0    YES
Host         Name              Platform                CPUs   Cores   Sockets Memory (G)
~~~~ -------------------------  ---------------------- ----- -----  ---------  ------------ -----------------
     jpintl005.jp.ora   Linux IA (32-bit)          8          8             2          4.0
Snapshot       Snap Id     Snap Time                  Sessions    Curs/Sess Comment
~~~~~~~~    ---------- ---------------------------------- -------------   --------- ------------------
Begin Snap:         36    21-10月-08 19:46:15           38            1.0
End Snap:         37    21-10月-08 19:47:51           38            1.0
Elapsed:                1.60 (mins) Av Act Sess:       0.4 
・			
・			
・			
・
・
・
SQL ordered by Elapsed time for DB: ORCL  Instance: orcl1  Snaps: 36 -37
-> Total DB Time (s):              42
-> Captured SQL accounts for  195.5% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time
Elapsed                Elap per            CPU                        Old
Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
40.69            1      40.69   95.9      40.67               0 3417014992
Module: SQL*Plus
declare   v_num number; begin   for i in 1..10000 loop     selec
t c1 into v_num from t1 where c1 = 100;   end loop; end;
40.48       10,000       0.00   95.4      40.46               0 2968801179
Module: SQL*Plus

AWR报告是指什么

AWR自动负载库

从Oracle 10g开始会自动获得DB内部的统计信息(snapshot)

AWR是使得Statspack进化的功能

  • 与Statspack相同,可以经常观察到以低负荷来创建各种报告
  • 通过Enterprise Manager(EM),可以变更、查看设定内容
  • 可以通过ADDM来自动进行性能减少/诊断

oracle-awr-1

 

AWR在创建DB时,也会在SYSAUX表空间中创建                      -不需要安装

MMON(Memory Monitor)会定期获得SGA的信息

-默认1小时内保存1次snapshot(11g中是8天)

-想查看、变更设定内容的话,需要Oracle EM可以使用

执行自动删除旧数据

oracle-awr-2

比较StatspackAWR的报告

statspackvsawr

 

Statspack 报告的分析

分析点

  • 吞吐量与负荷(Load Profile)
  • 实例效率(Instance Efficiency)
  • 待机 项目(Top-5 Timed Events)
  • SQL的详细信息(SQL ordered by ~)

特别是在正式环境中使用时,根据业务内容与时间段不同,所收集到的统计信息也会不同,所以我们推荐多多比较多个报告

Load Profile 负荷状況的读取

伴随着磁盘访问,我们需要着眼于是否会有数据的读入、写入,在DB的处理中,找出容易成为瓶颈的物理I/O

awr-load-profile

Instance Efficiency 实例效率.

确认DB缓冲区高速缓存命中率,确认实例是否高效运行

越接近100%就越好
参考値大于80%-% Non-Parse CPU
90%以上-Buffer Hit%、Optimal W/A Exec%、Sort Parse%
95%以上-Library Hit%、Redo Nowait%、Buffer Nowait%
100%以上-Latch Hit%

awr-instance-efficiency

 

参数名 説明
Buffer Hit% 高速缓存中所拥有的需要的数据的比例
Library Hit % Library cache中所拥有的需要的数据的比例
Soft Parse % 所有解析中可以重复利用的比例
In-Memory Sort% Sort在内存中执行的比例
Latch Hit% 所有latch的命中率
Parse CPU to Parse Elapsed % 解析CPU时间/ 解析的总计时间
Execute to Parse% 因为执行SQL而没执行解析的比例
%non-parse CPU 解析之外所使用的CPU时间的比例
Buffer Nowait% 高速缓冲区中提出需求时,可以马上应用的比例
Redo Nowait% 对redo log提出需求时,可以马上应用的比例

 

Top-5 Timed Events 待机 项目

检查实例level中待机时间前五的项目,确认监视对象的实例性能是否降低

Waits :项目待机的总次数

Time(s) :项目总计待机时间以及总计CPU时间(秒)

Avg wait(ms) : 项目平均待机时间

% Total Call Time: time for each timed event / total call time                                                 Total call time total CPU time + total wait time for non-idle events

 

awr-top5-timed-event

等待事件是什么?

  • 进程没有使用CPU的时间
  • 理想待机 项目(SQL的需求等待)
    • 有瓶颈时,意味着原因并不是DB资源
  • 其他待机 项目(SQL执行中)
    • DB资源(缓冲区竞争、I/O竞争、latch竞争等等)相关的待机时间

 

通过对SQL执行中的待机 项目与CPU使用时间进行调优 可以加快响应时间

awr-wait-event

SQL ordered by ~ SQL的处理信息

确认各SQL读入的缓冲区数以及磁盘的访问次数,确认是否使用了资源使用量较高的SQL

 

awr-sql-order

 

SQL trace是什么?

使用SQL trace的话就可以获得比执行SQL执行时更详细的信息通过分析刚刚获得的信息,就可以指定出现问题的SQL

 

SQL trace的获得方法如下所示
1. 获得所有会话信息的方法
2. 获得指定的会话信息的方法

获得的信息中,各个SQL包含以下信息

  1. 语句的分析、执行,fetch的执行次数
  2. CPU时间、消耗时间
  3. 物理读入(Physical read)、理论读入(Logical read)
  4. 处理的行数

 

SQL trace使用顺序

获得所有会话信息时

1. 设定初始化参数SQL_TRACE为TRUE

2. 重启Oracle
重启之后需要重新设定为FALSE,在重启期间,每次开始会话就都会创建追踪文件,就会输出各个会话的所有信息

仅仅获得指定应用的追踪方法

对应用中想开始SQL trace的point追加以下SQL语句

ALTER SESSION SET SQL_TRACE=TRUE;

这时,获得 trace期间,可以设定终止应用或者将SQL_TRACE设定为 FALSE

 

通过TKPROF进行的格式化

lSQL trace的输出结果通过使用TKPROF,可以以更加简明的形式来进行格式化

<UNIX/Linux环境中执行TKPROF的例>

TKPROF命令中orcl1_ora_12059.trc 这个trace文件,对         tkprof_1.prf进行格式化

$ tkprof orcl1_ora_12059.trc tkprof_1.prf

※ TKPROF的执行Module名由于环境不同而不同,具体请参考手册

 

TKPROF的输出例

tkprof

 

弄清楚是应用程序的问题还是网络问题

判断DB中的性能问题时需要调查到底是AP问题还是网络问题

Net 的 trace文件:确认通信(packet)的处理信息

 

 

Net trace文件是指什么?

每个网络组成(listener・客户端・服务器等等)都可以在信息交换是,生成 trace信息

获得网络通信时的 trace信息,指定获得的信息交换中可能产生性能问题的地方

※listener与服务器的 trace每次连接与启动时,都会创建得比客户端量要更多的

首先我们推荐检查客户端(可能的话请检查服务器)的 trace

 

net trace

 使用trace时需要考虑的问题

1.需要充分确保磁盘空间

trace以及日志可能使用大量磁盘空间

另外,通常使得设定无效。或者定期删除日志文件,尽量不对进程增加负荷

2.输出地址文件的位置 我们不推荐所有用户来写入的swap space

3.输出地址是否具有对目录的写入权限 连接用户、oracle软件拥有者、listener启动用户来写入

注意:  trace功能中,因为使用了大量磁盘区域,可能造成系统 性能大幅降低。所以只有在必要时才能使用trace

 

客户端中的 trace设定方法

<设定方法>

l通过客户端系统的sqlnet.ora文件来设定以下参数

TRACE_LEVEL_CLIENT

TRACE_DIRECTORY_CLIENT

TRACE_UNIQUE_CLIENT

8.0.6 以后会记录以下参数

TRACE_TIMESTAMP_CLIENT

通过上述设定之后,客户端每次新建连接时都会在指定目录中生成trace文件

XXX.trc 这个文件,只要在 trace子目录中输出了的话就成功了

 

 

获得客户端的 trace相关的注意事项

<获得方法>

通过Oracle Net Services 11g (11.1.0) 将默认的 trace输出地址设定为Automatic Diagnostic Repository (ADR)

为了在通过TRACE_DIRECTORY_CLIENT指定的目录中输出,可以指定以下参数

默认为$ORACLE_HOME/log/diag

 

sqlnet.ora

DIAG_ADR_ENABLED=OFF

<参考>服务器的 trace也与客户端的情况相同                       listener的 trace的情况如下所示

listener.ora

DIAG_ADR_ENABLED_<listener名>=OFF

 

tracelevel

设定通过TRACE_LEVEL_CLIENT获得的信息

正常运行时,一般为0或者OFF(默认,参考下列表,根据需求变更level

 trace level 収集数据
0或者OFF  没有trace的输出
4或者USER 用户・ trace信息
10或者ADMIN 管理 trace信息
16或者SUPPORT Cluster 支持 trace信息

 

客户端的 trace文件获得例

XXX.trc 这个文件是在trace子目录中输出了的话就成功

[21-JUN-2006 10:58:29:081] nscon: doing connect handshake…

[21-JUN-2006 10:58:29:081] nscon: sending NSPTCN packet

[21-JUN-2006 10:58:29:081] nspsend: entry

[21-JUN-2006 10:58:29:081] nspsend: plen=242, type=1

[21-JUN-2006 10:58:29:081] nttwr: entry

[21-JUN-2006 10:58:29:081] nttwr: socket 288 had bytes written=242

[21-JUN-2006 10:58:29:081] nttwr: exit

[21-JUN-2006 10:58:29:081] nspsend: 242 bytes to transport

[21-JUN-2006 10:58:29:081] nspsend: packet dump

[21-JUN-2006 10:58:30:081] nsprecv: 00 CD 00 00 06 00 00 00 |……..|

[21-JUN-2006 10:58:30:081] nsprecv: 00 00 08 00 01 00 00 00 |……..|

[21-JUN-2006 10:58:30:082] nsprecv: 0C 0C 41 55 54 48 5F 53 |..AUTH_S|

[21-JUN-2006 10:58:30:082] nsprecv: 45 53 53 4B 45 59 00 00 |ESSKEY..|

[21-JUN-2006 10:58:30:082] nsprecv: 32 37 46 32 45 38 39 36 |27F2E896|

 

分析的基本思路

[21-JUN-2006 10:58:30:081] nsprecv: 00 CD 00 00 06 00 00 00 |……..|

分析数据・packet的收发信息所花费的时间

packet-sqlnet

 

性能问题的区分①应用程序的问题例

客户端中的nsprecv(收信息)到nspsend(发信息)所花费的时间

sqlnet-performance-issue

 

性能问题的区分DB中的问题例

客户端中的nspsend(发信息)到nsprecv(收信息)所花费的时间

sqlnet-performance-issue2

 

性能问题的区分例③N/W的问题例

客户端中的nspsend(发信息)到nsprecv(收信息)为止,似乎没有等待时机,考虑到处理内容,收发信息的时间也花费太多了。

sqlnet-performance-issue3

 

数据库调优

参考分析中使用过的信息,执行合适的调优

  • SQL调优
    • 索引
    • 提示HINT
  • 使用ADDM的调优
  • 使用各个建议的调优
  • 确认效果
    • 确认使用了AUTOTRACE的调优效果

 

索引调优 例

Statspack的SQL ordered by Gets的确认

sql-index-tuning

索引的创建与删除

索引的创建

SQL> create index c1_index on t1(c1);

索引的删除

SQL> drop index c1_index;

即使建索引了,也会在下列情况中进行全表扫描,请大家注意

  • 搜索NULL值
  • 计算索引列
  • 隐秘地变更数据类型
  • 使用LIKE语句的中间一致、后方一致搜索
  • 使用!=和<>

索引的效果

sql-index-tuning1

 

提示HINT的使用

  • 通过使用提示HINT,就会用到指定的访问路径,可以在优化中进行指定

      <例>

  • 指定使用合适的索引(Index)
  • 指定合适的表结合方法以及结合顺序
  • 指定CBO的目标(重视吞吐量或者面向 OLTP等) etc.
  • 提示HINT的使用例 ( 在/*+ 与 */之间指定提示HINT、直接嵌入到SQL中 )

(例)就会使用表t1的c1列中附加的c1_indx这个Index

SQL>select /*+INDEX(t1 c1_index) */ * from where c1=100;

ADDM 自动数据库检测监视器
(Automatic Database Diagnostics Monitor)

  • 基于AWR中所示收集的统计信息,定期监视、诊断性能的,面向DB管理者(DBA)的功能

addm1

帮助功能

addm2

SQL调优・帮助

  • 诊断因为高负荷而发生问题的SQL语句以及执行计划
  • 以诊断为基础给予建议

sql-tuning-2

 

调优的流程

 

tuning-oracle

确认调优效果

随着SQL语句变得复杂,调优方法也越来越多,可能为了达到目标效果需要

反复调优,如果使用SQL*Plus的AUTOTRACE功能的话就能当即确认效果了

l要使用AUTOTRACE的话需要确认一下几条项目

  • lSQL*Plus中执行的SQL语句
  • 执行计划的结果
  • 统计信息

-物理读入  -論理读入

-REDO尺寸  -内存sort 尺寸

-处理件数  -磁盘sort 尺寸

 

AUTOTRACE功能的设定顺序

lAUTOTRACE的设定顺序如下所示

Step1 创建PLUSTRACE 角色(仅限首次)

SQL> connect sqlplus / as sysdba

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

 

Step2  对使用用户赋予已创建的PLUSTRACE角色

SQL> grant plustrace to 用户名>

Step3 通过使用用户创建AUTOTRACE功能将要使用的工作用的表

SQL> @$ORACLE_HOME/sqlplus/admin/utlxplan.sql

每个想使用AUTOTRACE功能的用户都需要重复Step2与Step3

 

AUTOTRACE的获得

1.通过使用用户执行SET AUTOTRACE命令

SQL> set autotrace on

2.SQL执行

SQL> select * from t1 where c1 = 100;

※想终止AUTOTRACE功能时

SQL> set autotrace off

 

AUTOTRACE的获得的例子

 

oracle-autotrace

 

总结

 

tuning-summary

 

【Maclean技术分享】开Oracle调优鹰眼,深入理解AWR性能报告 第二讲

Oracle调优鹰眼系列只有2讲,对AWR感兴趣的同学更多指标可以参考 【性能调优】Oracle AWR报告指标全解析 https://www.askmaclean.com/archives/performance-tuning-oracle-awr.html

 

 

【技术分享】开Oracle调优鹰眼,深入理解AWR性能报告 第二讲

涉及性能优化教学知识:Host CPU、Instance CPU、Wait Class、SQL Statistics、AWR FOR RAC集群特定调优 https://zcdn.askmaclean.com/oracle%20awr%20hawk%202.mp4

适合的学员: 对性能优化有兴趣,或给予提升自己Oracle调优技能的同学

预计时长: 2个小时左右

本次公开教学的视频地址:

 

 

正式版文档材料已上传:

 

【Maclean Liu技术分享】开Oracle调优鹰眼,深入理解AWR性能报告 第二讲 正式版 20130.pdf (2.27 MB, 下载次数: 30699)

 

 

【技术分享】开Oracle调优鹰眼,深入理解AWR性能报告 第一讲  https://zcdn.askmaclean.com/%E3%80%90Maclean%20Liu%E6%8A%80%E6%9C%AF%E5%88%86%E4%BA%AB%E3%80%91%E5%BC%80Oracle%E8%B0%83%E4%BC%98%E9%B9%B0%E7%9C%BC%EF%BC%8C%E6%B7%B1%E5%85%A5%E7%90%86%E8%A7%A3AWR%E6%80%A7%E8%83%BD%E6%8A%A5%E5%91%8A.mp4

适合参与成员: 对性能优化有兴趣或急于提升自己oracle技术水平的学员

 

 

 

 

讲座材料presentation 当前版本下载:

【Maclean Liu技术分享】开Oracle调优鹰眼,深入理解AWR性能报告_20130303版.pdf.pdf (1.79 MB, 下载次数: 32641)

GATHER_STATS_JOB: Stopped by Scheduler. Consider increasing the maintenance window duration if this happens frequently.

《Does GATHER_STATS_JOB gather all objects’ stats every time?》一文中 , 我们详细介绍了GATHER_STATS_JOB的一些特点,例如数据库一直打开的情况下,GATHER_STATS_JOB会伴随维护窗口一起被启动,默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close),剩下的有待收集信息的对象将在下一个维护窗口中得到处理。

 

在版本10gR2中对于那些需要收集而在维护窗口中没来及收集的对象,会由执行收集作业的J00x作业子进程写出哪些没能来得及收集对象的名单,例如:

 

GATHER_STATS_JOB: Stopped by Scheduler.
Consider increasing the maintenance window duration if this happens frequently.
The following objects/segments were not analyzed due to timeout:
TABLE: “MACLEAN”.”HANA”.”ORACLE”

……………………

error 1013 in job queue process
ORA-01013: user requested cancel of current operation

 

 

对于统计信息收集在版本12c中得到进一步的加强的dbms_stats.REPORT_GATHER_AUTO_STATS可以告知用户其将自动收集哪些对象。

已经几乎不需要修正应用了! 划时代的SQL调优方法

 

https://www.askmaclean.com/archives/sqltuning-sql-profile.html

 

  • 以前的SQL调优
  • SQL Profile是什么
  • 调优执行顺序
  • 经常被问到的问题
  • 总结

 

<一般而言SQL的调优流程>

  1. 诊断/指定瓶颈
  2. 执行合适的调优
  3. 检查效果  
  4. 根据需要进一步调优

Automatic Database Diagnostic Monitor(ADDM

ADDM是监视/诊断数据库性能的功能

发现内存不足以及I/O问题、性能较差的SQL、Real Application Clusters(RAC相关的问题等等各种问题,给数据库管理者提供建议。

那时,会为数据库管理者提供解决问题所需要进行的操作提出建议,数据库管理者就可以高效解决问题。

ADDM与其他建议相同都可以手动启动来诊断数据库,但一般而言是定期性地自动启动,监视数据库是否有性能问题。

ADDM自动启动的时机是在取得AWRsnapshot时,通过取得了snapshotMMON进程来自动启动

手动启动ADDM的情况下,可以诊断过去任一时间点的数据库。

ADDM是从两个snapshot中取得数据库的负荷信息,进行诊断。

自动启动的情况下,使用最新取得的snapshot以及1个之前的snapshot,手动启动的情况下,用户可以指定任意两个snapshot

要使用这个功能的话,需要将STATISTICS_LEVEL 初始化参数设定为TYPICAL(默认)或者ALL

 

SQL较慢的时候,首先要寻找原因

 

以前的SQL调优

<一般而言SQL的调优流程>

  • 诊断/指定瓶颈
  • STATSPACK:
    与DB整体的统计一起收集SQL统计
  • EXPLAIN PLAN:
    在每个SQL中表示执行计划
  • SQL TRACE与TKPROF:
    收集以session等单位来执行的SQL执行的统计信息,并且

总结结果报告。

  • SQL*Plus的AUTOTRACE機能:
    在每个SQL中表示执行计划以及性能统计

 

2.执行合适的调优

  • 优化的选择<到Oracle 9i 为止>
  • RBO ( Rule-based optimizer )<到Oracle 9i 为止>
  • 重新检查物理設計(Index相关)
  • SQL的改写           etc..
  • CBO ( cost-based optimizer )
  • 取得合适的对象的统计信息
  • 使用优化提示   不知所措时一定要使用这个功能
  • 检查Partitioning导入
  • 并行处理的检验   etc..

3.检查效果  =》 根据需要进一步调优

 

 

优化提示

  • 通过使用提示、可以指示优化使用特定的访问路径

      <例>

  • 指定使用合适的index
  • 指定合适的表结合方法与结合顺序
  • 指定CBO的目标(重视吞吐量 or面向 OLTP 等) etc.
  • 在使用RULE提示以外的情况下,就会自动执行CBP的最优化
  • 提示的使用例子( /*+ 与*/ 之间指定提示、直接嵌入到SQL中 )
  • 使其使用附着与sales表的customer_id列的cust_id_indx这个index。

SELECT  /*+ INDEX(sales cust_id_indx) */  sales_date , sales_amount

FROM sales WHERE customer_id=‘ABE’;

 

SELECT  /*+ USE_HASH(s c) LEADING(c s) */  *

FROM sales s , customers c

WHERE s.customer_id=c.customer_id AND s.sales_amount > 1000;

 

以前的SQL调优的缺点

HINT提示的缺点

  • 每个SQL都需要自定义
  • 要求高度的知识存量与技巧
  • 性能恶化时,需要修正应用

某些SQL语句可以高速嵌入提示,但这就不得不对应用进行修正。

package应用中无法使用提示

 

SQL Profile是什么

每个SQL中取得的固有的辅助性的统计信息

SQL Profile直到被删除或者被重新制作为止,都在数据库中保持原样

优化需要用到SQL Profile以及现存的优化统计两方面才能制成执行计划

Oracle Database 10g 开始,追加 SQL调优建议,从这里开始生成

 

简而言之 

制成服从建议的SQL profile的话,执行计划也会最优化。 =》 原因是 数据库有如提示一样可以改善执行计划的统计信息!=》

如果使用SQL profile的话、、就可以不修正应用,但又能实现与提示同样的效果!!

 

SQL profile的制成与使用

实际应用SQL profile之后,会说明直到Active session降低为止所需要等待的时间

SQL profile是指SQLTuning Advisor,收集的辅助统计信息。

通过接受被提示的SQL profile,就可以不加工SQL就控制执行计划。

<追加>

SQL profile是在10g中得到强化的功能之一,特征是不全面变更应用就可以进行调优。

 

SQL profile的制成与使用

SQL调优建议

  • 在高负荷背景下诊断有问题的SQL语句以及执行计划
  • 诊断结果是?
  • 寻找SQL语句的问题点,SQL语句的修正方法
  • 建议制成必要的索引
  • 制成SQL profile

一般而言,SQL调优建议等一系列的自动调优功能是从GUI的管理工具

Oracle Enterprise Manager 10g开始执行的。

 

sql_tuning_z1

SQL profile经常出现的问题

Q1. 制作一个SQL profile的话、能够加速多个SQL吗?

SQL_PROFILE1

Q2. 执行SQL调优建议时,可以明确指定制成SQLprofile吗?

A2. 不可以。

SQL调优建议是在分析SQL的时候,为了改善性能,做出最合适的建议的功能。在手动调优中,比起使用提示进行调优,制成index的效果更好的情况下,请选择制成index。与此相同在只要制成index就可以明显改善性能的情况下,不会建议制成SQL profile。

Q3. 可以确认SQL profile的内容吗?

A3. 无法确认SQL profile的内容。(SQL profile的列表可以通过搜索DBA_SQL_PROFILES视图来进行确认。)

Q4. SQL profile是随着数据量的增加而动态更新吗?

A4.因为SQLprofile是静态的信息,所以不会自动更新。 因此,使用时,                因为数据量的变化,SQL profile就会变旧,就可能使得性能恶化。

(已经产生恶化的情况下,可以再次制成SQL profile)

Q5. 我们已经明白了SQL profile的效果有多好了,那要制成性能更好的执     行计划的话,为什么不使用默认的优化呢?

A5. 制成SQL profile比一般的CBO需要花费的时间更多。如果,默认中,对于所有SQL,都检验SQL profile的话,就可能影响到整体的性能,所以现实中不采用那种架构。

 

Q6. 能将开发环境下制成的SQL profile移动到正式环境中使用吗?

A6. 如果是Oracle Database 10g R2的话就可以。

<顺序>

・在开发环境中、

  1. 执行 dbms_sqltune.create_stgtab_sqlprof,然后制成暂时储存SQL profile Staging表。
  2. 执行dbms_sqltune.pack_stgtab_sqlprof,将SQL profile储存在Staging表中。
  3. 使用 DataPump以及Export,在dump文件中取出Staging表。

・在正式环境中

4.使用在开发环境中制成的dump文件使用Data Pump进行import。

5. 执行dbms_sqltune.unpack_stgtab_sqlprof,从Staging表表中取出SQL profile,反应在正式环境中 Oracle Database 10g R1中无法将SQL profile移动到其他数据库中         (比如,制成index时,在开发环境下,可以将通过建议制成的项目用Export / Import 等移动到正式环境中,SQL profile不需要在正式环境中用别的途径来制成)

 

Q7. 有确认是否使用了 SQL profile的方法吗?

A7. 确认有两种方法。

①灵活使用SQL*Plus的Autotrace功能

1. 设定Autotrace为ON

SQL> set autotrace on

2. 每次执行SQL语句时,以下的note就都会被表示出来,使用了SQL profile的情况下,

是表示出SQLprofile的名字

Note

—–

– SQL profile SYS_SQLPROF_014564deb351c000 used for this statement

②灵活使用Explain Plan

1. 对于SQL执行Explain Plan

(例)

SQL> EXPLAIN PLAN FOR SELECT * FROM emp;

2. 执行下述SQL的话,就会得到与①同样的结果

SQL> select plan_table_output from table(dbms_xplan.display());

要使用SQL调优建议需要什么!?

  • 仅限Oracle Database 10g Enterprise Edition或以上版本
  • 需要购买以下两个Option

Database Diagnostics Pack

  • 可以使用的功能
  • AWR (Automatic Workload Repository)
  • ADDM (Automatic Database Diagnostic Monitor)
  • 性能监视(数据库以及主机)
  • 项目通知:
    通知method、规定、以及日程
  • 项目历史以及Metric历史
    (数据库以及主机)

Database Tuning Pack

  • 可以使用的功能
  • SQL Access Advisor
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • 重新编辑对象

 

总结

  • SQL profile非常有效
  • 克服了一直以来提示调优的弱点
  • 不需要修正应用就可以完成类似于提示的调优
  • 总结了US oracle的咨询的调优与窍门
  • 即使没有技能与经验也能完成高度的SQL调优

灵活使用Oracle Database 10g Enterprise Edition+ Option 非常重要!

灵活使用SQL调优建议

由建议开始制成SQL profile

 

【11g新特性】Cardinality Feedback基数反馈

Cardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性,该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况, Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估,造成CBO选择不当的执行计划。以上是Cardinality Feedback特性引入的初衷。

 

Cardinality Feedback2

Cardinality Feedback1

 

但是每一个Oracle新版本引入的新特性 都被一些老外DBA称之为buggy ,Cardinality Feedback基数反馈多少也造成了一些麻烦,典型的情况是测试语句性能时,第一次的性能最好,之后再运行其性能变差。

 

我们来看一下 Cardinality Feedback基数反馈是如何作用的:
注意使用普通用户来测试Cardinality Feedback,sys用户被默认禁用该特性

 

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn maclean/oracle
已连接。
SQL> show parameter dynamic
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_dynamic_sampling           integer                0
SQL> create table test as select * from dba_tables;
表已创建。
SQL> select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
2873
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  0p4u1wqwg6t9z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test
Plan hash value: 1950795681
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |   8904 |   2873 |00:00:00.01 |     104 |
-------------------------------------------------------------------------------------
已选择14行。
SQL> select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
2873
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  0p4u1wqwg6t9z, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test
Plan hash value: 1950795681
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |   2873 |   2873 |00:00:00.01 |     104 |
-------------------------------------------------------------------------------------
Note
-----
- cardinality feedback used for this statement
已选择18行。

 

 

上例中第一次运行时,由于未收集表上的统计信息且optimizer_dynamic_sampling=0 关闭了动态采样所以基数评估值(1)和实际值(2873)有着较大的差距。

 

 

cardinality feedback used for this statement这个信息说明第二次执行时使用了Cardinality Feedback基数反馈,且其基数评估也十分精确了,这是因为第二次执行时考虑到第一次执行时的基数反馈,我们来看看Oracle到底是如何做到的:

 

 

 

SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> alter session set events '10053 trace name context forever, level 1';
会话已更改。
SQL>  select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
2873
SQL>  select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
2873
10053 trace:
第一次执行:
sql= select /*+ gather_plan_statistics */ count(*) from test
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    31 |           |
| 1   |  SORT AGGREGATE     |         |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | TEST    |  8904 |       |    31 |  00:00:01 |
--------------------------------------+-----------------------------------+
SELECT /*+ OPT_ESTIMATE (TABLE "TEST" ROWS=2873.000000 ) */ COUNT(*) "COUNT(*)" FROM "MACLEAN"."TEST" "TEST"
SINGLE TABLE ACCESS PATH 
Single Table Cardinality Estimation for TEST[TEST] 
Table: TEST  Alias: TEST
Card: Original: 8904.000000    >> Single Tab Card adjusted from:8904.000000 to:2873.000000
Rounded: 2873  Computed: 2873.00  Non Adjusted: 8904.00
Access Path: TableScan
Cost:  31.10  Resp: 31.10  Degree: 0
Cost_io: 31.00  Cost_cpu: 1991217
Resp_io: 31.00  Resp_cpu: 1991217
Best:: AccessPath: TableScan
Cost: 31.10  Degree: 1  Resp: 31.10  Card: 2873.00  Bytes: 0
sql= select /*+ gather_plan_statistics */ count(*) from test
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    31 |           |
| 1   |  SORT AGGREGATE     |         |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | TEST    |  2873 |       |    31 |  00:00:01 |
--------------------------------------+-----------------------------------+

 

 

 

 

可以看到第二次执行时SQL最终转换加入了 OPT_ESTIMATE (TABLE “TEST” ROWS=2873.000000 )的HINT ,OPT_ESTIMATE HINT一般由 kestsaFinalRound()内核函数生成。该HINT用以纠正各种类型的优化器评估,例如某表上的基数或某个列的最大、最小值。反应出优化的不足或者BUG。

 

可以通过V$SQL_SHARED_CURSOR和来找出现有系统shared pool中仍存在的 使用了Cardinality Feedback基数反馈的子游标:

 

 

SQL> select sql_ID,USE_FEEDBACK_STATS  FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS ='Y';
SQL_ID                     US
-------------------------- --
159sjt1f6khp2              Y

 

 

 

还可以使用cardinality HINT来强制使用Cardinality Feedback 。

select /*+ cardinality(test,  1) */ count(*) from test;

 

 

如何禁用Cardinality Feedback基数反馈

 

对于这些”惹火”特性,为了stable,往往考虑关闭该特性。

可以通过多种方法禁用该特性

1. 使用 _optimizer_use_feedback 隐藏参数

session 级别

SQL> alter session set “_optimizer_use_feedback”=false;

会话已更改。

system级别

SQL> alter system set “_optimizer_use_feedback”=false;

系统已更改。

 

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT

例如:

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) cardinality(test,1) */ count(*) from test;

 

 

 

Library cache: mutex X

Library cache: mutex X

对于该Library cache: mutex X等待事件而言,可能存在多种原因:

  1. “library cache: mutex X” 等待事件症状在11g之后较为常见,原因多样,所以一定要找root cause。
  2. 已知有不少bug引起该”library cache: mutex X”的。这些bug有些与mutex有关有些又无关。bug信息见附录。
  3. 在一些例子中问题常由于应用程序变更而引发。 同时也要注意登陆 logon 或 登出logoff暴风所引起的问题。
  4. 这个mutex在KGL的bucket中。

 

Library cache: mutex X的其他一些常见原因:

  • 频繁的硬解析hard parse
  • 如果硬解析真的十分激烈,那么争用常发生在pin上
  • 高的version count,SQL游标版本过多
  • 当某些SQL的version count非常高时,一大串的version要被检验那么可能导致Library cache: mutex X
  • SQL的Invalidations 和 reloads
  • BUG
  • 操作系统相关问题 例如 Bug:7441165 – CPU Pre-emption can cause problems on Solaris (this fix only applies to this OS)

如果自己搞不定可以找诗檀软件专业ORACLE数据库优化团队成员帮您调优!

诗檀软件专业数据库优化团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

 

library cache: mutex X相关BUG:

 

NB Bug Fixed Description
15858022 12.1.0.0 Frequent invalidation of tuning objects with VPD
14401731 11.2.0.4, 12.1.0.0 Cursor leak using "SET TRANSACTION" within PLSQL
13810393 12.1.0.0 Deadlock waiting for 'library cache: mutex x' while producing an ORA-4031
diagnostic dump
13720753 11.2.0.4, 12.1.0.0 Diagnosability issue - mutex wait "idn" value truncated in session wait
output in tracefile
13588248
11.2.0.2.BP17, 11.2.0.3.3,
11.2.0.3.BP06, 12.1.0.0
"library cache: mutex X" contention for objects with very many library cache
locks
12976376
11.2.0.2.6, 11.2.0.2.BP16, 11.2.0.3.3,
11.2.0.3.BP05, 12.1.0.0
High VERSION_COUNT for SQL with binds, including recursive dictionary
SQL - superseded
12797420 11.2.0.3.3, 11.2.0.3.BP07, 12.1.0.0
"library cache: mutex X" waits on DB instance handle with
CURSOR_SHARING
11818335
11.1.0.7.9, 11.2.0.2.2, 11.2.0.2.BP06,
11.2.0.3, 12.1.0.0
Additional support for bug 10411618 to allow dynamic Mutex wait scheme
changes
11719151 11.2.0.3, 12.1.0.0 SQL Plan Management capture causes slowness
10632113 11.2.0.3, 12.1.0.0 OLS calls cause mutex contention even with low number of concurrent users
10417716
11.2.0.2.5, 11.2.0.2.BP05,
11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.0 Mutex X waits in 11G on an instance with heavy JAVA usage
10284845 11.2.0.3, 12.1.0.0 Need index on SID for X$KGLLK and X$KGLPN based views
(V$OPEN_CURSOR etc..)
10284838
11.2.0.2.5, 11.2.0.2.BP13,
11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.0
Cache line contention while waiting for mutexes
10204505 11.2.0.3, 12.1.0.0
SGA autotune can cause row cache misses, library cache reloads and
parsing
10086843 11.2.0.3, 12.1.0.0
Recursive SQL cursors not reused - PMON crashes instance with ORA-600
[kglLockOwnersListDelete]
9530750 11.2.0.2, 12.1.0.0 High waits for 'library cache: mutex X' for cursor Build lock
9239863 11.2.0.1.BP06, 11.2.0.2, 12.1.0.0 Excessive "library cache:mutex X" contention on hot objects
8981059
11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06,
11.2.0.2, 12.1.0.0
High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind
peeking
8860198 11.2.0.2, 12.1.0.0 "library cache:mutex X" waits using XMLType
7352775 11.2.0.2, 12.1.0.0 Many child cursors when PARALLEL_INSTANCE_GROUP set wrong
12670165 11.2.0.2.4, 11.2.0.2.BP12, 11.2.0.3 Library cache mutex contention caused by lookup of triggers
12633340 11.2.0.2.6, 11.2.0.2.BP13, 11.2.0.3 Heavy "library cache lock" and "library cache: mutex X" contention for a
"$BUILD$.xx" lock
8793492 11.2.0.1.BP07, 11.2.0.2 Mutex Waits with Resource Manager
10145558 11.1.0.7.7, 11.2.0.1.BP12, 11.2.0.2 Selects on library cache V$/X$ views cause "library cache: mutex X" waits
9398685 11.2.0.2 High "library cache: mutex X" when using Application Context
9282521 11.2.0.2 Excessive "library cache:mutex X" contention on hot objects
9140262 11.2.0.2 ORA-600 [ksliwat5] followed by cpu spike/"library cache: mutex X" Waits
9003145 11.2.0.1.BP03, 11.2.0.2 Dump (kglIsOwnerVersionable) / "library cache: mutex X" waits
7502237 11.1.0.7.7, 11.2.0.1 Unnecessary "library cache: mutex X" waits using stored Java
7307972 11.1.0.7.2, 11.2.0.1 Excessive waits on 'library cache: mutex x'
8431767 High "library cache: mutex X" when using Application Context
9312879 11.1.0.7.7, 11.2.0.1 "library cache: mutex x" waits after killing sessions / PMON slow to clean up
7648406 10.2.0.5, 11.1.0.7.4, 11.2.0.1
Child cursors not shared for "table_..." cursors (that show as "SQL Text Not Available") when NLS_LENGTH_SEMANTICS = CHAR
7155860 11.2.0.1 Spin on kgllkde causes 'library cache: mutex X'
8499043 11.1.0.7.2 SET_CONTEXT incurs unnecessary DLM overhead in RAC
5928271 11.1.0.7 Excessive waits on "library cache: mutex X"
7317117 11.2.0.1 Unnecessary "library cache: mutex X" waits on LOB operations

 

 

【Maclean Liu技术分享】拨开Oracle优化器迷雾探究Histogram之秘

【Maclean Liu技术分享】拨开Oracle CBO 优化器迷雾, 探究Histogram直方图之秘,讲座文档正式版已上传 
http://t.askmaclean.com/thread-2172-1-1.html  

 

 

预计时长: 1.5个小时

适合参与成员: 对于性能调优和CBO优化器有兴趣的同学,或急于提升SQL调优技能的同学。

讲座材料presentation 当前正式版本下载:

 

【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf.pdf(1.1 MB, 下载次数: 749) 

沪ICP备14014813号

沪公网安备 31010802001379号