一、监控正在执行的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
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
五、使用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.
（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;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7204674/viewspace-622214/，如需转载，请注明出处，否则将追究法律责任。
下一篇：
[转帖]日志挖掘一例
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/7204674/viewspace-622214/
497
到【灌水乐园】发言
