Why not use index oracle? 为什么不用索引？ 检查列表
sys_op_descend – Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.
Control the order in which the tables accessed join using sql hint leading and ordered
The LEADING hint causes Oracle to use the specified table as the first table in the join order.
If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.
Optimizer_mode ALL_ROWS and FIRST_ROWS[_N]
all_rows是默认参数，first_rows只是为了向前兼容，ORACLE建议你平时用时使用first_rows_N,N的值越大就越像ALL_ROWS靠近。
…
sql performance truning ,hint,parallel,10053 trace
在两个表的JOIN 中，CBO总是会计算三种JOIN的连接方式的代价，分别为NL JOIN,SM JOIN,HA JOIN,三种JOIN对前后两表的我称呼还不一样，分别是NL JOIN :outer table,inner table,SM :firest table,second table,HA:build table ,probe table,Nested jOIN中的outer table又叫driver table…
oracle 消除块竞争(hot blocks)
SELECT p1 “file#”, p2 “block#”, p3 “class#”
FROM v$session_wait
WHERE event = ‘read by other session’;
oracle high load案例及AWR分析过程
Begin Snap: 18125 20-Apr-12 11:01:00 337 1.0
End Snap: 18126 20-Apr-12 12:00:28 490 1.0
Elapsed: 59.46 (mins)
DB Time: 12,979.44 (mins)
AWR 之table scans (short tables)
Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD or the WriteAccelerator will significantly increase the speed of small-table full-table scans.
AWR中的physical writes
investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process.
AWR 中的consistent gets – examination
“Consistent gets – examination” is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O
QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)
前天有个朋友问我了个问题发现v$sqlarea 中文本相同的sql还有很多记录，也就是并未共享游标，也许很多人知道当cursor_sharing=force，可以尽可能的减小hard parse,记住是min,并不是完全防止硬解析，因为解析也很浪费资源
bulk collect into forall insert批量分次提交
难免开始以为批量提交的小事务会比一个大事务完成后一次提示会更快，可以TOM也再三强调这种想法是不对的，如果非要分次，最好的方法是按业务过程的要求以适当的频度提交，并且相应地设置undo大小 。
Events latch: cache buffers chains
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 26,173 42.3
latch: cache buffers chains 21,954 4,041 184 6.5 Concurrency
Tuning enq: TM – contention with foreign key (外键引起的队列)
通常会发生此事件的等待，因为包含外键约束的列上缺少索引。 在这种情况下，Oracle在DELETE，INSERT和UPDATE语句期间被迫在子表上获取TM锁。..
fast delete, Best practice on Very large table
Today I read foreign blog,I think I learned the true knowledge
One of most operations we are performing is “Deleting many rows from oracle big tables” ;
利用rowid快速在线更新海量数据
declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
cursor cur_t2 is….
Clustering_Factor(索引的集群因子)对执行计划影响
今天 在一个oracle高级调优的例子，提到Cluster_Factor的概念，觉的很有意思，在这分享一下,CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差….
oracle 同样的sql生产库比测试库执行慢(案例)
昨天开发的让我从生产库中同步一个库到测试机，刚好手头有这周一的exp备份，imp了一份，但是今天早上开发打电话找我说情况有点不对头，同一个SQL在生产库上与测试库的执行时间差很多，测试库2秒，生产库要1分钟多 …
Troubleshooting Wait event “enq:TX – row lock contention”
A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.
EXP 边导出边压缩打包
exp 可以导出oracle 的数据库逻辑备份，但一般都是导出完成后再用压缩工具打包，有没有边导出连压缩的办法呢？有
下面就用exp\gzip实现
shell版本bash，在rhel 5下操作
….
