拨开Oracle优化器迷雾探究Histogram之秘

拨开Oracle CBO 优化器迷雾, 探究Histogram直方图之秘,讲座文档正式版已上传


预计时长: 1.5个小时

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

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

http://www.tudou.com/programs/view/TGvP2pIyvwI/

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

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

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

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

Oracle数据恢复专题

恢复恢复是Oracle中永恒的话题, 只要有数据 就有备份恢复的需求。 而在国内对于备份以及备份的可用性往往被企业所忽视。这造成了再数据库恢复上存在着东西方的差异。 更多的老外DBA把经历花在对Oracle内部原理和性能优化的研究上。

oracle data block structure

而我们国内 DBA似乎必须要精通一门额外的技术==》 在没有任何备份的情况下 恢复Oracle数据库中数据的技术!  虽然这在大多数情况下是屠龙之技, 但很多时候却又变成了衡量一个DBA技术水准的标准了,(这样不好..  不好)。

当然也并不是说 这种无备份下的数据恢复是无技术含金量的,实际上它们很需要对Oracle数据文件、数据块及其数据结构的理解,以及对数据字典构成的了解。

这里我们总结Oracle数据恢复专题的专题,包括一些在无备份情况下的数据恢复:例如DUL和BBED工具恢复等技术。

【数据恢复】利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题

【数据恢复】ORA-600[kccpb_sanity_check_2]一例

Oracle rman中set newname可能很慢

如何清除Oracle控制文件中的无用记录,例如v$archived_log中的deleted归档日志记录

如何找回被create or replace覆盖的PL/SQL对象

Archivelog Completed Before VS UNTIL TIME

ASM丢失disk header导致ORA-15032、ORA-15040、ORA-15042 Diskgroup无法mount

Overcome ORA-600[4xxx] open database

数据恢复:解决ORA-600[kghstack_free2][kghstack_err+0068]一例

清理RMAN Catalog恢复目录

如何rename datafile name中存在乱码的数据文件

11g新特性recover corruption list

解决ORA-01578错误一例

Script:收集介质恢复诊断信息

如何重建SYSAUX表空间上的对象

Oracle数据恢复:解决ORA-00600:[4000] (more...)

【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 (more...)

【数据恢复】详解ORA-1410错误

ORA-1410 invalid rows错误是与ORA-8103相似的Oracle数据库逻辑层面的讹误。

了解ORA-1410逻辑坏块问题的成因,以及有效的解决手段十分重要。

解决方案之一:

可以通过如下PL/SQL过程将健康数据复制到新建表中,对于问题数据块中的数据将被跳过,对于能够容忍数据丢失的场景可以考虑这样恢复,之后truncate 原表/分区并将健康数据加载进去。 具体的脚本见下面的链接:

【数据恢复】利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题

oerr ora 1410
01410, 00000, “invalid ROWID”
// *Cause:
// *Action:

如果对ORA-1410做errorstack 一般会看到下面的LOG:

OBJD MISMATCH typ=6, seg.obj=%d, diskobj=%d, dsflg=%d, dsobj=%d, tid=%d, cls=%d

触发ORA-1410错误的stack call一般都是:  kcbgtcr=>kcbzib=>kcbz_check_objd_typ,即在对数据块做逻辑读时运行到kcbz_check_objd_typ函数时,检测到OBJD 不一致的问题。由于seg.obj和diskobj不一致,而10g以后的kcbz_check_objd_typ函数负责验证块上的objd是否mistmatch,若不一致则触发ORA-1410错误。

造成objd mimatch的主要可能有几种:

1、 写丢失 Lost Write, 写丢失造成相关数据块没有为现有对象正常格式化,导致虽然该数据块的checksum是正确的,但对应数据字典却是不一致的。 写丢失也可能由磁盘或卷组镜像同步软件的不完整复制造成。

If the on-disk objd is (more...)

【数据恢复】利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题

对于无备份情况下的ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题,可以通过如下PL/SQL 构造ROWID的方式挽救绝大多数非坏块的数据, 一般分成 2种情况 有索引可以用来获取ROWID, 或者 没有索引、索引不可用,必须通过dbms_rowid.ROWID_CREATE来构造ROWID的方式。

对于有索引的情况,可以直接使用MOS上提供的脚本:

