Script: AWR snap sessions,load profile, SCN or Transactions per second

自定义生成一些报表,曲线数据从AWR 数据表。

About like 5% selectivity引起的不完美执行计划

CBO优化器的内部有两个重要的标准:selectivity and cardinality,cardinality 就CBO在处理完后返回的行数,selectivity是返回数据范围百分比,cardinality = (number of input rows)* selectivity,所以selectivity 的直接影响了CBO表访问路径

rlwrap 独立的历史操作记录

我是习惯在sqlplus中做一些操作,在linux中安装rlwrap 支持方向键,但如果多个用户在同一台机器上登录,每个人常用的命令不同,方向键查找起来很麻烦,so.那就把我们经常用的操作分类或以用户分类,设立独立的历史操作记录log


Hint提示是优化SQL的一种手段,但不应该放在首位,记的国外有位大师说过顺序应该是学写SQL,写更好SQL,学写ORACLE SQL,写更好的ORACLE SQL,在了解原理且CBO没有走理想路线的情况下再去指引CBO。

Use “show paramter” in sqlplus display Hidden Init Parameter

有时需要参考一些隐藏参数值了解ORACLE内部的限制,在sqlplus 中使用show parameter 显示隐藏参数..

more about session_cached_cursors

Session cursor caching does not mean that server process caches the whole SQL area in the PGA.It just means that Oracle does not release the cursor object when the same statement was executed more than 2 times in the same process…

bind variable在11g 中出现Cursor: mutex S

今天看到一个案例,从10G升级到11GR2后cpu负载迅速上升,很快达到100%,通过OWI显示出现大量“Cursor: mutex S”事件,mutex S是发生在cursor cache上的序列化mutex

Installing Enterprise Manager Cloud Control 12c r2 Agent on Linux(安装em12c agent)

Oracle Management Agent 12c communicates only with Oracle Management Service 12c and not with any earlier release of Enterprise Manager

Installing Enterprise Manager Cloud Control 12c release2 on Linux(安装em12c)

EM 12c在安装时和11EM相似,只是增加了Plug-ins 的选择,Plug-ins 和 Connectors做为GRID CONTROLE的扩展并增加了一些非oracle的产品整合为”Cloud”云管理包含DATABASE,Middleware,VM server

SQL ordered by Version Count and Troubleshooting

the cause:
bug,related to name resolution issues, bind buffer length, fine grained access control, cursor invalidation, or any of the other common structural or coding reasons for multiple child cursors


DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during

RMAN-08137: WARNING archived log not deleted, needed for standby or upstream capture process

When he tried deleting archivelog manually with RMAN on primary db host, he got following warning:

“归档日志未删除, 因为备用或上游捕获进程需要它”

Memory Notification: Heap size nnnnK exceeds notification threshold (51200K)

Memory Notification: Library Cache Object loaded into SGA
Heap size 106084K exceeds notification threshold (51200K)
KGL object name …


know more about dbms_job fails

When job execution fails, the SNP processes attempts to rerun the job one minute later. If this run fails, another attempt is made in two minutes and another in four minutes. The job queue doubles the retry interval until it exceeds the normal execution interval..

sqlplus autotrace sql Statistics value全部为0

0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
62 rows processed

ora-600 [kcbgcur_9] internal error when ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [8388681], [25],
[100663296], [33554432], [], [], []

How to stop or kill datapump jobs?(停止数据泵job)

3 begin
4 hdl := dbms_datapump.attach(‘SYS_IMPORT_FULL_03′,’SYSTEM’);
6 end;

迁移controlfile 从文件系统到ASM

alter system set control_files=’+DATA/ANBOB/CONTROLFILE/current.260.793738081′,’+DATA/ANBOB/CONTROLFILE/current.261.793738087′,’+DATA/ANBOB/CONTROLFILE/current.276.793737555′ scope=spfile;

计算controlfile size,dd 迁移raw device中的controlfile

Control Seq=53995=0xd2eb, File size=594=0x252
File Number=0, Blksiz=16384, File Type=1 CONTROL
Logical block number 1 (header block)

Script: 查看datafile 的HWM,估算resize 最小size

数据文件如果有2g,先create一个大表占了1G,后来建了个几M小表,再把大表drop 掉加purge选项,向把数据文件resize到100M,往往会失败,在磁盘紧张的情况下,想知道数据文件最小值是多少?就要计算datafile HWM