抓取oracle数据库耗费资源的sql语句

oracle数据库连接业务系统，而有些sql语句的执行严重影响了oracle的性能，就如同mysql的慢查询一样，mysql可以开启慢查询日志定位这些造成数据库性能下降的语句，而oracle同样可以做到，而且有过之而无不及。

下面举例说明

首先用root用户登陆核心应用系统，su - oracle

执行命令 topas

可以查看到如下信息：

解释三个地方

1.Idle%：是cpu的空闲率

2.PID：进程唯一标示符

3.CPU%：该进程所占用的cpu的百分比

从上述信息可以定位进程为1454604的进程占用资源比较高，查询该进程的sql语句情况。

登陆核心系统oracle数据库，执行以下语句

select

c.spid,a.p1,a.p1raw,a.p2,a.event,b.sql_text,b.SQL_FULLTEXT,b.SQL_ID

2. from v$session a,v$sql b,v$process c

3. where a.wait_class<>'Idle' and a.sql_id=b.sql_id and a.PADDR=c.addr 4. order by event; 1.

情况如下图所示：

找到对应的SPID，点击SQL_FULLTEXT列下的CLOB后面的按钮，可以查看耗费系统资源的具体sql语句。 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11. select 'X' from lctcont where '1344926626000'='1344926626000' and certifycode like 'TTS%' and grpcontno in ( select c.grpcontno from ljaget a ,ljagetendorse b ,lcgrpcont c where a.EnterAccDate is null and a.actugetno = b.actugetno and b.grpcontno = c.grpcontno and a.otherno='8053000000139568' )

找到了根源，接着就是如何解决，可见由于字段grpcontno上没有索引，导致表lctcont的全表扫描，最终造成整个执行计划的错乱（其中有笛卡尔积）。因此，正确的再字段grpcontno上创建索引，即可解决此问题。由于这是开发人员写的sql语句，所以对于sql

语句的优化，要由他们来配合操作，作为dba不但要懂得如何在内存体系结构上优化数据库，更要懂得协同开发人员一起进行oracle数据库的优化。

 

第二篇：数据库集合(oracle sql)查询语句

oracle1. select * from emp;2. select empno, ename, job from emp;3. select empno 编号, ename 姓名, job 工作 from emp;4. select job from emp;5. select distinct job from emp;6. select distinct empno, job from emp;说明:因为雇员编号不重复, 所以此时证明所有的列没有重复,所以不能消除掉重复的列.7. 查询出雇员的编号, 姓名, 工作, 但是显示的格式:编号是: 7369 的雇员, 姓名是: smith, 工作是: clearselect '编号是: ' || empno || '的雇员, 姓名是: ' || ename || ', 工作是: ' || job from emp;8. 求出每个雇员的姓名及年薪select ename, sal * 12 income from emp;9. 求出工资大于 1500 的所有雇员信息select * from emp where sal > 1500;10. 查询每月可以得到奖金的雇员信息select * from emp where comm is not null;11. 查询没有奖金的雇员信息select * from emp where comm is null;12. 查询出基本工资大于 1500 同时可以领取奖金的雇员信息select * from emp where sal > 1500 and comm is not null;13. 查询出基本工资大于 1500 或者可以领取奖金的雇员信息select * from emp where sal > 1500 or comm is not null;14. 查询出基本工资不大于 1500 或者不可以领取奖金的雇员信息select * from emp where not(sal > 1500 and comm is not null);15. 查询基本工资大于 1500, 但是小于 3000 的全部雇员信息select * from emp where sal > 1500 and sal < 3000;16. 查询基本工资大于等于 1500, 但是小于等于 3000 的全部雇员信息select * from emp where sal >= 1500 and sal <= 3000;select * from emp where sal between 1500 and 3000;17. 查询出在 1981 年雇佣的全部雇员信息(1981 年 1 月 1 日 到 1981 年 12 月 31 日之间的雇佣的雇员)select * from emp where hiredate between '1-1月-81' and '31-12月-81';18. 要求查询出姓名是 smith 的雇员信息select * from emp where ename = 'SMITH';19. 要求查询出雇员是 7369, 7499, 7521 的雇员的具体信息select * from emp where empno = 7369 or empno = 7499 or empno = 7521;select * from emp where empno in(7369, 7499, 7521);20. 要求查询出雇员不是 7369, 7499, 7521 的雇员的具体信息select * from emp where empno not in(7369, 7499, 7521);21. 要求查询出姓名是 smith, allen, king 的雇员信息select * from emp where ename in('SMITH', 'ALLEN', 'KING');22. 查询出所有雇员姓名中第二个字母包含 "M" 的雇员信息select * from emp where ename like '_M%';23. 查询出雇员姓名中包含字母 M 的雇员信息select * from emp where ename like '%M%';24. 要求查询出在 1981 年雇佣的雇员信息select * from emp where hiredate like '%81%';25. 查询工资中包含 5 的雇员信息select * from emp where sal like '%5%';26. 查询雇员编号不是 7369 的雇员信息select * from emp where empno != 7369;select * from emp where empno <>

