正文

oracle数据库优化辅助SQL语句

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0928

这篇文章主要介绍了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、%导致的索引失效