sql监控与调优(sql monitoring and tuning)
来源：互联网 发布：网络大电影杀小姐 编辑：程序博客网 时间：2024/06/02 01:29
一、监控正在执行的sql的统计信息(11g)
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 b.sql_text sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 order by a.cpu_time desc)
12 where rownum <= 20;
未选定行
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 substr(b.sql_text, 1, 15) sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 and a.status = 'EXECUTING'
12 order by a.disk_reads desc)
13 where rownum <= 20;
未选定行
二、显示查询语句执行时的信息。(11g)
COL SID FORMAT 99999
COL status FORMAT A15
COL start_time FORMAT A12
COL plan_line_id FORMAT 99999 HEAD "Plan ID"
COL plan_options FORMAT A16
COL mem_bytes FORMAT 99999999
COL temp_bytes FORMAT 99999999
SET LINESIZE 132 PAGESI 100 TRIMSP ON
BREAK ON sid on status on start_time NODUP SKIP 1
select a.sid,
a.status,
to_char(a.sql_exec_start, 'yymmdd hh24:mi') start_time,
a.plan_line_id,
a.plan_operation,
a.plan_options,
a.output_rows,
a.workarea_mem mem_bytes,
a.workarea_tempseg temp_bytes
from v$sql_plan_monitor a, v$sql_monitor b
where a.status NOT LIKE '%DONE%'
and a.key = b.key
order by a.sid, a.sql_exec_start, a.plan_line_id;
三、监控sql执行的开始时间，已执行时间和剩余执行时间。
COL how_long FORMAT 99,990 HEAD "Time|Run"
COL secs_left FORMAT 99,990 HEAD "Appr.|Secs Left"
COL sofar FORMAT 9,999,990 HEAD "Work|Done"
COL totalwork FORMAT 9,999,990 HEAD "Total|Work"
COL percent FORMAT 999.90 HEAD "%|Done"
--
select a.username,
a.opname,
b.sql_text,
to_char(a.start_time, 'DD-MON-YY HH24:MI') start_time,
a.elapsed_seconds how_long,
a.time_remaining secs_left,
a.sofar,
a.totalwork,
round(a.sofar / a.totalwork * 100, 2) percent
from v$session_longops a, v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sofar <> a.totalwork
and a.totalwork != 0;
四、找出占用资源最多的sql语句
select *
from (select sql_text,
buffer_gets,
disk_reads,
sorts,
cpu_time / 1000000 cpu_sec,
executions,
rows_processed
from v$sqlstats
order by cpu_time DESC)
where rownum < 11;
select *
from (select b.sql_text,
a.username,
b.buffer_gets,
b.disk_reads,
b.sorts,
b.cpu_time / 1000000 cpu_sec
from v$sqlarea b, dba_users a
where b.parsing_user_id = a.user_id
order by b.cpu_time DESC)
where rownum < 11;
五、使用awr,ash,addm,statspack去监控（详细略）。
SQL> @?/rdbms/admin/awrrpt
SQL> @?/rdbms/admin/ashrpt
SQL> @?/rdbms/admin/addmrpt
SQL> @?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spauto.sql
SQL> @?/rdbms/admin/spreport.sql
六、使用操作系统命令去检查占用资源较多的查询。
（1）$ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
65.4 1165 oracle ? ora_j001_orcl
3.0 17571 oracle ? oracleorcl (LOCAL=NO)
0.8 17357 oracle ? oracleorcl (LOCAL=NO)
0.8 15950 oracle ? oracleorcl (LOCAL=NO)
0.7 605 oracle ? oracleorcl (LOCAL=NO)
0.5 17062 oracle ? oracleorcl (LOCAL=NO)
0.5 16259 oracle ? oracleorcl (LOCAL=NO)
0.3 15315 oracle ? oracleorcl (LOCAL=NO)
0.2 29187 oracle ? oracleorcl (LOCAL=NO)
0.2 17419 oracle ? oracleorcl (LOCAL=NO)
注：
1、ps命令解释：-e显示全部进程 -o显示用户指定的信息，如-o pcpu,pid,user,tty,args
2、| ：管道命令，把第一个的命令输出作为第二个命令的输入。
3、sort命令：-n依照数值的大小排序 ；-k key[position1,position2]如：-k 1；-r倒序输出。
4、head：查看命令。
（2）查处最占cpu时间的sql语句。
select 'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal || chr(10) ||
'SQL TEXT : ' || q.sql_text
from v$session s
,v$process p
,v$sql q
where s.paddr = p.addr
and s.sql_id = q.sql_id
and p.spid = 605;
（3）$ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head
注：grep指令用于查找内容包含指定的范本样式的文件，如果发现某文件的内容符合所指定的范本样式，预设grep指令会把含有范本样式的那一列显示出来。若不指定任何文件名称，或是所给予的文件名为“-”，则grep指令会从标准输入设备读取数据。其中-i为忽略字符大小写的差别。
（4）操作系统命令：top,vmstat,iostat,mpstat,netstat, and traceroute.
七、显示执行计划。
SQL> conn / as sysdba
SQL> desc plan_table;
SQL> @?/rdbms/admin/utlxplan
SQL> @?/sqlplus/admin/plustrce
SQL> grant plustrace to star1;
SQL> set autotrace on;
类似还有：
SET AUTOTRACE ON
SET AUTOTRACE OFF
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON EXPLAIN STAT
SET AUTOTRACE ON STAT
SET AUTOTRACE TRACE
八、通过DBMS_XPLAN包生成执行计划
SQL> desc plan_table
SQL> @?/rdbms/admin/utlxplan
SQL> select * from table(dbms_xplan.display);
九、sql跟踪。（略）
十、执行计划解释。（略）
十一、获得优化指导。
1.
GRANT ADMINISTER SQL TUNING SET TO &&tune_user;
GRANT ADVISOR TO &&tune_user;
GRANT CREATE ANY SQL PROFILE TO &&tune_user;
GRANT ALTER ANY SQL PROFILE TO &&tune_user;
GRANT DROP ANY SQL PROFILE TO &&tune_user;
2.
DECLARE
tune_task_name VARCHAR2(30);
tune_sql CLOB;
BEGIN
tune_sql := 'select a.emp_name, b.dept_name from emp a, dept b';
tune_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => tune_sql,
user_name => 'STAR_APR',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'tune1',
description => 'Basic tuning example');
END;
/
3.
SQL> SELECT task_name FROM user_advisor_log WHERE task_name LIKE 'tune1';
4.Run the tuning task:
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tune1');
5. Display the SQL Tuning Advisor report. Run the following SQL statements to display the output:
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 132
SET PAGESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune1') FROM dual;
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 b.sql_text sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 order by a.cpu_time desc)
12 where rownum <= 20;
未选定行
SQL> select *
2 from (select a.sid session_id,
3 a.sql_id,
4 a.status,
5 a.cpu_time / 1000000 cpu_sec,
6 a.buffer_gets,
7 a.disk_reads,
8 substr(b.sql_text, 1, 15) sql_text
9 from v$sql_monitor a, v$sql b
10 where a.sql_id = b.sql_id
11 and a.status = 'EXECUTING'
12 order by a.disk_reads desc)
13 where rownum <= 20;
未选定行
二、显示查询语句执行时的信息。(11g)
COL SID FORMAT 99999
COL status FORMAT A15
COL start_time FORMAT A12
COL plan_line_id FORMAT 99999 HEAD "Plan ID"
COL plan_options FORMAT A16
COL mem_bytes FORMAT 99999999
COL temp_bytes FORMAT 99999999
SET LINESIZE 132 PAGESI 100 TRIMSP ON
BREAK ON sid on status on start_time NODUP SKIP 1
select a.sid,
a.status,
to_char(a.sql_exec_start, 'yymmdd hh24:mi') start_time,
a.plan_line_id,
a.plan_operation,
a.plan_options,
a.output_rows,
a.workarea_mem mem_bytes,
a.workarea_tempseg temp_bytes
from v$sql_plan_monitor a, v$sql_monitor b
where a.status NOT LIKE '%DONE%'
and a.key = b.key
order by a.sid, a.sql_exec_start, a.plan_line_id;
三、监控sql执行的开始时间，已执行时间和剩余执行时间。
COL how_long FORMAT 99,990 HEAD "Time|Run"
COL secs_left FORMAT 99,990 HEAD "Appr.|Secs Left"
COL sofar FORMAT 9,999,990 HEAD "Work|Done"
COL totalwork FORMAT 9,999,990 HEAD "Total|Work"
COL percent FORMAT 999.90 HEAD "%|Done"
--
select a.username,
a.opname,
b.sql_text,
to_char(a.start_time, 'DD-MON-YY HH24:MI') start_time,
a.elapsed_seconds how_long,
a.time_remaining secs_left,
a.sofar,
a.totalwork,
round(a.sofar / a.totalwork * 100, 2) percent
from v$session_longops a, v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sofar <> a.totalwork
and a.totalwork != 0;
四、找出占用资源最多的sql语句
select *
from (select sql_text,
buffer_gets,
disk_reads,
sorts,
cpu_time / 1000000 cpu_sec,
executions,
rows_processed
from v$sqlstats
order by cpu_time DESC)
where rownum < 11;
select *
from (select b.sql_text,
a.username,
b.buffer_gets,
b.disk_reads,
b.sorts,
b.cpu_time / 1000000 cpu_sec
from v$sqlarea b, dba_users a
where b.parsing_user_id = a.user_id
order by b.cpu_time DESC)
where rownum < 11;
五、使用awr,ash,addm,statspack去监控（详细略）。
SQL> @?/rdbms/admin/awrrpt
SQL> @?/rdbms/admin/ashrpt
SQL> @?/rdbms/admin/addmrpt
SQL> @?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spauto.sql
SQL> @?/rdbms/admin/spreport.sql
六、使用操作系统命令去检查占用资源较多的查询。
（1）$ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
65.4 1165 oracle ? ora_j001_orcl
3.0 17571 oracle ? oracleorcl (LOCAL=NO)
0.8 17357 oracle ? oracleorcl (LOCAL=NO)
0.8 15950 oracle ? oracleorcl (LOCAL=NO)
0.7 605 oracle ? oracleorcl (LOCAL=NO)
0.5 17062 oracle ? oracleorcl (LOCAL=NO)
0.5 16259 oracle ? oracleorcl (LOCAL=NO)
0.3 15315 oracle ? oracleorcl (LOCAL=NO)
0.2 29187 oracle ? oracleorcl (LOCAL=NO)
0.2 17419 oracle ? oracleorcl (LOCAL=NO)
注：
1、ps命令解释：-e显示全部进程 -o显示用户指定的信息，如-o pcpu,pid,user,tty,args
2、| ：管道命令，把第一个的命令输出作为第二个命令的输入。
3、sort命令：-n依照数值的大小排序 ；-k key[position1,position2]如：-k 1；-r倒序输出。
4、head：查看命令。
（2）查处最占cpu时间的sql语句。
select 'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal || chr(10) ||
'SQL TEXT : ' || q.sql_text
from v$session s
,v$process p
,v$sql q
where s.paddr = p.addr
and s.sql_id = q.sql_id
and p.spid = 605;
（3）$ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head
注：grep指令用于查找内容包含指定的范本样式的文件，如果发现某文件的内容符合所指定的范本样式，预设grep指令会把含有范本样式的那一列显示出来。若不指定任何文件名称，或是所给予的文件名为“-”，则grep指令会从标准输入设备读取数据。其中-i为忽略字符大小写的差别。
（4）操作系统命令：top,vmstat,iostat,mpstat,netstat, and traceroute.
七、显示执行计划。
SQL> conn / as sysdba
SQL> desc plan_table;
SQL> @?/rdbms/admin/utlxplan
SQL> @?/sqlplus/admin/plustrce
SQL> grant plustrace to star1;
SQL> set autotrace on;
类似还有：
SET AUTOTRACE ON
SET AUTOTRACE OFF
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON EXPLAIN STAT
SET AUTOTRACE ON STAT
SET AUTOTRACE TRACE
八、通过DBMS_XPLAN包生成执行计划
SQL> desc plan_table
SQL> @?/rdbms/admin/utlxplan
SQL> select * from table(dbms_xplan.display);
九、sql跟踪。（略）
十、执行计划解释。（略）
十一、获得优化指导。
1.
GRANT ADMINISTER SQL TUNING SET TO &&tune_user;
GRANT ADVISOR TO &&tune_user;
GRANT CREATE ANY SQL PROFILE TO &&tune_user;
GRANT ALTER ANY SQL PROFILE TO &&tune_user;
GRANT DROP ANY SQL PROFILE TO &&tune_user;
2.
DECLARE
tune_task_name VARCHAR2(30);
tune_sql CLOB;
BEGIN
tune_sql := 'select a.emp_name, b.dept_name from emp a, dept b';
tune_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => tune_sql,
user_name => 'STAR_APR',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'tune1',
description => 'Basic tuning example');
END;
/
3.
SQL> SELECT task_name FROM user_advisor_log WHERE task_name LIKE 'tune1';
4.Run the tuning task:
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tune1');
5. Display the SQL Tuning Advisor report. Run the following SQL statements to display the output:
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 132
SET PAGESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune1') FROM dual;
- sql监控与调优(sql monitoring and tuning)
- SQL调优与SQL优化(SQL Tuning or SQL Optimization)
- 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)
- 《高性能SQL调优精要与案例解析》一书谈SQL调优（SQL TUNING或SQL优化）学习
- SQL Tuning
- SQL Tuning
- SQL Tuning
- Linux Performance Monitoring and Tuning Introduction
- WAS“Monitoring and Tuning”的问题
- STA(SQL Tuning Advisor) SQL调优顾问简介
- SQL Tuning Advisor(SQL调优顾问，STA)
- 初次使用SQL调优建议工具--SQL Tuning Advisor
- 【SQL Performance】历史SQL监控(Historical SQL Monitoring ) 功能（12c）
- tuning 11--SQL Statement Tuning
- Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_
- ORACLE9i_性能调优基础八(SQL tuning)
- Oracle调整顾问(SQL Tuning Advisor 与 SQL Access Advisor )
- 16 Tools for Server Performance Tuning and Monitoring
- drop user cascade出现ORA-04043问题的解决
- 获取指定的目录下面的指定的后缀的文件
- 数据库缩小表空间
- 导出表的部分数据到dmp文件中
- 《敏捷开发：高效程序员的45个习惯》 学习摘要 1
- sql监控与调优(sql monitoring and tuning)
- Struts2从后台传递数据到前台的主要方法和流程
- 数据库管理常用sql语句
- “选择算法”
- 《敏捷开发：高效程序员的45个习惯》 学习摘要 2
- struts1和struts2的标签引入
- Effective C++学习笔记 第一弹 1-4
- struts2配置文件详解
- Effective C++学习笔记 第二弹 5-10