7369;27. 要求按照工资由低到高排序select * frm emp order by sal;select * from emp order by sal asc;28. 要求按照工资由高到低排序select * from emp order by sal desc;29. 要求查询出 20 部门的所有雇员信息, 查询的信息按照工资由高到低排序,如果工资相等,则按照雇佣日期由早到晚排序.select * from emp where deptno = 20 order by sal desc, hiredate asc;30. 将小写字母变为大写字母select upper('hello') from dual;31. 将大写字母变为小写字母select lower('HELLO WORLD') from dual;32. 要求查询出姓名是 smith 的雇员信息select * from emp where ename = upper('smith');33. 使用 initcap() 函数将单词的第一个字母大写select initcap('hello world') from dual;34. 将雇员表中的雇员姓名变为开头字母大写select initcap(ename) from emp;35. 将字符串 "hello" 和 "world" 进行串联select concat('hello ', 'world') from dual;36. 对字符串进行操作的常用字符处理函数select substr('hello', 1, 3) 截取字符串, length('hello') 字符串的长度, replace('hello', 'l', 'x') 字符串替换 from dual;select substr('hello', 0, 3) 截取字符串, length('hello') 字符串的长度, replace('hello', 'l', 'x') 字符串替换 from dual;37. 显示所有雇员的姓名及姓名的后三个字符select ename, substr(ename, length(ename) -2) from emp;select ename, substr(ename, -3, 3) from emp;38. 使用数值函数执行四舍五入操作select round(789.536) from dual;39. 要求将 789.536 数值保留两位小数select round(789.536, 2) from dual;40. 要求将 789.536 数值中的整数的十位进行四舍五入进位select round(789.536, -2) from dual;41. 采用 trunc() 函数不会保留任何小数,而且小数点也不会执行四舍五入的操作select trunc(789.536) from dual;42. 通过 trunc() 也可以指定小数点的保留位数select trunc(789.536, 2) from dual;43. 作用负数表示位数select trunc(789.536, -2) from dual;44. 使用 mod() 函数可以进行取余的操作select mod(10, 3) from dual;45. 显示 10 部门雇员进入公司的星期数(当前日期 - 雇佣日期 = 天数 / 7 = 星期数)select empno, ename, round((sysdate - hiredate) / 7) from emp where deptno = 10;46. 日期函数months_between(): 求出给定日期范围的月数add_months(): 在指定的日期上加上指定的月数, 求出之后的日期next_day(): 指定日期的下一个日期last_day(): 求出给定日期当月的最后一天日期47. select empno, ename, months_between(sysdate, hiredate) from emp;select empno, ename, round(months_between(sysdate, hiredate)) from emp;48. select sysdate, add_months(sysdate, 4) from dual;49. select next_day(sysdate, '星期一') from dual;50. select last_day(sysdate) from dual;51. 转换函数to_char(): 转换成字符串to_number(): 转换成数字to

