首页
首页
文章目录
  1. 简介
  2. 查看总消耗时间最多的前10条SQL语句
  3. 获取单次执行耗时最长的10个SQL语句
  4. 获取执行次数最多的10个SQL
  5. 查看CPU消耗时间最多的前10条SQL语句
  6. 查看消耗磁盘读取最多的前10条SQL语句
  7. linux 进行impdp导入步骤
  8. window 进行expdp导出步骤

oracle一些常用语句

简介

本文主要写如何查询oracle耗时最长的语句与执行次数最多的语句,cpu耗时最高的语句等等一系列针对oracle优化的语句

查看总消耗时间最多的前10条SQL语句

1
2
3
4
5
6
7
8
9
10
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

获取单次执行耗时最长的10个SQL语句

1
2
3
4
5
6
7
8
9
10
11
select sql_id,sql_text,round(exec_time/1000000,0) exec_time
from(
select sql_id,sql_text,exec_time,rank() over (order by exec_time desc) exec_rank
from
(
select sql_id,sql_text,cpu_time,elapsed_time,executions,round(elapsed_time/executions,0) exec_time
from v$sql
where executions>1
)
)
where exec_rank <=10;

获取执行次数最多的10个SQL

1
2
3
4
5
6
select sql_text,executions
from (
select sql_text,executions,rank() over(order by executions desc) exec_rank
from v$sql
)
where exec_rank <=10;

查看CPU消耗时间最多的前10条SQL语句

1
2
3
4
5
6
7
8
9
10
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

查看消耗磁盘读取最多的前10条SQL语句

1
2
3
4
5
6
7
8
9
10
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

linux 进行impdp导入步骤

1
2
3
4
5
6
7
8
创建文件夹 /home/oracle/impdp_dir
mkdir /home/oracle/impdp_dir
文件夹权限 chmod 777 /home/oracle/impdp_dir
sqlplus /nolog
connect /as sysdba
create or replace directory impdp_dir as '/home/oracle/impdp_dir';
grant read,write on directory impdp_dir to herendh;
impdp herendh/herendh DIRECTORY=impdp_dir DUMPFILE=HEREN0724.DMP schemas=herendh logfile=2.log

window 进行expdp导出步骤

1
2
3
4
5
6
创建文件夹 c:\heren;
sqlplus /nolog
connect /as sysdba
create or replace directory dir_dump as 'c:\heren';
grant read,write on directory dir_dump to herendh;
expdp herendh/herendh DIRECTORY=dir_dump DUMPFILE=HEREN0724.DMP schemas=herendh logfile=2.log
支持一下
扫一扫,我会更有动力更新
  • 微信扫一扫
  • 支付宝扫一扫