Oracle 12c: index treedump

oracle 12C的index treedump比11G有了一点小变化, 这里简短的记录一下.
branch: 0x300252b 50341163 (0: nrow: 3, level: 1)
leaf: 0x300252c 50341164 (-1: row:485.0 avs:823)

, ,

Debug oracle 常用的一些trace命令

当oracle遇到问题时, 当表面的现象和现有的log无法为我们诊断问题提供足够的信息时, 希望可能通过打开oracle 的debug开关,生成更详细的trace 文件提供更多的信息, 这里整理了一些trace的命令.

The “abnormal” table column , Function index ,VIRTUAL column,unused column (说说那些sys_开头的列名)

有时从dba_tab_cols看到的表名是奇怪的sys_开头,有时在desc table时不显示,这里记录一下 Function index \VIRTUAL column \ unused column对列的影响,除了unsed column会把列搞的很“不一般” ,还有一些特殊场景。

How to estimate the cost if partition table missing partition level statistics ?(缺失分区表级统计信息如何计算多分区扫描的COST)

首先查看表的统计信息,分区表的统计信息分global stats(DBA_TABLE)和partition stats(DBA_TAB_PARTITION),发现该分区表是global stats 有,partition stats全为空, 查看该SQL发现有两个子游标有不同的执行计划

The FG(server process) and remote node LMSn process communication over the interconnect?(用户进程会和另一节点的LMS进程直接通信么?)

应该存在local SERVER process和remote LMS process通信, 原厂的工程师给我看了他们的白皮书来证明应该只有LMS和LMS通信,究竟是什么情况, LMS会不会直接和远程的SERVER进程通信?

,

Some interesting about oracle, did you know? (一)

By default DBUA removes all the hidden parameters during upgrade.If you want to keep the hidden parametersinvoke DBUA with ‘keepHiddenParams’ option.e.g./dbua -keepHiddenParams

More about dead transactions recovery and cleanup(死事务的回滚清理)

If you need to recovery the dead transaction record of more than 100(_cleanup_rollback_entries parameter value), PMON only responsible for cleaning up dead session, which is responsible for the SMON recovery dead transaction.

How to get call stack of oracle processes ?

Each time your program performs a function call, information about the call is generated. That information includes the location of the call in your program, the arguments of the call, and the local variables ..

,

scn format (scn格式)

scn(system change number) 结构主要维护 oracle数据库内部的数据一致性,SCN 有两部分组成: Base and wrap,wrap 是16bit的数字,base是32bit的数字,这样其实就可以算scn的有效范围,它的格式(redo dump trace)是wrap.base,当base超过了2的32次方,然后wrap 就会加1,其实用sql 很好验证。

To hold the latch manually, DB hang demo(手动持用latch使数据库hang)

It is widely known that the Oracle server uses kslgetl – Kernel Service Lock Management Get Latch function to acquire the latch,oradebug call kslgetl/kslfre can be used to acquire the latch manually. This is very useful to simulate latch related hangs and contention.

More about ITL in oracle

ITL(Interested Transaction List ),The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL).

DB File Sequential and Scattered Reads

Both “db file sequential read” and “db file scattered read” events signify time waited for I/O read requests to complete.

Estimate of the duration of a rollback operation(估算rollback事务回滚剩余时间)

when a long, running transaction has been rolled back, How Much Longer completion time of the rollback process? The questions are justified, because the transaction holds the locks and normal processing often suffers as the rollback progresses.

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…

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..

About Columns Order(列先后顺序)

有时总是忽略在列的顺序带来的性能开销,还有列的null值字段的位置,想理解这个问题要首先程序列数据是如何在block上存储的,并且是如何定位的?

揭秘 Oracle 11gR2 RAC ASM Instance 启动

存储asm instance spfile到asm,听上去很奇怪,启动自身,首先要解决的几个问题
1,asm diskgroup 没mount,如何读spfile?
2,在oracle_home/dbs下没有参数文件,如何知道spfile在哪?
3,不知道spfile,怎么知道的asm_diskstring?

,

SCN fast increase by database link(scn增长因DB_LINK)

为了保证分布式查询的一致性,通过dblink查询时会同步SCN,有时会撞见ORA-600错误。
alert日志中伴随着warning Rejected the attempt to advance SCN over limit

还原data block dumps实际值

转储了oracle block的值,如何得到它真正表中的值,也算 是dump(val,16)的逆运算,只含char,number,date