_date(): 转换成日期52. 查询所有雇员的雇员编号, 姓名, 雇佣日期select empno, ename,to_char(hiredate, 'yyyy') year,to_char(hiredate, 'mm') months,to_char(hiredate, 'dd') dayfrom emp;select empno, ename, to_char(hiredate, 'yyyy-mm-dd') from emp;select empno, ename, to_char(hiredate, 'fmyyyy-mm-dd') from emp;53. 查询所有雇员的编号, 姓名和工资select empno, ename, sal from emp;select empno, ename, to_char(sal, '99,999') from emp;select empno, ename, to_char(sal, 'L99,999') from emp;select empno, ename, to_char(sal, '$99,999') from emp;54. select to_number('123') + to_number('123') from dual;55. 将一个字符串转换成日期类型select to_date('2009-01-01', 'yyyy-mm-dd') from dual;56. 求出每个雇员的年薪(要求加上奖金)select empno, ename, sal, comm, (sal + comm) * 12 from emp;select empno, ename, sal, comm, nvl(comm, 0), (sal + nvl(comm, 0)) * 12 income from emp;57. decode() 函数类似于 if....elsif...else 语句select decode(1, 1, '内容是 1', 2, '内容是 2', 3, '内容是 3') from dual;58. 查询出雇员的编号, 姓名, 雇佣日期及工作, 要求将雇员的工作替换成以下信息:select empno 雇员编号, ename 雇员姓名, hiredate 雇佣日期, decode(job, 'CLERK', '业务员', 'SALESMAN', '销售人员','MANAGER', '经理','ANALYST', '分析员','PRESIDENT', '总裁') 职位from emp;59. 笛卡尔积(交差连接)select * from emp, dept;select * from emp cross join dept;60. 内连接select * from emp e, dept d where e.deptno = d.deptno;select * from emp e inner join dept d on e.deptno = d.deptno;select * from emp e join dept d on e.deptno = d.deptno;61. 自然连接select * from emp natural join dept;select * from emp e join dept d using(deptno); 62. 要求查询出雇员的编号, 姓名, 部门的编号, 名称, 地址select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;63. 要求查询出雇员的姓名, 工作, 雇员的直接上级领导姓名select e.ename, e.job, m.ename from emp e, emp m where e.mgr = m.empno;64. 要求查询出雇员的姓名, 工作, 雇员的直接上级领导姓名以及部门名称select e.ename, e.job, m.ename, d.dname from emp e, emp m, dept d where e.mgr = m.empno and e.deptno = d.deptno;65. 要求查询出每个雇员的姓名, 工资, 部门名称, 工资在公司的等级(salgrade), 及其领导的姓名及工资所在公司的等级select e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.gradefrom emp e, dept d, salgrade s, emp m, salgrade mswhere e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empnoand m.sal between ms.losal and ms.hisal;select e.ename, e.sal, d.dname, decode(s.grade, 1, '第五等级', 2, '第四等级', 3, '第三等级', 4, '第二等级', 5, '第一等级'), m.ename, m.sal, decode(ms.grade,

1, '第五等级', 2, '第四等级', 3, '第三等级', 4, '第二等级', 5, '第一等级')from emp e, dept d, salgrade s, emp m, salgrade mswhere e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empnoand m.sal between ms.losal and ms.hisal;66. select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno;select empno, ename, d.deptno, dname, loc from emp e inner join dept d on e.deptno = d.deptno;67. 左外连接select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno(+);select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;select empno, ename, d.deptno, dname, loc from emp e left join dept d on e.deptno = d.deptno(+);68. 右外连接select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;69. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno;70. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);71. select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;select * from emp e join dept d on e.deptno = d.deptno where d.deptno = 30;select * from emp e natural join dept d where deptno = 30;select * from emp e join dept d using(deptno) where deptno = 30;72. select e.ename, d.deptno, d.dname, d.loc from emp e right outer join dept d on e.deptno = d.deptno;select e.ename, d.deptno, d.dname, d.loc from emp e right join dept d on e.deptno = d.deptno;select e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno(+) = d.deptno;73. select count(ename) from emp;74. select min(sal) from emp;75. select max(sal) from emp;76. select sum(sal) from emp;77. select avg(sal) from emp;78. select sum(sal) from emp where deptno = 20;79. select avg(sal) from emp where deptno = 20;80. 求出每个部门的雇员数量select deptno, count(deptno) from emp group by deptno;select deptno, count(empno) from emp group by deptno;81. 求出每个部门的平均工资select deptno, avg(sal) from emp group by deptno;82. 按部门分组, 并显示部门的名称, 及每个部门的员工数select d.dname, count(e.empno) from emp e, dept d where e.deptno = d.deptnogroup by d.dname;select d.deptno, d.dname, temp.cfrom (select deptno, count(e.empno) c from emp e group by e.deptno) temp, dept dwhere temp.deptno = d.deptno;83. 要求显示出平均工资大于 2000 的部门编号和平均工资select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;84. 显示非销售人员工作名称以及从

