Performance tuning

When multiple connection created will insert a lot of rows in aud$ table, there may be contention in the segment header for the table. This contention can manifest itself as a buffer busy wait or a freelist wait.

今天开发的同事问有个表上的数据为什么删不掉？我看了一下，也就不到100000条数据，表上有外键，等了5分钟hang在那里…

前两天对oracle数据库(single instance)进行了迁移升级从10.2.0.4 升到11.2.0.3，有一个项目迁完后第二天，cpu负载升到了130更高（16cpus）. 向用户询问后使用上没有改变，平时就几个人使用，而该用户活动会话就有100多个。最在等待CBC latch. 怀疑是执行计划发生了改变。

log file sync wait 默认是发生在前台进程发进commits时比如用户commit,DDL,递归操作发生在dictionary table 上的DML,同时rollback 也会导致…

A few days ago a friend ask me to see a awr issue. In the TOP 5 Times Events, as you see, Obviously,database has relatively high sbtbackup wait event, Backup:sbtbackup falls under the administrative category also realted to the RMAN. These wait events will occur when there is RMAN backup running on the server. Backup: … Read more

This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it.

The log file sync wait occurs at the end of a transaction (COMMIT or End Of Job)and the database writer (DBWR) process must wait for the log file to synchronize with the database.

创建索引可以方便的给DB带来查询上的性能提升，但是DML时index的维护代价也是昂贵的。合理的创建索引，删除不必要的索引。

You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.

In some cases the query optimizer might can not accurately estimate cardinality (e.g. group by having or like with bind variables cause etc..),therefore the Cardinality estimates are based on default seletivity of build-in oracle coredefault seletivity(5%).

Now I used segment advisor performs analysis on the segments and makes recommendations on how space can be reclaimed…

今天查看AWR时，发现有个sql占用的cpu很高而且也并不复杂，top 5 event出现了cpu,read by other sessions,而且sql中再次看到的hint index,随后从生产环境中拿到绑定变量参数值进行本地调试，发现hint index 选择了错误的索引一致读为93万，去掉hint立即降低到了36，随后服务器压力将了下来

The log file switch – checkpoint incomplete event tells you that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log file. This results in redo generation being stopped until the log switch is done.

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

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

启用物化视图的重写有两个条件QUERY_REWRITE_ENABLED=true and materialized view enable query rewrite.
在CBO对查询sql 重写后估算cast后直接查询mv