SQL Turning

一些收集SQL的語句：）

[@more@]

1．查詢前十條效能差的sql

SELECT *
FROM (SELECT parsing_user_id executions,
sorts,
command_type,
disk_reads,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum < 10;

2．捕捉執行很久的SQL

SELECT username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || '%' AS progress,
time_remaining,
sql_text
FROM v$session_longops, v$sql
WHERE time_remaining <> 0 AND sql_address = address AND
sql_hash_value = hash_value;

3．耗資源的程式(top session)

SELECT s.schemaname schema_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' || to_char(command)) action,
status session_status,
s.osuser os_user_name,
s.sid,
p.spid,
s.serial# serial_num,
nvl(s.username, '[Oracle process]') user_name,
s.terminal terminal,
s.program program,
st.VALUE criteria_value
FROM v$sesstat st, v$session s, v$process p
WHERE st.sid = s.sid AND st.statistic# = to_number('38') AND
('ALL' = 'ALL' OR s.status = 'ALL') AND p.addr = s.paddr
ORDER BY st.VALUE DESC, p.spid ASC, s.username ASC, s.osuser ASC;

4．表資訊收集

收集整個schema下所有物件的統計資訊。

begin

dbms_stats.gather_schema_stats(

ownname => 'SCOTT',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns size skewonly',

degree => 4

);

end;

或者使用：

analyze table scott.test_key compute statistics;

不過這種方法oracle建議不要再用而使用下面的方法

exec dbms_stats.GATHER_DATABASE_STATS( cascade => TRUE);