事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于 5000, 输出结果按月工资的合计升序排序.select job, sum(sal) su from emp where job <> 'SALESMAN' group by job having sum(sal) > 5000 order by su;select temp.job, sum(temp.sal) sfrom (select job, sal from emp e where job <> 'SALESMAN') tempgroup by temp.jobhaving sum(temp.sal) > 5000order by s;85. 求出平均工资最高的部门工资select max(avg(sal)) from emp group by deptno;86. 要求查询出比雇员编号为 7654 工资高的所有雇员信息select * from emp where sal >(select sal from emp where empno = 7654);87. 要求查询出工资比 7654 高, 同时与 7788 从事相同工作的全部雇员信息select * from emp where sal >(select sal from emp where empno = 7654)and job = (select job from emp where empno = 7788);88. 要求查询出工资最低的雇员姓名, 工作, 工资select ename, job, sal from emp where sal = (select min(sal) from emp);89. 要求查询出:　部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名select d.dname, temp.c, temp.a, e.ename from dept d, (select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp, emp ewhere d.deptno = temp.deptno and e.sal = temp.m;select d.deptno, temp.dname, temp.c, temp.a, e.ename, e.salfrom (select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) mfrom emp e, dept dwhere e.deptno = d.deptnogroup by d.dname) temp, emp e,dept dwhere temp.m = e.saland temp.dname = d.dname;90. 求出每个部门的最低工资的雇员的信息select * from emp where sal in(select min(sal) from emp group by deptno);select * from emp where sal =any(select min(sal) from emp group by deptno);select * from (select min(sal) m from emp group by deptno) temp,emp ewhere e.sal = temp.m;91. 范例 90 中, 比子查询条件中最低(小)的工资要大的雇员信息select * from emp where sal >any(select min(sal) from emp group by deptno);select * from emp where sal > (select min(min(sal)) from emp group by deptno);92. 范例 90 中, 比子查询条件中最高(大)的工资要小的雇员信息select * from emp where sal <any(select min(sal) from emp group by deptno);select * from emp where sal < (select max(min(sal)) from emp group by deptno);93. 范例 90 中, 比子查询条件中最高(大)的工资要大的雇员信息select * from emp where sal >all(select min(sal) from emp group by deptno);select * from emp where sal > (select max(min(sal)) from emp group by deptno);94. 范例 90 中, 比子查询条件中最低(小)的工资要小的雇员信息select * from emp where sal <all(select min(sal) from emp group by deptno);select * from emp where sal < (select min(min(sal)) from emp group by deptno);95. 查找出 20 部门中没有奖金的雇员信息select * from emp where (sal,

nvl(comm, -1)) in (select sal, nvl(comm, -1) from emp where deptno = 20);select * from emp where deptno = 20 and comm is null;96. union 操作符返回两个查询选定的所有不重复的行select deptno from emp union select deptno from dept;97. union all 操作符合并两个查询选定的所有行，包括重复的行select deptno from emp union all select deptno from dept;98. intersect 操作符只返回两个查询都有的行select deptno from emp intersect select deptno from dept;99. minus 操作符只返回由第一个查询选定但是没有被第二个查询选定的行, 也就是在第一个查询结果中排除在第二个查询结果中出现的行select deptno from dept minus select deptno from emp;sqldistinct关键字显示没有重复记录的商品名称，商品价格和商品类别列表。select distinct ware_name,price from t_ware;使用计算列查询所有商品价格提高20%后的价格。select ware_id,ware_name,price*1.2 from t_ware;列的别名不使用asselect ware_id,ware_name,price*1.2 as price_raise from t_ware;使用asselect ware_id,ware_name,price*1.2 price_raise from t_ware;使用逻辑表达式not显示商品价格不大于100的商品select ware_id,ware_name,price,category_id from t_ware where not price>100;and显示商品价格大于100且商品类别编号为5的商品select ware_id,ware_name,price,category_id from t_ware where not price>100;or显示商品类别编号为5或6或7的商品select ware_id,ware_name,price,category_id from t_ware where category_id=5 or category_id=6 or category_id=7;使用between关键字显示商品价格在200元至1000元之间的商品（留心一下，是半开区间还是封闭区间？）select ware_id,ware_name,price,category_id from t_ware where price between 200 and 1000;使用in关键字显示商品类别为5，6，7且价格不小于200元的商品select ware_id,ware_name,price,category_id from t_ware where category_id in (5,6,7) and price>=200;使用like子句进行模糊查询a) %（百分号）表示0到n个任意字符select ware_id,ware_name,price,category_id from t_ware where ware_name like '%纯棉%';b) _（下划线）表示单个的任意字符select ware_id,ware_name,price,category_id from t_ware where ware_name like '%长袖_恤%';转义字符escape的使用select ware_id,ware_name,price,category_id from t_ware where ware_name like '%\%%' escape '\';使用order by给数据排序select * from t_ware_category where parent_id=0 order by seq;select * from t_ware_category where parent_id=0 order by seq asc;select * from t_ware_category where parent_id=0 order by seq desc;rownum查询前20条商品记录select ware_id,ware_name,price from t_ware where rownum<=20;查询第11条至第20条记录select ware_id,ware_name,price from t_ware where rownum<=10 and ware_id not in (select ware_id from t_ware where

