-
编辑 删除
查找bad sql的方法
值得怀疑的SQL select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_textfrom ( select address, disk_reads, executions, pct, rank() over (order by disk_reads desc) ranking from ( select address, disk_reads, executions, 100 * ratio_to_report(disk_reads) over () pct from sys.v_$sql where command_type != 47 ) where disk_reads > 50 * exec
-
编辑 删除
高效的SQL
1 最高效的删除重复记录方法 （ 因为使用了ROWID）DELETE FROM EMP EWHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP XWHERE X.EMP_NO = E.EMP_NO);[@more@]
-
编辑 删除
找出高CPU利用率的SQL：
SELECT /*+ ORDERED */sql_textFROM v$sqltext aWHERE (a.hash_value, a.address) IN(SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)FROM v$session bWHERE b.paddr = (SELECT addrFROM v$process cWHERE c.spid = '&pid'))ORDER BY piece ASC;查看该sql语句的执行计划（发现是全表扫）；分析这张表：analyze table t1 compute statistics for all indexed columns;再查看该sql语句的执行计划（rowid扫）；再查看OS的CPU利用率，显著下降。[Ref: http:/
