這篇文章主要介紹了oracle數據庫優化輔助SQL語句,需要的朋友可以參考下

具體詳情請看下文代碼分析。

1. --查詢正在執行的SQL語句 
2.  
3.  
4. SELECT OSUSER 電腦登錄身份,  
5. PROGRAM 發起請求的程序,  
6. USERNAME 登錄系統的用戶名,  
7. SCHEMANAME,  
8. B.Cpu_Time 花費cpu的時間,  
9. STATUS,  
10. B.SQL_TEXT 執行的sql, 
11. B.  
12. FROM V$SESSION A  
13. LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS  
14. AND A.SQL_HASH_VALUE = B.HASH_VALUE  
15. ORDER BY b.cpu_time DESC; 
16.  
17.  
18. --查詢比較耗CPU的SQL語句 
19. select * 
20. from (select v.sql_id, 
21. v.child_number, 
22. v.sql_text, 
23. v.elapsed_time, 
24. v.cpu_time, 
25. v.disk_reads, 
26. rank() over(order by v.cpu_time desc) elapsed_rank 
27. from v$sql v) a 
28. where elapsed_rank <= 10; 
29.  
30. --查詢比較耗磁盤的SQL語句 
31.  
32. select * from (select v.sql_id, 
33. v.child_number, v.sql_text,  
34. v.elapsed_time, v.cpu_time,  
35. v.disk_reads,  
36. rank() over(order by v.disk_reads desc) elapsed_rank  
37. from v$sql v) a where elapsed_rank <= 10; 
38.  
39. --查詢比較慢的SQL語句 
40.  
41. select * from ( 
42. select parsing_user_id,executions,sorts 
43. command_type,disk_reads,sql_text from v$sqlarea order by disk_reads desc 
44. )where rownum<10 
45.  
46. --Oracle 對未提交事務的查詢 
47.  
48. select a.sid,a.blocking_session,a.last_call_et,a.event, 
49. object_name, 
50. dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" , 
51. c.sql_text,c.sql_fulltext 
52. from v$session a,v$sqlarea c ,dba_objects,v$datafile 
53. where a.blocking_session is not null 
54. and a.sql_hash_value = c.hash_value  
55. and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#; 

ps：oracle常見sql語句優化

1、* 號引起的執行效率

盡量減少使用select * 來進行查詢,當你查詢使用*,

數據庫會進行解析并將*轉換為全部列。

select count(si.student_id)

from Student_info si(student_id為索引)

與

select count(*) from Student_info si

執行時.上面的語句明顯會比下面沒有用索引統計的語句要快

2、避免在索引列上使用計算．

WHERE 子句中，如果索引列是函數的一部分.優化器將不使用索引而使用全表掃描.

舉例 :

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效 :

SELECT … FROM DEPT WHERE SAL > 25000/12;

3、用 >= 替代 >

高效 :

SELECT * FROM EMP WHERE DEPTNO >=4

低效 :

SELECT * FROM EMP WHERE DEPTNO >3

兩者的區別在于 , 前者 DBMS 將直接跳到第一個 DEPT 等于 4 的記錄而后者將首先定位到 DEPTNO=3 的記錄并且向前掃描到第一個 DEPT 大于 3 的記錄 .

4 、 用 UNION 替換 OR ( 適用于索引列 )

通常情況下 , 用 UNION 替換 WHERE 子句中的 OR 將會起到較好的效果 . 對索引列使用 OR 將造成全表掃描 . 注意 , 以上規則只針對多個索引列有效 . 如果有 column 沒有被索引 , 查詢效率可能會因為你沒有選擇 OR 而降低 . 在下面的例子中 , LOC_ID 和 REGION 上都建有索引 .

高效 :

1. SELECT LOC_ID , LOC_DESC , REGION 
2.  
3. FROM LOCATION 
4.  
5. WHERE LOC_ID = 10 
6.  
7. UNION 
8.  
9. SELECT LOC_ID , LOC_DESC , REGION 
10.  
11. FROM LOCATION 
12.  
13. WHERE REGION = “MELBOURNE” 

低效 :

1. SELECT LOC_ID , LOC_DESC , REGION 
2.  
3. FROM LOCATION 
4.  
5. WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 

如果你堅持要用 OR, 那就需要返回記錄最少的索引列寫在最前面 .

5、用 IN 來替換 OR

這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在 ORACLE8i 下，兩者的執行路徑似乎是相同的.

低效 :

SELECT …. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

高效

SELECT … FROM LOCATION WHERE LOC_IN IN (10,20,30);

6、避免在索引列上使用 IS NULL 和 IS NOT NULL

7、where執行順序

where執行會從至下往上執行

select *

from student_info si --學生信息表

where si.school_id=10 --學院ID

and si.system_id=100--系ID

擺放where子句時,把能過濾大量數據的條件放在最下邊

8、from字段中的優化：

Oracle安照從右到左的順序加載表數據，應該把可以排除數據最多的表放到后面(基礎表)。

比如，在關聯查詢中，把課程表放到后面，成績表放到前面，因為課程表數據一般比較少，關聯的時候可以快速的過濾掉一些成績數據。

9、索引失效

1、運算導致的索引失效

2、類型轉換導致的索引失效

3、在索引列上進行計算引起的問題

4、 Is not null引起的問題(student_id為索引)

5、Order by導致索引失效(student_id為索引)

6、自動選擇索引

7、 !=導致索引失效

8、%導致的索引失效