REM Create a new table based on the table that is producing errors with no rows:

create table 
as
select *
from   
where  1=2;

REM Create the table to keep track of ROWIDs pointing to affected rows:

create table bad_rows (row_id  (more...)

深入理解Oracle中的Mutex

虽然Mutex中文翻译为互斥锁,但为了和OS mutex充分的区别,所以我们在本文里称Oracle Mutex为Mutex。

Oracle中的mutex,类似于Latch,是一种低级的串行机制,用以控制对SGA中部分共享数据结构的访问控制。  Oracle中的串行机制有不少,引入它们的目的是避免一个对象出现下述现象:

  • 当某些进程在访问该对象时,该资源被重新分配
  • 当某些进程在修改它时,被其他进程读取
  • 当某些进程在修改它时,被其他进程修改
  • 当某些进程在读取它时,被其他进程修改

不同于Latch,Mutex的使用更灵活,用途更多,例如:

  • 哪些需要被mutex保护的共享数据结构可以有自己独立的mutex,即一个对象拥有自己独立的mutex,不像Latch往往一个需要保护大量对象,举例来说,每一个父游标有其对应的mutex, 而每一个子游标也有其对应的mutex
  • 每一个数据结构可能有一个或多个mutex保护,每一个mutex负责保护其结构的不同部分
  • 当然一个mutex也可以用来保护多于一个的数据结构

理论上mutex即可以存放在其保护的结构本身中(其实是嵌入在结构里),也可以存放在其他地方。 一般情况下Mutex是在数据结构需要被保护时动态创建出来的。 如是嵌在需要保护结构体内的mutex,则当 所依附的数据结构被清理时 该mutex也将被摧毁。

Mutex带来的好处

虽然mutex和latch都是Oracle中的串行机制,但是mutex具有一些latch没有的好处

更轻量级且更快

Mutex作为Latch的替代品,具有更快速获得,更小等优势。 获取一个mutex进需要大约30~35个指令, 而Latch则需要150~200个指令。一个mutex结构的大小大约为16 bytes,而在10.2版本中一个latch需要112个bytes,在更早的版本中是200个bytes。 从200个bytes 精简到112个是通过减少不必要的统计指标 SLEEP1~SLEEP11、WAITERS_WOKEN, WAITS_HOLDING_LATCH等从而实现的。今后我们将看到更多关于Latch的代码优化。

减少伪争用

典型情况下一个Latch保护多个对象。 当一个Latch保护多个热对象时,并行地对这些对象的频繁访问让latch本身变成性能的串行点。 这也就是我们此处说的伪争用点, 因为争用是发生在这个串行保护的机制上,而不是进程去访问的对象本身。与latch不同, 使用mutex的情况下Oracle开发人员可以为每一个要保护的数据结构创建一个独立的mutex。 这意味着Latch的那种伪争用将大大减少,因为每一个对象均被自己独立拥有的mutex保护

 Mutex在一些地方替代了latch和PIN

一个Mutex可供多个Oracle进程并行地参考,反过来说进程们可以以S(Shared 共享) mode模式参考一个Mutex。以S mode一起共享参考这个mutex的进程的总数成为参考总数reference count。Mutex自身结构中存放了这个ref count的数据。另一方面,mutex也可以被以X (Exclusive)mode排他模式被仅有一个进程所持有Held。

Mutex有2种用途,一方面他们可以充当维护必要串行机制的结构,如同latch那样; 同时也可以充当pin,避免对象被age out。

举例来说,mutex结构中包含的ref count信息可以用作替代library (more...)

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

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

涉及性能优化教学知识:Host CPU、Instance CPU、Wait Class、SQL Statistics、AWR FOR RAC集群特定调优

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

预计时长: 2个小时左右

本次公开教学的视频观看:

 http://www.tudou.com/programs/view/ORRvMVNLKaU/?rpid=78129035&resourceId=78129035_06_05_99

正式版文档材料已上传:

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

【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                                  (more...)

Exadata offload incremental backup

网友在OTN中文官方技术论坛上提问问题:

“Exadata在rman备份时候的offloading功能需要数据库打开BCT吗?同题目,BCT=Block Change Tracking。
oracle数据库中BCT是使用文件来记录一组数据块中,修改过的数据块做个标记。
rman备份时,exadata的 offloading是如何实现的呢?”

As Maclean answered

“exadata的 offload incremental backup optimization 是基于数据块为单位, 而block change tracking通过位图维护一组block, 所以 offload incremental backup的 粒度更细化 也更智能。

With Exadata,changes are tracked at the individual oracle block level rather than at the level of a large group of blocks. This result in less (more...)

Exadata X2-2 1/4 RACK并行备份测试

[root@dm01db01 ~]# imageinfo

Kernel version: 2.6.18-274.18.1.0.1.el5 #1 SMP Thu Feb 9 19:07:16 EST 2012 x86_64
Image version: 11.2.3.1.1.120607
Image activated: 2012-08-14 19:16:01 -0400
Image status: success
System partition on device: /dev/mapper/VGExaDb-LVDbSys1

rman target /

Recovery Manager: Release 11. (more...)

测试Exadata单个cell失败

测试Exadata单个cell失败,模拟X2-2 3个cellserver中单个cell丢失的情况:

cell server

CellCLI> alter cell shutdown services all

Stopping the RS, CELLSRV, and MS services...
The SHUTDOWN of services was successful.




ASM LOG:


Sun Sep 02 09:08:48 2012
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_gmon_30756.trc:
ORA-27603: Cell storage I/O error, I/O failed on disk o/192.168.64.131/DATA_DM01_CD_00_dm01cel01 at offset  (more...)

Exadata Cellserv 如何列出存储告警信息

Exadata Cellserv 如何列出存储告警信息,可以通过cellcli命令行执行以下命令获得:

LIST ALERTDEFINITION命令用以显示cell服务器上可能生成的各种告警alert的定义。下面的示例列出了告警名字、度量名和描述。
度量名定义了告警所基于的度量。ADRALERT和HardwareAlert则不基于度量,因此没有度量名字

LIST ALERTHISTORY 命令用来显示一个cell服务器上的历史告警信息。在例子中仅列出所有严重性为critical的alert,且过滤条件为没有被管理员所查阅过(examinedBy)的。

create threshold命令用来定义一个阀值,指定条件生成一个度量告警。

CellCLI> LIST ALERTDEFINITION ATTRIBUTES name,metricname,description
         ADRAlert                                                                        "Incident Alert"
         HardwareAlert                                                                   "Hardware Alert"
         StatefulAlert_CD_IO_BY_R_LG                     CD_IO_BY_R_LG                   "Threshold Alert"
         StatefulAlert_CD_IO_BY_R_LG_SEC                 CD_IO_BY_R_LG_SEC               "Threshold Alert"
         StatefulAlert_CD_IO_BY_R_SM                     CD_IO_BY_R_SM                   "Threshold Alert"
         StatefulAlert_CD_IO_BY_R_SM_SEC                 CD_IO_BY_R_SM_SEC               "Threshold Alert"
         StatefulAlert_CD_IO_BY_W_LG                     CD_IO_BY_W_LG                   "Threshold Alert"
         StatefulAlert_CD_IO_BY_W_LG_SEC                 CD_IO_BY_W_LG_SEC               "Threshold Alert"
         StatefulAlert_CD_IO_BY_W_SM                     CD_IO_BY_W_SM                   "Threshold Alert"
          (more...)

如何重置Exadata cell上的flash cache的内容

如何重置Exadata cell上的flash cache的内容?

可以通过以下命令实现:

cellcli
CellCLI: Release 11.2.3.1.1 - Production on Sun Sep 02 07:29:08 EDT 2012

Copyright (c) 2007, 2011, Oracle.  All rights reserved.
Cell Efficiency Ratio: 527

CellCLI> LIST FLASHCACHECONTENT where objectnumber=17425 detail
         cachedKeepSize:         8755838976
         cachedSize:             8757706752
         dbID:                   2080757153
         dbUniqueName:           DBM
         hitCount:               12940
         hoursToExpiration:       (more...)

Exadata测试CELL_FLASH_CACHE KEEP性能

Exadata测试CELL_FLASH_CACHE KEEP  SMART Flash Cache性能

imageinfo

Kernel version: 2.6.18-274.18.1.0.1.el5 #1 SMP Thu Feb 9 19:07:16 EST 2012 x86_64
Cell version: OSS_11.2.3.1.1_LINUX.X64_120607
Cell rpm version: cell-11.2.3.1.1_LINUX.X64_120607-1

Active image version: 11.2.3.1.1. (more...)

如何修复重编译Datapump工具expdp/impdp

数据泵工具expdp/impdp是10g中引发的服务器端导入导出外部工具,虽然是外部的binary,但是实际expdp/impdp都依赖于内部的PL/SQL package主要是(dbms_datapump),在很多情况下我们需要修复或重新加载Datapump工具,方法如下:

对于版本10.1:

1. Catdp.sql orders the installation of all its components including 
   the Metadata API which was previously installed separately.
   By default catproc.sql invoke this script.

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

2. dbmspump.sql will create DBMS procedures for dataPUMP

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

对于版本10.2:

1.  (more...)

11g中如何禁用自动统计信息收集作业

11g中如何禁用自动统计信息收集作业?

因为11g中auto stats gather job被集成到auto task中,所以与10g中的禁用方式不一样:

SQL> select client_name,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.

SQL>  select  (more...)

Exadata:Smart Scan(一)

Smart Scan是Exadata的主要特性之一,该特性主要依赖于于Exadata Storage Software:

[oracle@database ~]$ sqlplus  maclean/maclean

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 18 22:46:39 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data  (more...)

增量检查点如何更新控制文件?

有同学在 T.askmaclean.com上提问关于增量检查点更新控制文件的问题:

Know more about checkpoint

checkpoint 分成很多种 full 、file、thread、parallel query、 object 、incremental 、logfile switch

每一种checkpoint 都有其自身的特性,例如Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile 但是不更新datafile header, 而FULL CHECKPOINT要求立即完成(同步的) 且会同时更新 controlfile 和 datafile header。

Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile
>>我想问的时:如何查看此时控制文件中更新的SCN?除了DUMP控制文件,有没有命令查询?

我希望通过以下演示说明该问题:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition  (more...)

诊断ORA-08103错误

ORA-08103问题的诊断最好是能生成8103错误的ERROR STACK TRACE, 在TRACE中会记录具体引发8103的对象的OBJ和OBJD,这便于我们定位可能存在corruption的对象。

问题在于往往前台进程遇到ORA-08103错误不会在后台生成TRACE文件,这需要我们手动设置8103 触发ERRORSTACK的EVENTS:

ALTER SYSTEM SET  EVENTS  ’ 8103 TRACE NAME ERRORSTACK LEVEL 3′;

解决思路包括:
1. 通过OBJD和DBA定位到具体的表名和块号
2. 有条件的情况下对该表做一个analyze .. validate structure
3. 有条件的情况下对该表所在tablespace做一个 dbms_space_admin.ASSM_TABLESPACE_VERIFY
4. 有条件的情况下move这张表或者相关的分区,尝试绕过该问题
5. 有条件的情况下降该表或分区移动到MSSM表空间上,绕过该问题

[oracle@nas ~]$ oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause: The object has been deleted  (more...)

Exadata混合列压缩如何处理INSERT和UPDATE

Hybrid Columnar Compression混合列压缩是Exadata数据库一体机的核心功能之一,与普通的高级压缩特性(advanced compression)不同,Hybrid columnar compression (HCC) 仅仅在Exadata平台上可用。使用HCC的情况下数据压缩存放在CU(compression unit压缩单位中),一个CU单位包括多个数据库块,这是出于单数据块不利于以列值压缩算法的考量所决定的,当一个CU包含多个block时可以实现较优的列值压缩算法。

同时对于普通的INSERT/UPDATE操作,需要造成对行级数据的压缩降级,即在经历UPDATE/INSERT后原本HCC压缩的行可能变成普通高级压缩的水平。

hybrid columnar compression与数据仓库行为的批量初始化导入(bulk initial load)配合,直接路径导入(direct load)例如ALTER TABLE MOVE, IMPDP或直接路径插入(append INSERT),使用HCC的前提是这些数据将不会被频繁修改或从不被修改。

当你更新混合列压缩启动的表中的数据行时,相关整个的压缩单位CU中的数据将被锁住。 被更新的这些数据将不得不从原HCC压缩级别降级到例如无压缩或for OLTP压缩的水准。

我们来看以下例子:

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 12 06:14:53 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database  (more...)