rownum<=10);常用统计函数sum()返回一个数字列或计算列的总和select sum(price) from t_ware;avg()对一个数字列或计算列求平均值min()返回一个数字列或一个数字表达式的最小值max（）返回一个数字列或一个数字表达式的最大值count()返回满面足select语句中指定的条件的记录值多表查询和笛卡儿乘积查询商品编号，商品名称，商品价格和商品类别名称select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware,t_ware_category where t_ware.category_id=t_ware_category.category_id;使用join左连接select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware left join t_ware_category on t_ware.category_id=t_ware_category.category_id;select w.ware_id,w.ware_name,w.price,wc.category_name from t_ware w left join t_ware_category wc on w.category_id=wc.category_id;右连接select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware left join t_ware_category on t_ware.category_id=t_ware_category.category_id;使用unionselect ware_id,ware_name from t_ware where ware_name like '%T恤%' union select ware_id,ware_name from t_ware where ware_name like '%手提包%'使用group by统计每个二级类别下有多少商品，以及商品总价值select w.category_id,wc.category_name,count(w.ware_id),sum(w.price) from t_ware w left join t_ware_category wc on w.category_id=wc.category_id group by w.category_id,wc.category_name;统计每个一级类别下有多少商品，以及商品总价值select wc2.category_id,wc2.category_name,sum(w.price) from t_ware w left join t_ware_category wc on w.category_id=wc.category_id left join t_ware_category wc2 on wc.parent_id=wc2.category_id group by wc2.category_id,wc2.category_name;使用having对结果进行筛选select w.category_id,wc.category_name,count(w.ware_id),sum(w.price) from t_ware w left join t_ware_category wc on w.category_id=wc.category_id group by w.category_id,wc.category_name having sum(w.price)>1000;