Performance tuning

sys_op_descend – Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.

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.

all_rows是默认参数，first_rows只是为了向前兼容，ORACLE建议你平时用时使用first_rows_N,N的值越大就越像ALL_ROWS靠近。
…

在两个表的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…

SELECT p1 “file#”, p2 “block#”, p3 “class#”
FROM v$session_wait
WHERE event = ‘read by other session’;

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)

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.

investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process.

“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

前天有个朋友问我了个问题发现v$sqlarea 中文本相同的sql还有很多记录，也就是并未共享游标，也许很多人知道当cursor_sharing=force，可以尽可能的减小hard parse,记住是min,并不是完全防止硬解析，因为解析也很浪费资源

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” ;

declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
cursor cur_t2 is….

一般向一个表插入数据时，为了提速用到append 提示，但有没有想过表上的索引是怎么维护的？

今天 在一个oracle高级调优的例子，提到Cluster_Factor的概念，觉的很有意思，在这分享一下,CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差….

昨天开发的让我从生产库中同步一个库到测试机，刚好手头有这周一的exp备份，imp了一份，但是今天早上开发打电话找我说情况有点不对头，同一个SQL在生产库上与测试库的执行时间差很多，测试库2秒，生产库要1分钟多 …

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.