•找到最佔用資源的SQL語句
–V$SQLAREA (Shared_pool)
–V$session_longops(6秒)
–StatsPack Report
–SQL*Trace + TKProf
–10g ADDM
–Toad、Quest Data Center
–…
•問題定位 How to find Bad SQL
–V$SQLAREA (Shared_pool)
–StatsPack
–SQL*Trace + TKProf
–10g ADDM
•優化SQL語句
–理解優化器、CBO & RBO和執行計劃
–Explain Plan, Tkprof & SQL_TRACE, autotrace, Toad
–Tune Join (Sort Merge, Nest Loop, Hash Join)
–Tune Index, MV (Summery and Join Table)
–分割槽、並行、使用hint和特殊SQL的優化
---
查詢大量邏輯讀的語句
select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc;
SQL語句的優化:
•索引和資料訪問
索引分類:
邏輯上
–單列或組合索引
–唯一非唯一索引
物理上
–分割槽和非分割槽索引
–B-tree 和 bitmap
正常或逆向(B-tree only)
–函式索引
•Join模式
•優化提示hint
•分割槽
•物化檢視
•並行
•關於SQL優化的其它事項
Oracle如何訪問資料
•Single Table Access Path
–Full Table Scan
–ROWID unique scan
–Index unique scan
–Index range scan
–Unique Index Range Scan
–Index Skip Scan
–Index (fast) full scan
–Bitmap Index
•Table Join
1, Nested Loop
The driving table should be small
The other tables is indexed.
2, Sort Merge
For large data sets (Sort_Area_Size)
The row sources are sorted already.
A sort operation does not have to be done.
3, Hash Join
For large data sets(Hash_Area_Size)
Optimizer_mode=CBO
Pga_aggregate_target is big enough
如何發現等待事件
